Data Analyst Interview
Menu
Browse in your language. All mock interviews, preparation sessions and feedback are in English only.

Rolling Seven-Day Distinct Customers in SQL

Quick answer: A rolling seven-day distinct count must deduplicate customer IDs inside each date’s seven-day window. Do not add daily distinct counts, and do not reuse an all-time first-appearance running sum: a rolling window removes older activity as it advances.

Define the date window before writing SQL

This synthetic example counts identified customers with at least one qualifying activity on the report date or the preceding six calendar dates. It uses US, UK and German market labels, globally unique customer IDs and ISO date strings. Dates have already been assigned using the agreed reporting timezone. The source is complete for every date needed by the fixture, including dates with no activity.

This is an illustrative customer-activity metric, not a GA4 active-user calculation. An activity could be a qualifying order, visit or product action only if your metric definition says so. Do not mix those definitions when comparing dashboards.

Build a fixture with repeat activity and a customer leaving the window

CREATE TABLE activity(day TEXT, customer_id TEXT, market TEXT);
INSERT INTO activity VALUES
 ('2026-10-30','A','US'),
 ('2026-10-31','B','UK'),
 ('2026-11-01','A','US'),
 ('2026-11-01','A','US'),
 ('2026-11-03','C','DE'),
 ('2026-11-04',NULL,'DE'),
 ('2026-11-05','B','UK');

A appears on October 30 and November 1, including a duplicate event on November 1. B appears on October 31 and November 5. C appears on November 3. One November 4 activity has no customer ID. The report extends to November 8, by which point A’s latest activity has left the seven-day window.

Count distinct IDs separately for every report date

WITH calendar(day) AS (
 VALUES ('2026-11-04'), ('2026-11-05'),
        ('2026-11-06'), ('2026-11-07'),
        ('2026-11-08')
)
SELECT c.day,
 (SELECT COUNT(DISTINCT a.customer_id)
  FROM activity a
  WHERE a.day >= date(c.day, '-6 days')
    AND a.day <= c.day
 ) AS rolling_7_day_customers
FROM calendar c
ORDER BY c.day;
day         rolling_7_day_customers
2026-11-04  3
2026-11-05  3
2026-11-06  3
2026-11-07  3
2026-11-08  2

For November 5, the included dates are October 30 through November 5. A, B and C contribute once each. On November 8, the window starts November 2, so only B and C remain. The calendar preserves report dates that have no event of their own; the count can stay positive because earlier activity is still eligible.

The SQLite date-function reference documents the date function and day modifiers used here. The six-day subtraction plus the inclusive report date gives seven calendar dates. This is not a promise of exactly 168 elapsed hours across daylight-saving changes.

Why daily counts and cumulative counts both answer different questions

In the November 5 window, the daily distinct counts sum to five: A contributes on two dates, B on two dates and C on one. Deduplicating across the entire window gives three. The duplicate November 1 event does not add a customer in either distinct calculation.

An all-history cumulative count would remain at three on November 8. The rolling result is two because customers can leave and later re-enter the window. A single first-ever date cannot represent those repeated eligibility changes. See the separate cumulative first-appearance exercise for the metric that intentionally never removes previously observed customers.

Keep enough history and audit missing identity

To start a chart on November 4, this query still needs activity from October 29 onward. Filtering the source to the chart start before calculating the window would discard eligible earlier activity. Validate that the lookback period is available and complete before interpreting a small count as low usage.

COUNT(DISTINCT customer_id) excludes NULL identities. The one missing-ID activity remains an unresolved event, not one known customer. Track it separately. If IDs are unique only within a source or market, use the full identity key rather than merging unrelated customers with the same local ID.

What changes for timestamp data and larger tables?

The example deliberately compares normalized dates. For timestamp inputs, define the reporting timezone and use explicit start and end boundaries, commonly an inclusive start and exclusive next-day end. Comparing raw UTC dates to local reporting dates can put near-midnight activity in the wrong window.

A correlated subquery is clear for learning, but may scan overlapping activity repeatedly. On production volumes, inspect the query plan and benchmark an appropriate date index or a daily customer-presence table deduplicated to date and customer. Even after that reduction, deduplicate customers across the full window; summing the table’s daily row counts is still wrong. Approximate distinct methods require their own documented error and merge semantics.

Verification

Four checks passed in SQLite 3.50.4: the full five-date output matches 3/3/3/3/2; November 5 has three distinct customers; its daily distinct counts sum to five; and the source contains one missing-ID activity. These are synthetic records with complete observation assumed, not a live business trend.

Continue with daily versus weekly distinct users and reporting dates across US time zones.

Leave a Comment

✉ WhatsApp