Coding questions · Meta

Compute multi-account activity and unread percentages in SQL

This question evaluates SQL-based data manipulation and analytics skills, including joining daily snapshots to event tables, applying time-window filters, mapping multiple accounts to a person, and computing aggregate counts and unread percentages.

You are given two tables. Use them as the source of truth and do not assume any other data.

Table: notifications
+--------+------------+------------+-------------------+--------+
| userid | ds | event_time | notification_type | status |
+--------+------------+------------+-------------------+--------+
| 111 | 2025-08-20 | 1766270400 | direct | unread |
| 112 | 2025-08-15 | 1765845600 | group | read |
| 114 | 2025-08-05 | 1764981600 | direct | unread |
| 115 | 2025-08-28 | 1766896800 | mention | read |
| 117 | 2025-08-09 | 1765327200 | direct | unread |
| 118 | 2025-08-30 | 1767069600 | group | unread |
| 119 | 2025-08-31 | 1767156000 | direct | read |
| 120 | 2025-08-31 | 1767159600 | mention | unread |
+--------+------------+------------+-------------------+--------+

Columns:

userid INT: account identifier.
ds DATE: event date (UTC).
event_time BIGINT: Unix epoch seconds for the event.
notification_type STRING: e.g., 'direct', 'group', 'mention'.
status STRING: 'unread' or 'read'.

Table: dim_circle_people (daily snapshot; one row per (person_id, userid, ds))

+-----------+--------+------------------+---------------+------------+
| person_id | userid | last_action_date | creation_date | ds |
+-----------+--------+------------------+---------------+------------+
| 1 | 111 | 2025-08-20 | 2024-01-01 | 2025-09-01 |
| 1 | 112 | 2025-08-15 | 2024-03-01 | 2025-09-01 |
| 2 | 113 | 2025-07-30 | 2022-10-10 | 2025-09-01 |
| 3 | 114 | 2025-08-05 | 2023-04-05 | 2025-09-01 |
| 3 | 115 | 2025-08-28 | 2023-05-10 | 2025-09-01 |
| 3 | 116 | 2025-06-10 | 2023-06-12 | 2025-09-01 |
| 4 | 117 | 2025-08-09 | 2025-08-01 | 2025-09-01 |
| 4 | 118 | 2025-08-30 | 2025-08-15 | 2025-09-01 |
| 4 | 119 | 2025-08-31 | 2025-08-20 | 2025-09-01 |
| 4 | 120 | 2025-08-31 | 2025-08-25 | 2025-09-01 |
+-----------+--------+------------------+---------------+------------+

Columns:

person_id INT: real person cluster id (one person may own multiple accounts/userids).
userid INT: account id belonging to that person.
last_action_date DATE: date of the account's most recent action.
creation_date DATE: account creation date.
ds DATE: snapshot date; use the latest snapshot.

Definitions for this problem:

Treat 'last month' as 2025-08-01 to 2025-08-31 inclusive.
Consider an account 'active during last month' if last_action_date is between 2025-08-01 and 2025-08-31 (inclusive) in the dim_circle_people snapshot where ds = '2025-09-01'.
A person is 'multi-account active' if they have >=2 accounts that meet the above activity rule.
An 'unread notification' is any notifications row with status = 'unread' and ds between 2025-08-01 and 2025-08-31 (inclusive).

Tasks: (a) Write a single SQL query that returns exactly three numbers in one row: the count of people with exactly two active accounts, exactly three active accounts, and more than three active accounts, all based on the above definitions. Name the output columns two_accts, three_accts, gt_three_accts. (b) Among people who are 'multi-account active' (>=2 active accounts by the above rule), write a SQL query that returns one row with a single column percent_unread: the percentage of those people who have at least one unread notification on any of their accounts during 2025-08-01..2025-08-31. Compute as 100.0 * numerator / denominator and round to two decimals. Ensure each person is counted at most once in the numerator even if they have multiple unread notifications across accounts. Edge cases to handle explicitly: do not count accounts with last_action_date outside August 2025; only use ds='2025-09-01' rows from dim_circle_people; ignore notifications outside August 2025; avoid double-counting per person; return 0.00 if the denominator is zero.

