Decoding datelist ints 🔓
Result Set
Regardless of the database, the result set should look like:
| active_date | user_1 | user_2 | user_3 | user_4 | user_5 | user_6 |
|---|---|---|---|---|---|---|
| 2024-05-03 | 0 | 1 | 0 | 0 | 0 | 0 |
| 2024-05-04 | 0 | 1 | 0 | 0 | 0 | 1 |
| 2024-05-05 | 0 | 0 | 1 | 0 | 1 | 1 |
| 2024-05-06 | 0 | 1 | 1 | 0 | 1 | 1 |
| 2024-05-07 | 0 | 1 | 0 | 0 | 1 | 0 |
| 2024-05-08 | 0 | 0 | 0 | 0 | 0 | 1 |
| 2024-05-09 | 0 | 0 | 0 | 1 | 1 | 0 |
| 2024-05-10 | 0 | 0 | 0 | 0 | 0 | 1 |
| 2024-05-11 | 0 | 0 | 0 | 0 | 1 | 0 |
| 2024-05-12 | 1 | 1 | 0 | 1 | 0 | 1 |
| 2024-05-13 | 0 | 0 | 0 | 0 | 1 | 1 |
| 2024-05-14 | 0 | 1 | 0 | 1 | 1 | 1 |
| 2024-05-15 | 0 | 0 | 0 | 0 | 0 | 0 |
| 2024-05-16 | 0 | 0 | 0 | 1 | 1 | 1 |
| 2024-05-17 | 0 | 0 | 0 | 0 | 0 | 0 |
| 2024-05-18 | 0 | 0 | 0 | 0 | 1 | 1 |
| 2024-05-19 | 0 | 1 | 1 | 0 | 1 | 1 |
| 2024-05-20 | 1 | 0 | 0 | 1 | 1 | 0 |
| 2024-05-21 | 1 | 0 | 0 | 0 | 0 | 1 |
| 2024-05-22 | 1 | 0 | 0 | 0 | 0 | 0 |
| 2024-05-23 | 1 | 1 | 0 | 0 | 0 | 0 |
| 2024-05-24 | 0 | 1 | 0 | 1 | 0 | 0 |
| 2024-05-25 | 0 | 0 | 1 | 0 | 0 | 1 |
| 2024-05-26 | 0 | 0 | 1 | 1 | 1 | 1 |
| 2024-05-27 | 0 | 0 | 1 | 1 | 0 | 1 |
| 2024-05-28 | 0 | 1 | 1 | 0 | 1 | 1 |
| 2024-05-29 | 0 | 1 | 1 | 0 | 0 | 0 |
| 2024-05-30 | 0 | 0 | 0 | 0 | 1 | 1 |
| 2024-05-31 | 0 | 0 | 0 | 0 | 1 | 1 |
| 2024-06-01 | 0 | 0 | 0 | 0 | 0 | 1 |
Expand for the DDL
solution(active_date, user_1, user_2, user_3, user_4, user_5, user_6) as (
values
('2024-05-03'::date, 0, 1, 0, 0, 0, 0),
('2024-05-04'::date, 0, 1, 0, 0, 0, 1),
('2024-05-05'::date, 0, 0, 1, 0, 1, 1),
('2024-05-06'::date, 0, 1, 1, 0, 1, 1),
('2024-05-07'::date, 0, 1, 0, 0, 1, 0),
('2024-05-08'::date, 0, 0, 0, 0, 0, 1),
('2024-05-09'::date, 0, 0, 0, 1, 1, 0),
('2024-05-10'::date, 0, 0, 0, 0, 0, 1),
('2024-05-11'::date, 0, 0, 0, 0, 1, 0),
('2024-05-12'::date, 1, 1, 0, 1, 0, 1),
('2024-05-13'::date, 0, 0, 0, 0, 1, 1),
('2024-05-14'::date, 0, 1, 0, 1, 1, 1),
('2024-05-15'::date, 0, 0, 0, 0, 0, 0),
('2024-05-16'::date, 0, 0, 0, 1, 1, 1),
('2024-05-17'::date, 0, 0, 0, 0, 0, 0),
('2024-05-18'::date, 0, 0, 0, 0, 1, 1),
('2024-05-19'::date, 0, 1, 1, 0, 1, 1),
('2024-05-20'::date, 1, 0, 0, 1, 1, 0),
('2024-05-21'::date, 1, 0, 0, 0, 0, 1),
('2024-05-22'::date, 1, 0, 0, 0, 0, 0),
('2024-05-23'::date, 1, 1, 0, 0, 0, 0),
('2024-05-24'::date, 0, 1, 0, 1, 0, 0),
('2024-05-25'::date, 0, 0, 1, 0, 0, 1),
('2024-05-26'::date, 0, 0, 1, 1, 1, 1),
('2024-05-27'::date, 0, 0, 1, 1, 0, 1),
('2024-05-28'::date, 0, 1, 1, 0, 1, 1),
('2024-05-29'::date, 0, 1, 1, 0, 0, 0),
('2024-05-30'::date, 0, 0, 0, 0, 1, 1),
('2024-05-31'::date, 0, 0, 0, 0, 1, 1),
('2024-06-01'::date, 0, 0, 0, 0, 0, 1)
)
Solution
Some SQL solutions per database are provided below.
DuckDB
with recursive
axis(active_date, max_, step) as (
select
(select last_update from user_history limit 1),
(select max(log2(activity_history)) from user_history),
1
union all
select active_date - 1, max_, step + 1
from axis
where step < max_
),
decoded as (
select
user_history.user_id,
axis.active_date,
(activity_history & power(2, axis.step - 1)::int > 0)::int as active_flag
from axis
cross join user_history
)
pivot decoded
on ('user_' || user_id)
using any_value(active_flag)
order by active_date