Module 6: Accumulating And Cumulative Patterns

Computing 7- and 30-day actives per store

Next →

Description

fact_store_activity already exists, complete and verified, since lesson 6. This lesson queries it, without touching fact_orders again, to answer the business question that motivated the whole pattern: which store was most active in the last 7 and 30 days? How much revenue did each one generate in that window? Does the 30-day array already "pay for itself" with only a week of history, or does it still not say anything the 7-day one doesn't? This lesson answers all three, with executed evidence.

Connection to the module. This is the module's second pattern's analysis lesson — the equivalent, for fact_store_activity, of what lesson 3 did with fact_sessions's funnel. Lesson 8 integrates both analyses into the closing project.

Worked example: the store ranking as of 2026-08-09

With fact_store_activity already loaded (21 rows, from lesson 6), the first question — which store was most active — gets answered by ordering the last day's snapshot by active_days_7d:

# store_activity_analysis.py -- continues on top of fact_store_activity already built (lesson 6)
print("=== Store ranking by active_days_7d, snapshot for 2026-08-09 ===")
con.sql("""
    SELECT store_id, activity_date, active_days_7d, active_days_30d,
           ROUND(list_sum(revenue_array_7d), 2) AS revenue_7d
    FROM fact_store_activity
    WHERE activity_date = DATE '2026-08-09'
    ORDER BY active_days_7d DESC, revenue_7d DESC
""").show(max_width=250)

What to expect.

=== Store ranking by active_days_7d, snapshot for 2026-08-09 ===
┌──────────┬───────────────┬────────────────┬─────────────────┬────────────┐
│ store_id │ activity_date │ active_days_7d │ active_days_30d │ revenue_7d │
│ varchar  │     date      │     int32      │      int32      │   double   │
├──────────┼───────────────┼────────────────┼─────────────────┼────────────┤
│ S02      │ 2026-08-09    │              7 │               7 │       38.8 │
│ S01      │ 2026-08-09    │              7 │               7 │       38.3 │
│ S03      │ 2026-08-09    │              6 │               6 │      29.05 │
└──────────┴───────────────┴────────────────┴─────────────────┴────────────┘

S02 (Lima) tops the ranking, although S01 (Bogota) and S02 tie on active_days_7d = 7 — the tiebreak by revenue_7d (38.8 against 38.3) gives S02 first place by 50 cents. S03 (Santiago) comes in third, with one fewer active day (6, not 7) and the lowest revenue of the three — the same no-sales day (2026-08-05) you already identified in lesson 6 is, precisely, the cause of both differences.

Recalculating active_days without the saved column, with list_filter

active_days_7d was saved as a precalculated column in lesson 6 (counted in Python, with sum(1 for v in arr if v > 0)). This lesson verifies it independently, recalculating it directly in SQL with list_filter — DuckDB's array function that applies a lambda function to each element and returns only the ones that meet the condition — combined with len():

print("=== active_days_7d saved vs recalculated with list_filter + len, in pure SQL ===")
con.sql("""
    SELECT store_id, activity_date, active_days_7d AS saved,
           len(list_filter(revenue_array_7d, x -> x > 0)) AS recalculated
    FROM fact_store_activity
    WHERE activity_date = DATE '2026-08-09'
    ORDER BY store_id
""").show(max_width=250)

mismatch = con.sql("""
    SELECT COUNT(*) FROM fact_store_activity
    WHERE active_days_7d != len(list_filter(revenue_array_7d, x -> x > 0))
""").fetchone()[0]
print(f"\nrows where saved active_days_7d differs from recalculated (all 21 rows, not just the snapshot): {mismatch}")

What to expect.

=== active_days_7d saved vs recalculated with list_filter + len, in pure SQL ===
┌──────────┬───────────────┬───────┬──────────────┐
│ store_id │ activity_date │ saved │ recalculated │
│ varchar  │     date      │ int32 │    int64     │
├──────────┼───────────────┼───────┼──────────────┤
│ S01      │ 2026-08-09    │     7 │            7 │
│ S02      │ 2026-08-09    │     7 │            7 │
│ S03      │ 2026-08-09    │     6 │            6 │
└──────────┴───────────────┴───────┴──────────────┘

rows where saved active_days_7d differs from recalculated (all 21 rows, not just the snapshot): 0

Zero differences, over the complete 21 rows, not just the three in the final snapshot. list_filter(revenue_array_7d, x -> x > 0) walks the array and returns a new, shorter array with only the values greater than zero; len(...) counts how many remained. This is the "no saved column" alternative to the active_days_7d lesson 6 computed in Python — useful for confirming the precalculated column is correct, and also as a reusable pattern if you ever need that count over an array that doesn't have a dedicated column.

Is the 30-day array still worth it?

The third question — whether the 30-day array "says something different" from the 7-day one with this amount of history — gets answered with a direct query on how many elements each 30-day array actually has:

