Latest Real Interview Questions & Interactive Labs

# Data Analysis @ undefined

Creating and Analyzing Sales Data Table in SQL Data Analyst @ Microsoft Difficulty Medium

Create a products table and a sales table with the following schemas:

products table schema:

product_id (INT)
product_name (VARCHAR)
category (VARCHAR)

sales table schema:

sale_id (INT)
product_id (INT)
sale_date (DATE)
quantity (INT)
amount (DECIMAL)

Write SQL statements to:

Create the products and sales tables.
Write a query to get the total sales amount for each product, grouped by category and sorted by total sales in descending order.
Write a query to rank products by their total sales amount within each category.

Solution:

Please sign-in to view the solution

Reporting Highest Revenue Products by Product Group Data Analyst @ Microsoft Difficulty Medium

Assume we have the following data in sales_fact and product_dimension tables:

sales_fact table:

| sale_id | product_id | sale_date  | quantity | amount |
|---------|------------|------------|----------|--------|
| 1       | 101        | 2023-10-01 | 2        | 150.00 |
| 2       | 102        | 2023-10-02 | 1        | 200.00 |
| 3       | 103        | 2023-10-03 | 3        | 300.00 |
| 4       | 101        | 2023-10-04 | 1        | 75.00  |
| 5       | 102        | 2023-10-05 | 2        | 400.00 |

product_dimension Table:

| product_id | product_name | product_group |
|------------|--------------|---------------|
| 101        | Product A    | Group 1       |
| 102        | Product B    | Group 1       |
| 103        | Product C    | Group 2       |

Expected Output: The output should list the product with the highest revenue within each product group:

| product_group | product_name | total_revenue |
|---------------|--------------|---------------|
| Group 1       | Product B    | 600.00        |
| Group 2       | Product C    | 300.00        |

Solution:

Please sign-in to view the solution

Analyzing Employee Salaries with SQL Joins and Group By Data Analyst @ Microsoft Difficulty Medium

Write a SQL query to find the average salary for each department. The average should be computed using the most recent salary for each employee.

Input Data: employees Table:

| employee_id | department_id | employee_name |
|-------------|---------------|---------------|
| 1           | 101           | John Doe      |
| 2           | 102           | Jane Smith    |
| 3           | 101           | Alice Brown   |
| 4           | 103           | Bob White     |

salaries Table:

| employee_id | salary | effective_date |
|-------------|--------|----------------|
| 1           | 50000  | 2023-01-01     |
| 1           | 55000  | 2023-07-01     |
| 2           | 60000  | 2023-03-01     |
| 3           | 45000  | 2023-02-01     |
| 3           | 48000  | 2023-08-01     |
| 4           | 70000  | 2023-05-01     |
| 4           | 75000  | 2023-10-01     |

Expected Output

| department_id | average_salary |
|---------------|----------------|
| 101           | 51500          |
| 102           | 60000          |
| 103           | 75000          |

Solution:

Please sign-in to view the solution

Using Self Join to Find Managers in SQL Data Analyst @ Microsoft Difficulty Medium

Write a SQL query to find the names of all employees along with the names of their respective managers.

Input: employees table:

| employee_id | employee_name | manager_id |
|-------------|---------------|------------|
| 1           | John Doe      | NULL       |
| 2           | Jane Smith    | 1          |
| 3           | Alice Brown   | 1          |
| 4           | Bob White     | 2          |

Expected Output

| employee_name | manager_name |
|---------------|--------------|
| John Doe      | NULL         |
| Jane Smith    | John Doe     |
| Alice Brown   | John Doe     |
| Bob White     | Jane Smith   |

Solution:

Please sign-in to view the solution

Creating Sales Charts and Highlighting Top Sales in Excel Data Analyst @ Microsoft Difficulty Easy

Perform the following tasks:

Create a bar chart to visualize the total sales amount for each product.
Use VLOOKUP to find the sales amount of a specific product by its Product ID.
Apply conditional formatting to highlight the top 10% of sales amounts in the Sales Amount column.

Input: sales_data in Excel has the following data:

| Product ID | Product Name | Sales Amount | Sale Date  |
|------------|--------------|--------------|------------|
| 1          | Product A    | 500          | 2023-10-01 |
| 2          | Product B    | 300          | 2023-10-02 |
| 3          | Product C    | 700          | 2023-10-03 |
| 4          | Product D    | 600          | 2023-10-04 |
| 5          | Product E    | 200          | 2023-10-05 |

Solution:

Please sign-in to view the solution

Analyzing Employee Performance Using SQL Data Analyst @ Microsoft Difficulty Medium

Calculate the average performance score for each department.
Identify the top performer in each department based on the latest performance reviews using window functions.
Rank employees within their departments based on their average performance scores.

Input: Assume the tables have the following data:

employees Table:

| employee_id | employee_name | department_id |
|-------------|---------------|---------------|
| 1           | Alice         | 10            |
| 2           | Bob           | 10            |
| 3           | Carol         | 20            |
| 4           | Dave          | 20            |
| 5           | Eve           | 30            |

