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:
-
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
-
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