Using CTEs to make your SQL code more readable - nested queries

ctes-nested-queries.sql

\set current_offset '0'
\set previous_week_offset '7'

-- EXPLAIN

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 (
    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 (
        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
    ) AS base
    ORDER BY COALESCE(count_current_week, 0) DESC, COALESCE(count_last_week, 0) DESC
    LIMIT 10
) _