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.
Quick Answer: This question evaluates proficiency with SQL data manipulation concepts such as joins, aggregation (SUM/GROUP BY), and window functions for ranking customers by spending.