Monthly completed revenue
hardMonthly completed revenue
Stripe SQL Interview Question
A business uses Stripe to take payment for its online orders. Stripe's reporting team wants to show that business how its revenue changed from month to month. Every order was placed in 2024.
Find the total revenue from completed orders in each month. Show the month number (1 for January through 12 for December) as order_month and the total as revenue. Sort the rows by order_month, earliest first.
Asked of
- Data 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 |
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_month | revenue |
|---|---|
| 1 | 8271.49 |
| 2 | 188 |
Explanation
In January, six completed orders add up to 8,271.49, and the canceled order is not counted. In February, the refunded order is left out, so revenue is 55.40 plus 132.60, which is 188.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Stripe
Difficulty
hard
Topic
aggregations
Your query
Starting the SQL engine...