Compute multi-account user distribution and unread pct

You are working on a product where a user can have multiple accounts, and each account can receive notifications.

Tables

Assume the following schemas:

users

• user_id BIGINT PRIMARY KEY
• created_at TIMESTAMP

accounts

• account_id BIGINT PRIMARY KEY
• user_id BIGINT NOT NULL REFERENCES users(user_id)
• created_at TIMESTAMP

notifications

• notification_id BIGINT PRIMARY KEY
• account_id BIGINT NOT NULL REFERENCES accounts(account_id)
• created_at TIMESTAMP
• is_read BOOLEAN -- FALSE means unread

Tasks

1. Account-count distribution : Return the number of users who have:
   • exactly 2 accounts
   • exactly 3 accounts
   • 4 or more accounts
   Required output columns : account_bucket , num_users .
2. Unread-notification rate among multi-account users : Among users with 2+ accounts , compute the percentage of users who have at least one unread notification across any of their accounts. Required output columns : pct_users_with_unread (as a percent or decimal; specify which you choose).