The experiment exclusion tripped me up more than I expected.
Start by clarifying the schema and definitions (e.g., how to identify bot events, experiment treatment users, and new vs returning users). Then structure the query using CTEs to compute daily DAU per country for the prior 7 days and the target date, apply filters for bots and experiment treatment, and finally calculate the drop metrics and rank countries. Ensure the final output includes the required columns and breaks down today's DAU into returning and new users.
Pro tip: Always confirm the definition of 'new user' (e.g., first-ever activity vs first activity in the period) and whether the experiment exclusion applies to all days or only the target date; these details can significantly affect the results.
Ask about table structures, bot identification, experiment assignment, and new vs returning user definitions. Confirm the date range and that the experiment is active during the period.
Write a CTE to filter out bot events and users in the treatment arm, then aggregate distinct users per country per day for the prior 7 days and the target date.
Join the aggregated data to calculate the 7-day average DAU per country, then compute absolute drop (avg - today) and percent drop ((avg - today)/avg * 100).
Use a subquery or join to classify today's users as returning (active before 2025-09-01) or new (first activity on 2025-09-01), and count them per country.
Order countries by absolute drop descending, then by percent drop descending for ties, and limit to top 3. Output all required columns.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.