Revenue by country
mediumRevenue by country
Stripe SQL Interview Question
Stripe's international team wants to show an online store which countries its revenue comes from, so the store can decide where to expand next.
Find the total revenue from completed orders for each customer country, as revenue. Sort the rows by revenue, highest first.
Asked of
- Data Analyst
- Business Analyst
- BI Analyst
- Product Analyst
- Analytics Engineer
ordersTable35 rows
| Column Name | Type |
|---|---|
| order_id | BIGINT |
| customer_id | BIGINT |
| order_date | DATE |
| status | VARCHAR |
| amount | DOUBLE |
customersTable10 rows
| Column Name | Type |
|---|---|
| customer_id | BIGINT |
| name | VARCHAR |
| country | VARCHAR |
| segment | VARCHAR |
| signup_date | DATE |
ordersExample Input
| order_id | customer_id | order_date | status | amount |
|---|---|---|---|---|
| 1001 | 1 | 2024-01-05 | completed | 120.5 |
| 1002 | 2 | 2024-01-07 | completed | 2400 |
| 1003 | 1 | 2024-01-11 | completed | 89.99 |
| 1004 | 3 | 2024-01-15 | canceled | 45 |
| 1005 | 4 | 2024-01-18 | completed | 610.75 |
| 1006 | 2 | 2024-01-22 | completed | 1850.25 |
| 1007 | 5 | 2024-01-29 | completed | 3200 |
| 1008 | 1 | 2024-02-02 | refunded | 75 |
| 1009 | 6 | 2024-02-06 | completed | 55.4 |
| 1010 | 3 | 2024-02-09 | completed | 132.6 |
customersExample Input
| customer_id | name | country | segment | signup_date |
|---|---|---|---|---|
| 1 | Priya Sharma | India | consumer | 2023-01-14 |
| 2 | Marcus Webb | United States | enterprise | 2023-02-03 |
| 3 | Ana Sousa | Brazil | consumer | 2023-02-27 |
| 4 | Liam O'Connor | Ireland | small_business | 2023-03-15 |
| 5 | Yuki Tanaka | Japan | enterprise | 2023-04-02 |
| 6 | Fatima Al-Rashid | United Arab Emirates | consumer | 2023-05-21 |
| 7 | Daniel Okafor | Nigeria | small_business | 2023-06-08 |
Example Output
| country | revenue |
|---|---|
| United States | 4250.25 |
| Japan | 3200 |
| Ireland | 610.75 |
| India | 210.49 |
| Brazil | 132.6 |
| United Arab Emirates | 55.4 |
Explanation
Marcus Webb, the only customer in the United States, completed orders worth 2,400 and 1,850.25, so the United States leads with 4,250.25. Priya Sharma's refunded order is not counted, so India's revenue is 120.50 plus 89.99, which is 210.49.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Stripe
Difficulty
medium
Topic
joins
Your query
Starting the SQL engine...