Skip to content
>_sqlbuddy
easyGROUP BYAggregationDuplicate handling

Active Users Per Day

Report the number of distinct active users for each date of activity.

Start practice

Prompt

Active Users Per Day

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_idevent_dateevent_type
12024-01-01view
12024-01-01click
22024-01-01view
12024-01-02click

Expected result:

event_dateactive_users
2024-01-012
2024-01-021

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.

Expected concepts

  • COUNT(DISTINCT)
  • GROUP BY
  • Duplicate handling

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.