Calculate Top Countries' Gmail Usage and MoM Change
Company: Google
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
emails
+----+---------+-----------+-----------+------------+
| id | user_id | country | provider | send_date |
+----+---------+-----------+-----------+------------+
| 1 | 101 | US | gmail | 2023-06-01 |
| 2 | 102 | IN | gmail | 2023-06-02 |
| 3 | 103 | US | yahoo | 2023-06-03 |
| 4 | 104 | BR | gmail | 2023-07-01 |
| 5 | 105 | DE | gmail | 2023-07-02 |
+----+---------+-----------+-----------+------------+
##### Scenario
You have an email-send log and need to understand Gmail usage by geography.
##### Question
Write a SQL query to calculate the total number of Gmail emails sent per country and return the top five countries by volume. Extend the query to compute the month-over-month (MoM) percentage change in Gmail email volume for each country.
##### Hints
Filter provider = 'gmail'; aggregate by country and month; use window functions or self-join for MoM.
Overview: This question evaluates proficiency in SQL data aggregation, time-based grouping, and analytical window functions for computing month-over-month percentage changes in event volumes.
You have an email send log and need to understand Gmail usage by geography. Write a SQL query to calculate the total number of Gmail emails sent per country and return the top five countries by total Gmail volume. For those top countries, compute the monthly Gmail email counts and the month-over-month (MoM) percentage change in Gmail volume for each country.
Tables
emails(id INTEGER, user_id INTEGER, country VARCHAR(2), provider VARCHAR(50), send_date DATE)
Hints
- Filter rows where provider = 'gmail'.
- Aggregate by country and by month (for example, using date_trunc('month', send_date)).