The 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

Asked in

Microsoft

Problem

We're chasing the engagement gap: among active, inactive, and suspended accounts only (leave out anyone still awaiting verification), find the days when our two lapsed segments, the inactive and the suspended, together drew more than half of that day's page views. For each such day, report how many views came from the inactive users and how many from the suspended, earliest first.

Two kinds of gone. One still stirs more.

Tables

page_views

Columns: view_id INTEGER, page_url TEXT, user_id INTEGER, referrer TEXT, dur_ms INTEGER, device TEXT, viewed_at TEXT

users

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

Expected output

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 versus active, lives on users, but the views you count live on the 600M-row page_views fact, so the per-day totals only exist after the join. Here is the part that separates people: 'more than half of that day's views' makes COUNT(*) the denominator, and COUNT(*) counts whatever survives the WHERE. Scope it to the three account types you care about and the majority test means what you intend; forget to drop pending_verification and half-onboarded users silently pad the denominator, quietly flipping which days qualify. One more quiet trap: on this data the two lapsed segments never post views on the same day, so anyone who tries to pit inactive directly against suspended gets a column of zeros. The real comparison is the lapsed pair against the active users. And group on the raw timestamp instead of date(viewed_at) and every day shatters into per-second buckets that never clear the threshold.

Break down the requirements

Step 1: Join and scope the population

Inner join page_views to users on user_id, then restrict to account_status IN ('inactive','suspended','active') in WHERE. This defines the population whose views form the denominator of the majority test, and deliberately leaves out pending_verification accounts that are still onboarding. It must happen before aggregation.

Step 2: Pivot per day

Group by date(viewed_at) and use one SUM(CASE WHEN ...) per lapsed status. This collapses the day's rows into one row with two named columns, inactive_views and suspended_views, off a single scan.

Step 3: Filter on the aggregates

Test the majority in HAVING: the two lapsed segments together must exceed half the day's scoped total, written (SUM(CASE WHEN ... 'inactive' ...) + SUM(CASE WHEN ... 'suspended' ...)) * 2 > COUNT(*). Because WHERE scoped the group to active plus the two lapsed segments, this is exactly the lapsed pair out-drawing the active users. Order by view_date ascending so the earliest days surface first.

The solution

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', 'active')
GROUP BY date(pv.viewed_at)
HAVING (SUM(CASE WHEN u.account_status = 'inactive'  THEN 1 ELSE 0 END)
      + SUM(CASE WHEN u.account_status = 'suspended' THEN 1 ELSE 0 END)) * 2 > COUNT(*)
ORDER BY view_date;

COMMON FOLLOW-UP QUESTIONS

Common follow-up questions

• How would you also flag days where the suspended segment alone out-drew the active users? (Emit the active count as a third column, or add a HAVING conjunct comparing SUM(CASE WHEN 'suspended') against SUM(CASE WHEN 'active').)
• 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 >= date('now','-7 day')` so the partition pruner reads seven partitions, not all of them.)