SQL Challenges/Monthly Transactions I

Challenge DetailMediumReviewed 2026-04-20

Monthly Transactions I

Find for each month and country: the number of transactions and their total amount, the number of approved transactions and their total amount.

AggregationCase WhenDate Logic

This challenge now meets the current minimum standard for sitemap inclusion and independent indexing.

2026-01-04Medium

Monthly Transactions I

Find for each month and country: the number of transactions and their total amount, the number of approved transactions and their total amount.

Available Tables:

SQL Editor

What This Challenge Is Testing

This is a standard dashboard query shape. Finance, ops, and marketplace teams constantly need monthly totals plus a status-filtered subset in the same table, and conditional aggregation is usually the cleanest way to do it.

Topic Tags

AnalyticsDatesAggregations

Schema At A Glance

transactions

idcountrystateamounttrans_date

Solution Approaches

Conditional aggregation in one grouped query

SELECT
  SUBSTR(trans_date, 1, 7) AS month,
  country,
  COUNT(*) AS trans_count,
  SUM(CASE WHEN state = 'approved' THEN 1 ELSE 0 END) AS approved_count,
  SUM(amount) AS trans_total_amount,
  SUM(CASE WHEN state = 'approved' THEN amount ELSE 0 END) AS approved_total_amount
FROM transactions
GROUP BY month, country

This is the most direct answer. One grouped scan produces both the all-transaction metrics and the approved-only metrics, which is exactly what conditional aggregation is designed for.

Prepare month first in a CTE

WITH monthly_base AS (
  SELECT
    SUBSTR(trans_date, 1, 7) AS month,
    country,
    state,
    amount
  FROM transactions
)
SELECT
  month,
  country,
  COUNT(*) AS trans_count,
  SUM(CASE WHEN state = 'approved' THEN 1 ELSE 0 END) AS approved_count,
  SUM(amount) AS trans_total_amount,
  SUM(CASE WHEN state = 'approved' THEN amount ELSE 0 END) AS approved_total_amount
FROM monthly_base
GROUP BY month, country

The CTE version separates date bucketing from metric construction. It is useful when the production query later adds more derived fields, filters, or joins before the final aggregation layer.

Common Pitfalls

The usual mistake is filtering `WHERE state = 'approved'` too early, which destroys the total transaction metrics and leaves you with only approved rows. Another common bug is forgetting to group by both month and country, which collapses countries together.

Frequently Asked Questions

Why use CASE inside SUM instead of filtering approved rows in WHERE?

Because the report needs both total metrics and approved-only metrics in the same output. A WHERE filter would remove declined rows before aggregation and make the total counts wrong.

What concept is this challenge really testing?

It is mainly testing conditional aggregation: using CASE inside aggregate functions so one grouped query can produce multiple related business metrics at once.

Concept Guides

Use these guides when you want the fuller explanation behind the SQL pattern this challenge is testing.

SQL Conditional Aggregation: Beyond Basic GROUP BY

Turn multiple queries into one with conditional aggregation. Learn how CASE WHEN inside aggregate functions creates powerful single-query reports and pivot tables.

Time Series Analysis with SQL: Trends, Growth, and Moving Averages

Turn raw timestamps into business insights. Learn how to calculate Month-over-Month growth and smooth out noisy data with 7-day moving averages.

Mastering SQL GROUP BY: From Basics to Advanced Aggregations

Learn how GROUP BY transforms rows into summaries. Watch data collapse into groups with animations and master COUNT, SUM, AVG, and HAVING.

Topic Hubs

If this problem exposed a deeper weakness, use a topic hub to move through a curated cluster instead of random article hopping.

Analytics

Analytics SQL and business metrics

A practical cluster for cohorts, funnels, customer value, and the kinds of reporting questions teams actually ask every week.

Advanced SQL

Advanced query patterns

Use this path when you are moving beyond beginner SELECT queries into CTEs, window functions, and conditional logic.

Practice Path

If you want the rotating practice flow, open this same question from the daily stream. If you want interview-style packaging, continue into the interview pool after this rep.

Back to Challenges Open In Daily Browse Interviews

Related Challenges

Immediate Food Delivery IIMedium

Measure what share of customers got an immediate delivery on their first-ever order instead of across all orders.