print("=== How many elements does revenue_array_30d have today (should be <= 30) ===")
con.sql("""
    SELECT store_id, activity_date, len(revenue_array_30d) AS elements_in_array_30d
    FROM fact_store_activity
    WHERE activity_date = DATE '2026-08-09'
    ORDER BY store_id
""").show(max_width=200)

What to expect.

=== How many elements does revenue_array_30d have today (should be <= 30) ===
┌──────────┬───────────────┬───────────────────────┐
│ store_id │ activity_date │ elements_in_array_30d │
│ varchar  │     date      │         int64         │
├──────────┼───────────────┼───────────────────────┤
│ S01      │ 2026-08-09    │                     7 │
│ S02      │ 2026-08-09    │                     7 │
│ S03      │ 2026-08-09    │                     7 │
└──────────┴───────────────┴───────────────────────┘

All three stores have exactly 7 elements in their 30-day array — the same number as in the 7-day one — because Kiosko, in this dataset, only has one full week of history. The honest answer to the question is: not yet, with only 7 days of data available, active_days_30d and active_days_7d are necessarily identical for all three stores — the 30-day array only starts to diverge from the 7-day one starting on the eighth day of operation, when the 7-day one starts discarding the oldest day while the 30-day one keeps accumulating. Declaring this explicitly, instead of hiding it, is part of this guide's discipline: an identical number between two differently named columns isn't a calculation error, it's an honest consequence of how much history is available.

Diagram: why 7d and 30d converge with little history

