First Interaction Credit

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

L5

Asked in

Netflix

Problem

Pull each converted user's user_id with the ad_campaign and impression_time of the very first impression they were ever served in ad_impressions, clicked or not, since the analyst building the first-touch attribution credits every conversion there. A user counts as converted as soon as they show up in transactions.

Attribute transactions to earliest touchpoint

Tables

ad_impressions

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

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 a per-user argmin wearing an attribution badge. Finding each converted user's earliest impression_time is the easy half. The skill is returning the ad_campaign from that same row. GROUP BY user_id with MIN(impression_time) gets the timestamp right and then has to pick a campaign some other way, and MIN(ad_campaign) picks the alphabetically first campaign, not the first-touch one. Fall for that and the timestamps look perfect while each conversion is credited to a campaign the user may have seen years later.

Break down the requirements

Step 1: Restrict to converters

A converter is any user_id present in transactions, and each one has 12 to 14 purchase rows. Test membership with EXISTS instead of joining: a join repeats every impression once per purchase before you have picked anything. The impressions with a NULL user_id match no transaction and drop out here too.

Step 2: Pick the earliest impression row

ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY impression_time) numbers each converter's impressions oldest first, and rn = 1 is the first touch. The filter sits outside the CTE because a window value cannot be referenced in the WHERE of the query that computes it. There is no predicate on clicked: a view counts.

Step 3: Return the trio

Project user_id, ad_campaign and impression_time from the surviving row. That leaves one row per converted user, ten on this data, with no aggregation left to do and no order to promise.

The solution

WITH ranked AS (
  SELECT
    i.user_id,
    i.ad_campaign,
    i.impression_time,
    ROW_NUMBER() OVER (
      PARTITION BY i.user_id
      ORDER BY i.impression_time
    ) AS rn
  FROM ad_impressions i
  WHERE EXISTS (
    SELECT 1 FROM transactions t WHERE t.user_id = i.user_id
  )
)
SELECT user_id, ad_campaign, impression_time
FROM ranked
WHERE rn = 1
;

A minimum per column

GROUP BY user_id with MIN(impression_time) and MIN(ad_campaign) computes two independent minimums. The timestamp is the first touch; the campaign is whichever name sorts first among everything the user saw, which matches only by luck.

Pick the whole row

ROW_NUMBER orders the rows themselves, so rn = 1 returns the timestamp and the campaign of one physical impression. A self-join back to MIN(impression_time) also works, but it reads ad_impressions twice.

Common follow-up questions

Common follow-up questions

• How would you switch this to a last-touch model? (Flip the `ORDER BY` inside the window to `DESC`; the rest is identical.)
• What if two impressions share an `impression_time` for the same user? (`ROW_NUMBER` then picks one arbitrarily. Add `impression_id` as a tiebreaker, or use `RANK` and decide how to split credit.)
• How do you handle users who converted before their first impression? (Add `t.transaction_date >= i.impression_time` inside the `EXISTS`, or you credit campaigns for purchases they could not have caused.)