profile pic # Data Analysis @ Google
Upvote 0 Downvote
Understanding Machine Learning Data Analyst @ Google Difficulty Medium

Explain what machine learning is and how it helps us to train data. Include examples of different types of machine learning and their typical applications.

Solution:

Please sign-in to view the solution

Upvote 0 Downvote
SQL Queries and Window Functions Data Analyst @ Google Difficulty Medium

Given the following Sales table in a SQL database, write queries to: (1) Calculate the cumulative sales amount for each salesperson, ordered by SaleDate; and (2) Find the average sales amount over a rolling window of the last 3 sales for each salesperson.

Sales Table:


| SaleID | SalespersonID | SaleAmount | SaleDate  |
|--------|---------------|------------|-----------|
| 1      | 101           | 100        | 2023-01-01|
| 2      | 102           | 200        | 2023-01-02|
| 3      | 101           | 150        | 2023-01-03|
| 4      | 102           | 250        | 2023-01-04|
| 5      | 101           | 200        | 2023-01-05|
| 6      | 101           | 300        | 2023-01-06|
Solution:

Please sign-in to view the solution

Upvote 0 Downvote
When to Use Mean vs. Median Data Analyst @ Google Difficulty Easy

When is it appropriate to use mean versus median in data analysis? Provide specific examples where one measure may be preferred over the other.

Solution:

Please sign-in to view the solution

Upvote 0 Downvote
SQL Coding Question with JOIN and GROUP BY Data Analyst @ Google Difficulty Easy

Given the following Orders and Customers tables, write a SQL query to calculate the total amount spent by each customer. The result should include the CustomerID, CustomerName, and TotalAmountSpent.

Orders Table:

| OrderID | CustomerID | OrderAmount | OrderDate  |
|---------|------------|-------------|------------|
| 1       | 101        | 250         | 2023-01-01 |
| 2       | 102        | 150         | 2023-01-03 |
| 3       | 101        | 100         | 2023-01-04 |
| 4       | 103        | 200         | 2023-01-06 |
| 5       | 102        | 50          | 2023-01-07 |

Customers Table:

| CustomerID | CustomerName |
|------------|--------------|
| 101        | John Doe     |
| 102        | Jane Smith   |
| 103        | Bob Johnson  |
Solution:

Please sign-in to view the solution

Upvote 0 Downvote
SQL Query Optimization and Comparison Data Analyst @ Google Difficulty Medium

Given the following two SQL queries, recommend improvements to each query and determine which query would run faster.

Sample Queries:

  1. Query A:

    SELECT
        Orders.OrderID,
        Customers.CustomerName,
        Orders.OrderAmount,
        Orders.OrderDate
    FROM
        Orders
        INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID
    WHERE
        Orders.OrderDate >= '2023-01-01';
    
  2. Query B:

    SELECT
        o.OrderID,
        c.CustomerName,
        o.OrderAmount,
        o.OrderDate
    FROM
        Orders o
        INNER JOIN Customers c ON o.CustomerID = c.CustomerID
    WHERE
        o.OrderID IN (SELECT OrderID FROM Orders WHERE OrderDate >= '2023-01-01');
    
Solution:

Please sign-in to view the solution

Upvote 0 Downvote
SQL, Data Forecasting, Past Experiences Business Analyst @ Google Difficulty Medium

You mentioned your proficiency with SQL and data forecasting. Can you describe a past experience where you used SQL to analyze data and generate a forecast? What challenges did you face, and how did you address them?

Solution:

Please sign-in to view the solution

Upvote 0 Downvote
SQL Joins Knowledge Data Analyst @ Google Difficulty Easy

What type of SQL joins do you know?

Solution:

Please sign-in to view the solution

Upvote 0 Downvote
Types of Database Schemas Data Analyst @ Google Difficulty Medium

In the context of database systems, explain the different kinds of schemas that could be used. Provide examples of each schema type to illustrate their use.

Solution:

Please sign-in to view the solution

