SQL data engineering interview problem. Difficulty: advanced. Pattern: Joins. About 18 minutes. Part of the Pro drill bank.
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.
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
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`.