Monthly summary of active users based on a start/end date roster
p.s Hoping to get an answer in Postgres (I'm using Redshift)

Solution for PostgreSQL:
WITH cte AS ( SELECT '2021-01-01'::DATE AS month_start, '2021-01-31'::DATE AS month_end UNION ALL
              SELECT '2021-02-01', '2021-02-28' UNION ALL
              SELECT '2021-03-01', '2021-03-31' )

SELECT cte.month_end, COUNT(*)
FROM roster
CROSS JOIN cte
WHERE start_date &lt;= month_end 
  AND LEAST(end_date, closed_date) &gt;= month_start
GROUP BY cte.month_end
ORDER BY cte.month_end;

or
WITH cte AS ( SELECT '2021-01-31'::DATE AS month_end UNION ALL
              SELECT '2021-02-28' UNION ALL
              SELECT '2021-03-31' )

SELECT cte.month_end, COUNT(*)
FROM roster
CROSS JOIN cte
WHERE start_date &lt;= month_end 
  AND LEAST(end_date, closed_date) &gt;= DATE_TRUNC('month', month_end)
GROUP BY cte.month_end
ORDER BY cte.month_end;

https://dbfiddle.uk/?rdbms=postgres_12&amp;fiddle=710260e9fa44a89cc9c2d536739f7c92
Replace cte code with the code which you need (generate first and last days of months for the months in interest in the first variant or only months last days in the second variant). For example, use dates generator, start from YYYY-MM-01 of the next month for a month in interest, step as a month, and substract - a month for first day of month and a day for the last day.