Rolling windows with array columns

Description

This lesson scales lesson 5's mechanism — list_prepend(todays_value, yesterdays_array) — to Kiosko's three stores and the full seven days of the week (2026-08-03 through 2026-08-09), adding the piece the minimal example didn't need yet: trimming the array to a fixed size with slicing, so that revenue_array_7d never has more than 7 elements and revenue_array_30d never has more than 30. At the end, you build fact_store_activity in full — 21 rows — and verify it twice: against each store's already-known weekly revenue, and against a second version, calculated from scratch with a SQL window function instead of the incremental loop.

Connection to the module. This lesson is the central build of the module's second pattern, just as lesson 3 was for the first. Lesson 7 uses this same table to answer the real business question: 7- and 30-day actives by store.

Worked example: fact_store_activity, day by day, store by store

Each store's daily revenue is calculated by aggregating fact_orders — the same 40-row table with 106.15 total revenue from module 1 — by store_id and by day. The loop that follows walks through the week's seven days, and within each day, the three stores, applying at each step lesson 5's same list_prepend mechanism, with two differences: it also builds the 30-day array in parallel, and it trims both arrays to their maximum size with DuckDB slicing (arr[1:7] to keep only the first 7 elements, arr[1:30] for the first 30).

# fact_store_activity_build.py -- continues on top of fact_orders already loaded (module 1)
import duckdb

con.execute("""
    CREATE TABLE fact_store_activity (
        store_id VARCHAR,
        activity_date DATE,
        daily_revenue DOUBLE,
        revenue_array_7d DOUBLE[],
        active_days_7d INTEGER,
        revenue_array_30d DOUBLE[],
        active_days_30d INTEGER
    )
""")

STORES = ["S01", "S02", "S03"]
DAYS = ["2026-08-03", "2026-08-04", "2026-08-05", "2026-08-06", "2026-08-07", "2026-08-08", "2026-08-09"]

for day in DAYS:
    for store_id in STORES:
        daily_revenue = con.sql(f"""
            SELECT COALESCE(ROUND(SUM(revenue), 2), 0.0)
            FROM fact_orders
            WHERE store_id = '{store_id}' AND CAST(order_ts AS DATE) = DATE '{day}'
        """).fetchone()[0]

        prev = con.sql(f"""
            SELECT revenue_array_7d, revenue_array_30d
            FROM fact_store_activity
            WHERE store_id = '{store_id}'
            ORDER BY activity_date DESC
            LIMIT 1
        """).fetchone()

        if prev is None:
            new_array_7d = [daily_revenue]
            new_array_30d = [daily_revenue]
        else:
            prev_7d, prev_30d = prev
            new_array_7d = ([daily_revenue] + list(prev_7d))[:7]    # equivalent to arr[1:7]
            new_array_30d = ([daily_revenue] + list(prev_30d))[:30]  # equivalent to arr[1:30]

        active_7d = sum(1 for v in new_array_7d if v > 0)
        active_30d = sum(1 for v in new_array_30d if v > 0)

        con.execute(
            "INSERT INTO fact_store_activity VALUES (?, ?, ?, ?, ?, ?, ?)",
            (store_id, day, daily_revenue, new_array_7d, active_7d, new_array_30d, active_30d),
        )

print(f"fact_store_activity rows: {con.sql('SELECT COUNT(*) FROM fact_store_activity').fetchone()[0]}\n")
con.sql("""
    SELECT store_id, activity_date, daily_revenue, revenue_array_7d, active_days_7d
    FROM fact_store_activity ORDER BY store_id, activity_date
""").show(max_width=250)

A note on the trim: ([daily_revenue] + list(prev_7d))[:7] in Python is the exact equivalent of DuckDB's revenue_array_7d[1:7] slicing — keeping, at most, the array's first 7 elements — this lesson does it in Python because the loop builds each row with an individual INSERT, but further below the lesson includes the 100% SQL version with the same trim, so you can see both syntaxes. DuckDB's documentation confirms array slicing uses 1-based indices (not 0-based), with the form list[begin:end] — exactly the syntax you're going to use in this same lesson's following section.

