The Phantom Readers

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

Problem

Which users have never made a purchase, with no row anywhere in transactions, yet logged more than 10 page_views in the past 60 days? The promotion list shows each of those window shoppers with their account details and total view count, highest count first and then alphabetically by username.

They read everything. They bought nothing.

Tables

users

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

page_views

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

transactions

Columns: transaction_id INTEGER, user_id INTEGER, product_id INTEGER, quantity INTEGER, total_amount REAL, transaction_date TEXT

Expected output

Worked solution and explanation

What this is really asking

This is an anti-join with a time window on only one side, dressed up as a promotion list. Anyone can count recent page_views. What separates candidates is keeping the two scopes apart: the 60-day window belongs to the views, and 'never made a purchase' is lifetime, tested as the absence of any transactions row. Window the purchases too and every lapsed buyer lands in the promotion. Test the threshold before the count exists and the user sitting at exactly 10 views slips through.

Break down the requirements

Step 1: Keep only users with no transactions row

LEFT JOIN transactions on user_id and keep the rows where p.transaction_id IS NULL. A user with even one purchase, from any year, gets a matched row and drops out. No window touches this side: the absence test runs over the whole table.

Step 2: Restrict views to the last 60 days

av.viewed_at >= date('now', '-60 days') sits in WHERE, so COUNT(*) sees only recent activity. A never-buyer has no matching transactions row, so the left join cannot repeat their view rows, and COUNT(*) is a true view count.

Step 3: Apply the threshold to the per-user count

GROUP BY the identity columns, then apply HAVING COUNT(*) > 10. On this data, one never-buyer has exactly 10 recent views. The threshold is what drops them, and it can only run once the count exists, which is why it lives in HAVING and not in WHERE.

Step 4: Sort by activity, then by name

ORDER BY total_views DESC, username lists the heaviest browsers first and settles equal counts alphabetically. Both keys are returned columns, so the order is fully visible in the result.

The solution

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', '-60 days')
  AND p.transaction_id IS NULL
GROUP BY u.user_id, u.username, u.email
HAVING COUNT(*) > 10
ORDER BY total_views DESC, username;

Window on views

av.viewed_at >= date('now', '-60 days') scopes only the browsing. Anyone with a purchase in any year is excluded.

Window on transactions

p.transaction_date >= date('now', '-60 days') in the ON clause makes the absence test cover recent purchases only. A buyer whose last order was last year now counts as never having bought and gets promoted.

Common follow-up questions

Common follow-up questions

• How would you rewrite the exclusion with `NOT EXISTS`, and when is it faster? (`NOT EXISTS` stops at the first matching purchase per user; the left join builds every match before filtering.)
• What if growth wants more than 10 different pages, not more than 10 views? (Swap `COUNT(*)` for `COUNT(DISTINCT av.page_url)` in the `SELECT` and the `HAVING`, and decide whether '/Home' and '/home' are one page.)
• What changes if 'never made a purchase' means none in the last 90 days? (Move a `p.transaction_date` window into the `ON` clause and keep `IS NULL` in `WHERE`.)