# Months in Motion > Engagement, counted one month at a time. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L3 ## Problem We're reading the seasonal rhythm of the business by counting engagement one calendar month at a time, across our full history and limited to transactions worth at least $5. For each month, find how many unique buyers there were and how many transactions happened in total. ## Worked solution and explanation ### What this problem is really about Underneath the engagement-summary framing, this is a plain per-month grouping, and the whole difficulty is picking the right grain. You are counting two different things at two different cardinalities in the same row: how many distinct people bought, which is COUNT(DISTINCT user_id), and how many purchases happened, which is COUNT(*). The trap is treating them as one number. Reach for COUNT(*) on both and you inflate the buyer count every time one person buys twice in a month; put DISTINCT on everything and you undercount the transactions. Get the boundary wrong and the two columns quietly disagree with reality while still returning a plausible-looking result. > **Trick to solving** > > Decide what a single output row represents before you write anything else. Here it is one row per calendar month, pooled across every year in the history. Once the grain is fixed as month, the GROUP BY writes itself and the two counts just hang off it: one collapses buyers to distinct, the other counts every surviving row. --- ### Building it #### Step 1: Filter to the target rows One condition narrows the scan: drop anything below the line with total_amount >= 5. It lives in WHERE so the filtering happens before grouping, not after, and the >= keeps rows landing exactly on the $5 boundary. #### Step 2: Group by month Establish the output grain. Pull the month out as an integer with CAST(strftime('%m', transaction_date) AS INTEGER) and GROUP BY that expression, so each surviving row folds into exactly one of twelve month buckets regardless of its year. #### Step 3: Compute the two counts Inside each month bucket, COUNT(DISTINCT user_id) gives unique buyers and COUNT(*) gives total transactions. They differ whenever a buyer transacts more than once in a month, which is exactly why both columns exist. ORDER BY month lays the result out chronologically. --- ### The solution **Filtered multi-aggregate by month** ```sql 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 >= 5 GROUP BY CAST(strftime('%m', transaction_date) AS INTEGER) ORDER BY month ``` > **Cost analysis** > > Against 100M rows partitioned by transaction_date, total_amount >= 5 keeps almost every row, so there is nothing to prune: this is a full scan that folds into at most twelve output rows. The COUNT(DISTINCT user_id) is the expensive part: it has to track the set of distinct user_ids per bucket, so it dominates the cost. > **Interviewers watch for** > > Naming the output grain (one row per month) before writing the GROUP BY signals you think in data shape, not syntax. The other tell: knowing that COUNT(DISTINCT user_id) and COUNT(*) measure different things, distinct entities versus raw volume, and being able to say precisely when they diverge. > **Common pitfall** > > transaction_date is stored as TEXT. Extraction works here because ISO 'YYYY-MM-DD' strings sort and slice correctly, but do not assume that for a raw range comparison on some other text format, which would silently mis-order rows. strftime is the safe way to pull the month out of the string. --- ## 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 COUNT(DISTINCT) skips NULLs and what that implies for the metric.)_ - How would duplicate transaction rows, the same transaction_id appearing twice, distort unique_users versus total_transactions? _(Tests data-quality awareness and how duplicates hit the two counts differently.)_ - With millions of distinct user_ids, what index or physical layout would keep the per-month distinct count efficient? _(Tests indexing intuition for a high-cardinality distinct aggregation over a large scan.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/months_in_motion) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.