Day 1   Day 2   Day 3   Day 4   Day 5   Day 6   Day 7   Day 8 (doesn't exist in this dataset)
  │       │       │       │       │       │       │        │
  └───────┴───────┴───────┴───────┴───────┴───────┘        │
              7-day array                                    │
              (7 elements on day 7, cap reached)              │
  └───────┴───────┴───────┴───────┴───────┴───────┴─── ... ──┘
              30-day array
              (7 elements on day 7 -- the SAME number,
               because there isn't 30 days of history yet
               to fill it. Only on day 8 would the 7d
               array start DISCARDING day 1 while the
               30d one STILL keeps it -- that's where they diverge.)

Common mistakes

Interpreting active_days_7d == active_days_30d as a pipeline bug. What happens: someone, seeing both columns give the same number across this dataset's 21 rows, assumes the 30-day column "isn't working" or that there's a bug copying one value into the other. Why it happens: two differently named columns that always show the same value seem, at first glance, redundant or broken. How to spot it: if you check lesson 6's code, you'll confirm both columns are calculated with independent logic (new_array_7d and new_array_30d, each with its own trim) — there's no place where one gets copied from the other. How to fix it: the equality is a mathematical consequence of having less than 7 days of history, not a bug — verify by extending this lesson's exercise 1 to more than 7 simulated days, and you'll see the two columns start to diverge naturally.

Using active_days_7d from a row that isn't the most recent to answer "how many active days did the store have this week." What happens: someone queries active_days_7d from the 2026-08-05 row (day 3 of the week) expecting it to answer the question about the full week. Why it happens: every row in fact_store_activity has a column with the same name, and it's easy to forget that each row answers "the last 7 days up to that specific date," not "the last 7 days from today." How to spot it: if your question is about "Kiosko's full week," and your query doesn't filter by activity_date = DATE '2026-08-09' (the last available day), you're looking at an incomplete window. How to fix it: to answer any question about "the most recent activity," always filter by the highest available date (MAX(activity_date) or a known fixed date, as this lesson does with 2026-08-09) — each row in fact_store_activity is a valid snapshot for its own date, not for any other.

Comparing revenue_7d between stores without considering that the ranking can change depending on the tiebreak criterion. What happens: someone reports "S02 is the most active store" based only on active_days_7d, without mentioning that S01 has exactly the same number and that the tiebreak depends on a different column (revenue_7d). Why it happens: a ranking with a single criterion feels simpler to communicate than one with an explicit tiebreak. How to spot it: if your "most active store" report doesn't mention that there's a technical tie in active_days_7d between S01 and S02, you're oversimplifying an answer that actually has two nearly equal stores. How to fix it: when a ranking depends on a tiebreak criterion, state it explicitly in the report — "S02 leads on revenue, tied with S01 on active days" is a more honest statement than "S02 is the most active" on its own.

Exercises

Exercise 1 — Simulate an eighth day and watch active_days_7d and active_days_30d start to diverge. Add a fictitious row for S01 on 2026-08-10 with daily_revenue = 0.0 (a day with no sales), applying the same list_prepend + trim mechanism from lesson 6. Compare active_days_7d and active_days_30d for that new row.

See solution 
prev = con.sql("""
    SELECT revenue_array_7d, revenue_array_30d FROM fact_store_activity
    WHERE store_id = 'S01' AND activity_date = DATE '2026-08-09'
""").fetchone()

new_7d = ([0.0] + list(prev[0]))[:7]    # the 8.45 (2026-08-03) LEAVES the 7d array
new_30d = ([0.0] + list(prev[1]))[:30]  # but STAYS in the 30d array -- there's still room for 30

active_7d = sum(1 for v in new_7d if v > 0)
active_30d = sum(1 for v in new_30d if v > 0)

con.execute(
    "INSERT INTO fact_store_activity VALUES ('S01', DATE '2026-08-10', 0.0, ?, ?, ?, ?)",
    [new_7d, active_7d, new_30d, active_30d],
)
print(con.sql("""
    SELECT activity_date, revenue_array_7d, active_days_7d, len(revenue_array_30d) AS len_30d, active_days_30d
    FROM fact_store_activity WHERE store_id = 'S01' AND activity_date = DATE '2026-08-10'
"""))

Expected output:

┌───────────────┬──────────────────────────────────────────┬────────────────┬─────────┬─────────────────┐
│ activity_date │              revenue_array_7d             │ active_days_7d │ len_30d │ active_days_30d │
│     date      │                  double[]                 │     int32      │  int64  │      int32      │
├───────────────┼──────────────────────────────────────────┼────────────────┼─────────┼─────────────────┤
│ 2026-08-10    │ [0.0, 1.1, 13.05, 5.0, 6.7, 0.55, 3.45]  │              6 │       8 │               7 │
└───────────────┴──────────────────────────────────────────┴────────────────┴─────────┴─────────────────┘

Exactly what this lesson's diagram predicts: revenue_array_7d trimmed off the oldest value (8.45, from 2026-08-03) to make room for the new day, and active_days_7d dropped from 7 to 6 (the new day has revenue 0.0). revenue_array_30d, on the other hand, still has room (8 elements, well below the cap of 30), so it keeps the 8.45 and its active_days_30d stays at 7. This is exactly the point where the two windows start telling different stories.

Exercise 2 — Find the store with the highest average revenue per active day (not per calendar day). Using list_sum(revenue_array_7d) / active_days_7d, calculate the average revenue per active day (not per the 7 calendar days) for each store, and compare with the simple average (list_sum / 7).

See solution 
print(con.sql("""
    SELECT store_id,
           ROUND(list_sum(revenue_array_7d) / active_days_7d, 2) AS avg_per_active_day,
           ROUND(list_sum(revenue_array_7d) / 7, 2) AS avg_per_calendar_day
    FROM fact_store_activity
    WHERE activity_date = DATE '2026-08-09'
    ORDER BY avg_per_active_day DESC
"""))

Expected output:

┌──────────┬─────────────────────┬──────────────────────┐
│ store_id │ avg_per_active_day  │ avg_per_calendar_day │
│ varchar  │       double        │        double        │
├──────────┼─────────────────────┼───────────────────────┤
│ S03      │                4.84 │                  4.15 │
│ S02      │                5.54 │                  5.54 │
│ S01      │                5.47 │                  5.47 │
└──────────┴─────────────────────┴───────────────────────┘

S03 is the only store where the two averages differ (4.84 against 4.15), because it's the only one with a no-sales day inside the window — dividing by active_days_7d (6) instead of by 7 gives S03 a fairer average per day actually worked, instead of diluting its revenue across a day that contributed nothing. S01 and S02, with all 7 days active, show no difference between the two averages — dividing by 6 or by 7 gives the same thing when active_days_7d = 7.

Exercise 3 — Explain, in your own words, when active_days_30d would start being genuinely more useful than active_days_7d for Kiosko. Without looking at lesson 6's deep dive, describe in 2-3 sentences a business scenario where the 30-day window would reveal something the 7-day one can't see.

See solution

The 7-day window is sensitive to short-term fluctuations — a slow weekend can make an "active" store look less active that specific week — while the 30-day one smooths out those fluctuations and reveals a more stable trend. A concrete scenario: if S03 had two or three slow days scattered across a month (not consecutive), its active_days_7d would fluctuate week to week depending on which days those slow days fall within each 7-day window, while active_days_30d would give a more consistent read of the store's real pattern across the full month, without a single bad day dominating the number. That's precisely why a real business — not just Kiosko — keeps both windows in parallel: the 7-day one to react quickly, the 30-day one to see the underlying trend.

Summary and next step

This lesson queried fact_store_activity without touching fact_orders again, answering three business questions: S02 leads the activity ranking (tied on active days with S01, but with more revenue), active_days_7d counts days with real sales — not days elapsed, confirmed with list_filter recalculated independently and zero differences against the saved column — and active_days_30d doesn't yet contribute information different from active_days_7d because Kiosko only has a week of history, something this lesson declared explicitly instead of hiding.

Before moving on you should be able to: interpret a ranking with an explicit tie and tiebreak; write from memory the list_filter(arr, x -> x > 0) + len(...) pattern for counting positive values in an array; and explain why two differently named columns can, legitimately, show the same value without that being an error.

Lesson 8 — the module's closing project — integrates fact_sessions and fact_store_activity into a single flow verified with assert, documenting both patterns in a formal structure, exactly as modules 1 and 5 closed.

Resources

← PreviousRolling windows with array columnsNext →Mini-project: Kiosko's funnel and cumulative activity