What to expect.

fact_store_activity rows: 21

┌──────────┬───────────────┬───────────────┬──────────────────────────────────────────┬────────────────┐
│ store_id │ activity_date │ daily_revenue │             revenue_array_7d             │ active_days_7d │
│ varchar  │     date      │    double     │                 double[]                 │     int32      │
├──────────┼───────────────┼───────────────┼──────────────────────────────────────────┼────────────────┤
│ S01      │ 2026-08-03    │          8.45 │ [8.45]                                   │              1 │
│ S01      │ 2026-08-04    │          3.45 │ [3.45, 8.45]                             │              2 │
│ S01      │ 2026-08-05    │          0.55 │ [0.55, 3.45, 8.45]                       │              3 │
│ S01      │ 2026-08-06    │           6.7 │ [6.7, 0.55, 3.45, 8.45]                  │              4 │
│ S01      │ 2026-08-07    │           5.0 │ [5.0, 6.7, 0.55, 3.45, 8.45]             │              5 │
│ S01      │ 2026-08-08    │         13.05 │ [13.05, 5.0, 6.7, 0.55, 3.45, 8.45]      │              6 │
│ S01      │ 2026-08-09    │           1.1 │ [1.1, 13.05, 5.0, 6.7, 0.55, 3.45, 8.45] │              7 │
│ S02      │ 2026-08-03    │           3.9 │ [3.9]                                    │              1 │
│ S02      │ 2026-08-04    │           4.6 │ [4.6, 3.9]                               │              2 │
│ S02      │ 2026-08-05    │           9.0 │ [9.0, 4.6, 3.9]                          │              3 │
│ S02      │ 2026-08-06    │          3.15 │ [3.15, 9.0, 4.6, 3.9]                    │              4 │
│ S02      │ 2026-08-07    │          7.25 │ [7.25, 3.15, 9.0, 4.6, 3.9]              │              5 │
│ S02      │ 2026-08-08    │           9.7 │ [9.7, 7.25, 3.15, 9.0, 4.6, 3.9]         │              6 │
│ S02      │ 2026-08-09    │           1.2 │ [1.2, 9.7, 7.25, 3.15, 9.0, 4.6, 3.9]    │              7 │
│ S03      │ 2026-08-03    │           3.5 │ [3.5]                                    │              1 │
│ S03      │ 2026-08-04    │           7.8 │ [7.8, 3.5]                               │              2 │
│ S03      │ 2026-08-05    │           0.0 │ [0.0, 7.8, 3.5]                          │              2 │
│ S03      │ 2026-08-06    │           1.2 │ [1.2, 0.0, 7.8, 3.5]                     │              3 │
│ S03      │ 2026-08-07    │           5.8 │ [5.8, 1.2, 0.0, 7.8, 3.5]                │              4 │
│ S03      │ 2026-08-08    │           9.1 │ [9.1, 5.8, 1.2, 0.0, 7.8, 3.5]           │              5 │
│ S03      │ 2026-08-09    │          1.65 │ [1.65, 9.1, 5.8, 1.2, 0.0, 7.8, 3.5]     │              6 │
└──────────┴───────────────┴───────────────┴──────────────────────────────────────────┴────────────────┘

21 rows — 3 stores × 7 days, exactly. Stop on S03's 2026-08-05 row: daily_revenue = 0.0, and its active_days_7d stays at 2 instead of rising to 3 — the first evidence that active_days_7d does not count elapsed days, it counts days with real activity. You're going to come back to this exact number in lesson 7.

Verifying against the already-known weekly revenue

list_sum(revenue_array_7d) on each store's last row (2026-08-09, the seventh and last day) should match, exactly, the total weekly revenue you already know from module 1 — 106.15 split into S01: 38.3, S02: 38.8, S03: 29.05:

print("=== Snapshot for 2026-08-09: list_sum(revenue_array_7d) vs known weekly revenue ===")
con.sql("""
    SELECT store_id, ROUND(list_sum(revenue_array_7d), 2) AS sum_7d, active_days_7d
    FROM fact_store_activity
    WHERE activity_date = DATE '2026-08-09'
    ORDER BY store_id
""").show(max_width=200)

