# Inactive vs Suspended Engagement

> Premium versus free. The engagement gap.

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

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

`account_status` lives on `users`, not on the 600M-row `page_views` fact, so the day-level totals only exist after a join. Filter the two statuses before grouping, then keep days where inactive beat suspended.

---

### 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/inactive_vs_suspended_engagement)
- [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.