Q:

What's the simplest way to make a SQL query to generate cumulative subscribers?

I have a subscriptions table (in postgresql) with the columns subscribed_at and unsubscribed_at. I'd like to write a simple query that produces a list of the cumulative subscriber count (subscribed minus unsubscribed as of that date) for each month end.
Each row is essentially querying: if the group_by month is "January-2020" how many subscriptions have subscribed_at date with a month on or before January 2020, and a unsubscribed_at date of null or after January 2020.
I'm just not familiar enough with SQL to know the right syntax here.
Any help would be much appreciated!
Table
+----+------+---------------+-----------------+
| id | name | subscribed_at | unsubscribed_at |
+----+------+---------------+-----------------+
|  1 | John | '2020-01-08'  | null            |
|  2 | Mary | '2020-01-09'  | '2020-01-20'    |
|  3 | Jeff | '2020-01-10'  | null            |
|  4 | Bill | '2020-02-02'  | null            |
|  5 | Bob  | '2020-02-08   | '2020-02-21'    |
+----+------+---------------+-----------------+

Query
SELECT DATE_TRUNC('month', subscribed_at) as month,
COUNT(*) as subscribers
FROM subscriptions
GROUP BY 1
ORDER BY 1

Desired Output
+---------------+-------------+
|     month     | subscribers |
+---------------+-------------+
| January 2020  |           2 |
| February 2020 |           3 |
+---------------+-------------+

Where subscribers is the cumulative net subscribers at the end of that month.
Here is a SQLFiddle with the data and query: http://www.sqlfiddle.com/#!15/cd7725/1

A:

You could use generate_series() to enumerate all months between the first subscription start and the latest subscription end, and then a lateral join to compute the active subscriptions count.
select d.dt, n.no_active_subscriptions
from (
    select generate_series(
        date_trunc('month', min(subscribed_at)), 
        date_trunc('month', max(unsubsribed_at)), 
        interval '1 month'
    ) dt
    from subscriptions
) d
cross join lateral (
    select count(*) no_active_subscriptions
    from subscriptions s
    where 
        s.subscribed_at < d.dt + interval '1 month' 
        and (s.unsubscribed_at >= d.dt or s.unsubscribed_at is null)
) n

