test_gist_idioti.sql

test_gist_idioti.sql

WITH
    filtered_table AS (
        SELECT
            *
        FROM
            customer_traffic_hits
        WHERE
            hit_hour_utc >= DATEADD(day, -90, GETDATE())
            AND (page_type = 'ClientMetrics' OR page_type = 'DetailPage')
            AND is_robot = 0
            AND hit_type = 'page-hit'
    ),
    partitioned_table AS (
        SELECT
            *,
            LAG(request_id) OVER (PARTITION BY session_id, page_type_id, marketplace_id ORDER BY start_time_local, page_type DESC) AS prev_reqId,
            LAG(page_type) OVER (PARTITION BY session_id, page_type_id, marketplace_id ORDER BY start_time_local, page_type DESC) AS prev_pT,
           LAG(sub_page_type) OVER (PARTITION BY session_id, page_type_id, marketplace_id ORDER BY start_time_local, page_type DESC) AS prev_sPT,
            LAG(ref_marker) OVER (PARTITION BY session_id, page_type_id, marketplace_id ORDER BY start_time_local, page_type DESC) AS prev_TAG
        FROM
            filtered_table
    ),
    clickstream AS (
        SELECT
            *
        FROM
            partitioned_table
        WHERE
            page_type = 'ClientMetrics'
            AND prev_pT = 'DetailPage'
            AND prev_sPT = 'Quick-buy-Page-Visit'
    ),
    csa AS (
        SELECT
            session_id,
            page_requestid,
            customer_id
        FROM
            customer_content_impressions
        WHERE
            ingestion_time >= DATEADD(day, -91, GETDATE())
            AND (element_contentid = 'Quick-buy-Mobile' OR element_contentid = 'Quick-buy-Desktop')
            AND customer_id IS NOT NULL
    ),
    aowtab AS (
        SELECT
            clickstream.page_type_id,
            csa.session_id,
            clickstream.customer_id,
            csa.page_requestid,
            clickstream.hit_hour_utc
        FROM
            csa
            JOIN clickstream ON clickstream.prev_reqId = csa.page_requestid
    )
SELECT
    EXTRACT(WEEK FROM date_trunc('week', hit_hour_utc + INTERVAL '1 day')) AS Week_Number,
    COUNT(DISTINCT aowtab.page_requestid),
    MIN(hit_hour_utc) AS Week_Start_Date,
    MAX(hit_hour_utc) AS Week_End_Date
FROM
    aowtab
GROUP BY
    Week_Number
ORDER BY
    Week_Number