This part looked manageable until I remembered the edge cases they listed out loud: userids in notifications that have no match in dim_circle_people should be excluded from person-level counts, and some accounts were created mid-August so you still count them.
First, identify active accounts by filtering the notifications table for last_action_date within August 2025. Then, join to dim_circle_people to map userids to persons, aggregate the count of distinct active accounts per person, and finally bucket persons by account count (exactly 2, exactly 3, >3) and count distinct persons in each bucket, returning a single row with three columns.
Pro tip: Clarify whether 'active accounts' should be counted based on the notifications table alone or if other activity sources exist; also confirm that 'person' is the unique identifier in dim_circle_people and that userids are unique to a person.
Select distinct userids from the notifications table where last_action_date is between '2025-08-01' and '2025-08-31' (inclusive). This gives the set of active accounts for the month.
Join the active accounts to dim_circle_people on userid to associate each active account with its owning person. Ensure the join is correct and handle any potential duplicates.
Group by person and count the number of distinct active userids. This yields the number of active accounts each person had during August 2025.
Use conditional aggregation (e.g., CASE WHEN) to categorize each person into 'exactly 2', 'exactly 3', or 'more than 3' based on their active account count.
Count the number of distinct persons in each bucket and return a single row with three columns: count_exactly_2, count_exactly_3, count_more_than_3.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
First, identify the denominator by counting distinct persons who had at least two active accounts in August 2025. Then, for each person, check if any of their accounts had at least one unread notification during that month, and count those persons as the numerator. Finally, compute the percentage as (numerator / denominator) * 100, rounded to two decimal places.
Pro tip: Clarify the definitions of 'active account' and 'unread notification' upfront, as these can vary by product context; also consider edge cases like persons with multiple accounts where only one has unread notifications, ensuring deduplication at the person level.
Clearly define what constitutes an 'active account' (e.g., logged in, performed an action) and an 'unread notification' (e.g., notification delivered but not opened) for August 2025. Specify the time frame as the entire month.
From the accounts table, filter for accounts active in August 2025, then group by person to count distinct persons with at least two such accounts. This gives the denominator.
From the notifications table, filter for unread notifications in August 2025, join with accounts to get person IDs, and deduplicate to count distinct persons who had at least one unread notification across any account. Ensure these persons are also in the denominator.
Divide the numerator by the denominator and multiply by 100 to get the percentage. Round the result to two decimal places.
Check for data quality issues (e.g., missing person IDs, duplicate accounts) and ensure the numerator is a subset of the denominator. Perform a quick sanity check on the percentage (e.g., it should be between 0 and 100).
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.