task Rolling windows & frames · medium
Seven-day rolling active shoppers
For each date in app_logins, return login_date, active_shoppers (different shoppers who logged in that day) and rolling_7d: the sum of active_shoppers over that date and the 6 calendar days before it.
what to return
- Columns
login_dateactive_shoppersrolling_7din this order- Rows
- 20 rows · any order
- Watch for
- A shopper logging in twice in a day counts once.
- Days with no logins contribute 0 — the window is 7 calendar days, not 7 rows.
- Skill
- Use a RANGE frame so missing days count as zero. Refresher: Daily active users & engagement →
expected result
Your query should return exactly these columns and values. Run it and compare.
expected result20 rows · any order
| login_date | active_shoppers | rolling_7d |
|---|---|---|
| 2025-02-27 | 1 | 1 |
| 2025-02-28 | 1 | 2 |
| 2025-03-01 | 3 | 5 |
first 3 of 20 rows
data you can query
Sample rows from each table. Each column shows its type underneath; → marks a link to another table.
app_logins2 columns · 35 rows
| shopper_idinteger | login_datetext |
|---|---|
| 1 | 2025-03-01 |
| 1 | 2025-03-02 |
| 1 | 2025-03-03 |
| 1 | 2025-03-04 |
| 1 | 2025-03-07 |
showing 5 of 35 rows
solution.sql
Hints and the answer are always available. Using them counts the question as solved with help.