title: Counting subscriber numbers given events on SQL
body: <p>I have a dataset on mysql in the following format, showing the history of events given some client IDs:
  <a href="https://i.stack.imgur.com/uLxdl.png" rel="nofollow noreferrer">Base Data</a></p>
  <p>Text of the dataset (subscriber_table):</p>
  <pre><code>user_id   type  created_at
  A   past_due    2021-03-27 10:15:56
  A   reactivate  2021-02-06 10:21:35
  A   past_due    2021-01-27 10:30:41
  A   new 2020-10-28 18:53:07
  A   cancel  2020-07-22 9:48:54
  A   reactivate  2020-07-22 9:48:53
  A   cancel  2020-07-15 2:53:05
  A   new 2020-06-20 20:24:18
  B   reactivate  2020-06-14 10:57:50
  B   past_due    2020-06-14 10:33:21
  B   new 2020-06-11 10:21:24
  </code></pre>
  <p>date_table:</p>
  <pre><code>full_date
  2020-05-01
  2020-06-01
  2020-07-01
  2020-08-01
  2020-09-01
  2020-10-01
  2020-11-01
  2020-12-01
  2021-01-01
  2021-02-01
  2021-03-01
  </code></pre>
  <p>I have been struggling to come up with a query to count subscriber counts given a range of months, which are not necessary included in the event table either because the client is still subscribed or they cancelled and later resubscribed. The output I am looking for is this:</p>
  <p><a href="https://i.stack.imgur.com/mM26n.png" rel="nofollow noreferrer">Output</a></p>
  <pre><code>date    subscriber_count
  2020-05-01  0
  2020-06-01  2
  2020-07-01  2
  2020-08-01  1
  2020-09-01  1
  2020-10-01  2
  2020-11-01  2
  2020-12-01  2
  2021-01-01  2
  2021-02-01  2
  2021-03-01  2
  </code></pre>
  <p>Reactivation and Past Due events do not change the subscription status of the client, however only the Cancel and New event do. If the client cancels in a month, they should still be counted as active for that month.</p>
  <p>My initial approach was to get the latest entry given a month per subscriber ID and then join them to the premade date table, but when I have months missing I am unsure on how to fill them with the correct status. Maybe a lag function?</p>
  <pre><code>with last_record_per_month as (
      select
  date_trunc('month', created_at)::date order by created_at) as month_year ,
          user_id ,
          type,
          created_at as created_at
      from
          subscriber_table
      where
          user_id in ('A', 'B')
      order by
          created_at desc
      ), final as (
      select
          month_year,
          created_at,
          type
      from
          last_record_per_month lrpm
      right join (
          select
              date_trunc('month', full_date)::date as month_year
          from
              date_table
          where
              full_date between '2020-05-01' and '2021-03-31'
          group by
              1
          order by
              1
          ) dd
      on lrpm.created_at = dd.month_year
      and num = 1
      order by
          month_year
      )
  select
      *
  from
      final
  </code></pre>
  <p>I do have a premade base table with every single date in many years to use as a joining table</p>
  <p>Any help with this is GREATLY appreciated</p>
  <p>Thanks!</p>