Derive Key Business Metrics Using SQL or Python
Company: Amazon
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
Orders
+----------+-------------+------------+---------+------------------+
| order_id | customer_id | order_date | amount | product_category |
+----------+-------------+------------+---------+------------------+
| 1 | 101 | 2023-01-05 | 120.00 | Electronics |
| 2 | 102 | 2023-01-06 | 55.00 | Home |
| 3 | 101 | 2023-01-15 | 220.00 | Electronics |
| 4 | 103 | 2023-02-01 | 80.00 | Books |
| 5 | 102 | 2023-02-03 | 35.00 | Home |
+----------+-------------+------------+---------+------------------+
Customers
+-------------+---------------+
| customer_id | customer_name |
+-------------+---------------+
| 101 | Alice |
| 102 | Bob |
| 103 | Carol |
| 104 | Dan |
| 105 | Eve |
+-------------+---------------+
##### Scenario
You are given two tables from an e-commerce platform and need to derive business metrics for the analytics team.
##### Question
For each calendar month, return the three product_category values with the highest total sales amount. Break ties by larger total sales, then alphabetically. 2. For every customer, produce a result showing order_id, order_date, amount, and that customer’s running cumulative_amount ordered by order_date. 3. Classify each order as 'High' (amount >
200), 'Medium' (amount BETWEEN 100 AND
200) or 'Low' (else). Return the count of orders in each class.
##### Hints
Expect to JOIN, aggregate with SUM, use CASE WHEN, and apply window functions such as ROW_NUMBER or RANK; avoid unnecessary sub-queries where possible.
Overview: This question evaluates data manipulation and analytical querying skills, including joins, aggregations, conditional classification, and running-window computations in the Data Manipulation (SQL/Python) domain.
You are given two e-commerce tables: Orders and Customers.
Write one PostgreSQL query that returns a single labeled result table for these three analyses:
1. For each calendar month, return up to the three product_category values with the highest total sales amount. Break ties by total_sales descending, then product_category ascending.
2. For every customer order, return the customer's running cumulative order amount ordered by order_date and then order_id.
3. Classify every order amount as High when amount > 200, Medium when amount is between 100 and 200 inclusive, and Low otherwise; return the count of orders in each class.
Return all rows in one output with a result_set column. Columns that do not apply to a given result_set should be NULL.
Tables
Customers(customer_id INTEGER, customer_name VARCHAR(100))
Orders(order_id INTEGER, customer_id INTEGER, order_date DATE, amount DECIMAL(10,2), product_category VARCHAR(50))
Hints
- Compute each requested result in its own CTE.
- Use ROW_NUMBER for top categories and SUM(...) OVER (...) for running totals.