Quick 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.

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.

Quick 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.

Loading coding console...