performance Table:

| employee_id | review_date  | performance_score |
|-------------|--------------|-------------------|
| 1           | 2023-10-01   | 85                |
| 2           | 2023-10-01   | 90                |
| 1           | 2023-11-01   | 88                |
| 2           | 2023-11-01   | 92                |
| 3           | 2023-10-01   | 78                |
| 3           | 2023-11-01   | 80                |
| 4           | 2023-10-01   | 85                |
| 4           | 2023-11-01   | 87                |
| 5           | 2023-10-01   | 95                |

Solution:

Please sign-in to view the solution

Understanding Linear Regression and P-Value Testing Data Analyst @ Microsoft Difficulty Medium

Explain how you would use linear regression to predict a continuous variable and how you would interpret the p-values of the regression coefficients.

Solution:

Please sign-in to view the solution

Creating a Joined Table from Two SQL Tables Data Analyst @ Microsoft Difficulty Medium

If you have two SQL database tables that are not joined together, how would you create another table to join them.

Solution:

Please sign-in to view the solution

SQL and Tableau Report Generation Data Analyst @ Microsoft Difficulty Medium

Given two tables in an SQL database: Orders and Customers. The Orders table has columns OrderID, CustomerID, OrderDate, and Amount. The Customers table has columns CustomerID and CustomerName. Write an SQL query to fetch the total Amount spent by each CustomerName in the current year. Then describe how you would visualize this data using Tableau.

Input:

Orders table:

| OrderID | CustomerID | OrderDate   | Amount |
|---------|------------|-------------|--------|
| 1       | 101        | 2023-01-10  | 100    |
| 2       | 102        | 2023-03-15  | 200    |
| 3       | 101        | 2023-07-22  | 150    |
| 4       | 103        | 2022-12-01  | 300    |

Customers table:

| CustomerID | CustomerName |
|------------|--------------|
| 101        | John Doe     |
| 102        | Jane Smith   |
| 103        | Alice Brown  |

Solution:

Please sign-in to view the solution

Analyzing Customer Purchase Trends Using SQL Data Analyst @ Microsoft Difficulty Medium

You have a table called customer_purchases:

| customer_id | purchase_date | amount |
|-------------|---------------|--------|
| 1           | 2022-10-10    | 100.50 |
| 1           | 2022-11-15    | 150.75 |
| 2           | 2022-10-25    | 200.00 |
| 2           | 2022-12-14    | 250.00 |
| 1           | 2023-01-10    | 300.00 |

Write a SQL query to find out the average monthly expenditure of each customer over the past year. Use window functions to achieve this.

Solution:

Please sign-in to view the solution

Visualizing Sales Data Using SQL and Tableau Data Analyst @ Microsoft Difficulty Medium

Write a SQL query to calculate the total revenue generated per product per month over the last year. Explain how you would visualize this data in Tableau, mentioning the types of charts you would use and why.

Input Data: sales table:

| sale_id | product_id | sale_date  | quantity | price  |
|---------|------------|------------|----------|--------|
| 1       | 101        | 2023-01-10 | 10       | 15.00  |
| 2       | 102        | 2023-02-15 | 5        | 20.00  |
| 3       | 101        | 2023-01-20 | 7        | 15.00  |
| 4       | 103        | 2022-12-10 | 3        | 30.00  |
| 5       | 101        | 2022-11-10 | 12       | 15.00  |

Output: The query should return the total revenue generated per product per month over the last year.

Solution:

Please sign-in to view the solution

Understanding Primary Keys and Indexes in DBMS Data Analyst @ Microsoft Difficulty Easy

What is a primary key in a database, and why is it important?
Explain the difference between a clustered index and a non-clustered index. Provide a use case for each.

Solution:

Please sign-in to view the solution

Finding Customers with 3 Consecutive Logins Using SQL Data Analyst @ Microsoft Difficulty Medium

Write a SQL query to find customers who logged in for at least 3 consecutive days.

Input: logins table:

| customer_id | login_date |
|-------------|------------|
| 1           | 2023-10-01 |
| 1           | 2023-10-02 |
| 1           | 2023-10-03 |
| 2           | 2023-10-01 |
| 2           | 2023-10-03 |
| 2           | 2023-10-04 |
| 3           | 2023-10-05 |
| 3           | 2023-10-06 |
| 3           | 2023-10-07 |
| 4           | 2023-10-08 |
| 4           | 2023-10-09 |
| 4           | 2023-10-11 |

Expected Output

| customer_id |
|-------------|
| 1           |
| 3           |

Solution:

Please sign-in to view the solution

Fetching Data Category-Wise by Month and Hour Data Analyst @ Microsoft Difficulty Medium

You have a sales table with the following schema:

sale_id (INT)
category (VARCHAR)
sale_date (DATETIME)
amount (DECIMAL)

Write a SQL query to fetch the total sales amount category-wise, grouped by month and by hour of the day.

Solution:

Please sign-in to view the solution