print("=== Weekly revenue by store, calculated directly from fact_orders ===")
con.sql("""
    SELECT store_id, ROUND(SUM(revenue), 2) AS total_week_revenue
    FROM fact_orders GROUP BY store_id ORDER BY store_id
""").show(max_width=200)

What to expect.

=== Snapshot for 2026-08-09: list_sum(revenue_array_7d) vs known weekly revenue ===
┌──────────┬────────┬────────────────┐
│ store_id │ sum_7d │ active_days_7d │
│ varchar  │ double │     int32      │
├──────────┼────────┼────────────────┤
│ S01      │   38.3 │              7 │
│ S02      │   38.8 │              7 │
│ S03      │  29.05 │              6 │
└──────────┴────────┴────────────────┘

=== Weekly revenue by store, calculated directly from fact_orders ===
┌──────────┬────────────────────┐
│ store_id │ total_week_revenue │
│ varchar  │       double       │
├──────────┼────────────────────┤
│ S01      │               38.3 │
│ S02      │              38.8 │
│ S03      │              29.05 │
└──────────┴────────────────────┘

38.3, 38.8, 29.05 — identical in both queries. This isn't a coincidence: by construction, the seventh day of a 7-element window contains exactly the seven days of the week, so its sum has to match that week's total revenue. If at any point in your implementation these two numbers didn't match, it would be direct evidence of a bug in the accumulation mechanism — a day counted twice, or one lost in the trim — not a real business difference.

Going deeper: the 100% SQL version, for comparison

This lesson's Python loop builds fact_store_activity incrementally — each row is calculated from the previous row, without rereading past days, exactly the cumulative table design's production mechanism. There's a second way to reach the same result: recalculating everything from scratch with a SQL window function, using ROWS BETWEEN 6 PRECEDING AND CURRENT ROW to capture each row's last 7 days:

con.execute("""
    CREATE TABLE daily_store_revenue AS
    WITH stores AS (SELECT DISTINCT store_id FROM fact_orders),
         days AS (SELECT UNNEST(generate_series(DATE '2026-08-03', DATE '2026-08-09', INTERVAL 1 DAY))::DATE AS activity_date)
    SELECT s.store_id, d.activity_date,
           COALESCE((SELECT ROUND(SUM(f.revenue), 2) FROM fact_orders f
                     WHERE f.store_id = s.store_id AND CAST(f.order_ts AS DATE) = d.activity_date), 0.0) AS daily_revenue
    FROM stores s CROSS JOIN days d
""")

print("=== Pure SQL version: recalculated from scratch with a window function, not incremental ===")
con.sql("""
    SELECT store_id, activity_date, daily_revenue,
           list_reverse(array_agg(daily_revenue) OVER (
               PARTITION BY store_id ORDER BY activity_date
               ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
           )) AS revenue_array_7d_recalculated
    FROM daily_store_revenue
    ORDER BY store_id, activity_date
""").show(max_width=250)

What to expect (excerpt; the full table has 21 rows, identical in shape to the table shown above).

=== Pure SQL version: recalculated from scratch with a window function, not incremental ===
┌──────────┬───────────────┬───────────────┬──────────────────────────────────────────┐
│ store_id │ activity_date │ daily_revenue │       revenue_array_7d_recalculated       │
│ varchar  │     date      │    double     │                 double[]                 │
├──────────┼───────────────┼───────────────┼──────────────────────────────────────────┤
│ S01      │ 2026-08-03    │          8.45 │ [8.45]                                   │
│ S01      │ 2026-08-04    │          3.45 │ [3.45, 8.45]                             │
│ S01      │ 2026-08-05    │          0.55 │ [0.55, 3.45, 8.45]                       │
│ S01      │ 2026-08-06    │           6.7 │ [6.7, 0.55, 3.45, 8.45]                  │
│ S01      │ 2026-08-07    │           5.0 │ [5.0, 6.7, 0.55, 3.45, 8.45]             │
│ S01      │ 2026-08-08    │         13.05 │ [13.05, 5.0, 6.7, 0.55, 3.45, 8.45]      │
│ S01      │ 2026-08-09    │           1.1 │ [1.1, 13.05, 5.0, 6.7, 0.55, 3.45, 8.45] │
└──────────┴───────────────┴───────────────┴──────────────────────────────────────────┘
  (21 rows total -- S02 and S03 follow the same pattern)

