Learn

To get the UTM parameters, we’ll need to JOIN these results back with the original table.

We’ll join tables first_touch, akaft, and page_visits, aka pv, on user_id and timestamp.

ft.user_id = pv.user_id AND ft.first_touch_at = pv.timestamp

Remember that first_touch_at is the earliest timestamp for each user. Here’s the simplified query:

WITH first_touch AS ( /* ... */ ) SELECT * FROM first_touch AS 'ft' JOIN page_visits AS 'pv' ON ft.user_id = pv.user_id AND ft.first_touch_at = pv.timestamp;

Now fill in the WITH clause using the first_touch query from the previous exercise. We’ll also specify the columns to SELECT.

WITH first_touch AS ( SELECT user_id, MIN(timestamp) AS 'first_touch_at' FROM page_visits GROUP BY user_id) SELECT ft.user_id, ft.first_touch_at, pv.utm_source FROM first_touch AS 'ft' JOIN page_visits AS 'pv' ON ft.user_id = pv.user_id AND ft.first_touch_at = pv.timestamp;

Instructions

The diagram on the right illustrates the JOIN we need to get the UTM parameters of each first touch:

  • On the left is page_visits (just three columns from the original table). We get the UTM parameters from there.
  • On the right is first_touch (the result of the GROUP BY query). We get the first touches from there.
Folder Icon

Sign up to start coding

By signing up for Codecademy, you agree to Codecademy's Terms of Service & Privacy Policy.

Already have an account?