Find max value of previous day, second previous day from moving window using bigquery

Question

I have a requirement to find the max value of the previous day (val_prev_day), the second previous day (val_sec_prev_day ), third previous day (val_third_prev_day) from the corresponding list of raw values (val_raw) for each day as shown in the below example.

Answer 1

I am assuming dataset schema, but basically you can shift days in the subqueries and do LEFT OUTER JOIN.

WITH
dataset AS (
    SELECT           1 as day,   0 as val_raw,
    UNION ALL SELECT 1 as day,  10 as val_raw,
    UNION ALL SELECT 1 as day, 110 as val_raw,
    UNION ALL SELECT 2 as day,  30 as val_raw,
    UNION ALL SELECT 2 as day,  70 as val_raw,
    UNION ALL SELECT 2 as day,  90 as val_raw,
    UNION ALL SELECT 3 as day,   0 as val_raw,
    UNION ALL SELECT 3 as day,   5 as val_raw,
    UNION ALL SELECT 3 as day,   5 as val_raw,
    UNION ALL SELECT 4 as day,   0 as val_raw,
    UNION ALL SELECT 4 as day,   5 as val_raw,
    UNION ALL SELECT 4 as day,  10 as val_raw,
),
shift_one_day_max AS (
    SELECT day + 1 as day, max(val_raw) as max_val,
    FROM dataset
    GROUP BY day
),
shift_two_day_max AS (
    SELECT day + 2 as day, max(val_raw) as max_val,
    FROM dataset
    GROUP BY day
)
SELECT *
FROM dataset
LEFT OUTER JOIN shift_one_day_max USING (day)
LEFT OUTER JOIN shift_two_day_max USING (day)
;

c.f. I omitted event_ts since it's not affecting the operations.