0

I am struggling writing a query than can count users every month based on a condition where two date fields interact. First, I have a field that identifies users uniquely, a second field that registers the date when the user subscribed, and a third field that registers the date when the unsubscribes.

user, subscribed, unsubscribed
1234, 2017-01-01, null
2345, 2017-01-01, 2017-12-01
3456, 2017-03-01, 2017-05-20

The table that I would expect to see is:

date, user_count
2017-01-01, 2
2017-02-01, 2
2017-03-01, 3
2017-04-01, 3
2017-05-01, 3
2017-06-01, 2
etc...

I know that I have to include a where condition where subscribed has to be greater than unsubscribed. WHERE subscribed > unsubscribed. Null just shows that there is an empty value where the user hasn't unsubscribed. I am just not quiet sure how to count a user every time the condition is met every month of the year. Do I need to join my table to a date-month series?

Javier
  • 39
  • 9

1 Answers1

0

yes, you would need a monthly series - see this post

then, you can join your table to a subquery that returns distinct months with the following condition:

WHERE first_date_of_month BETWEEN date_trunc('month',subscribed) AND date_trunc('month',coalesce(unsubscribed,getdate())

The coalesce function will replace NULL values from the first argument by the second argument (current date)

AlexYes
  • 4,088
  • 2
  • 15
  • 23
  • 1
    Yes, you need a join and also you need use "group by" for show the count for month – Marcos Riveros May 24 '18 at 20:40
  • @AlexYes - Thank you, your solution seems to work on a smaller table sample. I am wondering if I can easily scale this since I have around 70M users and I am doing a cross join with three years of the monthly series table, which creates close to 2.5B rows before the query does the final count (70M rows X 36 rows). – Javier May 24 '18 at 22:02
  • @Javier which approach did you take? if you have performance problems you might break the monthly series into smaller chunks, let's say one year, materialize the results of the first year into a table, then append the second year and the third year. – AlexYes May 25 '18 at 08:18