How do I compare a current partial month vs a previous partial month with postgres?

Question

I'm building some basic reports and I want to see if I'm on track to surpass last month's metrics without waiting for the month to end. Basically I want to compare June 1 (start of current month) through June 23 (current_date) against May 1 (start of previous month) through May 23 (current_date - 1 month).

My goal is to show a count of distinct users that did event1 and event2.

Here's what I have so far:

CREATE VIEW events AS
  (SELECT *
   FROM public.event
   WHERE TYPE in ('event1',
                  'event2')
     AND created_at > now() - interval '1 months' );


CREATE VIEW MAU AS
  (SELECT EXTRACT(DOW
                  FROM created_at) AS month,
          DATE_TRUNC('week', created_at) AS week,
          COUNT(*) AS total_engagement,
          COUNT(DISTINCT user_id) AS total_users
   FROM events
   GROUP BY 2,
            1
   ORDER BY week DESC);

SELECT month,
       week,
       SUM(total_engagement) OVER (PARTITION BY month
                                   ORDER BY week) AS total_engagment
FROM MAU
ORDER BY 1 DESC,
         2

Here's an example of what that returns:

Month  Week                 Unique Engagement
6      2017-05-22 00:00:00  165
6      2017-05-29 00:00:00  355
6      2017-06-05 00:00:00  572
6      2017-06-12 00:00:00  723
5      2017-05-22 00:00:00  757
5      2017-05-29 00:00:00  1549
5      2017-06-05 00:00:00  2394
5      2017-06-12 00:00:00  3261
5      2017-06-19 00:00:00  3592

Expected return

Month  Day  Total Engagement
6      1     50
6      2     100
6      3     180
5      1     89
5      2     213
5      3     284
5      4     341

Can you point out where I've got this wrong or if there's an easier way to do it?

@schwern this is close to what I would expect except I don't understand why the first row's month 6 (6th month of the year?) is showing data for May. — Jeremy, Jun 23 '17 at 19:08
`EXTRACT(DOW FROM created_at) AS month` - DOW is a Day Of Week, not a month. — Abelisto, Jun 23 '17 at 19:09
If you need the month number then, obviously, `EXTRACT(month FROM created_at) AS month` or, if you need the month of week's start: `EXTRACT(month FROM DATE_TRUNC('week', created_at)) AS month` — Abelisto, Jun 23 '17 at 19:16

score 2 · Answer 1 · answered Jun 23 '17 at 19:30

2

You are confusing days, weeks and months in your question but from the expected output I assume that you want month number, week number within a month and a count of those pairs.

SELECT 
  month,
  week,
  count(*) as total_engagement
FROM (
  SELECT
    extract(month from created_at) as month,
    extract('day' from date_trunc('week', created_at::date) -
                   date_trunc('week', date_trunc('month', created_at::date))) / 7 + 1 as week
  FROM public.event
  WHERE type IN ('event1', 'event2')
    AND created_at > now() - interval '1 month'
  ) t
GROUP BY 1,2

The most interesting part could be getting the week number within a month and for that you can check this answer.

answered Jun 23 '17 at 19:30

Kamil Gosciminski

16,547
8
49
72

This seems closer, but what I'm looking for is a running count of unique users by day, each month. So day 1 has 5 total users (all unique). Day 2 has 10 total users (but let's assume only 3 of them did not also engage on day 1) so the day 2 count would 5 (from day 1) + 3 (from day 2) = 8. Does that make sense? – Jeremy Jun 23 '17 at 21:13
Attach sample data for your expected result. – Kamil Gosciminski Jun 23 '17 at 21:39
I added the "expected return". Basically if on day one, 3 users interact (user_ids 1,2, and 3) that would give a "Unique Engagement" of 3. If on day 2, 5 users interact (user_ids 1, 2, 4, 5, 6), that would bring "Unique Engagement to 6 (because users 1 & 2 already interacted on day 1) – Jeremy Jul 13 '17 at 17:03
My approach might be way off. But what I want is a rolling count (by day) of unique users by month – Jeremy Jul 13 '17 at 17:05

How do I compare a current partial month vs a previous partial month with postgres?

1 Answers1