Practice Interview Questions
| Question | Status |
|---|---|
Given 2 tables, attribution and user_sessions, from Etsy, your task is to calculate the first touch attribution for each user_id that converted. First touch attribution is defined as the channel with which the converted user was associated when they first discovered the website.
The attribution table logs a session visit for each row, and has a conversion column. If conversion is 1, 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.
attribution table:
| Column Name | Description |
|---|---|
| session_id | Unique identifier for each session visit |
| channel | Platform attributed to the session |
| conversion | Indicator for conversion |
user_sessions table:
| Column Name | Description |
|---|
| session_id | Unique identifier for each session visit |
| created_at | Timestamp of when the session was created |
| user_id | Unique identifier for each user |
| user_id | channel |
|---|---|
| 2 | |
| 3 | |
| 4 |
Given 2 tables, attribution and user_sessions, from Etsy, your task is to calculate the first touch attribution for each user_id that converted. First touch attribution is defined as the channel with which the converted user was associated when they first discovered the website.
The attribution table logs a session visit for each row, and has a conversion column. If conversion is 1, 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.
attribution table:
| Column Name | Description |
|---|---|
| session_id | Unique identifier for each session visit |
| channel | Platform attributed to the session |
| conversion | Indicator for conversion |
user_sessions table:
| Column Name | Description |
|---|---|
| session_id | Unique identifier for each session visit |
| created_at | Timestamp of when the session was created |
| user_id | Unique identifier for each user |
| user_id | channel |
|---|---|
| 2 | |
| 3 | |
| 4 |