Merchant Revenue
ASKED IN INTERVIEWDropbox
Problem Statement
Dropbox provides payment infrastructure for merchants. You need to generate a report showing the revenue per merchant for each month.
Rules:
- Output columns: merchant_id, merchant_name, transaction_month, total_revenue.
- transaction_month format: 'YYYY-MM'.
- Only include transactions with a status of 'Success'.
- Results must be sorted by transaction_month in descending order, then by total_revenue in descending order.
Tests your understanding of
Date Truncation, Aggregation and Filtering
Input Tables
merchants
| merchant_id(INTEGER) | merchant_name(VARCHAR) |
|---|---|
| 1 | Cloud Storage Co |
| 2 | DevTools Inc |
| 3 | SaaS Solutions |
transactions
| transaction_id(INTEGER) | merchant_id(INTEGER) | amount(DECIMAL) | status(VARCHAR) | created_at(TIMESTAMP) |
|---|---|---|---|---|
| 10 | 1 | 500 | Success | 2024-01-15 10:00:00 |
| 11 | 1 | 200 | Success | 2024-01-20 12:00:00 |
| 12 | 2 | 1000 | Success | 2024-01-05 09:00:00 |
| 13 | 1 | 300 | Failed | 2024-01-25 15:00:00 |
| 14 | 1 | 400 | Success | 2024-02-10 08:00:00 |
| 15 | 3 | 1500 | Success | 2024-02-15 11:00:00 |
| 16 | 2 | 1200 | Success | 2024-02-20 14:00:00 |
merchant_tiers
| merchant_id(INTEGER) | tier(VARCHAR) |
|---|---|
| 1 | Gold |
| 2 | Silver |
| 3 | Platinum |
Expected Output
| merchant_id(INTEGER) | merchant_name(VARCHAR) | transaction_month(VARCHAR) | total_revenue(DECIMAL) |
|---|---|---|---|
| 3 | SaaS Solutions | 2024-02 | 1500 |
| 2 | DevTools Inc | 2024-02 | 1200 |
| 1 | Cloud Storage Co | 2024-02 | 400 |
| 2 | DevTools Inc | 2024-01 | 1000 |
| 1 | Cloud Storage Co | 2024-01 | 700 |
Tags
MediumASKED IN INTERVIEWDate TruncationAggregationFiltering
15-20 min
52%