Active Users Per Day SQL Query | SQL Interview Question

Problem statement

Prompt

Given the activity table below, write a query that returns, for each date that has any activity, the number of distinct users who were active that day.

A user is "active" on a day if they have at least one event row that day.
A user with several events on the same day must be counted once.
Output columns: event_date and active_users, ordered by event_date.
activity.user_id may be NULL; a NULL user_id is not a user and must not be counted.

Example

user_id	event_date	event_type
1	2024-01-01	view
1	2024-01-01	click
2	2024-01-01	view
1	2024-01-02	click

Expected result:

event_date	active_users
2024-01-01	2
2024-01-02	1

Constraints

The activity table has one row per event; a user can appear many times per day.
activity.user_id is nullable.

Tips

COUNT(DISTINCT user_id) grouped by event_date is the whole trick — COUNT(*) would overcount.

Schema

CREATE TABLE activity (
    user_id    INTEGER,  -- NULL allowed
    event_date DATE NOT NULL,
    event_type TEXT NOT NULL
);

Sample data

INSERT INTO activity (user_id, event_date, event_type) VALUES
  (1, '2024-01-01', 'view'),
  (1, '2024-01-01', 'click'),
  (2, '2024-01-01', 'view'),
  (1, '2024-01-02', 'click'),
  (3, '2024-01-02', 'view'),
  (3, '2024-01-02', 'click');

Additional hidden fixtures are applied during validation to test edge cases.

Solution

One correct approach — try solving it yourself in the practice editor first, then compare. There is usually more than one valid solution.

-- Reference: distinct active users per date with any activity.
SELECT event_date, COUNT(DISTINCT user_id) AS active_users
FROM activity
WHERE user_id IS NOT NULL
GROUP BY event_date
ORDER BY event_date;

Key concepts

COUNT(DISTINCT)
GROUP BY
Duplicate handling

Active Users Per Day