Analyze Multiple-Account Users in SQL

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

Use the following tables:

accounts

• account_id BIGINT — unique account ID
• user_id BIGINT — owner of the account
• created_at TIMESTAMP — when the account was created
• last_visit_at TIMESTAMP — most recent time the user visited that account

notifications

• notification_id BIGINT — unique notification ID
• account_id BIGINT — account that received the notification
• sent_at TIMESTAMP — when the notification was sent
• read_at TIMESTAMP NULL — when the notification was read; NULL means unread

Assumptions:

• Each account belongs to exactly one user.
• A user may have one or more accounts.
• All timestamps are stored in UTC.
• For these questions, use the current state of the tables; no additional time window is required.

Write SQL for the following:

1. Using only the accounts table, return the number of users who have exactly 2 accounts, exactly 3 accounts, and 4 or more accounts.
   • Required output columns:
     • account_bucket ( '2' , '3' , or '4+' )
     • user_count
2. Among users who have 2 or more accounts, compute the percentage of users who have at least one unread notification across any of their accounts.
   • An unread notification is defined as read_at IS NULL .
   • Required output column:
     • pct_multi_account_users_with_unread_notifications as a decimal between 0 and 1.