Running revenue over time
hardRunning revenue over time
Stripe SQL Interview Question
Stripe's dashboard shows each business a chart of its revenue building up over time. You are building the numbers behind that chart for one online store.
Using completed orders only, show each order's order_date, its amount, and the total revenue up to and including that order as running_total. Sort the rows by order_date, earliest first.
Asked of
- Data Analyst
- BI Analyst
- Product Analyst
- Analytics Engineer
- Data Engineer
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
| order_date | amount | running_total |
|---|---|---|
| 2024-01-05 | 120.5 | 120.5 |
| 2024-01-07 | 2400 | 2520.5 |
| 2024-01-11 | 89.99 | 2610.49 |
| 2024-01-18 | 610.75 | 3221.24 |
| 2024-01-22 | 1850.25 | 5071.49 |
| 2024-01-29 | 3200 | 8271.49 |
| 2024-02-06 | 55.4 | 8326.89 |
| 2024-02-09 | 132.6 | 8459.49 |
Explanation
The first completed order is worth 120.50, so the running total starts at 120.50. The next completed order adds 2,400, bringing it to 2,520.50. The canceled order on January 15 does not appear and does not change the total.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Stripe
Difficulty
hard
Topic
window functions
Your query
Starting the SQL engine...