MarquinSmith

-- Desription:
-- Calculate the number of exitsfor each page from Adobe Click stream data


WITH prep AS (
    SELECT
        post_visid_high,
        post_visid_low,
        visit_num,
        url,
        CAST(visit_page_num AS INT64) AS visit_page_num,
        -- get the maximum page number in each visit in each row for that visit
        MAX(
            CAST(visit_page_num AS INT64)
        ) OVER (
            PARTITION BY
                CONCAT(
                    CAST( post_visid_high AS STRING),
                    CAST( post_visid_low AS STRING),
                    "_",
                    CAST( visit_num AS STRING)
                )) AS max_page_num

    FROM adobe_click_stream_table
    WHERE DATE(date_time) = "2024-01-20"
)

SELECT
    url,
    COUNT( DISTINCT
        CASE
            WHEN max_page_num = visit_page_num
                THEN CONCAT(
                    CAST( post_visid_high AS STRING),
                    CAST( post_visid_low AS STRING),
                    "_",
                    CAST( visit_num AS STRING)
                )
        END
    ) AS exits

FROM prep