flipsidecryptoDashboard Views + Impressions Flow
    Updated 2023-08-16
    --Since 5/22/23 (when we started collected twitter data)

    with db_views AS (
    SELECT
    RIGHT(SPLIT(context_page_tab_url, '?')[0]::string, 6) AS slug_id,
    count(*) AS views
    FROM bi_analytics.core.fact_pages p
    where timestamp::DATE >= '2023-05-22'
    AND LEN(path) > 1
    AND path != '/discover'
    AND STARTSWITH(path, '/data') = FALSE
    AND STARTSWITH(path, '/?sortBy=') = FALSE
    AND STARTSWITH(path, '/previews') = FALSE
    AND STARTSWITH(path, 'analysts') = FALSE
    AND CHARINDEX('/q/', path) = 0
    AND STARTSWITH(name, '/edit') = FALSE
    AND STARTSWITH(context_page_url, 'http://localhost') = FALSE
    AND LEN(path) - LEN(REPLACE(path,'/','')) > 1
    group by 1
    ),

    slugs AS (
    select
    id AS dashboard_id,
    slug_id
    from bi_analytics.velocity_app_prod.dashboards
    QUALIFY( ROW_NUMBER() OVER (PARTITION BY dashboard_id ORDER BY updated_at DESC) = 1 )
    ),

    impressions AS (
    select
    RIGHT(SPLIT(clean_url, '?')[0]::string, 6) AS slug_id,
    sum(impression_count) AS n_impressions
    from BI_ANALYTICS.TWITTER.TWEET
    where platform = 'Flipside'
    and slug_id <> ''
    Run a query to Download Data