Katkat
Back

Calculate Top Countries' Gmail Usage and MoM Change

GoogleData Scientist · Onsite
MediumData ScientistOnsiteData Manipulation (SQL/Python)

Last updated

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.

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.