Months in Motion

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

medium

Seniority

L3

Asked in

Whole Foods

Problem

Show how many unique buyers and how many transactions each calendar month drew, with the month numbered 1 for January through 12 for December and every year in transactions pooled into those same twelve months. The analyst studying this seasonality only wants purchases of at least $50 counted.

Engagement, counted one month at a time.

Tables

transactions

Columns: transaction_id INTEGER, user_id INTEGER, product_id INTEGER, quantity INTEGER, total_amount REAL, transaction_date TEXT

Expected output

Worked solution and explanation

What this problem is really about

Underneath the seasonality framing, this is one row per calendar month with two counts at two different cardinalities hanging off it. How many people bought is COUNT(DISTINCT user_id); how many purchases happened is COUNT(*). Everyone gets the grouping roughly right. What separates candidates is refusing to treat those two numbers as one. On this seed 15 buyers make 12 to 14 purchases each, so a row count reports 19 January buyers where only 10 people bought. The quieter failure is the grain: group on the year-month string and January splits into one row per year, so the twelve-month shape never appears. Let the $50 floor slip and every month also counts the cheap, voided and refunded sales.

Building it

Step 1: Filter to qualifying purchases

Keep only rows with total_amount >= 50, in WHERE, so the floor applies before any counting. The >= keeps a purchase landing exactly on $50. This matters on the seed: a real slice of rows sits under $50, including a $0 void and a -$45 refund, and leaving the filter out changes every month's counts.

Step 2: Group on the bare month

Pull the month out of the date with strftime('%m', transaction_date) and group on that, so each row folds into one of twelve buckets regardless of its year. In the SELECT, wrap it in CAST(... AS INTEGER) so the label comes back as 1 through 12 rather than the text '01' through '12'.

Step 3: Count people and purchases separately

Inside each bucket, COUNT(DISTINCT user_id) gives unique buyers and COUNT(*) gives total transactions. They diverge whenever a buyer purchases more than once in that month, which is why both columns exist.

The solution

SELECT CAST(strftime('%m', transaction_date) AS INTEGER) AS month,
       COUNT(DISTINCT user_id) AS unique_users,
       COUNT(*) AS total_transactions
FROM transactions
WHERE total_amount >= 50
GROUP BY strftime('%m', transaction_date)

Common follow-up questions

• If `user_id` could be NULL on some rows, how would `COUNT(DISTINCT user_id)` treat them, and does that match what the business wants? (Tests whether the candidate knows the unique count skips NULLs while `COUNT(*)` does not.)
• How would duplicate rows sharing a `transaction_id` distort `unique_users` versus `total_transactions`? (Tests data-quality awareness and how duplicates hit the two counts differently.)
• With millions of buyers per month, when would an approximate unique count be acceptable here? (Tests judgment about exactness versus cost for a high-cardinality unique count.)