Find multi-account buckets and unread rate

You are analyzing a product in which one user can own multiple accounts.

Use the following schema:

Table: accounts

• account_id BIGINT
• user_id BIGINT
• created_at TIMESTAMP
• last_visited_at TIMESTAMP

Table: notifications

• notification_id BIGINT
• account_id BIGINT
• created_at TIMESTAMP
• read_at TIMESTAMP NULL

Relationships

• accounts.user_id identifies the person who owns the account.
• notifications.account_id references accounts.account_id.

Definitions

• A multi-account user is a user with at least 2 distinct accounts.
• A notification is unread if read_at IS NULL as of 2025-01-01 00:00:00 UTC.
• Use all rows with created_at <= '2025-01-01 00:00:00 UTC'.
• Assume all timestamps are stored in UTC.

Write SQL for the following:

1. Using only the accounts table, return the number of users who have:

   • exactly 2 accounts
   • exactly 3 accounts
   • 4 or more accounts

   Required output columns:

   • account_bucket
   • user_count

2. Using accounts and notifications, among multi-account users, calculate the percentage of users who have at least one unread notification across any of their accounts.

   Required output columns:

   • multi_account_users
   • users_with_unread_notifications
   • pct_users_with_unread_notifications

Find multi-account buckets and unread rate

You are given two tables describing a product's user accounts and their notifications.

accounts — one row per account (a single user may own several accounts):

column	type	meaning
account_id	BIGINT	unique account id
user_id	BIGINT	the person who owns the account
created_at	TIMESTAMP	when the account was created
last_visited_at	TIMESTAMP	last time the account was visited

notifications — one row per notification sent to an account:

column	type	meaning
notification_id	BIGINT	unique notification id
account_id	BIGINT	the account the notification was sent to
created_at	TIMESTAMP	when the notification was created
read_at	TIMESTAMP	when it was read; NULL means still unread

Apply a fixed cutoff of '2025-01-01 00:00:00': in both tables, only consider rows whose created_at <= '2025-01-01 00:00:00'.

Return a single result set that stacks two parts.

Part 1 — account buckets. Using only accounts, count how many distinct users own exactly 2 accounts, exactly 3 accounts, and 4 or more accounts. Emit one row per non-empty bucket.

Part 2 — unread rate among multi-account users. A user is multi-account if they own 2 or more accounts. Among those users, count how many have at least one unread notification (read_at IS NULL) on any of their accounts, and compute that count as a fraction of all multi-account users.

Output columns (in this exact order):

• section — 'part1' for bucket rows, 'part2' for the summary row.
• account_bucket — one of 'exactly_2', 'exactly_3', 'four_or_more' for Part 1 rows; NULL in the Part 2 row.
• user_count — number of users in that bucket (Part 1 only); NULL in the Part 2 row.
• multi_account_users — total count of multi-account users (Part 2 only); NULL in Part 1 rows.
• users_with_unread_notifications — count of multi-account users with at least one unread notification (Part 2 only); NULL in Part 1 rows.
• pct_users_with_unread_notifications — users_with_unread_notifications / multi_account_users, rounded to 4 decimal places (Part 2 only); NULL in Part 1 rows.

Sort by section ascending, then by account_bucket ascending with NULL last.

Table accounts

column	type	constraints
account_id	BIGINT
user_id	BIGINT
created_at	TIMESTAMP
last_visited_at	TIMESTAMP

Sample data:

account_id	created_at	last_visited_at	user_id
1001	2024-01-05 12:00:00	2024-12-28 09:15:00	101
1002	2024-03-10 08:30:00	2024-12-30 20:00:00	101
1003	2024-02-14 10:00:00	2024-12-20 18:45:00	102
1004	2024-05-01 09:00:00	2024-12-19 11:30:00	102
1005	2024-08-23 16:20:00	2024-12-31 21:10:00	102
1006	2023-12-31 23:00:00	2024-12-10 08:00:00	103
1007	2024-04-02 14:45:00	2024-12-12 07:50:00	103
1008	2024-07-07 13:10:00	2024-12-15 12:05:00	103
1009	2024-09-09 17:25:00	2024-12-22 16:40:00	103
1010	2024-11-11 08:15:00	2024-12-29 10:10:00	104
1011	2024-03-03 12:30:00	2024-12-14 09:05:00	105
1012	2024-06-06 15:00:00	2024-12-27 13:25:00	105
1013	2023-11-01 07:40:00	2024-12-05 19:35:00	106
1014	2024-01-20 11:55:00	2024-12-08 18:00:00	106
1015	2024-03-18 09:25:00	2024-12-16 11:45:00	106
1016	2024-07-22 10:10:00	2024-12-24 14:20:00	106
1017	2024-10-30 13:35:00	2024-12-30 08:55:00	106

Table notifications

column	type	constraints
notification_id	BIGINT
account_id	BIGINT
created_at	TIMESTAMP
read_at	TIMESTAMP

Sample data:

account_id	created_at	notification_id	read_at
1001	2024-12-15 09:00:00	2001	2024-12-20 08:00:00
1002	2024-12-31 23:00:00	2002	NULL
1003	2024-11-05 10:00:00	2003	2024-11-06 08:00:00
1005	2024-12-25 12:00:00	2004	2024-12-26 12:30:00
1011	2024-10-01 14:00:00	2005	NULL
1013	2024-06-15 11:00:00	2006	2024-06-16 08:30:00
1014	2023-12-20 07:45:00	2007	2023-12-21 09:15:00
1010	2024-12-01 09:30:00	2008	NULL

Expected output

Single result set: three Part 1 rows (account buckets exactly_2=2 users, exactly_3=1 user, four_or_more=2 users) plus one Part 2 summary row (5 multi-account users, 2 with unread notifications, 0.4000 rate).

account_bucket	multi_account_users	pct_users_with_unread_notifications	section	user_count	users_with_unread_notifications
exactly_2	NULL	NULL	part1	2	NULL
exactly_3	NULL	NULL	part1	1	NULL
four_or_more	NULL	NULL	part1	2	NULL
NULL	5	0.4000	part2	NULL	2

Hints

1. Aggregate accounts by user_id with COUNT(DISTINCT account_id), then label each user with a CASE expression for the 2 / 3 / 4+ buckets.
2. Apply the same created_at <= '2025-01-01 00:00:00' cutoff to BOTH accounts and notifications before counting anything.
3. For unread detection use EXISTS over an inner join to notifications with read_at IS NULL, so a user with zero notifications is correctly treated as having no unread.