Engagement Gap

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
L4

Problem

The product analytics team wants purchasing activity across the entire user base, including the people who have never bought anything, laid out as an alphabetical roster. Show each user's username next to how many purchases they have made, running from A to Z by username.

Zero transactions is still a data point. Count everyone.

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_idusernameemailsignup_dateaccount_statusage_bucket
100alicealice@example.com2025-02-02inactive25-34
197aaron42aaron42@example.com2026-03-03suspended35-44
294 ameliaamelia@example.com2024-04-04pending_verification45-54
391arjun arjun@example.com2025-05-05active55-64
488ava99ava99@example.com2026-06-06inactive65+
585andrew_kandrew_k@example.com2024-07-07suspendedNULL
682anikaanika@example.com2025-08-08 16:34:00pending_verification18-24
779aidenaiden@example.com2026-09-09 14:53:00active25-34
876aria_baria_b@example.com2024-10-10inactive35-44
973brianbrian@example.com2025-11-11suspended45-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_iduser_idproduct_idquantitytotal_amounttransaction_date
10671971001223.462026-02-02
11342941050336.932026-03-03
12013911099450.42026-04-04
12684881148563.872026-05-05
13355851197002026-06-06
14026821246290.812026-07-07
146977912953104.282026-08-08
153687613444117.752026-09-09
160397313935-452026-01-10
1670107014421144.692026-02-11

Expected output

First 5 of 200 rows
usernametransaction_count
amelia14
Alice0
aaron4214
abel0
alice12

Worked solution and explanation

What this really tests

Strip the business framing and this is a count that has to survive an empty set: for every user, count their transactions, where the users with zero transactions are the whole reason the question exists. On the seed only 15 of the 200 users ever bought anything, so an inner join returns 15 rows and looks correct until you notice 185 people quietly disappeared. The second, quieter trap is what you count: an outer join hands each non-buyer a single all-null transaction row, so COUNT(*) scores that phantom row as 1 and no user ever reads as zero.

Two traps stacked together

Step 1: Keep every user with an outer join

Drive from users and LEFT JOIN transactions on user_id. Buyers match their rows; non-buyers survive with nulls on the transaction side. Swap in an inner join and the exact population the question asked for vanishes without any error.

Step 2: Count the key, not the row

COUNT(t.transaction_id) counts only non-null transaction keys, so a non-buyer's lone all-null row contributes nothing and lands on zero. COUNT(*) counts rows and hands every non-buyer a bogus 1. This one-column choice is the difference between right and confidently wrong.

Step 3: Group by the account, not the name

GROUP BY u.user_id, u.username keeps the account key in the grain. u.username rides along only so it can be selected; two accounts sharing a display name would still come back as two rows.

The solution

Outer join, then count the transaction key
SELECT u.username, COUNT(t.transaction_id) AS transaction_count
FROM users u
LEFT JOIN transactions t ON u.user_id = t.user_id
GROUP BY u.user_id, u.username
ORDER BY u.username
Inner join, count rows
Only the 15 buyers come back; the 185 zero-purchase users you were asked to surface never appear in the result.
Outer join, count the key
All 200 users return exactly one row each: buyers get their true count, non-buyers get 0.

Common follow-up questions