Restructuring actions table into a daily snapshot view

Question

Assuming the default status is always "Released" and given a table that contains history of actions taken:

I'd like to have this to be expanded to a daily snapshot view (last month only) like this:

Any advice how this can coded efficiently?

Answer 1

You need to do the following:

1. Generate a sequence of dates. Here are good examples of it for Snowflake.
2. Cross join them with unique IDs.
3. Join the (2) table with actions and use window function to fill the blanks

WITH date_range AS (
    SELECT
        -1 + ROW_NUMBER() OVER(ORDER BY 0) AS i,
        start_date + i AS generated_date 
    FROM (SELECT '2024-06-01'::DATE AS start_date, '2024-07-17'::DATE AS end_date)
    JOIN TABLE(GENERATOR(rowcount => 10000 )) x
    QUALIFY i < 1 + end_date - start_date
),

actions AS (
    SELECT 1 AS id, 'Blocked' AS action, '2024-07-02'::date AS date
    UNION ALL
    SELECT 1 AS id, 'Released' AS action, '2024-07-04'::date AS date
    UNION ALL
    SELECT 1 AS id, 'Blocked' AS action, '2024-07-06'::date AS date
    UNION ALL
    SELECT 2 AS id, 'Blocked' AS action, '2024-06-02'::date AS date
    UNION ALL
    SELECT 2 AS id, 'Released' AS action, '2024-07-06'::date AS date
),

unique_ids AS (
    SELECT DISTINCT id FROM actions
),

dates_with_ids AS (
    SELECT
        generated_date,
        id
    FROM date_range
    CROSS JOIN unique_ids
)

SELECT
    d.generated_date AS date,
    d.id,
    COALESCE(
        LAST_VALUE(a.action) IGNORE NULLS
        OVER(
            PARTITION BY d.id
            ORDER BY d.generated_date
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
            )
        , 'Released'
    ) AS status
FROM dates_with_ids d
LEFT JOIN actions a ON a.id = d.id AND a.date = d.generated_date
ORDER BY d.id, d.generated_date
;