Calculate Regional Revenue and Identify Top Customers
Company: Amazon
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
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.
Overview: This question evaluates proficiency with SQL data manipulation concepts such as joins, aggregation (SUM/GROUP BY), and window functions for ranking customers by spending.
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).
Tables
Customers(customer_id INTEGER, name VARCHAR(100), region VARCHAR(50))
Sales(sale_id INTEGER, customer_id INTEGER, product_id VARCHAR(50), order_date DATE, amount DECIMAL(10,2))
Hints
- Decide between INNER JOIN and LEFT JOIN depending on whether to include customers with no sales.
- Use SUM(amount) with GROUP BY region to compute regional revenue.
Community answers
Answer by anna.k.liljeberg
Even this simple query does not work, it claims that the table named "Sales" doesn't exist.
SELECT *FROM Sales s
LEFT JOIN Customers c
ON s.customer_id = c.customer_id;