Derive Key Business Metrics Using SQL or Python

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.

Monthly top categories, customer running totals, and order classification

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.

Table Customers

column	type	constraints
customer_id	INTEGER	PRIMARY KEY, NOT NULL
customer_name	VARCHAR(100)	NOT NULL

Sample data:

customer_id	customer_name
101	Alice
102	Bob
103	Carol
104	Dan
105	Eve

Table Orders

column	type	constraints
order_id	INTEGER	PRIMARY KEY, NOT NULL
customer_id	INTEGER	NOT NULL, FOREIGN KEY REFERENCES Customers(customer_id)
order_date	DATE	NOT NULL
amount	DECIMAL(10,2)	NOT NULL
product_category	VARCHAR(50)	NOT NULL

Sample data:

amount	customer_id	order_date	order_id	product_category
120	101	2023-01-05	1	Electronics
55	102	2023-01-06	2	Home
220	101	2023-01-15	3	Electronics
80	103	2023-02-01	4	Books
35	102	2023-02-03	5	Home

Expected output

Sample output for the single labeled result table.

amount	amount_class	cumulative_amount	customer_id	month	order_count	order_date	order_id	product_category	result_set	total_sales	year
NULL	NULL	NULL	NULL	1	NULL	NULL	NULL	Electronics	top_categories_by_month	340.00	2023
NULL	NULL	NULL	NULL	1	NULL	NULL	NULL	Home	top_categories_by_month	55.00	2023
NULL	NULL	NULL	NULL	2	NULL	NULL	NULL	Books	top_categories_by_month	80.00	2023
NULL	NULL	NULL	NULL	2	NULL	NULL	NULL	Home	top_categories_by_month	35.00	2023
120.00	NULL	120.00	101	NULL	NULL	2023-01-05	1	NULL	customer_running_totals	NULL	NULL
220.00	NULL	340.00	101	NULL	NULL	2023-01-15	3	NULL	customer_running_totals	NULL	NULL
55.00	NULL	55.00	102	NULL	NULL	2023-01-06	2	NULL	customer_running_totals	NULL	NULL
35.00	NULL	90.00	102	NULL	NULL	2023-02-03	5	NULL	customer_running_totals	NULL	NULL
80.00	NULL	80.00	103	NULL	NULL	2023-02-01	4	NULL	customer_running_totals	NULL	NULL
NULL	High	NULL	NULL	NULL	1	NULL	NULL	NULL	order_class_counts	NULL	NULL
NULL	Medium	NULL	NULL	NULL	1	NULL	NULL	NULL	order_class_counts	NULL	NULL
NULL	Low	NULL	NULL	NULL	3	NULL	NULL	NULL	order_class_counts	NULL	NULL

Hints

1. Compute each requested result in its own CTE.
2. Use ROW_NUMBER for top categories and SUM(...) OVER (...) for running totals.
3. UNION ALL the labeled outputs into one flat result table.