Interview Drills · to_char · GROUP BY month
28. Monthly spend
Concept: to_char · GROUP BY monthshow
To aggregate by month, turn each date into a month label and group by it. to_char(d, 'YYYY-MM') gives text like '2024-03'; date_trunc('month', d) gives a timestamp at the start of the month.
Example (different data):
SELECT to_char(created_at, 'YYYY-MM') AS month, COUNT(*) AS signups FROM accounts GROUP BY 1 ORDER BY 1;
Your task
The finance team's dashboard needs total card spend per month for the first half of 2024.
Return month (formatted YYYY-MM) and total_spend (sum of amount) for each month, oldest first.
Starting Postgres in your browser…
Checking your access…