# The Phantom Readers > They read everything. They bought nothing. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L4 ## Problem The growth team wants to target window shoppers with a promotion. Find users who viewed at least 5 pages in the past 30 days but have zero transactions ever. Return their user_id, username, email, and total view count, listing the most active browsers first and settling ties alphabetically by username. ## Tables ### users - user_id INTEGER - username TEXT - email TEXT - signup_date TEXT - account_status TEXT - age_bucket TEXT Sample rows (10 of 200): | user_id | username | email | signup_date | account_status | age_bucket | | --- | --- | --- | --- | --- | --- | | 100 | alice | alice@example.com | 2025-02-02 | inactive | 25-34 | | 197 | aaron42 | aaron42@example.com | 2026-03-03 | suspended | 35-44 | | 294 | amelia | amelia@example.com | 2024-04-04 | pending_verification | 45-54 | | 391 | arjun | arjun@example.com | 2025-05-05 | active | 55-64 | | 488 | ava99 | ava99@example.com | 2026-06-06 | inactive | 65+ | | 585 | andrew_k | andrew_k@example.com | 2024-07-07 | suspended | NULL | | 682 | anika | anika@example.com | 2025-08-08 16:34:00 | pending_verification | 18-24 | | 779 | aiden | aiden@example.com | 2026-09-09 14:53:00 | active | 25-34 | | 876 | aria_b | aria_b@example.com | 2024-10-10 | inactive | 35-44 | | 973 | brian | brian@example.com | 2025-11-11 | suspended | 45-54 | ### page_views - view_id INTEGER - page_url TEXT - user_id INTEGER - referrer TEXT - dur_ms INTEGER - device TEXT - viewed_at TEXT Sample rows (10 of 200): | view_id | page_url | user_id | referrer | dur_ms | device | viewed_at | | --- | --- | --- | --- | --- | --- | --- | | 5053 | /products | 1555 | amazon.com | 637 | desktop | 2026-09-02 01:09:00 | | 5106 | /checkout | 1652 | twitter.com | 774 | tablet | 2026-09-03 02:18:00 | | 5159 | /profile | 1749 | github.com | 911 | Mobile | 2026-09-04 03:27:00 | | 5212 | /search | 1846 | NULL | 1048 | DESKTOP | 2026-09-05 04:36:00 | | 5265 | /about | 1943 | reddit.com | 1185 | mobile | 2026-09-06 05:45:00 | | 5318 | /blog/intro | 1555 | NULL | NULL | desktop | 2026-09-07 06:54:00 | | 5371 | /pricing | 1652 | bing.com | 1459 | tablet | 2026-09-08 07:03:00 | | 5424 | /docs/api | 1749 | news.ycombinator.com | 1596 | Mobile | 2026-09-09 08:12:00 | | 5477 | /settings | 1846 | NULL | 1733 | DESKTOP | 2026-09-10 09:21:00 | | 5530 | /Home | 1943 | bing.com | 1870 | mobile | 2026-09-11 10:30:00 | ### transactions - transaction_id INTEGER - user_id INTEGER - product_id INTEGER - quantity INTEGER - total_amount REAL - transaction_date TEXT Sample rows (10 of 200): | transaction_id | user_id | product_id | quantity | total_amount | transaction_date | | --- | --- | --- | --- | --- | --- | | 1067 | 197 | 1001 | 2 | 23.46 | 2026-02-02 | | 1134 | 294 | 1050 | 3 | 36.93 | 2026-03-03 | | 1201 | 391 | 1099 | 4 | 50.4 | 2026-04-04 | | 1268 | 488 | 1148 | 5 | 63.87 | 2026-05-05 | | 1335 | 585 | 1197 | 0 | 0 | 2026-06-06 | | 1402 | 682 | 1246 | 2 | 90.81 | 2026-07-07 | | 1469 | 779 | 1295 | 3 | 104.28 | 2026-08-08 | | 1536 | 876 | 1344 | 4 | 117.75 | 2026-09-09 | | 1603 | 973 | 1393 | 5 | -45 | 2026-01-10 | | 1670 | 1070 | 1442 | 1 | 144.69 | 2026-02-11 | ## Expected output First 5 of 5 rows: | user_id | username | email | total_views | | --- | --- | --- | --- | | 1846 | cameron | cameron@example.com | 11 | | 1652 | carlos | carlos@example.com | 11 | | 1749 | chloe | chloe@example.com | 11 | | 1943 | cyrus | cyrus@example.com | 11 | | 1555 | brooke7 | brooke7@example.com | 10 | ## Worked solution and explanation ### What this is really asking `p.transaction_id IS NULL` after a LEFT JOIN is the anti-join. Users with any transaction ever, even from 2019, get dropped. The 30-day window applies only to `page_views`, never to transactions. --- ### Break down the requirements #### Step 1: Anti-join transactions LEFT JOIN transactions on `user_id` and keep only rows where `transaction_id` IS NULL. That filters to users with zero transactions across the entire 80M-row table, lifetime. #### Step 2: Restrict views to the last 30 days Push `av.viewed_at >= date('now', '-30 days')` into the WHERE so COUNT(*) counts only recent activity. The lifetime check stays on transactions. #### Step 3: Group, HAVING, ORDER BY Group by user identity columns, keep groups with COUNT(*) >= 5, sort by `total_views` DESC. HAVING runs after WHERE, so the count reflects only the 30-day window. --- ### The solution **WINDOW SHOPPERS WITH NO PURCHASES** ```sql SELECT u.user_id, u.username, u.email, COUNT(*) AS total_views FROM users u INNER JOIN page_views av ON u.user_id = av.user_id LEFT JOIN transactions p ON u.user_id = p.user_id WHERE av.viewed_at >= date('now', '-30 days') AND p.transaction_id IS NULL GROUP BY u.user_id, u.username, u.email HAVING COUNT(*) >= 5 ORDER BY total_views DESC, username; ``` > **Cost Analysis** > > `page_views` is 500M rows partitioned on `viewed_at`, so the 30-day predicate prunes to ~40M scanned. The transactions LEFT JOIN can be done as NOT EXISTS to short-circuit on first hit per user, often cheaper than building the full outer-joined relation. > **Interviewers Watch For** > > Whether you read 'zero transactions ever' as lifetime, not 30-day. Also whether you understand that putting `p.transaction_id IS NULL` in WHERE rather than ON is what makes the anti-join work; moving it to ON would return everyone. > **Common Pitfall** > > Putting the date filter on transactions instead of `page_views`: `p.transaction_date >= date('now', '-30 days')`. That flips the meaning to users with no transactions in the last 30 days, which catches every dormant buyer too. > **The False Start** > > First instinct is `WHERE user_id NOT IN (SELECT user_id FROM transactions)`. Looks clean, but NOT IN returns zero rows the moment one `transactions.user_id` is NULL because of three-valued logic. Pivot to LEFT JOIN with IS NULL, or NOT EXISTS, both of which are NULL-safe. --- ### COMMON FOLLOW-UP QUESTIONS ## Common follow-up questions - How would you rewrite the anti-join using NOT EXISTS, and when is it faster? _(NOT EXISTS short-circuits on the first matching transaction per user; LEFT JOIN materializes all matches before filtering.)_ - What if growth wants users who viewed 5 distinct pages, not 5 total views? _(Swap COUNT(*) for COUNT(DISTINCT `av.page_url`) in both the SELECT and HAVING.)_ - How would the query change if 'zero transactions' meant zero in the last 90 days instead of ever? _(Move the transactions filter into the ON clause as `p.transaction_date >= date('now', '-90 days')`, keeping IS NULL in WHERE.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_phantom_readers) - [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.