Calculate Top Countries' Gmail Usage and MoM Change

Q: How do I practice SQL interview questions?

PracHub provides an interactive SQL console where you can write and test queries against real database schemas. Get instant feedback and compare your solution with the expected output.

Q: What difficulty level is this coding question?

This is a Medium difficulty Data Manipulation (SQL/Python) question, commonly asked during Onsite rounds at Google.

Q: What role is this question designed for?

This question is commonly asked for Data Scientist candidates at Google during technical interviews.

Question

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.

PracHub · Accepted Answer

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.

Quick Overview

Quick Overview