# Engagement Gap

> Zero transactions is still a data point. Count everyone.

Canonical URL: <https://datadriven.io/problems/engagement_gap>

Domain: SQL · Difficulty: medium · Seniority: L4

## Problem

The product analytics team needs purchase counts for every user in the system, including those who have never bought anything. Show each user's username alongside their transaction count, with non-buyers showing zero.

## Worked solution and explanation

### Why this problem exists in real interviews

Engagement gap analysis identifies users with zero purchases. This tests LEFT JOIN for preserving all users, combined with aggregation to count transactions per user.

---

### Break down the requirements

#### Step 1: Left join users to transactions

`LEFT JOIN transactions ON users.user_id = transactions.user_id` preserves all users, including those with zero purchases.

#### Step 2: Count purchases per user

`COUNT(transaction_id)` gives purchase count. NULLs from the LEFT JOIN yield a count of zero.

---

### The solution

**LEFT JOIN to count purchases for every user**

```sql
SELECT u.user_id, u.username, COUNT(t.transaction_id) AS purchase_count
FROM users u
LEFT JOIN transactions t ON u.user_id = t.user_id
GROUP BY u.user_id, u.username
ORDER BY purchase_count ASC, u.user_id
```

> **Cost Analysis**
>
> LEFT JOIN fans out users to transactions. An index on `transactions(user_id)` is essential.

> **Interviewers Watch For**
>
> The interviewer watches for LEFT JOIN (not INNER JOIN) to preserve zero-purchase users. This is the whole point of the query.

> **Common Pitfall**
>
> Using INNER JOIN drops users with no transactions, which is exactly the population you need to find.

---

## Common follow-up questions

- How would you find users who purchased once but never returned? _(Tests HAVING COUNT = 1 for single-purchase users.)_
- Could you solve this with NOT EXISTS? _(Tests anti-join: SELECT * FROM users WHERE NOT EXISTS (...).)_
- What if you needed the gap as a percentage of all users? _(Tests scalar subquery for the denominator.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/engagement_gap)
- [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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.