Last active
October 29, 2022 09:15
-
-
Save waltton/6b262d430fb1c90bb901ee8c59851a97 to your computer and use it in GitHub Desktop.
Using CTEs to make your SQL code more readable - cte
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
\set current_offset '0' | |
\set previous_week_offset '7' | |
-- EXPLAIN | |
WITH base AS ( | |
SELECT COUNT(*) FILTER (WHERE date_trunc('week', timestamp) = date_trunc('week', now()) - (:'current_offset' || ' days')::interval) as count_current_week | |
, COUNT(*) FILTER (WHERE date_trunc('week', timestamp) = date_trunc('week', now()) - (:'previous_week_offset' || ' days')::interval) as count_last_week | |
, CASE | |
WHEN user_agent ~ '^(?!.*Edge).*Chrome' THEN 'Chrome' | |
WHEN user_agent ~ '^(?!.*(?:Chrome|Edge)).*Safari' THEN 'Safari' | |
WHEN user_agent ~ 'MSIE ([0-9]{1,}[\.0-9]{0,})' THEN 'Internet Explorer' | |
WHEN user_agent ~ 'Firefox\/(\d+(?:\.\d+)+)' THEN 'Firefox' | |
WHEN user_agent ~ 'Edge' THEN 'Edge' | |
ELSE 'Others' | |
END AS user_agent_group | |
FROM logs | |
GROUP BY user_agent_group | |
ORDER BY COUNT(*) DESC | |
), | |
ranked AS ( | |
SELECT * | |
, RANK() OVER (ORDER BY COALESCE(count_current_week, 0) DESC) AS rank_current_week | |
, RANK() OVER (ORDER BY COALESCE(count_last_week, 0) DESC) AS rank_last_week | |
FROM base | |
ORDER BY COALESCE(count_current_week, 0) DESC, COALESCE(count_last_week, 0) DESC | |
LIMIT 10 | |
) | |
SELECT json_agg( | |
json_build_object( | |
'user_agent_group', user_agent_group, | |
'count', count_current_week, | |
'count_delta', COALESCE(count_current_week, 0) - COALESCE(count_last_week, 0), | |
'rank', rank_current_week, | |
'rank_delta', COALESCE(rank_last_week, 0) - COALESCE(rank_current_week, 0) | |
) | |
) | |
FROM ranked |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment