theericstoneuser rankings
    Updated 2022-08-11
    with elo as (
    select * from bi_analytics.content_rankings.elo_ratings
    where project_name = 'Overall'
    )
    /*popularity as (
    WITH mx_date AS (
    SELECT MAX(timestamp) AS mx_timestamp
    FROM bi_analytics.flipside_app_prod.pages
    WHERE timestamp <= CURRENT_DATE + 1
    ), views AS (
    SELECT split(context_page_tab_url,'/')[array_size(split(context_page_tab_url,'/'))-1]::string as dashboard_slug
    , CASE WHEN timestamp < DATEADD('days', -15, m.mx_timestamp) then 1 else 0.5 end as recency_wt
    , COUNT(1) AS n_views
    FROM bi_analytics.flipside_app_prod.pages p
    JOIN mx_date m ON p.timestamp >= DATEADD('days', - 90, m.mx_timestamp)
    AND p.timestamp <= m.mx_timestamp
    WHERE context_page_tab_url LIKE '%dashboard%'
    AND NOT context_page_tab_url ILIKE '%datadog%'
    AND NOT context_page_tab_url ILIKE '%view-test%'
    AND NOT context_page_tab_url ILIKE '%kpi-metrics-dashboard%'
    GROUP BY 1,2
    order by 1 desc
    ), b2 AS (
    SELECT
    dashboard_slug,
    SUM(n_views * recency_wt) AS n_views_wt
    FROM views
    GROUP BY dashboard_slug
    ), b3 AS (
    select
    b2.dashboard_slug,
    b2.n_views_wt,
    d.created_by_id as user_id,
    u.username,
    d.created_at
    from b2
    Run a query to Download Data