Net balance by account
mediumNet balance by account
Stripe SQL Interview Question
Stripe's account dashboard shows how much a business charged its customers and what is left in its balance after refunds and payouts.
For each account_id, return the total of charges as gross_charges and the total of every transaction as net_balance, both rounded to 2 decimal places. Refunds and payouts are already stored as negative amounts. Sort by account_id.
Asked of
- Data Analyst
- Business Analyst
- BI Analyst
- Product Analyst
- Analytics Engineer
transactionsTable12 rows
| Column Name | Type |
|---|---|
| txn_id | BIGINT |
| account_id | BIGINT |
| created_date | DATE |
| type | VARCHAR |
| amount | DOUBLE |
transactionsExample Input
| txn_id | account_id | created_date | type | amount |
|---|---|---|---|---|
| 1 | 9001 | 2024-08-01 | charge | 120 |
| 2 | 9002 | 2024-08-01 | charge | 75.5 |
| 3 | 9001 | 2024-08-02 | charge | 60.25 |
| 4 | 9001 | 2024-08-03 | refund | -20 |
| 5 | 9002 | 2024-08-03 | charge | 210 |
| 6 | 9001 | 2024-08-05 | payout | -150 |
| 7 | 9003 | 2024-08-05 | charge | 40 |
| 8 | 9002 | 2024-08-06 | refund | -75.5 |
| 9 | 9003 | 2024-08-06 | charge | 55.75 |
| 10 | 9002 | 2024-08-08 | payout | -180 |
| 11 | 9001 | 2024-08-09 | charge | 99.99 |
| 12 | 9003 | 2024-08-10 | payout | -60 |
Example Output
| account_id | gross_charges | net_balance |
|---|---|---|
| 9001 | 280.24 | 110.24 |
| 9002 | 285.5 | 30 |
| 9003 | 95.75 | 35.75 |
Explanation
Account 9001 charged 120.00, 60.25 and 99.99, a gross of 280.24. After a 20.00 refund and a 150.00 payout, 110.24 is left in its balance.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Stripe
Difficulty
medium
Topic
conditional logic
Language
SQL
Your query
Starting the SQL engine...