How can I have 1 line per id, instead of 2 in snowflake?

July 24, 2023

I want to know what is the 1st and the 2nd page (and so on) visited by a user in a session.
I already managed to get the flow of interaction through the row_n function.

However, I want a column with the flow of the interaction, but, taking the following example, instead of 2 lines I want 1 line with FIRST and SECOND columns filled up.

Here’s my code:

SELECT
    ID,
    PAGE_TITLE,
    FIRST,
    SECOND
FROM (
SELECT
    distinct
    concat(user_pseudo_id, ga_session_id) as id,
    page_title,
    ROW_NUMBER() OVER (PARTITION BY user_pseudo_id, ga_session_id ORDER BY event_timestamp) AS interaction_number,
    iff(interaction_number = 1, page_title, '') as first,
    iff(interaction_number = 2, page_title, '') as second
FROM CDP_GA4_RAW.RAW_EVENTS_PIVOT
where id = '1000002360.16826696111682669619'
GROUP BY page_title, event_timestamp, user_pseudo_id, ga_session_id, ga_session_number
order by id)
;```

>Solution :

Can you do something like:

SELECT
    MAX(CASE WHEN interaction_number = 1 THEN page_title ELSE '' END) as first,
    MAX(CASE WHEN interaction_number = 2 THEN page_title ELSE '' END) as second,
    MAX(CASE WHEN interaction_number = 3 THEN page_title ELSE '' END) as third,
    MAX(CASE WHEN interaction_number = 4 THEN page_title  ELSE '' END) as fourth
FROM (
SELECT
    concat(user_pseudo_id, ga_session_id) as id,
    page_title ,
    TO_TIMESTAMP(EVENT_TIMESTAMP) AS EVENT_TIMESTAMP,
    ROW_NUMBER() OVER (PARTITION BY user_pseudo_id, ga_session_id ORDER BY TO_TIMESTAMP(event_timestamp)) AS interaction_number
FROM CDP_GA4_RAW.RAW_EVENTS_PIVOT
GROUP BY page_title , event_timestamp, user_pseudo_id, ga_session_id, ga_session_number
order by id)
group by id
;```