# NOTE: This _v3 Tinybird experiment is NOT currently in use.
# It was added to test a faster unfiltered top-pages path using a daily materialized view.
# The router was intended to send filtered requests to api_top_pages and unfiltered requests to api_top_pages_v3 without changing Ghost's endpoint version wiring.
# The experiment has been put on hold, and these pipes may be removed in a future release.

# mv_daily_pages - Daily aggregation of page visits for api_top_pages_v3
#
# Pre-aggregates unique sessions per page per day using uniqExactState,
# reducing query scans from millions of raw hits to thousands of daily rows.

TOKEN "axis" READ

NODE mv_daily_pages_0
SQL >
    SELECT
        site_uuid,
        toDate(timestamp) as day,
        case when post_uuid = 'undefined' then '' else post_uuid end as post_uuid,
        pathname,
        post_type,
        member_status,
        uniqExactState(session_id) as visits,
        countState() as hits
    FROM _mv_hits
    GROUP BY site_uuid, day, post_uuid, pathname, post_type, member_status

TYPE materialized
DATASOURCE _mv_daily_pages