Active Users With April Transactions

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

Domain: SQL
Difficulty: easy
Seniority: L3
Asked in: Google

Problem

The growth team is measuring transacting reach for April 2026. How many users with active accounts completed at least one transaction during that month? Return a single count.

Active accounts that also opened their wallets. How many?

Tables

users

Columns: user_id INTEGER, username TEXT, email TEXT, signup_date TEXT, account_status TEXT, age_bucket TEXT

users: 10 of 200 rows
user_id	username	email	signup_date	account_status	age_bucket
100	alice	alice@example.com	2025-02-02	inactive	25-34
197	aaron42	aaron42@example.com	2026-03-03	suspended	35-44
294	amelia	amelia@example.com	2024-04-04	pending_verification	45-54
391	arjun	arjun@example.com	2025-05-05	active	55-64
488	ava99	ava99@example.com	2026-06-06	inactive	65+
585	andrew_k	andrew_k@example.com	2024-07-07	suspended	NULL
682	anika	anika@example.com	2025-08-08 16:34:00	pending_verification	18-24
779	aiden	aiden@example.com	2026-09-09 14:53:00	active	25-34
876	aria_b	aria_b@example.com	2024-10-10	inactive	35-44
973	brian	brian@example.com	2025-11-11	suspended	45-54

transactions

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

transactions: 10 of 200 rows
transaction_id	user_id	product_id	quantity	total_amount	transaction_date
1067	197	1001	2	23.46	2026-02-02
1134	294	1050	3	36.93	2026-03-03
1201	391	1099	4	50.4	2026-04-04
1268	488	1148	5	63.87	2026-05-05
1335	585	1197	0	0	2026-06-06
1402	682	1246	2	90.81	2026-07-07
1469	779	1295	3	104.28	2026-08-08
1536	876	1344	4	117.75	2026-09-09
1603	973	1393	5	-45	2026-01-10
1670	1070	1442	1	144.69	2026-02-11

Expected output

First 1 of 1 rows
active_users_with_transactions
1

Worked solution and explanation

Why this problem exists in real interviews

Working with users, transactions, this problem isolates grouped aggregation. The interviewer expects candidates to articulate why username, email, signup_date matter for correctness before touching the keyboard.

Break down the requirements

Step 1: Join `users` with `transactions`

Join users to transactions on user_id to link each transaction to its account.

Step 2: Group by `account_status`

Filter to active accounts (account_status = 'active') whose transaction falls in April of 2026 via strftime year/month.

Step 3: Aggregate with MIN

Return COUNT(DISTINCT u.user_id) so each qualifying user is counted once as a single reach figure.

Step 4: Order by the metric

Sort descending to surface the top values first.

The solution

Group-aggregate for active users april transactions

SELECT COUNT(DISTINCT u.user_id) AS active_users_with_transactions
FROM users u
JOIN transactions t ON u.user_id = t.user_id
WHERE u.account_status = 'active'
  AND strftime('%Y', t.transaction_date) = '2026'
  AND strftime('%m', t.transaction_date) = '04'

Common follow-up questions

What happens to your results if `username` in `users` contains trailing whitespace or mixed casing? (Tests awareness of text normalization issues that silently fragment GROUP BY results.)
If `users` and `transactions` have a one-to-many relationship, how does that affect the COUNT in your GROUP BY? (Tests understanding of fan-out: joining before grouping can inflate counts.)
`user_id` in `users` has ~20M distinct values. What index strategy keeps your query from doing a full table scan? (Tests whether the candidate can design indexes for high-cardinality columns and understands selectivity.)
Could you express this same logic as a single query without CTEs or subqueries? What readability trade-off does that introduce? (Tests whether the candidate can flatten nested logic and understands when decomposition aids maintainability.)