Decoding datelist ints 🔓

Scenario

The same social media platform from the customer churn problem wants to view their user activity in a more human-readable format.

Question

Explode the user activity into a table with one row per day and a column for each user, showing whether they were active.

The number of rows to show in the output should be the number of days in the activity_history column for the user with the most days.

Just like in the customer churn problem, the last_update column will always have the same date for all users.

The output should have a single row per day with the columns:

active_date as the date of the activity, starting from the last_update date
user_X which is 1 if user_id = X was active on that day, 0 otherwise

...where X is each user_id in the activity table.

Order the output by active_date.

Expand for the DDL

create table user_history (
    user_id          int primary key,
    last_update      date not null,
    activity_history bigint not null,
);
insert into user_history
values
    (1, '2024-06-01',   1056256),
    (2, '2024-06-01', 907289368),
    (3, '2024-06-01', 201335032),
    (4, '2024-06-01',   9769312),
    (5, '2024-06-01', 246247510),
    (6, '2024-06-01', 492660983)
;

The solution can be found at:

decoding-datelist-ints.md

Sample input

user_id	last_update	activity_history
1	2024-03-01	81
2	2024-03-01	2688
3	2024-03-01	13144

with user_history(user_id, last_update, activity_history) as (
    values
        (1, '2024-03-01'::date,    81),
        (2, '2024-03-01'::date,  2688),
        (3, '2024-03-01'::date, 13144)
)

Sample output

active_date	user_1	user_2	user_3
2024-02-17	0	0	1
2024-02-18	0	0	1
2024-02-19	0	1	0
2024-02-20	0	0	0
2024-02-21	0	1	1
2024-02-22	0	0	1
2024-02-23	0	1	0
2024-02-24	1	0	1
2024-02-25	0	0	0
2024-02-26	1	0	1
2024-02-27	0	0	1
2024-02-28	0	0	0
2024-02-29	0	0	0
2024-03-01	1	0	0

solution(active_date, user_1, user_2, user_3) as (
    values
        ('2024-02-17'::date, 0, 0, 1),
        ('2024-02-18'::date, 0, 0, 1),
        ('2024-02-19'::date, 0, 1, 0),
        ('2024-02-20'::date, 0, 0, 0),
        ('2024-02-21'::date, 0, 1, 1),
        ('2024-02-22'::date, 0, 0, 1),
        ('2024-02-23'::date, 0, 1, 0),
        ('2024-02-24'::date, 1, 0, 1),
        ('2024-02-25'::date, 0, 0, 0),
        ('2024-02-26'::date, 1, 0, 1),
        ('2024-02-27'::date, 0, 0, 1),
        ('2024-02-28'::date, 0, 0, 0),
        ('2024-02-29'::date, 0, 0, 0),
        ('2024-03-01'::date, 1, 0, 0)
)

Hint 1

Use a recursive CTE to construct a table with the dates.

Hint 2

Use the "bitwise and" operation to determine if a user was active on a given day.