SQL data engineering interview problem. Difficulty: advanced. Pattern: Joins. About 18 minutes. Part of the Pro drill bank.
Same setup as first-touch, but attribute to the last impression strictly before conversion. Return user_id, campaign, conversion_time. 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 | 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
Interview-style drill: Attribute each conversion to the latest prior impression campaign.
Same setup as first-touch, but attribute to the last impression strictly before conversion. Return `user_id`, `campaign`, `conversion_time`. Order by `user_id`.