The tricky bit is that one person can own multiple userids, so you have to group by person_id first to count active accounts per person, then pivot that into three buckets in one row using conditional aggregation.
First, filter the snapshot table to the most recent snapshot date and the notifications table to August 2025 active records, then join them to count active accounts per person. Finally, aggregate those counts into three conditional sums (exactly 2, exactly 3, and >3) in a single row using a subquery or CTE.
Pro tip: Explicitly state your assumption about the grain of the snapshot table (e.g., one row per person-account per snapshot date) and clarify that 'active' is defined by last_action_date within August 2025, not by the snapshot date itself. This shows you understand data nuances and prevents misinterpretation.
Identify the maximum snapshot_date in the snapshot table and filter the table to only that date. This ensures you're using the latest mapping of persons to accounts.
From the notifications table, select records where last_action_date falls within August 2025 (e.g., between '2025-08-01' and '2025-08-31'). This defines 'active' accounts.
Join the filtered snapshot table with the filtered notifications table on account_id (or person_id if notifications are at person level). Then group by person_id and count distinct active accounts to get the number of active accounts per person.
Using the per-person counts as a subquery or CTE, compute three conditional sums: SUM(CASE WHEN active_accounts = 2 THEN 1 ELSE 0 END) AS exactly_two, similarly for exactly three, and SUM(CASE WHEN active_accounts > 3 THEN 1 ELSE 0 END) AS more_than_three. Return these in a single row.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
This is where I started second-guessing myself.
First, define the denominator as the set of users who had at least two active accounts in August 2025. Then, for each such user, check if any of their accounts had at least one unread notification during that month, and compute the percentage as the ratio of users with at least one unread notification to the total denominator, handling division by zero by returning 0 or NULL as appropriate.
Pro tip: Clarify the definitions of 'active account' and 'unread notification' upfront, as these can vary by product and may require alignment with stakeholders. Also, consider the time zone and whether 'during that same period' means the notification was unread at any point in August or remained unread at the end of August.
Establish precise criteria for what constitutes an 'active account' (e.g., logged in, performed an action) and an 'unread notification' (e.g., notification not marked as read). Confirm these definitions with the interviewer or product context.
Filter the user base to those who have at least two distinct accounts that meet the active criteria during August 2025. This forms the denominator population.
For each user in the denominator, check if any of their active accounts had at least one unread notification during August 2025. Flag these users as the numerator.
Calculate the percentage as (numerator / denominator) * 100, rounded to two decimal places. If the denominator is zero, return 0 or NULL as specified, ensuring no division by zero error.
Perform sanity checks, such as ensuring the numerator is a subset of the denominator and the percentage is between 0 and 100. Consider edge cases like users with accounts that became inactive mid-month.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.