Datelist integers for true/false attribute history

datelist-integers.sql

/*
    Datelist integers for true/false attribute history

    DuckDB version: 0.10.2

    Bill Wallis, 2024-06-19
*/


select version();


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-19',   1056256),
    (2, '2024-06-19', 907289368),
    (3, '2024-06-19', 201335032),
    (4, '2024-06-19',   9769312),
    (5, '2024-06-19', 246247510),
    (6, '2024-06-19', 492660983)
;


/* Users who were active 5 days ago */
select user_id
from user_history
where activity_history & power(2, 5)::int > 0
;


/* Number of active days out of the last 7 days for each user */
select
    user_id,
    bit_count(activity_history & (power(2, 7)::int - 1)) as active_days
from user_history
;


/* Number of users who churned this week */
select count(*) as churned_users
from user_history
where 1=1
    /* No activity this week... */
    and activity_history & (power(2, 7)::int - 1) = 0
    /* ...but some activity last week */
    and (activity_history >> 7) & (power(2, 14)::int - 1) > 0
;

This is part of the following LinkedIn post:

https://www.linkedin.com/posts/bill-wallis_sql-analyticsengineering-dataengineering-activity-7209175507471347712-_8Sw