Redshift – Monthly Summary of Active Users Based on Start/End Date Roster

redshift

I have a table that dictates who was on a project, from a startdate to an enddate. I'd like some help writing a query that will return the number of "active" users at the end of every month, since the start of this year.

DROP TABLE "public"."roster";
CREATE TABLE "public"."roster" ("id" int,"user_id" int,"project_id" int,"start_date" datetime,"end_date" datetime,"closed_date" datetime, PRIMARY KEY ("id"));

INSERT INTO "public"."roster" ("id", "user_id", "project_id", "start_date", "end_date", "closed_date") VALUES
(1, 1, 1, '2019-05-27 00:00:00', '2021-01-15 00:00:00', NULL);

INSERT INTO "public"."roster" ("id", "user_id", "project_id", "start_date", "end_date", "closed_date") VALUES
(2, 2, 2, '2020-05-27 00:00:00', '2021-02-01 00:00:00', '2021-02-05 00:00:00');

INSERT INTO "public"."roster" ("id", "user_id", "project_id", "start_date", "end_date", "closed_date") VALUES
(3, 3, 3, '2020-05-27 00:00:00', '2024-02-01 00:00:00', '2021-02-05 00:00:00');

INSERT INTO "public"."roster" ("id", "user_id", "project_id", "start_date", "end_date", "closed_date") VALUES
(4, 4, 4, '2020-05-27 00:00:00', '2021-03-05 00:00:00', NULL);

id  user_id project_id  start_date            end_date              closed_date
1   1       1           2019-05-27 00:00:00   2021-01-15 00:00:00   NULL
2   2       2           2020-05-27 00:00:00   2021-02-01 00:00:00   2020-02-05 00:00:00
3   3       3           2020-05-27 00:00:00   2024-02-01 00:00:00   2020-02-05 00:00:00
4   4       4           2020-05-27 00:00:00   2021-03-05 00:00:00   NULL

And the result would be something that shows how many distinct users had an active project within each month (only since the start of this year).

So for the dataset above, we can see that all 4 projects were "active" in the month January 2021, because the end date is in the future.

2021-01-31 | 3 (4 projects were active in some way, during January)
2021-02-28 | 2 (3 projects were active in some way, during February)
2021-03-31 | 1 (1 project was active in some way, during March)

The last bit of complexity is that sometimes a project can be closed before the EndDate and I'd like to exclude any users who's endDate is in the future but the project has actually closed.

For example in the dataset above, the third project has an end date of Feb 2024, however the project closed in Feb 2021. So technically the person was active in 2021 Jan and Feb, but not March 2021 and beyond.

p.s Hoping to get an answer for Redshift

Best Answer

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 <= month_end 
  AND LEAST(end_date, closed_date) >= month_start
GROUP BY cte.month_end
ORDER BY cte.month_end;

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 <= month_end 
  AND LEAST(end_date, closed_date) >= DATE_TRUNC('month', month_end)
GROUP BY cte.month_end
ORDER BY cte.month_end;

https://dbfiddle.uk/?rdbms=postgres_12&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.

Simple case

If users can join teams only once (which would be enforced by a UNIQUE constraint on ("user", team), ideally), then the solution is a simple GROUP BY and works in Redshift as well as in most any RDBMS:

SELECT "user", team
     , min(CASE WHEN event = 'joined' THEN timestamp END) AS joined
     , max(CASE WHEN event = 'left'   THEN timestamp END) AS "left"
FROM   event
GROUP  BY "user", team
ORDER  BY "user", joined NULLS FIRST;

Note the NULLS FIRST clause. Seems like you want to sort an open start with joined IS NULL first. Redshift supports that, too.

Apart from that it's the most basic form of a crosstab / pivot query.

Not so simple

Judging from your column names and example data it might not be so simple. If users can join teams more than once (non-overlapping), you have to do more. You wouldn't want to merge multiple team memberships into one row like in this related answer:

Postgres: Use result of group by as column names

Instead you have to pair up adjacent 'joined' and 'left' rows somehow. There are many ways ...

Postgres 9.4+

For modern Postgres I like this best:

SELECT "user", team
     , min(timestamp) FILTER (WHERE event = 'joined') AS joined
     , max(timestamp) FILTER (WHERE event = 'left'  ) AS "left"
FROM  (
   SELECT *, count(*) FILTER (WHERE event = 'joined')
                      OVER (PARTITION BY "user", team ORDER BY timestamp) AS ct
   FROM   event
   ) sub
GROUP  BY "user", team, ct
ORDER  BY "user", joined NULLS FIRST;

Using the aggregate FILTER clause in a window function as well as in an aggregate function. Related (with links to alternatives):

Return counts for multiple ranges in a single SELECT statement

This way we count how many times the same user joined the same team, so we can group adjacent rows. Works for a missing 'joined' at the begin or a missing 'left' at the end as well.

Redshift

... does not support the new FILTER clause. We can substitute with a plain old CASE:

SELECT "user", team
     , min(CASE WHEN event = 'joined' THEN timestamp END) AS joined
     , max(CASE WHEN event = 'left'   THEN timestamp END) AS "left"
FROM  (
   SELECT *, count(CASE WHEN event = 'joined' THEN 1 END)
                      OVER (PARTITION BY "user", team ORDER BY timestamp, event) AS ct
   FROM   event
   ) sub
GROUP  BY "user", team, ct
ORDER  BY "user", joined NULLS FIRST;

SQL Fiddle.

Aside: you shouldn't use reserved words as identifiers, even if it's permitted by Redshift (or Postgres).

How to join sparse events for individual users on a “filled in” date ranges in Redshift

Non-optimized solution:

WITH RECURSIVE
cte1 AS ( SELECT MIN(created_at) "date" 
          FROM test
          UNION ALL
          SELECT ("date" + INTERVAL '1 DAY')::DATE 
          FROM cte1 
          WHERE "date" < ( SELECT MAX(created_at)
                           FROM test ) ),
cte2 AS ( SELECT DISTINCT account_id
          FROM test ),
cte3 AS ( SELECT cte1."date", cte2.account_id, MAX(test.created_at) created_at
          FROM cte1
          CROSS JOIN cte2
          LEFT JOIN test ON cte1."date" >= test.created_at
                        AND cte2.account_id = test.account_id
          GROUP BY cte1."date", cte2.account_id )
SELECT cte3."date", cte3.account_id "user", test.plan_name
FROM cte3 
LEFT JOIN test ON cte3.account_id = test.account_id
              AND cte3.created_at = test.created_at
ORDER BY 2, 1

fiddle