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