Upvote 0 Downvote
Calculating RMSE in SQL Data Analyst @ Google Difficulty Medium

How do you write a query to calculate the Root Mean Square Error (RMSE) in SQL? Assume you have two columns, Actual and Predicted, in a table called Predictions.

Solution:

Please sign-in to view the solution

Upvote 0 Downvote
Writing SQL Queries Given the Schema Data Analyst @ Google Difficulty Medium

Given the following database schema, write SQL queries to answer the specific questions.

Schema:

**Employees Table**:
| EmployeeID | EmployeeName | DepartmentID |
|------------|--------------|--------------|
| 1          | John Doe     | 10           |
| 2          | Jane Smith   | 20           |
| 3          | Bob Johnson  | 10           |
| 4          | Alice Brown  | 30           |

**Departments Table**:
| DepartmentID | DepartmentName |
|--------------|----------------|
| 10           | Sales          |
| 20           | Marketing      |
| 30           | Support        |

**Sales Table**:
| SaleID | EmployeeID | SaleAmount | SaleDate  |
|--------|------------|------------|-----------|
| 1      | 1          | 500        | 2023-01-01|
| 2      | 2          | 300        | 2023-01-02|
| 3      | 1          | 700        | 2023-01-04|
| 4      | 3          | 400        | 2023-01-06|
| 5      | 4          | 600        | 2023-01-07|

Specific SQL Queries:

  1. Query 1: Find the total sales amount for each department.
  2. Query 2: Identify the top-performing employee in terms of sales within each department.
  3. Query 3: List all employees along with their department names and total sales.
Solution:

Please sign-in to view the solution

Upvote 0 Downvote
Understanding OLAP and Its Differences from Other Database Types Data Analyst @ Google Difficulty Medium

What is OLAP and what are its differences compared to other database types?

Solution:

Please sign-in to view the solution

Upvote 0 Downvote
Estimating the Number of Websites on the Internet Data Analyst @ Google Difficulty Medium

How many websites are on the internet? How would you find out the number?

Solution:

Please sign-in to view the solution

Upvote 0 Downvote
Data Normalization in SQL Data Analyst @ Google Difficulty Medium

You are given a database containing customer orders. The database currently has redundant and repetitive data, leading to inconsistency and excess storage usage. Explain how you would apply normalization techniques to this database. What specific steps would you take to achieve Third Normal Form (3NF)? Provide example tables before and after normalization.

Solution:

Please sign-in to view the solution

Upvote 0 Downvote
SQL Query with JOINs and Analytical Functions Data Analyst @ Google Difficulty Medium

Write a SQL query that combines the use of JOINs and analytical functions. Assume we have two tables: Sales and Employees. The query should calculate the total sales for each employee, and also rank the employees based on their total sales amount within each department. The result should include EmployeeID, EmployeeName, Department, TotalSales, and SalesRank.

Tables:

**Sales Table**:
| SaleID | EmployeeID | SaleAmount | SaleDate  |
|--------|------------|------------|-----------|
| 1      | 101        | 500        | 2023-01-01|
| 2      | 102        | 300        | 2023-01-02|
| 3      | 101        | 700        | 2023-01-04|
| 4      | 103        | 400        | 2023-01-06|
| 5      | 104        | 600        | 2023-01-07|

**Employees Table**:
| EmployeeID | EmployeeName | Department |
|------------|--------------|------------|
| 101        | John Doe     | Sales      |
| 102        | Jane Smith   | Sales      |
| 103        | Bob Johnson  | Support    |
| 104        | Alice Brown  | Sales      |
Solution:

Please sign-in to view the solution

Upvote 0 Downvote
Understanding Big Data and Hadoop Data Analyst @ Google Difficulty Medium

What is Big Data and what is Hadoop used for?

Solution:

Please sign-in to view the solution

Upvote 0 Downvote
Building a Patent System Data Analyst @ Google Difficulty Hard

How will you build a patent system?

Solution:

Please sign-in to view the solution