Count people by number of active accounts in August 2025

You are given two tables: dim_circle_people (a daily snapshot of which userids belong to which real person) and notifications.

Use ONLY the snapshot rows where dim_circle_people.ds = '2025-09-01'.

Definitions:

Treat “last month” as the date range 2025-08-01 to 2025-08-31 inclusive.
An account (userid) is considered active during last month if last_action_date is between 2025-08-01 and 2025-08-31 inclusive.
A person is multi-account active if they have at least 2 active accounts by the rule above.

Task (a): Write a single SQL query that returns exactly three numbers in one row:

two_accts: count of people with exactly 2 active accounts
three_accts: count of people with exactly 3 active accounts
gt_three_accts: count of people with more than 3 active accounts

Important edge cases:

Do not count accounts with last_action_date outside August 2025 (or NULL).
Only use dim_circle_people rows from ds = '2025-09-01'.
Return 0 for a bucket if no people fall into it.

Table `notifications`

column	type	constraints
userid	INT	NOT NULL
ds	DATE	NOT NULL
event_time	BIGINT
notification_type	VARCHAR(20)
status	VARCHAR(10)	NOT NULL

Sample data:

ds	event_time	notification_type	status	userid
2025-08-20	1766270400	direct	unread	111
2025-08-15	1765845600	group	read	112
2025-08-05	1764981600	direct	unread	114
2025-08-28	1766896800	mention	read	115
2025-08-09	1765327200	direct	unread	117
2025-08-30	1767069600	group	unread	118
2025-08-31	1767156000	direct	read	119
2025-08-31	1767159600	mention	unread	120
2025-08-20	1766274000	group	unread	116
2025-08-12	1765586400	direct	unread	121
2025-08-13	1765672800	mention	unread	123
2025-08-11	1765500000	group	read	124
2025-07-31	1764463200	direct	unread	126
2025-09-01	1767220000	direct	unread	124
2025-08-10	1765413600	direct	unread	999
2025-08-16	1765932000	NULL	read	112

Table `dim_circle_people`

column	type	constraints
person_id	INT	NOT NULL
userid	INT	NOT NULL
last_action_date	DATE
creation_date	DATE	NOT NULL
ds	DATE	NOT NULL

Sample data:

creation_date	ds	last_action_date	person_id	userid
2024-01-01	2025-09-01	2025-08-20	1	111
2024-03-01	2025-09-01	2025-08-15	1	112
2022-10-10	2025-09-01	2025-07-30	2	113
2023-04-05	2025-09-01	2025-08-05	3	114
2023-05-10	2025-09-01	2025-08-28	3	115
2023-06-12	2025-09-01	2025-06-10	3	116
2025-08-01	2025-09-01	2025-08-09	4	117
2025-08-15	2025-09-01	2025-08-30	4	118
2025-08-20	2025-09-01	2025-08-31	4	119
2025-08-25	2025-09-01	2025-08-31	4	120
2025-07-01	2025-09-01	2025-08-10	5	121
2025-01-01	2025-09-01	NULL	6	122
2025-02-02	2025-09-01	2025-08-12	6	123
2025-01-10	2025-09-01	2025-08-02	7	124
2025-02-10	2025-09-01	2025-08-01	7	125
2025-03-10	2025-09-01	2025-08-31	7	126
2024-01-01	2025-08-01	2025-07-31	1	111
2024-03-01	2025-08-01	2025-07-15	1	112
2025-08-01	2025-08-01	2025-07-29	4	117

Expected output

One row with three integer columns showing how many people have exactly 2, exactly 3, and more than 3 active accounts in August 2025 (based on the ds='2025-09-01' snapshot).

gt_three_accts	three_accts	two_accts
1	1	2

Hints

First, filter to the latest snapshot (ds='2025-09-01') and count August-active accounts per person_id.
Then turn per-person counts into 3 buckets using conditional aggregation.

Percent of multi-account active people with at least one unread notification in August 2025

You are given two tables: dim_circle_people (a daily snapshot) and notifications.

Use ONLY the snapshot rows where dim_circle_people.ds = '2025-09-01'.

Definitions:

