Users With and Without Ad Clicks

A hard SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

hard

Seniority

L4

Asked in

Microsoft

Problem

Account reviews keep asserting that suspended and inactive accounts never engage with ads, yet nobody has checked that claim against the whole users base. For the growth team, split every user within each account status by whether they have ever clicked an ad in ad_impressions, with the number of users in each group.

Clicked an ad versus never clicked. The split.

Tables

users

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

ad_impressions

Columns: impression_id INTEGER, user_id INTEGER, ad_campaign TEXT, impression_time TEXT, clicked INTEGER, revenue REAL

Expected output

Worked solution and explanation

What this really is

This is an existence test over an entire population, dressed up as an engagement breakdown. The skill being probed: can you put every user into exactly one bucket based on whether a row exists somewhere else, without letting the other table decide who counts? Anyone can filter clicked = 1. The trick is that the answer has to be driven from users, because the people you are measuring the silence of are exactly the ones ad_impressions never mentions. Start from the impressions and every never-served user vanishes; join the raw impressions and every heavy clicker is counted once per click.

Building it

Step 1: Build the clicker set as c

Inside the join, a small subquery pulls user_id from ad_impressions where clicked = 1 and dedupes it with DISTINCT. Aliased c, this set has one row per clicker no matter how many ads they clicked, so the join that follows cannot fan out.

Step 2: Anchor on users

LEFT JOIN that set onto users. Every user survives: a clicker gets a matching c.user_id, everyone else (saw ads and ignored them, or never saw one) gets NULL. An inner join here would quietly delete the entire no-click population.

Step 3: Label and count per status

A CASE on c.user_id IS NULL names the bucket, and account_status comes straight from users. Grouping on both gives one row per status and bucket, and COUNT(*) is a user count because each user is exactly one row after step 1.

SELECT
    u.account_status,
    CASE WHEN c.user_id IS NULL THEN 'no_click' ELSE 'has_click' END AS click_group,
    COUNT(*) AS user_count
FROM users AS u
LEFT JOIN (
    SELECT DISTINCT user_id
    FROM ad_impressions
    WHERE clicked = 1
) AS c
    ON c.user_id = u.user_id
GROUP BY u.account_status, click_group

Starting from impressions

SELECT clicked, COUNT(DISTINCT user_id) FROM ad_impressions GROUP BY clicked never sees a user with zero impressions, and a user with both a clicked and an unclicked impression shows up in both buckets.

Starting from users

users LEFT JOIN the deduped clicker set keeps every account, and the IS NULL test puts each one in exactly one bucket.

Common follow-up questions

• How would you split the no-click group into users who saw ads and users who never saw one? (Needs a second existence test against all impressions, not only clicked ones.)
• How would you turn these counts into a click-through share per `account_status`? (Divide the has-click count by the status total, ideally in one pass.)
• What changes if you rewrite the clicker subquery as an `EXISTS` test? (Tests whether the candidate sees the semi-join as the same existence check.)