Monthly Transactions I

Monthly Transactions I

What is this problem about?

The Monthly Transactions I problem is a SQL database challenge where you need to aggregate transaction data by month and country. For each month and country combination, you report the total number of transactions, total approved transactions, total transaction amount, and total approved amount. This Monthly Transactions I coding problem tests SQL aggregation, grouping, and conditional counting.

Why is this asked in interviews?

Uber, Microsoft, Meta, Amazon, Google, and Bloomberg ask this SQL problem because aggregating data by multiple dimensions (month + country) with conditional metrics is a core data analysis task. It tests GROUP BY with multiple columns, SUM with CASE WHEN for conditional aggregation, and date formatting. The database interview pattern is the foundation here.

Algorithmic pattern used

SQL GROUP BY with conditional SUM. Group by DATE_FORMAT(trans_date, '%Y-%m') and country. For each group, compute:

• COUNT(*) for total transactions.
• SUM(CASE WHEN state = 'approved' THEN 1 ELSE 0 END) for approved count.
• SUM(amount) for total amount.
• SUM(CASE WHEN state = 'approved' THEN amount ELSE 0 END) for approved amount.

Example explanation

Transactions table: rows with trans_date, country, state (approved/declined), amount. Query groups by month='2019-01' and country='US': total=3, approved=2, total_amount=300, approved_amount=200. Group by month='2019-01' and country='DE': total=1, approved=1, total_amount=50, approved_amount=50.

Common mistakes candidates make

• Grouping by the full date instead of truncated month.
• Using COUNT with conditions instead of SUM(CASE WHEN...) for approved counts.
• Forgetting to include NULL countries (use country directly without filtering).
• Not formatting the month output as 'YYYY-MM'.

Interview preparation tip

Monthly aggregation SQL problems follow a standard template: GROUP BY formatted date + dimension, then use CASE WHEN inside SUM for conditional metrics. Memorize DATE_FORMAT(date_col, '%Y-%m') for MySQL or TO_CHAR(date_col, 'YYYY-MM') for PostgreSQL. Practice writing conditional aggregation (SUM(CASE WHEN ... THEN amount ELSE 0 END)) fluently — it appears in nearly every SQL interview that involves multi-dimensional reporting.