Month-over-month spend change
hardMonth-over-month spend change
Visa SQL Interview Question
Visa's economics team tracks how card spending moves from one month to the next.
Using approved transactions, find the total spend for each month and how much it changed from the month before. Show the first day of the month as spend_month (a date such as 2024-01-01), the total as total_spend and the change as change_from_previous. The first month has no previous month, so its change_from_previous is NULL. Do not round. Sort the rows by spend_month, earliest first.
Asked of
- Data Analyst
- BI Analyst
- Analytics Engineer
- Data Engineer
- Data Scientist
card_transactionsTable18 rows
| Column Name | Type |
|---|---|
| transaction_id | BIGINT |
| card_id | BIGINT |
| merchant_country | VARCHAR |
| merchant_category | VARCHAR |
| amount_usd | DOUBLE |
| transaction_date | DATE |
| status | VARCHAR |
card_transactionsExample Input
| transaction_id | card_id | merchant_country | merchant_category | amount_usd | transaction_date | status |
|---|---|---|---|---|---|---|
| 3001 | 1 | United States | Groceries | 84.2 | 2024-01-04 | approved |
| 3003 | 3 | Canada | Restaurants | 52.75 | 2024-01-15 | approved |
| 3005 | 6 | Japan | Electronics | 1299 | 2024-01-28 | approved |
| 3007 | 1 | Mexico | Travel | 275 | 2024-02-08 | approved |
| 3009 | 2 | United States | Restaurants | 68.1 | 2024-02-17 | approved |
| 3011 | 6 | United States | Travel | 520 | 2024-02-27 | declined |
| 3013 | 2 | Italy | Travel | 780.5 | 2024-03-09 | approved |
| 3015 | 3 | United States | Groceries | 120.25 | 2024-03-19 | approved |
| 3017 | 4 | France | Electronics | 220 | 2024-03-29 | approved |
Example Output
| spend_month | total_spend | change_from_previous |
|---|---|---|
| 2024-01-01 | 1435.95 | NULL |
| 2024-02-01 | 343.1 | -1092.85 |
| 2024-03-01 | 1120.75 | 777.65 |
Explanation
Approved spending was 1,435.95 in January and 343.10 in February, a change of -1,092.85. March's 1,120.75 is 777.65 more than February. January has no earlier month to compare with, so its change is empty. The declined transaction in February is not counted.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Visa
Difficulty
hard
Topic
window functions
Your query
Starting the SQL engine...