select date, count and avg over month



    Manuel Antunes

    The SELECT statement will return a list of data, where each element in the list is a date. The date_trunc() function will truncate the date to a month and year. The date will be cast to a date_type, which is MM/YY. The date will then be compared to the created_at column in the posts table. If the two dates are equal, the count will be 0. If the date is earlier than the created_at column, the count will be 1. If the date is later than the created_at column, the count will be the difference between the two dates, which is also 1. The round() function will round the number to the nearest integer. The text column will include the percent_growth field, which will show the percent growth for the month.

    select cast(date_trunc('month', created_at) as date) as date,
      count(id) as count,
      round(((count(*)::numeric - lag(count(*), 1) OVER w) 
        / lag(count(*), 1) OVER w * 100),1)::text || '%' as percent_growth     
    from posts
    group by 1
    WINDOW w as (ORDER BY cast(date_trunc('month', created_at) as date))
    order by 1;
    Codiga Logo
    Codiga Hub
    • Rulesets
    • Playground
    • Snippets
    • Cookbooks
    soc-2 icon

    We are SOC-2 Compliance Certified

    G2 high performer medal

    Codiga – All rights reserved 2022.