Cumulative distinct users per day

PySpark data engineering interview problem. Difficulty: intermediate. Pattern: Window Functions. About 16 minutes. Part of the Pro drill bank.

Compute the cumulative count of unique users seen on or before each activity date without expensive history cross-joins. Treat this as a production helper: match the contracted return shape, including empty and duplicate inputs.

Given a DataFrame activity with columns dt (date string) and user_id: Calculate cumulative_users for each date: the total number of distinct users who have been seen on that date or on any preceding date in the dataset. Each user contributes to the cumulative count starting from their earliest observed date. Return 1 row per active date dt with columns: dt, cumulative_users. Sort by dt ascending. Assign the final DataFrame to result.

Requirements

  • Each user is counted exactly once at their first appearance.
  • Output columns are dt, cumulative_users.

Constraints

  • dt strings are formatted YYYY-MM-DD.

Examples

Input: activity dt | user_id 2024-01-01 | u1 2024-01-01 | u2 2024-01-02 | u1 2024-01-02 | u3 2024-01-03 | u4 Output: dt | cumulative_users 2024-01-01 | 2 2024-01-02 | 3 2024-01-03 | 4 Day 1 brings u1 and u2 (2). Day 2 adds u3 (3 total, u1 was already known). Day 3 adds u4 (4 total).

Topics: lakebench, pyspark, min-date, running-count.

More PySpark interview questions · All interview problems · Learn data engineering

intermediate

Cumulative distinct users per day

Interview-style drill: Compute the cumulative count of unique users seen on or before each activity date without expensive history cross-joins.

Given a DataFrame `activity` with columns `dt` (date string) and `user_id`: Calculate `cumulative_users` for each date: the total number of distinct users who have been seen on that date or on any preceding date in the dataset. - Each user contributes to the cumulative count starting from their earliest observed date. - Return 1 row per active date `dt` with columns: `dt`, `cumulative_users`. - Sort by `dt` ascending. Assign the final DataFrame to `result`.