Calculate Regional Revenue and Identify Top Customers

Q: How do I practice SQL interview questions?

PracHub provides an interactive SQL console where you can write and test queries against real database schemas. Get instant feedback and compare your solution with the expected output.

Q: What difficulty level is this coding question?

This is a Medium difficulty Data Manipulation (SQL/Python) question, commonly asked during Technical Screen rounds at Amazon.

Q: What role is this question designed for?

This question is commonly asked for Data Scientist candidates at Amazon during technical interviews.

Question

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.

PracHub · Accepted 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.

Quick Overview

Calculate Regional Revenue and Identify Top Customers