Created
June 8, 2022 06:59
-
-
Save abmathewks/8db21dd95693081dc3af5b1d310703e4 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment