Daily Active Users in the 30 Days to 15 March — SQL Easy Problem

Problem statement

The growth team of a food-delivery app wants its daily active users for the 30 days ending 15 March 2025 — from 2025-02-14 to 2025-03-15, both days included. A user is active on a day if they have at least one event that day, of any type.

Return day and active_users (the number of different users active that day) for every day in that window with at least one event. Days without events are left out. Return the rows in any order.

Tables

Table: AppEvent

Primary key: event_id.

One row per thing a user did in the app. A user can have many events on the same day.

Examples

Example 1

AppEvent

Output

How to solve Daily Active Users in the 30 Days to 15 March

Two steps: keep the events inside the window, then count users per day.

The window is 30 days ending on 15 March 2025, both ends included. Counting back, the first day is 14 February: 2025 is not a leap year, so the window is 15 days of February plus 15 of March. WHERE event_date BETWEEN '2025-02-14' AND '2025-03-15' keeps exactly those days, because BETWEEN includes both bounds and dates in YYYY-MM-DD form compare correctly.

If you would rather not count days by hand, let the database do it: DATEDIFF('2025-03-15', event_date) BETWEEN 0 AND 29 keeps the dates 0 to 29 days before the end — thirty days. event_date > DATE_SUB('2025-03-15', INTERVAL 30 DAY) AND event_date <= '2025-03-15' says the same with a half-open range. The classic off-by-one is BETWEEN 0 AND 30, which is 31 days and lets 13 February in.

Then GROUP BY event_date with COUNT(DISTINCT user_id): a user with three events in a day is one active user, while COUNT(*) would count events. Days without events produce no group, so they are absent, as asked. One scan of the table plus the grouping; with an index on event_date the range filter reads only the window.

Reference solution (MySQL)

SELECT event_date AS day, COUNT(DISTINCT user_id) AS active_users
FROM AppEvent
WHERE event_date BETWEEN '2025-02-14' AND '2025-03-15'
GROUP BY event_date

Another way

SELECT event_date AS day, COUNT(DISTINCT user_id) AS active_users FROM AppEvent WHERE DATEDIFF('2025-03-15', event_date) BETWEEN 0 AND 29 GROUP BY event_date

Another way

SELECT event_date AS day, COUNT(DISTINCT user_id) AS active_users FROM AppEvent WHERE event_date > DATE_SUB('2025-03-15', INTERVAL 30 DAY) AND event_date <= '2025-03-15' GROUP BY event_date