Last-touch attribution

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

In last-touch attribution, a conversion gives credit to the last campaign the user saw before converting. It is the opposite of the first-touch problem, and the setup is the same. For each row in conversions, find the latest impression of the same user in ad_impressions that happened strictly before the conversion time. Return user_id, the campaign of that impression, and conversion_time. Sort by user_id.

Requirements

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

Constraints

• The tables are conversions and ad_impressions.
• Join on the same user_id and impression_time < conversion_time.
• Use ROW_NUMBER() ordered by impression_time DESC 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 | brand | 2024-01-03 12:00:00 U2 | spring_sale | 2024-01-06 10:00:00 Why this passes: Last touch picks the most recent impression strictly before conversion.

Topics: lakebench, sql, attribution, last touch.

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