Start by clarifying the schema and assumptions (e.g., how Gmail is identified, country field, date granularity). Then write a SQL query that filters Gmail emails, groups by country, counts sends, orders descending, and limits to 5. For the extension, use a window function to compute month-over-month percentage change per country, handling edge cases like missing months or zero denominators.
Pro tip: Mention that you would validate the query by checking for NULLs, duplicates, and time zone consistency, and that you'd consider using a CTE to make the logic modular and readable. Also, discuss how you'd handle countries with no Gmail sends in a given month to avoid misleading MoM changes.
Ask about the table structure, how to identify Gmail emails (e.g., domain in email address), country field, and date column. Confirm whether 'sent per country' means the recipient's country or sender's country.
Filter for Gmail emails, group by country, count the number of emails, order by count descending, and limit to 5. Use appropriate date filtering if needed.
Modify the query to group by country and month (using DATE_TRUNC or EXTRACT). Compute monthly Gmail volume per country.
Use the LAG window function partitioned by country and ordered by month to get previous month's volume. Calculate percentage change as (current - previous) / previous * 100, handling division by zero.
Combine results, perhaps using CTEs, and ensure the output includes country, month, volume, and MoM change. Discuss how to handle missing months (e.g., fill with zero or use a calendar table).
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.