MarquinSmith

-- Description:
-- Count entries in Adobe Analytics click stream data

SELECT
    url,
    COUNT( DISTINCT
        CASE WHEN visit_page_num = '1' THEN CONCAT (post_visid_high, post_visid_low, visit_num)
        END
    ) AS entries
FROM `adobe_click_stream_table`
-- only look at data for June 2022
WHERE 
    DATE(date_time) >= '2022-06-01'
    AND DATE(date_time) < '2022-07-01'

GROUP BY
    url
ORDER BY
    entries DESC