Calculate the Percentage of Highly Active Users by Country
Company: Pinterest
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
## Calculate the Percentage of Highly Active Users by Country
The `impressions` table has these columns:
| Column | Meaning |
|---|---|
| `impression_id` | Identifier of an impression. |
| `user_id` | User receiving the impression. |
| `pin_id` | Pin associated with the impression. |
| `surface` | Surface where the impression occurred, such as home, search, or related pins. |
| `impression_ts` | Impression timestamp. |
| `country_code` | Country recorded for the impression. |
Use the seven calendar dates consisting of today and the six preceding dates. Use the date component of `impression_ts` when determining the date of activity.
Within each country, an **active user** has at least one impression in this window. A **highly active user** meets both conditions within the same window:
- The user has impressions on at least four distinct calendar dates.
- On at least one of those dates, the user has impressions on at least three distinct surfaces.
Evaluate a user separately for each country in which that user has impressions. The three-surface condition must hold within one date; combining surfaces from different dates does not satisfy it.
Write one read-only PostgreSQL query that returns one row for each country with active users in the window. Return these columns in this order:
- `country_code`.
- `active_users`: the number of distinct active users in that country.
- `highly_active_users`: the number of those users satisfying both conditions.
- `highly_active_pct`: `highly_active_users` divided by `active_users`, multiplied by 100 and rounded to two decimal places.
Use the same date window for the numerator and denominator. Include a country even if none of its active users are highly active. Order the result by `highly_active_pct` descending and then `country_code` ascending.
Overview: Calculate highly active users by country over seven calendar days using distinct active dates and same-day surface diversity, with percentage output.
Read the full Pinterest Data Scientist interview experience this question came from