/*
Query collects the reader funnel stats from item views, enters & reader mode
tap actions. Counts the number of events and the distinct count of users per day
*/
WITH 
--CTE to declare common variables used in the CTE 
vars_cte as (
    select
       '2022-05-01'::DATE                   AS start_date,
       '2022-06-02'::DATE                   AS end_date,
       '2022-05-01'                         AS start_date_str,
       '2022-06-02'                         AS end_date_str,
       2::INT                               AS reader_view_taps_threshold
       --5556::BIGINT                       AS some_id
),
total_displays AS (
    --CTE to collect all item display events and uids
    SELECT
        TO_DATE(ue.day,'YYYY-MM-DD')        AS event_date,
        ue.prod_type                        AS prod_type,
        COUNT(DISTINCT ue.uid)              AS item_display_user_count,
        COUNT(*)                            AS item_displays
    --FROM core_schema.usage_event_052022     AS ue
    FROM core_schema.usage_event            AS ue
    WHERE ue.event_category = 'section' 
        AND ue.event_action = 'display_item'
        AND ue.event_item_type = 'post'
        AND ue.prod_type IN ('android','ios')
        AND ue.day >= (SELECT start_date_str FROM vars_cte)
        AND ue.day <= (SELECT end_date_str FROM vars_cte)
        --AND TO_DATE(ue.day,'YYYY-MM-DD') >= '2022-05-01'::DATE
        --AND ue.dayhour >= DATEADD(DAY,-33, DATE_TRUNC('day',GETDATE()))
        --AND TO_DATE(ue.day,'YYYY-MM-DD') >= CURRENT_DATE - INTERVAL '30' day
    GROUP BY 1,2
    ORDER BY 1
),
total_enters AS (
    --CTE to collect all item enter events and uids
    SELECT
        TO_DATE(ue.day,'YYYY-MM-DD')        AS event_date,
        ue.prod_type                        AS prod_type,
        COUNT(DISTINCT ue.uid)              AS item_enter_user_count,
        COUNT(*)                            AS item_enters
    --FROM core_schema.usage_event_052022     AS ue
    FROM core_schema.usage_event            AS ue
    WHERE ue.event_category = 'item' 
        AND ue.event_action = 'enter'
        AND ue.event_item_type = 'post'
        AND ue.prod_type IN ('android','ios')
        AND ue.day >= (SELECT start_date_str FROM vars_cte)
        AND ue.day <= (SELECT end_date_str FROM vars_cte)
        --AND TO_DATE(ue.day,'YYYY-MM-DD') >= '2022-05-01'::DATE
        --AND ue.dayhour >= DATEADD(DAY,-33, DATE_TRUNC('day',GETDATE()))
        --AND TO_DATE(ue.day,'YYYY-MM-DD') >= CURRENT_DATE - INTERVAL '30' day
    GROUP BY 1,2
),
total_views AS (
    SELECT
        TO_DATE(ue.day,'YYYY-MM-DD')        AS event_date,
        ue.prod_type                        AS prod_type,
        COUNT(DISTINCT ue.uid)              AS item_view_user_count,
        COUNT(*)                            AS item_views
    --FROM core_schema.usage_event_052022     AS ue
    FROM core_schema.usage_event            AS ue
    WHERE ue.event_category = 'item' 
        AND ue.event_item_type = 'post'
        AND ue.event_action = 'viewed'
        AND ue.prod_type IN ('android','ios')
        AND ue.day >= (SELECT start_date_str FROM vars_cte)
        AND ue.day <= (SELECT end_date_str FROM vars_cte)
        --AND TO_DATE(ue.day,'YYYY-MM-DD') >= '2022-05-01'::DATE
        --AND ue.dayhour >= DATEADD(DAY,-33, DATE_TRUNC('day',GETDATE()))
        --AND TO_DATE(ue.day,'YYYY-MM-DD') >= CURRENT_DATE - INTERVAL '30' day
    GROUP BY 1,2
),
reader_view AS (
    SELECT
        TO_DATE(ue.day,'YYYY-MM-DD')        AS event_date,
        ue.prod_type                        AS prod_type,
        COUNT(DISTINCT ue.uid)              AS reader_view_user_count,
        COUNT(*)                            AS reader_view_tap_events
    --FROM core_schema.usage_event_052022     AS ue
    FROM core_schema.usage_event            AS ue
    WHERE ue.event_category = 'general' 
        AND ue.event_action = 'tap_action' 
        AND ue.event_type = 'reader_mode'
        AND ue.event_item_type = 'post'
        AND ue.prod_type IN ('android','ios')
        AND ue.day >= (SELECT start_date_str FROM vars_cte)
        AND ue.day <= (SELECT end_date_str FROM vars_cte)
        --AND TO_DATE(ue.day,'YYYY-MM-DD') >= '2022-05-01'::DATE
        --AND ue.dayhour >= DATEADD(DAY,-33, DATE_TRUNC('day',GETDATE()))
        --AND TO_DATE(ue.day,'YYYY-MM-DD') >= CURRENT_DATE - INTERVAL '30' day
    GROUP BY 1,2
),
reader_view_funnel AS (
    SELECT
        td.event_date                       AS event_date,
        --user count
        SUM(CASE WHEN td.prod_type = 'ios' THEN td.item_display_user_count
                ELSE 0 END)                 AS ios_item_display_users,
        SUM(CASE WHEN td.prod_type = 'android' THEN td.item_display_user_count
                ELSE 0 END)                 AS android_item_display_users,
        SUM(item_display_user_count)        AS total_item_display_users,
        SUM(CASE WHEN te.prod_type = 'ios' THEN te.item_enter_user_count
                ELSE 0 END)                 AS ios_item_enter_users,
        SUM(CASE WHEN te.prod_type = 'android' THEN te.item_enter_user_count
                ELSE 0 END)                 AS android_item_enter_users,
        SUM(item_enter_user_count)          AS total_item_enter_users,
        SUM(CASE WHEN tv.prod_type = 'ios' THEN tv.item_view_user_count
                ELSE 0 END)                 AS ios_item_view_users,
        SUM(CASE WHEN tv.prod_type = 'android' THEN tv.item_view_user_count
                ELSE 0 END)                 AS android_item_view_users,
        SUM(item_view_user_count)           AS total_item_view_users,
        SUM(CASE WHEN rv.prod_type = 'ios' THEN rv.reader_view_user_count
                ELSE 0 END)                 AS ios_reader_view_users,
        SUM(CASE WHEN rv.prod_type = 'android' THEN rv.reader_view_user_count
                ELSE 0 END)                 AS android_reader_view_users,
        SUM(reader_view_user_count)         AS total_reader_view_users,
        --event count
        SUM(CASE WHEN td.prod_type = 'ios' THEN td.item_displays
                ELSE 0 END)                 AS ios_item_displays,
        SUM(CASE WHEN td.prod_type = 'android' THEN td.item_displays
                ELSE 0 END)                 AS android_item_displays,
        SUM(item_displays)                  AS total_item_displays,
        SUM(CASE WHEN te.prod_type = 'ios' THEN te.item_enters
                ELSE 0 END)                 AS ios_item_enters,
        SUM(CASE WHEN te.prod_type = 'android' THEN te.item_enters
                ELSE 0 END)                 AS android_item_enters,
        SUM(item_views)                     AS total_item_enters,
        SUM(CASE WHEN tv.prod_type = 'ios' THEN tv.item_views
                ELSE 0 END)                 AS ios_item_views,
        SUM(CASE WHEN tv.prod_type = 'android' THEN tv.item_views
                ELSE 0 END)                 AS android_item_views,
        SUM(item_views)                     AS total_item_views,
        SUM(CASE WHEN rv.prod_type = 'ios' THEN rv.reader_view_tap_events
                ELSE 0 END)                 AS ios_reader_view_tap_events,
        SUM(CASE WHEN rv.prod_type = 'android' THEN rv.reader_view_tap_events
                ELSE 0 END)                 AS android_reader_view_tap_events,
        SUM(reader_view_tap_events)         AS total_reader_view_tap_events
    FROM total_displays                     AS td
    LEFT JOIN total_enters                  AS te
        ON td.event_date = te.event_date
            AND td.prod_type = te.prod_type
    LEFT JOIN total_views                   AS tv
        ON td.event_date = tv.event_date
            AND td.prod_type = tv.prod_type
    --Joining on item views as you cannot enable reader mode without item view
    LEFT JOIN reader_view                   AS rv
        ON tv.event_date = rv.event_date
            AND tv.prod_type = rv.prod_type
    GROUP BY 1
    ORDER BY 1
)
SELECT 
    *
FROM reader_view_funnel