# The Engagement Gap

> Two kinds of gone. One still stirs more.

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

Domain: SQL · Difficulty: medium · Seniority: L4

## Problem

We want to compare page view volume between inactive and suspended user segments. For each day, compute total page views for each group. Only show dates where inactive users generated more views than suspended users, earliest first.

## Worked solution and explanation

### What this is really asking

This is a cross-grain conditional aggregation wearing a product-analytics costume. The status you split on, `inactive` versus `suspended`, lives on `users`, but the views you count live on the 600M-row `page_views` fact, so the per-day totals do not exist until you join. Anyone can write two SUMs. What separates candidates: prefiltering the two statuses before the group, collapsing `viewed_at` to a calendar day, and putting the aggregate comparison in HAVING. Group on the raw timestamp instead and each day silently splinters into per-second buckets that never trip `inactive_views > suspended_views`.

---

### Break down the requirements

#### Step 1: Join and prefilter

Inner join `page_views` to `users` on `user_id`, then restrict to `account_status IN ('inactive','suspended')` in WHERE so the other statuses never reach aggregation.

#### Step 2: Pivot per day

Group by `date(viewed_at)` and use one `SUM(CASE WHEN ...)` per status. This collapses two rows per day into one row with two named columns.

#### Step 3: Filter on the aggregates

Compare the two SUMs in HAVING (not WHERE) and order by `view_date` ascending so earliest dates surface first.

---

### The solution

**DAYS WHERE INACTIVE OUTVIEWED SUSPENDED**

```sql
SELECT date(pv.viewed_at) AS view_date,
       SUM(CASE WHEN u.account_status = 'inactive'  THEN 1 ELSE 0 END) AS inactive_views,
       SUM(CASE WHEN u.account_status = 'suspended' THEN 1 ELSE 0 END) AS suspended_views
FROM page_views pv
INNER JOIN users u ON pv.user_id = u.user_id
WHERE u.account_status IN ('inactive', 'suspended')
GROUP BY date(pv.viewed_at)
HAVING inactive_views > suspended_views
ORDER BY view_date;
```

> **Cost Analysis**
>
> Join is 600M to 15M on `user_id`. The IN-list prefilter prunes `users` to two segments before the hash build, shrinking the probe side dramatically. `viewed_at` partitioning helps only if a date predicate exists; here we scan all partitions.

> **Interviewers Watch For**
>
> Pushing the status filter into WHERE rather than gating it inside the CASE. Both produce the same totals, but WHERE shrinks the join and the group state, which matters at 600M rows.

> **Common Pitfall**
>
> Putting `inactive_views > suspended_views` in WHERE breaks: those aliases do not exist before GROUP BY. The comparison is on aggregates, so it belongs in HAVING.

---

### COMMON FOLLOW-UP QUESTIONS

## Common follow-up questions

- How would you also surface days where suspended views were within 10 percent of inactive? _(Add a second HAVING clause comparing `suspended_views >= 0.9 * inactive_views`, or emit a ratio column and filter on it.)_
- What changes if a user's `account_status` flipped between active and suspended over the window? _(`users.account_status` is point-in-time. You would need an SCD2 status history joined on `viewed_at BETWEEN valid_from AND valid_to`.)_
- How do you make this query run only against last week's partitions? _(Add `WHERE pv.viewed_at >= current_date - INTERVAL '7 day'` so the partition pruner reads seven partitions, not all of them.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/the_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). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.