Calculate Regional Revenue and Identify Top Customers

Customers

customer_id	name	region
1	Alice	East
2	Bob	West
3	Carol	East

Sales

sale_id	customer_id	product_id	order_date	amount
101	1	P1	2023-01-10	120.00
102	2	P2	2023-01-11	250.00
103	1	P3	2023-01-12	80.00

Scenario

An e-commerce analyst needs to compute regional revenue and then list the top-3 highest-spending customers in every region for a dashboard.

Question

Write an SQL query that joins the Customers and Sales tables, groups by region, and returns total revenue per region. 2. Using window functions, extend the query to return the three customers with the highest total spending in their respective regions (use RANK() OVER(PARTITION BY … ORDER BY … DESC)).

Hints

Think INNER vs. LEFT JOIN, SUM(amount), GROUP BY region, and RANK() window function with PARTITION BY region.

Regional Revenue and Top Customers per Region

You are given two tables, Customers and Sales. First, write an SQL query that joins Customers and Sales to compute total revenue per region. Then extend your solution (using window functions) to return, for each region, the three customers with the highest total spending in that region. The final result should include: region, total regional revenue, customer_id, customer_name, that customer's total revenue, and their rank within the region by spending. Customers with no sales should still appear with 0 spending if they fall within the top 3 of their region (for example, when there are fewer than three customers in a region). Use RANK() OVER (PARTITION BY region ORDER BY total_spent DESC).

Table Customers

column	type	constraints
customer_id	INTEGER	PRIMARY KEY, NOT NULL
name	VARCHAR(100)	NOT NULL
region	VARCHAR(50)	NOT NULL

Sample data:

customer_id	name	region
1	Alice	East
2	Bob	West
3	Carol	East

Table Sales

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

Sample data:

amount	customer_id	order_date	product_id	sale_id
120	1	2023-01-10	P1	101
250	2	2023-01-11	P2	102
80	1	2023-01-12	P3	103

Expected output

For each region, show total regional revenue and up to the top three customers in that region ranked by their total spending. Customers with no sales should appear with 0 spending if they rank within the top three because of limited customer count in the region.

customer_id	customer_name	customer_rank	customer_total_revenue	region	region_total_revenue
1	Alice	1	200	East	200
3	Carol	2	0	East	200
2	Bob	1	250	West	250

Hints

1. Decide between INNER JOIN and LEFT JOIN depending on whether to include customers with no sales.
2. Use SUM(amount) with GROUP BY region to compute regional revenue.
3. Use a window function like RANK() OVER (PARTITION BY region ORDER BY total_spent DESC) to rank customers by spending within each region.