Monthly Transactions I
Stripe
Problem Statement
Financial analysts at Stripe need to monitor transaction health across different markets. They require a report that breaks down activity by month and country.
Write an SQL query to find for each month and country:
- The number of transactions and their total amount.
- The number of approved transactions and their total amount.
Rules:
- The month should be formatted as YYYY-MM.
- If the country is NULL, it should still be included as a group.
- Return the columns: month, country, trans_count, approved_count, trans_total_amount, and approved_total_amount.
- Results must be sorted by month in descending order, then by country in ascending order.
Tests your understanding of
Basic SQL, Aggregation, Date Functions and Conditional Logic
Input Tables
transactions
| id(INTEGER) | country(VARCHAR) | state(ENUM('approved', 'declined')) | amount(INTEGER) | trans_date(DATE) |
|---|---|---|---|---|
| 121 | US | approved | 1000 | 2024-12-18 |
| 122 | US | declined | 2000 | 2024-12-19 |
| 123 | US | approved | 2000 | 2025-01-01 |
| 124 | DE | approved | 3000 | 2025-01-07 |
Expected Output
| month(VARCHAR) | country(VARCHAR) | trans_count(INTEGER) | approved_count(INTEGER) | trans_total_amount(INTEGER) | approved_total_amount(INTEGER) |
|---|---|---|---|---|---|
| 2025-01 | DE | 1 | 1 | 3000 | 3000 |
| 2025-01 | US | 1 | 1 | 2000 | 2000 |
| 2024-12 | US | 2 | 1 | 3000 | 1000 |
Tags
MediumBasic SQLAggregationDate FunctionsConditional Logic
15-20 min
64%