Game Play Analysis IVMedium

Compute next-day retention by checking whether each player returned on the day after their first login.

Trips and UsersHard

Compute daily cancellation rate after excluding any trip where either the client or driver is banned.

SQL Challenges/Monthly Transactions I

Challenge DetailMediumReviewed 2026-04-20

Monthly Transactions I

Find for each month and country: the number of transactions and their total amount, the number of approved transactions and their total amount.

AggregationCase WhenDate Logic

This challenge now meets the current minimum standard for sitemap inclusion and independent indexing.

2026-01-04Medium

Monthly Transactions I

Find for each month and country: the number of transactions and their total amount, the number of approved transactions and their total amount.

Available Tables:

SQL Editor

What This Challenge Is Testing

Topic Tags

AnalyticsDatesAggregations

Schema At A Glance

transactions

idcountrystateamounttrans_date

Solution Approaches

Conditional aggregation in one grouped query

SELECT
  SUBSTR(trans_date, 1, 7) AS month,
  country,
  COUNT(*) AS trans_count,
  SUM(CASE WHEN state = 'approved' THEN 1 ELSE 0 END) AS approved_count,
  SUM(amount) AS trans_total_amount,
  SUM(CASE WHEN state = 'approved' THEN amount ELSE 0 END) AS approved_total_amount
FROM transactions
GROUP BY month, country

This is the most direct answer. One grouped scan produces both the all-transaction metrics and the approved-only metrics, which is exactly what conditional aggregation is designed for.

Prepare month first in a CTE

WITH monthly_base AS (
  SELECT
    SUBSTR(trans_date, 1, 7) AS month,
    country,
    state,
    amount
  FROM transactions
)
SELECT
  month,
  country,
  COUNT(*) AS trans_count,
  SUM(CASE WHEN state = 'approved' THEN 1 ELSE 0 END) AS approved_count,
  SUM(amount) AS trans_total_amount,
  SUM(CASE WHEN state = 'approved' THEN amount ELSE 0 END) AS approved_total_amount
FROM monthly_base
GROUP BY month, country

The CTE version separates date bucketing from metric construction. It is useful when the production query later adds more derived fields, filters, or joins before the final aggregation layer.

Common Pitfalls

Frequently Asked Questions

Why use CASE inside SUM instead of filtering approved rows in WHERE?

Because the report needs both total metrics and approved-only metrics in the same output. A WHERE filter would remove declined rows before aggregation and make the total counts wrong.

What concept is this challenge really testing?

It is mainly testing conditional aggregation: using CASE inside aggregate functions so one grouped query can produce multiple related business metrics at once.

Concept Guides

Use these guides when you want the fuller explanation behind the SQL pattern this challenge is testing.

SQL Conditional Aggregation: Beyond Basic GROUP BY

Turn multiple queries into one with conditional aggregation. Learn how CASE WHEN inside aggregate functions creates powerful single-query reports and pivot tables.

Time Series Analysis with SQL: Trends, Growth, and Moving Averages

Turn raw timestamps into business insights. Learn how to calculate Month-over-Month growth and smooth out noisy data with 7-day moving averages.

Mastering SQL GROUP BY: From Basics to Advanced Aggregations

Learn how GROUP BY transforms rows into summaries. Watch data collapse into groups with animations and master COUNT, SUM, AVG, and HAVING.

Topic Hubs

If this problem exposed a deeper weakness, use a topic hub to move through a curated cluster instead of random article hopping.

Analytics

Analytics SQL and business metrics

A practical cluster for cohorts, funnels, customer value, and the kinds of reporting questions teams actually ask every week.

Advanced SQL

Advanced query patterns

Use this path when you are moving beyond beginner SELECT queries into CTEs, window functions, and conditional logic.

Practice Path

If you want the rotating practice flow, open this same question from the daily stream. If you want interview-style packaging, continue into the interview pool after this rep.

Back to Challenges Open In Daily Browse Interviews

Related Challenges

Immediate Food Delivery IIMedium

Measure what share of customers got an immediate delivery on their first-ever order instead of across all orders.

Game Play Analysis IVMedium

Compute next-day retention by checking whether each player returned on the day after their first login.

Trips and UsersHard

Compute daily cancellation rate after excluding any trip where either the client or driver is banned.