Compute daily net users from event logs

Given an event log table events, write a SQL query that calculates user lifecycle counts for the target date 2026-04-10. Treat 2026-04-09 as the previous day and 2026-04-10 as "today". All timestamps are UTC.

The table schema is:

events(device_id VARCHAR(50), event_name VARCHAR(50), ts TIMESTAMP, device_platform VARCHAR(50))

A device is considered active on a calendar date when at least one row has DATE(ts) equal to that date.

Define the metrics as follows:

• new_users: devices whose earliest DATE(ts) in the entire table is 2026-04-10.
• retained_users: devices active on both 2026-04-09 and 2026-04-10.
• churn_users: devices active on 2026-04-09 but not active on 2026-04-10.
• net_users: $\text{net\_users} = \text{new\_users} + \text{retained\_users} - \text{churn\_users}$

For a new or retained user, use the device_platform observed on 2026-04-10. For a churn user, use the device_platform observed on 2026-04-09.

Return one row for each device_platform that appears in at least one of the new_users, retained_users, or churn_users groups, plus one overall row where device_platform = 'ALL'. Each returned row must have event_date = '2026-04-10' and includes the four count columns: new_users, retained_users, churn_users, and net_users.

Assumptions:

• A device reports at most one device_platform on a single calendar date.
• Multiple events from the same device on the same date count as one active device.
• Devices may switch platforms on different dates; use the attribution rule above.

Write one standard SQL query using CTEs. Window functions are not required.

Example 1:

Input:
Target date: 2026-04-10

events rows:
| device_id | event_name | ts                   | device_platform |
|-----------|------------|-----------------------|-----------------|
| d1        | launch     | 2026-04-10T08:00:00Z  | android         |
| d1        | click      | 2026-04-10T08:05:00Z  | android         |
| d2        | launch     | 2026-04-10T09:00:00Z  | ios             |
| d3        | launch     | 2026-04-09T10:00:00Z  | android         |
| d4        | launch     | 2026-04-09T11:00:00Z  | ios             |
| d5        | launch     | 2026-04-09T12:00:00Z  | android         |
| d6        | launch     | 2026-04-09T13:00:00Z  | web             |
| d7        | launch     | 2026-04-09T14:00:00Z  | android         |
| d7        | click      | 2026-04-10T09:30:00Z  | android         |
| d8        | launch     | 2026-04-09T15:00:00Z  | ios             |
| d8        | purchase   | 2026-04-10T10:15:00Z  | ios             |
| d9        | launch     | 2026-04-10T11:00:00Z  | web             |
| d10       | launch     | 2026-04-08T08:00:00Z  | android         |
| d11       | launch     | 2026-04-09T16:00:00Z  | android         |
| d11       | click      | 2026-04-09T16:05:00Z  | android         |
| d12       | launch     | 2026-04-09T17:00:00Z  | web             |
| d13       | launch     | 2026-04-09T18:00:00Z  | web             |
| d13       | purchase   | 2026-04-10T12:00:00Z  | web             |

Output:
| event_date | device_platform | new_users | retained_users | churn_users | net_users |
|------------|-----------------|-----------|----------------|-------------|-----------|
| 2026-04-10 | ALL             | 3         | 3              | 6           | 0         |
| 2026-04-10 | android         | 1         | 1              | 3           | -1        |
| 2026-04-10 | ios             | 1         | 1              | 1           | 1         |
| 2026-04-10 | web             | 1         | 1              | 2           | 0         |

Explanation: d1, d2, and d9 are new; d7, d8, and d13 are retained; d3, d4, d5, d6, d11, and d12 are churned.

Example 2:

Input:
Target date: 2026-05-20

events rows:
| device_id | event_name | ts                   | device_platform |
|-----------|------------|-----------------------|-----------------|
| x1        | open       | 2026-05-19T09:00:00Z  | android         |
| x1        | click      | 2026-05-20T08:00:00Z  | ios             |
| x2        | open       | 2026-05-20T10:00:00Z  | web             |
| x3        | open       | 2026-05-19T11:00:00Z  | android         |
| x4        | open       | 2026-05-19T12:00:00Z  | ios             |
| x4        | click      | 2026-05-19T12:05:00Z  | ios             |
| x5        | open       | 2026-05-19T13:00:00Z  | web             |
| x5        | click      | 2026-05-20T11:00:00Z  | web             |

Output:
| event_date | device_platform | new_users | retained_users | churn_users | net_users |
|------------|-----------------|-----------|----------------|-------------|-----------|
| 2026-05-20 | ALL             | 1         | 2              | 2           | 1         |
| 2026-05-20 | android         | 0         | 0              | 1           | -1        |
| 2026-05-20 | ios             | 0         | 1              | 1           | 0         |
| 2026-05-20 | web             | 1         | 1              | 0           | 2         |

Explanation: x1 is retained and attributed to ios based on its 2026-05-20 platform; x4 has two previous-day events but is counted only once as churned.

Constraints:

• ts is a UTC TIMESTAMP; use DATE(ts) to determine the activity date.
• Each device has at most one device_platform per calendar date.
• The query must deduplicate multiple events from the same device on the same date.
• Return all count columns as INT values.
• Use a single standard SQL query with CTEs; window functions are not required.

Schema

CREATE TABLE events (
  device_id VARCHAR(50),
  event_name VARCHAR(50),
  ts TIMESTAMP,
  device_platform VARCHAR(50)
);