Completed versus other orders
mediumCompleted versus other orders
Stripe SQL Interview Question
Stripe's support team wants a quick health check on each customer of an online store: how many of their orders were paid in full, and how many were canceled or refunded.
For each customer_id, count their completed orders as completed_orders and all their other orders as other_orders.
Asked of
- Data Analyst
- BI Analyst
- Product Analyst
- Analytics Engineer
- Data Engineer
- Data Scientist
ordersTable35 rows
| Column Name | Type |
|---|---|
| order_id | BIGINT |
| customer_id | BIGINT |
| order_date | DATE |
| status | VARCHAR |
| amount | DOUBLE |
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 |
Example Output
| customer_id | completed_orders | other_orders |
|---|---|---|
| 1 | 2 | 1 |
| 2 | 2 | 0 |
| 3 | 1 | 1 |
| 4 | 1 | 0 |
| 5 | 1 | 0 |
| 6 | 1 | 0 |
Explanation
Customer 1 has two completed orders, 1001 and 1003, and one refunded order, 1008, so they get 2 and 1. Customer 3 has one completed order and one canceled order, so they get 1 and 1. Customer 2 has two completed orders and nothing else.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Stripe
Difficulty
medium
Topic
conditional logic
Your query
Starting the SQL engine...