SWW(fast) Aggregate by Project (2024, calls, users) copy
    Updated 2025-01-09
    WITH ranked_projects AS (SELECT * FROM $query('d5d3ca77-73b7-4f6a-8b4e-7cbf66f2be44')),

    project_ranks AS (
    SELECT project,
    MIN(rank) AS min_rank
    FROM ranked_projects
    GROUP BY 1
    ),
    project_aggregate AS (
    SELECT rp.date,
    CASE
    WHEN pr.min_rank <= 5 THEN rp.project
    ELSE 'Others'
    END AS project,
    SUM(rp.num_users) AS total_users,
    SUM(rp.num_calls) AS total_calls
    FROM ranked_projects rp
    JOIN project_ranks pr
    ON rp.project = pr.project
    GROUP BY 1, 2
    )

    SELECT *
    FROM project_aggregate
    ORDER by date, total_users DESC


    QueryRunArchived: QueryRun has been archived