Coding questions · Pinterest
Write SQL to rank categories by impressions
This question evaluates proficiency in SQL data manipulation—covering joins, aggregations, grouping, tie-breaking, date-window logic, and scalability considerations such as indexing and query performance—in the Data Manipulation (SQL/Python) domain.
You are given the following schema and sample data. Today is 2025-09-01.
Schema:
- users(user_id INT, country_id VARCHAR)
- countries(country_id VARCHAR, country_name VARCHAR)
- daily_impressions(dt DATE, user_id INT, category VARCHAR, impressions INT)
- app_events(dt DATE, user_id INT) -- each row = user active on that day
- feature_usage(dt DATE, user_id INT, feature VARCHAR) -- one row per feature used per day
Sample tables (small, non-exhaustive):
users
user_id | country_id
1 | US
2 | US
3 | CN
4 | IN
countries
country_id | country_name
US | United States
CN | China
IN | India
daily_impressions
dt | user_id | category | impressions
2025-08-29 | 1 | Pets | 100
2025-08-30 | 1 | Pets | 50
2025-08-31 | 2 | Fashion | 70
2025-08-31 | 3 | Pets | 80
2025-08-31 | 4 | Electronics | 90
2025-08-15 | 1 | Fashion | 10
app_events
dt | user_id
2025-08-26 | 1
2025-08-27 | 1
2025-08-28 | 1
2025-08-29 | 1
2025-08-30 | 2
2025-08-31 | 2
2025-08-27 | 3
2025-08-30 | 3
2025-08-31 | 3
2025-09-01 | 3
2025-08-28 | 4
feature_usage
dt | user_id | feature
2025-08-29 | 1 | Search
2025-08-29 | 1 | Share
2025-08-29 | 1 | Save
2025-08-27 | 3 | Search
2025-08-27 | 3 | Share
2025-08-27 | 3 | Save
2025-08-31 | 2 | Search
2025-08-31 | 2 | Share
2025-09-01 | 3 | Comment
2025-09-01 | 3 | Save
2025-09-01 | 3 | Share
Tasks: A) For August 2025 (2025-08-01 to 2025-08-31), return the category with the highest total impressions per country. Output: country_name, top_category, top_impressions, and the share of that category among the country’s August impressions (as a decimal rounded to 4 dp). Break ties by the lexicographically smallest category. Use only the provided tables; joining three tables (daily_impressions → users → countries) is expected. B) For the last 7 days ending today (2025-08-26 to 2025-09-01 inclusive), compute the heavy_user_rate per country. Heavy user definition: active on at least 4 distinct days in the window (from app_events) AND on at least one of those days used 3+ distinct features (from feature_usage). Define active_users as users with ≥1 app_events day in the window. Output: country_name, heavy_user_count, active_user_count, heavy_user_rate = heavy_user_count/active_user_count (round 4 dp). Consider users with missing country_id by excluding them from both numerator and denominator.
Requirements:
- Write standard SQL (CTEs allowed). Avoid windowing over excessively large intermediate sets. Ensure correct handling of users with no impressions (for A) and no feature_usage (for B). Explain how you would index these tables to make A and B performant on 10^9 daily_impressions rows.
Top Impression Category per Country for August 2025
Using the schema and sample data below, write a SQL query to find, for August 2025 (from '2025-08-01' to '2025-08-31' inclusive), the category with the highest total impressions per country.
Requirements:
- Use only the provided tables; joining three tables (daily_impressions → users → countries) is expected.
- For each country, sum impressions per category over August 2025.
- Return, per country, only the single category with the highest total impressions.
- Break ties by choosing the lexicographically smallest category name.
- Output columns: country_name, top_category, top_impressions, top_impressions_share.
- top_impressions_share = top_impressions / (all impressions in that country in August), as a decimal rounded to 4 decimal places.
- Ensure correct handling of users with no impressions (they should not appear in the result).
- Assume standard SQL (CTEs allowed).
- Briefly consider what indexes you would create on these tables to make this query performant if daily_impressions contained 10^9 rows (you do not need to implement the indexes in SQL here).
Table users
| column | type | constraints |
|---|---|---|
| user_id | INT | PRIMARY KEY, NOT NULL |
| country_id | VARCHAR(10) | NOT NULL |
Sample data:
| country_id | user_id |
|---|---|
| US | 1 |
| US | 2 |
| CN | 3 |
| IN | 4 |
Table countries
| column | type | constraints |
|---|---|---|
| country_id | VARCHAR(10) | PRIMARY KEY, NOT NULL |
| country_name | VARCHAR(100) | NOT NULL |
Sample data:
| country_id | country_name |
|---|---|
| US | United States |
| CN | China |
| IN | India |
Table daily_impressions
| column | type | constraints |
|---|---|---|
| dt | DATE | NOT NULL |
| user_id | INT | NOT NULL |
| category | VARCHAR(100) | NOT NULL |
| impressions | INT | NOT NULL |
Sample data:
| category | dt | impressions | user_id |
|---|---|---|---|
| Pets | 2025-08-29 | 100 | 1 |
| Pets | 2025-08-30 | 50 | 1 |
| Fashion | 2025-08-31 | 70 | 2 |
| Pets | 2025-08-31 | 80 | 3 |
| Electronics | 2025-08-31 | 90 | 4 |
| Fashion | 2025-08-15 | 10 | 1 |
Table app_events
| column | type | constraints |
|---|---|---|
| dt | DATE | NOT NULL |
| user_id | INT | NOT NULL |
Sample data:
| dt | user_id |
|---|---|
| 2025-05-26 | 1 |
| 2025-05-27 | 1 |
| 2025-05-28 | 1 |
| 2025-05-29 | 1 |
| 2025-05-30 | 2 |
| 2025-05-31 | 2 |
| 2025-05-27 | 3 |
| 2025-05-30 | 3 |
| 2025-05-31 | 3 |
| 2025-06-01 | 3 |
| 2025-05-28 | 4 |
Table feature_usage
| column | type | constraints |
|---|---|---|
| dt | DATE | NOT NULL |
| user_id | INT | NOT NULL |
| feature | VARCHAR(100) | NOT NULL |
Sample data:
| dt | feature | user_id |
|---|---|---|
| 2025-05-29 | Search | 1 |
| 2025-05-29 | Share | 1 |
| 2025-05-29 | Save | 1 |
| 2025-05-27 | Search | 3 |
| 2025-05-27 | Share | 3 |
| 2025-05-27 | Save | 3 |
| 2025-05-31 | Search | 2 |
| 2025-05-31 | Share | 2 |
| 2025-06-01 | Comment | 3 |
| 2025-06-01 | Save | 3 |
| 2025-06-01 | Share | 3 |
Expected output
One row per country, showing the country name, the category with the highest total impressions in August 2025, the total impressions for that category, and the share of that category among all August impressions for that country (rounded to 4 decimal places).
| country_name | top_category | top_impressions | top_impressions_share |
|---|---|---|---|
| China | Pets | 80 | 1 |
| India | Electronics | 90 | 1 |
| United States | Pets | 150 | 0.6522 |
Hints
- First aggregate impressions by country and category for August before picking the top category per country.
- Use a window function like ROW_NUMBER() partitioned by country to break ties by impressions (and then category name).
Heavy User Rate per Country over the Last 7 Days
Assume today's date is 2025-06-01. Using the same schema and sample data, write a SQL query to compute the heavy_user_rate per country for the last 7 days ending on 2025-06-01 (i.e., from '2025-05-26' to '2025-06-01' inclusive).
Definitions:
- A user is active in the window if they have at least 1 row in app_events between '2025-05-26' and '2025-06-01'.
- A user is heavy if BOTH of the following hold within that same window:
- They are active on at least 4 distinct days (count distinct dt in app_events).
- On at least one of those days, they used 3 or more distinct features (count distinct feature in feature_usage for that user and date in the window).
- For each country, define:
- active_user_count = number of active users in that country in the window.
- heavy_user_count = number of heavy users in that country in the window.
- heavy_user_rate = heavy_user_count / active_user_count, rounded to 4 decimal places.
- Consider users with missing country_id by excluding them from both numerator and denominator.
Requirements:
- Output columns: country_name, heavy_user_count, active_user_count, heavy_user_rate.
- Use only the provided tables (users, countries, app_events, feature_usage).
- Ensure correct handling of users with no feature_usage (they can still be active but never heavy).
- Use standard SQL (CTEs allowed). Avoid windowing over excessively large intermediate sets by aggregating early where possible.
- Briefly consider what indexes you would create on app_events and feature_usage to make this query performant on large datasets (you do not need to implement the indexes in SQL here).
Table users
| column | type | constraints |
|---|---|---|
| user_id | INT | PRIMARY KEY, NOT NULL |
| country_id | VARCHAR(10) | NOT NULL |
Sample data:
| country_id | user_id |
|---|---|
| US | 1 |
| US | 2 |
| CN | 3 |
| IN | 4 |
Table countries
| column | type | constraints |
|---|---|---|
| country_id | VARCHAR(10) | PRIMARY KEY, NOT NULL |
| country_name | VARCHAR(100) | NOT NULL |
Sample data:
| country_id | country_name |
|---|---|
| US | United States |
| CN | China |
| IN | India |
Table daily_impressions
| column | type | constraints |
|---|---|---|
| dt | DATE | NOT NULL |
| user_id | INT | NOT NULL |
| category | VARCHAR(100) | NOT NULL |
| impressions | INT | NOT NULL |
Sample data:
| category | dt | impressions | user_id |
|---|---|---|---|
| Pets | 2025-08-29 | 100 | 1 |
| Pets | 2025-08-30 | 50 | 1 |
| Fashion | 2025-08-31 | 70 | 2 |
| Pets | 2025-08-31 | 80 | 3 |
| Electronics | 2025-08-31 | 90 | 4 |
| Fashion | 2025-08-15 | 10 | 1 |
Table app_events
| column | type | constraints |
|---|---|---|
| dt | DATE | NOT NULL |
| user_id | INT | NOT NULL |
Sample data:
| dt | user_id |
|---|---|
| 2025-05-26 | 1 |
| 2025-05-27 | 1 |
| 2025-05-28 | 1 |
| 2025-05-29 | 1 |
| 2025-05-30 | 2 |
| 2025-05-31 | 2 |
| 2025-05-27 | 3 |
| 2025-05-30 | 3 |
| 2025-05-31 | 3 |
| 2025-06-01 | 3 |
| 2025-05-28 | 4 |
Table feature_usage
| column | type | constraints |
|---|---|---|
| dt | DATE | NOT NULL |
| user_id | INT | NOT NULL |
| feature | VARCHAR(100) | NOT NULL |
Sample data:
| dt | feature | user_id |
|---|---|---|
| 2025-05-29 | Search | 1 |
| 2025-05-29 | Share | 1 |
| 2025-05-29 | Save | 1 |
| 2025-05-27 | Search | 3 |
| 2025-05-27 | Share | 3 |
| 2025-05-27 | Save | 3 |
| 2025-05-31 | Search | 2 |
| 2025-05-31 | Share | 2 |
| 2025-06-01 | Comment | 3 |
| 2025-06-01 | Save | 3 |
| 2025-06-01 | Share | 3 |
Expected output
One row per country, showing the number of heavy users, the number of active users, and the heavy_user_rate (heavy_user_count / active_user_count, rounded to 4 decimal places) for the 7-day window from 2025-05-26 to 2025-06-01.
| active_user_count | country_name | heavy_user_count | heavy_user_rate |
|---|---|---|---|
| 1 | China | 1 | 1 |
| 1 | India | 0 | 0 |
| 2 | United States | 1 | 0.5 |
Hints
- Start by aggregating app_events to count distinct active days per user within the 7-day window.
- Separately find users who have at least one day with 3+ distinct features in feature_usage, then combine this with the active-day counts to flag heavy users before aggregating by country.