Monthly Billing Summary by Region

A SaaS company charges customer cards every month, and each charge attempt is logged with its outcome. Finance wants a summary per calendar month and region.

Return one row per (month, region) pair that has at least one attempt, with these columns in this order:

• month: the attempt month as text 'YYYY-MM'
• region (attempts with an unknown region form their own group with region = NULL)
• attempts: number of attempts
• approved_attempts: attempts with status 'approved'
• attempted_cents: total amount of all attempts
• approved_cents: total amount of approved attempts, 0 (not NULL) when none were approved

Rows may be in any order. Return no rows if the log is empty.

Tables

charge_attempts — One row per attempt to charge a customer card.

Example

charge_attempts

Expected output

Rows may be returned in any order. Your query also runs against hidden datasets with tricky edge cases.

SQL Path · Aggregation & Grouping · problem 17 of 70 · SQLite

The idea

The grain of the report is one row per month and region, so derive the month with strftime('%Y-%m', attempted_at) and GROUP BY that value together with region. Keeping the year in the key is essential: grouping by the month number alone would merge different years. GROUP BY treats all NULL regions as one group, which is exactly what the statement asks for.

All four numbers come from the same scan using conditional aggregation. COUNT(*) and SUM(amount_cents) cover every attempt. For the approved figures, a CASE keeps the value for approved rows and turns every other row into 0. Without ELSE 0, the non-approved rows become NULL, and a group where nothing was approved sums to NULL instead of 0.

The alternative uses COUNT(CASE … THEN 1 END), which counts only non-NULL values, and wraps the sum in COALESCE(…, 0).

Solution

SELECT
  strftime('%Y-%m', attempted_at) AS month,
  region,
  COUNT(*) AS attempts,
  SUM(CASE WHEN status = 'approved' THEN 1 ELSE 0 END) AS approved_attempts,
  SUM(amount_cents) AS attempted_cents,
  SUM(CASE WHEN status = 'approved' THEN amount_cents ELSE 0 END) AS approved_cents
FROM charge_attempts
GROUP BY month, region;

Another way

SELECT
  substr(attempted_at, 1, 7) AS month,
  region,
  COUNT(*) AS attempts,
  COUNT(CASE WHEN status = 'approved' THEN 1 END) AS approved_attempts,
  SUM(amount_cents) AS attempted_cents,
  COALESCE(SUM(CASE WHEN status = 'approved' THEN amount_cents END), 0) AS approved_cents
FROM charge_attempts
GROUP BY substr(attempted_at, 1, 7), region;

Why common answers fail

Leaves out ELSE 0, so a group with no approved attempts sums only NULLs and returns NULL instead of 0.

SELECT strftime('%Y-%m', attempted_at) AS month, region, COUNT(*) AS attempts,
  SUM(CASE WHEN status = 'approved' THEN 1 ELSE 0 END) AS approved_attempts,
  SUM(amount_cents) AS attempted_cents,
  SUM(CASE WHEN status = 'approved' THEN amount_cents END) AS approved_cents
FROM charge_attempts
GROUP BY month, region;

COUNT counts every non-NULL value, and a false comparison is 0, not NULL, so it counts all attempts.

SELECT strftime('%Y-%m', attempted_at) AS month, region, COUNT(*) AS attempts,
  COUNT(status = 'approved') AS approved_attempts,
  SUM(amount_cents) AS attempted_cents,
  SUM(CASE WHEN status = 'approved' THEN amount_cents ELSE 0 END) AS approved_cents
FROM charge_attempts
GROUP BY month, region;

Groups by month number only, merging March 2025 with March 2026.

SELECT strftime('%m', attempted_at) AS month, region, COUNT(*) AS attempts,
  SUM(CASE WHEN status = 'approved' THEN 1 ELSE 0 END) AS approved_attempts,
  SUM(amount_cents) AS attempted_cents,
  SUM(CASE WHEN status = 'approved' THEN amount_cents ELSE 0 END) AS approved_cents
FROM charge_attempts
GROUP BY month, region;

What the interviewer is checking

A standard reporting query: the interviewer checks conditional aggregation, date bucketing, and that empty buckets show 0 rather than NULL.

Mistakes to avoid

• Missing ELSE 0, producing NULL totals.
• Using COUNT(condition), which counts false values too.
• Bucketing by '%m' and merging years.

Follow-ups to expect

• Add an approval rate column (approved_attempts * 100.0 / attempts).
• Include months with zero attempts (generate months with a recursive CTE and LEFT JOIN).
• Pivot statuses into one column each (one conditional SUM per status).

In real work

Payment teams monitor authorisation rates and approved volume per market and month to detect issuer or gateway problems.

Practice next