Treat “last month” as 2025-08-01 to 2025-08-31 inclusive.
An account is active during last month if last_action_date is between 2025-08-01 and 2025-08-31 inclusive.
A person is multi-account active if they have >= 2 active accounts by the rule above.
An unread notification is any notifications row with status = 'unread' and notifications.ds between 2025-08-01 and 2025-08-31 inclusive.

Task (b): Among people who are multi-account active, return one row with a single column percent_unread: the percentage of those people who have at least one unread notification on any of their accounts during 2025-08-01..2025-08-31.

Compute as 100.0 * numerator / denominator
Round to two decimals.
Count each person at most once in the numerator even if they have multiple unread notifications across accounts.
Return 0.00 if the denominator is 0.

Edge cases to handle explicitly:

Only use dim_circle_people.ds='2025-09-01'.
Ignore notifications outside August 2025.
Avoid double-counting per person.

Table `notifications`

column	type	constraints
userid	INT	NOT NULL
ds	DATE	NOT NULL
event_time	BIGINT
notification_type	VARCHAR(20)
status	VARCHAR(10)	NOT NULL

Sample data:

ds	event_time	notification_type	status	userid
2025-08-20	1766270400	direct	unread	111
2025-08-15	1765845600	group	read	112
2025-08-05	1764981600	direct	unread	114
2025-08-28	1766896800	mention	read	115
2025-08-09	1765327200	direct	unread	117
2025-08-30	1767069600	group	unread	118
2025-08-31	1767156000	direct	read	119
2025-08-31	1767159600	mention	unread	120
2025-08-20	1766274000	group	unread	116
2025-08-12	1765586400	direct	unread	121
2025-08-13	1765672800	mention	unread	123
2025-08-11	1765500000	group	read	124
2025-07-31	1764463200	direct	unread	126
2025-09-01	1767220000	direct	unread	124
2025-08-10	1765413600	direct	unread	999
2025-08-16	1765932000	NULL	read	112

Table `dim_circle_people`

column	type	constraints
person_id	INT	NOT NULL
userid	INT	NOT NULL
last_action_date	DATE
creation_date	DATE	NOT NULL
ds	DATE	NOT NULL

Sample data:

creation_date	ds	last_action_date	person_id	userid
2024-01-01	2025-09-01	2025-08-20	1	111
2024-03-01	2025-09-01	2025-08-15	1	112
2022-10-10	2025-09-01	2025-07-30	2	113
2023-04-05	2025-09-01	2025-08-05	3	114
2023-05-10	2025-09-01	2025-08-28	3	115
2023-06-12	2025-09-01	2025-06-10	3	116
2025-08-01	2025-09-01	2025-08-09	4	117
2025-08-15	2025-09-01	2025-08-30	4	118
2025-08-20	2025-09-01	2025-08-31	4	119
2025-08-25	2025-09-01	2025-08-31	4	120
2025-07-01	2025-09-01	2025-08-10	5	121
2025-01-01	2025-09-01	NULL	6	122
2025-02-02	2025-09-01	2025-08-12	6	123
2025-01-10	2025-09-01	2025-08-02	7	124
2025-02-10	2025-09-01	2025-08-01	7	125
2025-03-10	2025-09-01	2025-08-31	7	126
2024-01-01	2025-08-01	2025-07-31	1	111
2024-03-01	2025-08-01	2025-07-15	1	112
2025-08-01	2025-08-01	2025-07-29	4	117

Expected output

One row with percent_unread, the percent of multi-account active people (>=2 August-active accounts) who have at least one unread notification in August 2025 on any of their accounts. Rounded to two decimals.

percent_unread
75

Hints

Compute the denominator as the number of distinct person_id with >=2 active accounts in August (from the latest snapshot).
For the numerator, find distinct person_id (from that same cohort) that have at least one unread notification in August; use DISTINCT to avoid double-counting.

Count people by number of active accounts in August 2025

Table notifications

Table dim_circle_people

Expected output

Hints

Percent of multi-account active people with at least one unread notification in August 2025

Table notifications

Table dim_circle_people

Expected output

Hints

Table `notifications`

Table `dim_circle_people`

Table `notifications`

Table `dim_circle_people`