Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE activity_log (
  period_start timestamp with time zone NOT NULL
, user_id      text NOT NULL
, action       text NOT NULL
);

SET datestyle = 'MDY';

INSERT INTO activity_log VALUES
  ('12/01/2013', 'X', 'create_entry')
, ('12/02/2013', 'X', 'create_entry')
, ('12/03/2013', 'X', 'create_entry')
, ('01/03/2014', 'X', 'create_entry')
, ('01/14/2014', 'X', 'create_entry')
, ('02/01/2014', 'X', 'create_entry')
, ('02/03/2014', 'X', 'create_entry')
;

CREATE TABLE



SET



INSERT 0 7


SELECT to_char(date_trunc('month', period_start), 'Mon YYYY') AS month
     , COUNT(*) AS month_ct
     , sum(count(*)) OVER (ORDER BY date_trunc('month', period_start)) AS running_ct
FROM   activity_log
WHERE  action = 'create_entry'
GROUP  BY date_trunc('month', period_start);


month
month_ct
running_ct


Dec 2013
3
3

Jan 2014
2
5

Feb 2014
2
7



SELECT 3


-- Fix corner case issues with time zone
SELECT to_char(date_trunc('month', period_start AT TIME ZONE 'Europe/Vienna'), 'Mon YYYY') AS month
     , COUNT(*) AS month_ct
     , sum(count(*)) OVER (ORDER BY date_trunc('month', period_start AT TIME ZONE 'Europe/Vienna')) AS running_ct
FROM   activity_log
WHERE  action = 'create_entry'
GROUP  BY date_trunc('month', period_start AT TIME ZONE 'Europe/Vienna');


month
month_ct
running_ct


Dec 2013
3
3

Jan 2014
2
5

Feb 2014
2
7



SELECT 3