Skip to content

Instantly share code, notes, and snippets.

@waltton

waltton/ctes-cte.sql

Last active October 29, 2022 09:15
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save waltton/6b262d430fb1c90bb901ee8c59851a97 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save waltton/6b262d430fb1c90bb901ee8c59851a97 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. waltton revised this gist Oct 29, 2022. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions ctes-cte.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,5 +1,5 @@
    \set current_offset '21'
    \set previous_week_offset '28'
    \set current_offset '0'
    \set previous_week_offset '7'

    -- EXPLAIN

  2. waltton created this gist Oct 29, 2022.
    40 changes: 40 additions & 0 deletions ctes-cte.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,40 @@
    \set current_offset '21'
    \set previous_week_offset '28'

    -- 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