And the formal verification, comparing both versions row by row:

mismatches = con.sql("""
    WITH recalculated AS (
        SELECT store_id, activity_date,
               list_reverse(array_agg(daily_revenue) OVER (
                   PARTITION BY store_id ORDER BY activity_date
                   ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
               )) AS revenue_array_7d_recalc
        FROM daily_store_revenue
    )
    SELECT COUNT(*) AS mismatches
    FROM fact_store_activity f
    JOIN recalculated r ON f.store_id = r.store_id AND f.activity_date = r.activity_date
    WHERE f.revenue_array_7d != r.revenue_array_7d_recalc
""").fetchone()[0]
print(f"mismatches between the incremental version and the recalculated one: {mismatches}")
assert mismatches == 0

mismatches between the incremental version and the recalculated one: 0

Zero differences, again. This confirms the same idea you already saw in lesson 4 with fact_sessions: how you get to the result (incremental, day by day, or recalculated from scratch with a SQL window) doesn't change what result you get, as long as the pattern is well defined. The real difference between the two approaches is computational cost at scale: this script's incremental version never touches a daily_revenue from several days ago again — it only reads yesterday's row —; the window-function version, on the other hand, recalculates each row's full array every time it runs, rereading all the available history. With 7 days and 3 stores, the difference is invisible. With years of history and thousands of stores — the real scale that motivated Zach Wilson's pattern at Meta — that difference is, literally, what separates a job that runs in minutes from one that runs in hours.

Diagram: incremental vs recalculated

flowchart TD
    subgraph inc["Incremental (real production)"]
        A["Yesterday's row\n(array ALREADY calculated)"] --> B["+ today's data"]
        B --> C["Today's row\nNEVER reread the full fact_orders"]
    end

    subgraph win["Window function (recalculation)"]
        D["Full fact_orders,\nevery time it runs"] --> E["ROWS BETWEEN 6 PRECEDING\nAND CURRENT ROW"]
        E --> F["Array recalculated\nfrom scratch, every row"]
    end

    C -.->|"0 differences, verified"| F

Common mistakes

Forgetting the size trim and letting the array grow without limit. What happens: someone implements list_prepend without the [:7] (or the equivalent list_slice/slicing in pure SQL), and the array keeps growing one element per day indefinitely. Why it happens: in a 7-day week, the 7-day array never actually needs the trim — the seventh day happens to also be the last day of available data — so the bug can go unnoticed in this specific dataset. How to spot it: if you extended this exercise to an eighth day of data, and revenue_array_7d had 8 elements instead of 7, the trim isn't working. How to fix it: always apply the trim — (new_array)[:7] in Python, or arr[1:7] in pure DuckDB SQL — as part of the same step that prepends the new value, never as a separate, optional step.

