Interview Query

First Touch Attribution

30
Have you seen this question before?

attribution table

column type
session_id integer
channel string
conversion boolean

user_sessions table

column type
session_id integer
created_at datetime
user_id integer

The schema above is for a retail online shopping company consisting of two tables, attribution and user_sessions. 

  • The attribution table logs a session visit for each row.
  • If conversion is true, then the user converted to buying on that session.
  • The channel column represents which advertising platform the user was attributed to for that specific session.
  • Lastly the user_sessions table maps many to one session visits back to one user.

First touch attribution is defined as the channel to which the converted user was associated with when they first discovered the website.

Calculate the first touch attribution for each user_id that converted. 

Example output:

user_id channel
123 facebook
145 google
153 facebook
172 organic
173 email
Next question: Google Docs Drop
MySQL 8.0.17
Loading editor