Why the weekly total is smaller than the sum of daily users

A product analyst preparing a US growth dashboard reports three active users on Monday and three on Tuesday. That does not necessarily mean six people used the product. If two people returned on Tuesday, only four distinct people were active across those two days. The sum, six, measures active user-days.

Daily active users (DAU) and weekly active users (WAU) require an explicit qualifying action and identity rule. This exercise counts any listed event as qualifying activity and uses a stable account ID. It is an exact count over synthetic records, not an implementation of any particular analytics vendor’s active-user definition.

Create the event data

Run this in a fresh SQLite database. Dates have already been assigned to the agreed reporting timezone. The complete reporting week is September 21 through September 27, 2026; the remaining five dates have no qualifying events. Assume the event feed is complete and all users have nonmissing IDs.

CREATE TABLE activity(day TEXT, user_id TEXT);
INSERT INTO activity VALUES
('2026-09-21','A'), ('2026-09-21','A'),
('2026-09-21','B'), ('2026-09-21','C'),
('2026-09-22','B'), ('2026-09-22','C'),
('2026-09-22','D');

User A generated two events on Monday. Users B and C appeared on both days. An event count would therefore answer a different question from either DAU or WAU.

Count at the requested reporting grain

SELECT day, COUNT(DISTINCT user_id) AS dau
FROM activity
WHERE day >= '2026-09-21' AND day < '2026-09-28'
GROUP BY day
ORDER BY day;

SELECT COUNT(DISTINCT user_id) AS wau
FROM activity
WHERE day >= '2026-09-21' AND day < '2026-09-28';

The first query returns Monday: 3 and Tuesday: 3. The second returns WAU: 4. The seven events became six user-days but only four weekly users. DISTINCT removes repeated IDs within the group being counted. Grouping by day and then summing cannot remove repeats across different days.

The half-open date filter includes September 21 and excludes September 28. Keep both boundaries consistent in all queries. A UK or European team may choose a different business timezone from a US team; convert timestamps before assigning reporting dates. See daily active users across US time zones for that separate problem.

Include zero-activity days in the daily average

The seven-day daily-user average is (3 + 3 + 0 + 0 + 0 + 0 + 0) / 7 = 0.857. Averaging only the two rows returned by the daily query gives 3, which is the average on recorded active dates, not the full-week daily average. In a larger report, join counts to a calendar table so every date appears.

Do not automatically fill an uncollected day with zero. A zero means the data is complete and nobody qualified; an unknown day means the measurement is missing. Show the latter as unavailable and investigate the pipeline before publishing an average.

What if you only have aggregated daily counts?

You cannot recover an exact weekly distinct count from daily totals alone. In this example, two daily counts of three could represent the same three people on both days, six different people, or anything between. With consistent identity and activity rules, weekly users must be at least the largest daily count and at most the sum of daily counts. Those bounds do not identify the actual value.

Request a period-level distinct count from the source, or retain approved user-level data for the calculation. Do not export personal information unnecessarily. If IDs are missing, document coverage rather than treating every anonymous event as a different known person. See the missing customer-ID audit.

An interview answer and a test

“I would calculate weekly distinct users from events for the entire reporting window. Summing DAU measures user-days. I would also include all seven complete dates when calculating average DAU and verify identity coverage and timezone boundaries.”

Test the query by adding another Monday event for A: neither DAU nor WAU should change. Add a Tuesday event for a new user E: Tuesday DAU becomes 4 and WAU becomes 5. If those checks fail, investigate the grouping or the identity field before trusting the dashboard.

Reference: SQLite aggregate functions. Continue with GROUP BY practice or eligible retention cohorts.