Coding questions · Amazon
Create SQL Queries for Sales and Customer Analysis
This question evaluates proficiency in SQL-based data manipulation, covering aggregation and dispersion metrics, null handling, relational joins, and window functions for ranking customers.
SALES
+----------+----------+------------+------------+----------+---------+
| sale_id | store_id | product_id | sale_date | quantity | revenue |
+----------+----------+------------+------------+----------+---------+
| 1 | 101 | 501 | 2024-06-01 | 3 | 75.00 |
| 2 | 102 | 502 | 2024-06-02 | NULL | 40.00 |
| 3 | 101 | 501 | 2024-06-03 | 2 | 50.00 |
+----------+----------+------------+------------+----------+---------+
ORDERS
+----------+-------------+------------+--------------+
| order_id | customer_id | order_date | total_amount |
+----------+-------------+------------+--------------+
| 1001 | 2001 | 2024-06-01 | 120.00 |
| 1002 | 2002 | 2024-06-02 | 75.00 |
| 1003 | 2001 | 2024-06-03 | 50.00 |
+----------+-------------+------------+--------------+
ORDER_ITEMS
+----------+------------+----------+---------+
| order_id | product_id | quantity | revenue |
+----------+------------+----------+---------+
| 1001 | 501 | 2 | 50.00 |
| 1001 | 502 | 3 | 70.00 |
| 1002 | 503 | 1 | 75.00 |
+----------+------------+----------+---------+
CUSTOMERS
+-------------+--------+
| customer_id | region |
+-------------+--------+
| 2001 | West |
| 2002 | East |
+-------------+--------+
Scenario
Retail analytics team needs ad-hoc reports on sales performance and customer behavior.
Question
a) Given a single table SALES, write SQL to: • compute the average and standard deviation of revenue by product_id; • create a column that maps NULL quantity to 0 using CASE WHEN. b) Given three tables ORDERS, ORDER_ITEMS, CUSTOMERS, write SQL to: • join them to get total revenue per customer; • rank and dense-rank customers by total revenue within each region.
Hints
Focus on GROUP BY, JOIN conditions, COALESCE/CASE, aggregate functions, and window functions (RANK/DENSE_RANK).
Revenue stats by product
From the SALES table, compute the average and population standard deviation of revenue for each product_id.
Table SALES
| column | type | constraints |
|---|---|---|
| sale_id | INTEGER | PRIMARY KEY |
| store_id | INTEGER | NOT NULL |
| product_id | INTEGER | NOT NULL |
| sale_date | DATE | NOT NULL |
| quantity | INTEGER | |
| revenue | DECIMAL(10,2) | NOT NULL |
Sample data:
| product_id | quantity | revenue | sale_date | sale_id | store_id |
|---|---|---|---|---|---|
| 501 | 3 | 75 | 2024-06-01 | 1 | 101 |
| 502 | NULL | 40 | 2024-06-02 | 2 | 102 |
| 501 | 2 | 50 | 2024-06-03 | 3 | 101 |
Expected output
Average and population standard deviation of revenue for each product_id.
| avg_revenue | product_id | stddev_revenue |
|---|---|---|
| 62.5 | 501 | 12.5 |
| 40 | 502 | 0 |
Hints
- Use STDDEV_POP for population standard deviation.
- Group the aggregation by product_id.
Map NULL quantity to 0
From the SALES table, return sale_id, quantity, and a derived column that maps NULL quantity to 0 using CASE WHEN.
Table SALES
| column | type | constraints |
|---|---|---|
| sale_id | INTEGER | PRIMARY KEY |
| store_id | INTEGER | NOT NULL |
| product_id | INTEGER | NOT NULL |
| sale_date | DATE | NOT NULL |
| quantity | INTEGER | |
| revenue | DECIMAL(10,2) | NOT NULL |
Sample data:
| product_id | quantity | revenue | sale_date | sale_id | store_id |
|---|---|---|---|---|---|
| 501 | 3 | 75 | 2024-06-01 | 1 | 101 |
| 502 | NULL | 40 | 2024-06-02 | 2 | 102 |
| 501 | 2 | 50 | 2024-06-03 | 3 | 101 |
Expected output
Each sale with original quantity and quantity mapped to 0 when NULL.
| quantity | quantity_mapped | sale_id |
|---|---|---|
| 3 | 3 | 1 |
| NULL | 0 | 2 |
| 2 | 2 | 3 |
Hints
- Use CASE WHEN ... THEN ... ELSE ... END to replace NULL.
- Alternatively, COALESCE(quantity, 0) also works.
Total revenue per customer and regional ranking
Given ORDERS, ORDER_ITEMS, and CUSTOMERS, write a query that: (1) joins the tables to compute total revenue per customer as the sum of ORDER_ITEMS.revenue across all their orders, and (2) ranks and dense-ranks customers by this total revenue within each region.
Table ORDERS
| column | type | constraints |
|---|---|---|
| order_id | INTEGER | PRIMARY KEY |
| customer_id | INTEGER | NOT NULL |
| order_date | DATE | NOT NULL |
| total_amount | DECIMAL(10,2) | NOT NULL |
Sample data:
| customer_id | order_date | order_id | total_amount |
|---|---|---|---|
| 2001 | 2024-06-01 | 1001 | 120 |
| 2002 | 2024-06-02 | 1002 | 75 |
| 2001 | 2024-06-03 | 1003 | 50 |
Table ORDER_ITEMS
| column | type | constraints |
|---|---|---|
| order_id | INTEGER | NOT NULL |
| product_id | INTEGER | NOT NULL |
| quantity | INTEGER | NOT NULL |
| revenue | DECIMAL(10,2) | NOT NULL |
Sample data:
| order_id | product_id | quantity | revenue |
|---|---|---|---|
| 1001 | 501 | 2 | 50 |
| 1001 | 502 | 3 | 70 |
| 1002 | 503 | 1 | 75 |
Table CUSTOMERS
| column | type | constraints |
|---|---|---|
| customer_id | INTEGER | PRIMARY KEY |
| region | VARCHAR(20) | NOT NULL |
Sample data:
| customer_id | region |
|---|---|
| 2001 | West |
| 2002 | East |
Expected output
Customer total revenue (sum of ORDER_ITEMS.revenue) ranked within each region using RANK and DENSE_RANK.
| customer_id | region | rev_dense_rank | rev_rank | total_revenue |
|---|---|---|---|---|
| 2002 | East | 1 | 1 | 75 |
| 2001 | West | 1 | 1 | 120 |
Hints
- First join CUSTOMERS to ORDERS, then LEFT JOIN ORDER_ITEMS to bring in line-item revenue.
- Aggregate ORDER_ITEMS.revenue by customer_id and region to get total_revenue.
- Use a CTE or subquery for customer totals, then apply RANK() and DENSE_RANK() partitioned by region and ordered by total_revenue DESC.