avg_daily_consumption_query.sql

        WITH impressions AS(
            SELECT serve_id, MIN(dt) AS dt
            FROM little_sister_kraken_events
            WHERE event_name = 'impression'
            AND get_json_object(event_details, '$.trackable_type') = 'Post'
            AND page IN ('Dashboard', 'DashboardTab')
            AND uuid_type = 'u'
            AND uuid != ''
            AND dt BETWEEN '2022-05-16' AND '2022-05-23'
            GROUP BY serve_id
        ),
        serves AS(
            SELECT user_id, serve_id, session_id, MIN(dt) AS dt
            FROM stream_object_serve
            WHERE dt BETWEEN '2022-05-16' AND '2022-05-23'
            AND context='dashboard'
            GROUP BY user_id, serve_id, session_id
        ),
        backend_serves AS (
            SELECT user_id, session_id, COUNT(*) AS served_elements_per_user_per_session, MIN(dt) AS dt
            FROM stream_object_serve
            WHERE dt BETWEEN '2022-05-16' AND '2022-05-23'
            AND context='dashboard'
            GROUP BY user_id, session_id
        ),
        front_serves AS (
            SELECT s.user_id, s.session_id, COUNT(*) AS post_impressions, MIN(i.dt) AS dt
            FROM impressions i, serves s
            WHERE i.serve_id = s.serve_id
            GROUP BY s.user_id, s.session_id
        ),
        session_info AS (
            SELECT front_serves.user_id AS user_id, post_impressions,
                   served_elements_per_user_per_session, front_serves.dt as dt
            FROM backend_serves
                 JOIN front_serves ON front_serves.user_id = backend_serves.user_id
                       AND front_serves.session_id = backend_serves.session_id
        ),
        daily_consumptions AS (
            SELECT user_id, MAX(post_impressions / served_elements_per_user_per_session) AS consumption, dt
            FROM session_info
            GROUP BY user_id, dt
        )

        SELECT user_id, SUM(consumption) / 7 AS 7d_avg_daily_consumption
        FROM daily_consumptions
        GROUP BY user_id