Confusing active_days_7d with len(revenue_array_7d). What happens: someone computes active_days_7d as the array's length, instead of counting how many of the array's values are greater than zero. Why it happens: in most of this dataset's rows, both numbers coincide — the store had sales almost every day — so the difference only shows up in one specific case. How to spot it: S03's 2026-08-05 row has len(revenue_array_7d) = 3 but active_days_7d = 2 — if your implementation gives 3 for both, you confused "days elapsed" with "days with real activity." How to fix it: active_days_7d counts values greater than zero (sum(1 for v in arr if v > 0) in Python, or len(list_filter(arr, x -> x > 0)) in pure SQL — the exact syntax you're going to use in lesson 7), never the array's raw length.

Assuming daily_revenue = 0.0 means data is missing. What happens: someone sees daily_revenue = 0.0 for S03 on 2026-08-05 and assumes it's a loading error — a row that should have a value and doesn't. Why it happens: a zero can easily be confused with a missing value, especially in columns where almost all other values are positive. How to spot it: if you check fact_orders for S03 on that specific date, you're going to confirm that, indeed, there's no order registered that day for that store — the zero is correct, not an error. How to fix it: COALESCE(ROUND(SUM(revenue), 2), 0.0) in the daily_revenue query is calculated on purpose to turn "no row to sum" into an explicit 0.0, instead of leaving a NULL that would break list_sum and the rest of the array calculations — a real zero, with business meaning ("this store sold nothing that day"), not a missing value.

Exercises

Exercise 1 — Confirm that revenue_array_30d never exceeded 7 elements this week of data. Without looking at lesson 7 yet, write a query that shows len(revenue_array_30d) for each store's last row (2026-08-09), and explain why no value exceeds 7.

print(con.sql("""
    SELECT store_id, len(revenue_array_30d) AS elements_in_array_30d
    FROM fact_store_activity
    WHERE activity_date = DATE '2026-08-09'
    ORDER BY store_id
"""))

┌──────────┬───────────────────────┐
│ store_id │ elements_in_array_30d │
│ varchar  │         int64         │
├──────────┼───────────────────────┤
│ S01      │                     7 │
│ S02      │                     7 │
│ S03      │                     7 │
└──────────┴───────────────────────┘

Exercise 2 — Verify that S01 was the only store with activity all 7 full days. Using fact_store_activity, confirm which stores have active_days_7d = 7 in the 2026-08-09 row, and which one doesn't.

print(con.sql("""
    SELECT store_id, active_days_7d,
           CASE WHEN active_days_7d = 7 THEN 'active all 7 days' ELSE 'had at least one day with no sales' END AS status
    FROM fact_store_activity
    WHERE activity_date = DATE '2026-08-09'
    ORDER BY store_id
"""))

┌──────────┬────────────────┬────────────────────────────────────┐
│ store_id │ active_days_7d │               status               │
│ varchar  │     int32      │              varchar               │
├──────────┼────────────────┼────────────────────────────────────┤
│ S01      │              7 │ active all 7 days                  │
│ S02      │              7 │ active all 7 days                  │
│ S03      │              6 │ had at least one day with no sales │
└──────────┴────────────────┴────────────────────────────────────┘

Exercise 3 — Explain why the incremental version and the window-function version give the same result, even though they process data in a completely different order. In 2-3 sentences, describe why the calculation order (incremental day by day, or recalculated all at once) doesn't affect the final result, as long as the input data is the same.

Summary and next step

This lesson built fact_store_activity in full: 21 rows (3 stores × 7 days), with revenue_array_7d and revenue_array_30d columns accumulated day by day with list_prepend and trimmed to their maximum size, verified two ways — against each store's already-known weekly revenue (38.3/38.8/29.05), and against a second, 100% SQL implementation with a window function, with zero differences between the two. You also saw, with a real case (S03 on 2026-08-05), that active_days_7d counts days with activity, not days elapsed — a zero in daily_revenue is a valid data point, not a missing one.

Before moving on you should be able to: explain why the size trim ([:7]/[:30]) is a mandatory part of the mechanism, not an optional detail; distinguish len(array) from active_days; and describe the computational-cost difference between the incremental approach and the window-function one, even though both give the same result.

Lesson 7 uses fact_store_activity as already built to directly answer this module's business question: how many active days did each store have in the last 7 and 30 days, with list_sum and active_days as the central tools, without touching fact_orders again.

Resources

• DataExpert-io — cumulative-table-design repository (Zach Wilson) — the pattern this lesson scales to Kiosko's full population of stores and days. github.com/DataExpert-io/cumulative-table-design. In English.
• DuckDB — documentation on list functions (list_prepend, list_slice, list_sum, list_reverse), the basis of both implementations in this lesson. duckdb.org/docs/current/sql/functions/list. In English.
• DuckDB — documentation on window functions (array_agg with ROWS BETWEEN ... PRECEDING), the basis of this lesson's pure SQL version. duckdb.org/docs/current/sql/functions/window_functions. In English.