First-touch attribution

SQL data engineering interview problem. Difficulty: advanced. Pattern: Joins. About 18 minutes. Part of the Pro drill bank.

A marketing team wants to know which advertisement made a customer aware of the product. In first-touch attribution, a conversion (a sale or sign-up) gets credit for the first campaign the user saw before converting. For each row in conversions, find the first impression of that same user in ad_impressions that happened strictly before the conversion time. Return user_id, the campaign of that first impression, and conversion_time. If there is no earlier impression, skip that conversion. Sort by user_id.

Requirements

  • Match each conversion to the impressions of the same user that happened before it.
  • Choose the earliest of those impressions.
  • Impressions at or after the conversion time do not count.
  • Skip conversions that have no earlier impression.
  • Return user_id, campaign and conversion_time, sorted by user_id.

Constraints

  • The tables are conversions and ad_impressions.
  • The join condition is the same user_id and impression_time < conversion_time.
  • Use ROW_NUMBER() ordered by impression_time to pick the first one, and keep rn = 1.

Examples

Input: ad_impressions user_id | campaign | impression_time U1 | spring_sale | 2024-01-01 09:00:00 U1 | retarget | 2024-01-02 10:00:00 U1 | brand | 2024-01-03 08:00:00 U2 | brand | 2024-01-01 12:00:00 U2 | spring_sale | 2024-01-05 09:00:00 conversions user_id | conversion_time U1 | 2024-01-03 12:00:00 U2 | 2024-01-06 10:00:00 Output: user_id | campaign | conversion_time U1 | spring_sale | 2024-01-03 12:00:00 U2 | brand | 2024-01-06 10:00:00 Why this passes: First touch is the earliest impression strictly before the conversion.

Topics: lakebench, sql, attribution, first touch.

More SQL interview questions · All interview problems · Learn data engineering

advanced

First-touch attribution

Interview-style drill: Attribute each conversion to the earliest prior impression campaign.

For each conversion in `conversions`, attribute to the first `ad_impressions.campaign` for that user with `impression_time` strictly before `conversion_time`. Return `user_id`, `campaign`, `conversion_time`. Skip conversions with no prior impression. Order by `user_id`.