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
Community answers
Answer by stilllearning
WITH daily AS (
SELECT
impression_ts::date AS impression_date,
country_code,
user_id,
COUNT(DISTINCT surface) AS surface_count
FROM impressions
WHERE impression_ts::date
BETWEEN CURRENT_DATE - INTERVAL '6 day'
AND CURRENT_DATE
GROUP BY
impression_ts::date,
country_code,
user_id
),
user_country AS (
SELECT
country_code,
user_id,
COUNT(*) AS active_days,
MAX(
CASE
WHEN surface_count >= 3 THEN 1
ELSE 0
END
) AS has_3_surface_day
FROM daily
GROUP BY
country_code,
user_id
)
SELECT
country_code,
COUNT(*) AS active_users,
SUM(
CASE
WHEN active_days >= 4
AND has_3_surface_day = 1
THEN 1
ELSE 0
END
) AS highly_active_users,
ROUND(
100.0 *
SUM(
CASE
WHEN active_days >= 4
AND has_3_surface_day = 1
THEN 1
ELSE 0
END
)
/ COUNT(*),
2
) AS highly_active_pct
FROM user_country
GROUP BY country_code
ORDER BY highly_active_pct DESC, country_code ASC;
Answer by gnanadeepika95
WITH user_daily_metrics AS (
SELECT
user_id,
country_code,
impression_ts::date AS activity_date,
COUNT(DISTINCT surface) AS distinct_surfaces_today
FROM impressions
WHERE impression_ts::date BETWEEN CURRENT_DATE - INTERVAL '6 days' AND CURRENT_DATE
GROUP BY user_id, country_code, impression_ts::date
),
user_classifications AS (
SELECT
user_id,
country_code,
-- Condition 1: Active if they have at least 1 date (always true if in this CTE)
1 AS is_active,
-- Condition 2: Highly active if >= 4 dates AND >= 3 surfaces on at least one of those dates
CASE
WHEN COUNT(DISTINCT activity_date) >= 4
AND MAX(distinct_surfaces_today) >= 3 THEN 1
ELSE 0
END AS is_highly_active
FROM user_daily_metrics
GROUP BY user_id, country_code
)
SELECT
country_code,
SUM(is_active) AS active_users,
SUM(is_highly_active) AS highly_active_users,
ROUND((SUM(is_highly_active)::numeric / SUM(is_active)) * 100, 2) AS highly_active_pct
FROM user_classifications
GROUP BY country_code
ORDER BY highly_active_pct DESC, country_code ASC;
Answer by rebelmegan11
In mySQL:
CREATE PROCEDURE solution()
BEGIN
WITH user_days AS (
SELECT country_code, user_id, DATE(impression_ts) AS activity_date, COUNT( DISTINCT surface) AS surfaces
FROM impressions
WHERE DATE(impression_ts) BETWEEN DATE_SUB(CURDATE(), INTERVAL 6 DAY) AND CURDATE()
GROUP BY country_code, user_id, DATE(impression_ts)
),
daily_impressions AS (
SELECT country_code, user_id, CASE WHEN MAX(surfaces) >= 3 AND COUNT(DISTINCT activity_date) >= 4 THEN 1 ELSE 0 END AS is_highly_active
FROM user_days
GROUP BY country_code, user_id
)
SELECT country_code,
COUNT(user_id) AS active_users,
SUM(is_highly_active) AS highly_active_users,
ROUND(((SUM(is_highly_active) /COUNT(user_id)) * 100), 2) AS highly_active_pct
FROM daily_impressions
GROUP BY country_code
ORDER BY highly_active_pct DESC, country_code ASC;
END
Answer by danielmao1994
-- output: country_code, active_users, highly_active_users, highly_active_pct
-- logic
1.get seven calendar days
active user has at leat 1 impression
highly active user meeths bont condtions;
user has impression on at least 4 idstinct calendar dates
user has impression at least 3 distinct surfaces;
-- SUM(sales) OVER (ORDER BY mth
RANGE BETWEEN INTERVAL '2 months' PRECEDING ---- AND CURRENT ROW) / 3.0 AS trailing_3m_avg,
with seven_days as ( -- get the time-frame right
select
user_id,
country_code,
impression_ts::date AS activity_date,
count(distinct surface) as distinct_surfaces_today
from impressions
where impression_ts::date BETWEEN CURRENT_DATE - INTERVAL '6 days' AND CURRENT_DATE
group by user_id, country_code, impression_ts::date
),
cohorts as ( -- get the users right
select
count(distinct user_id) as active_user.
count(case
when count(activity_date) >= 4
and sum(distinct_surfaces_today) >= 3
then user_id end) as highly_active_users,
from seven_days
)
select
country_code,
active_users,
highly_active_users,
(100.0 * highly_active_users / active_users) as highly_active_pct
from cohorts
Answer by QuirkQuotient
WITH imp AS (
SELECT impression_id, user_id, CAST(impression_ts AS DATE) AS impression_dt, country_code, surface
FROM impressions
WHERE CAST(impression_ts AS DTAE) BETWEEN current_date - 6 AND current_date()
),
dt_ct AS (
SELECT user_id, COUNT(DISTINCT impression_dt) day_ct
FROM imp
GROUP BY 1
HAVING day_ct >=4
),
sur_ct AS (
SELECT user_id, impression_dt, COUNT(DISTINCT surface) AS surface_ct
FROM imp m
INNER JOIN dt_ct d ON d.user_id = m.user_id
GROUP BY 1,2
HAVING surface_ct >= 3
),
act_tot AS (
SELECT country_code, COUNT(DISTINCT m.user_id) AS active_users
, COUNT(DISTINCT s.user_id) AS > highly_active_users
FROM imp m
LEFT JOIN sur_ct s
GROUP BY 1
)
SELECT country_code, active_users, highly_active_users, (COALESCE(highly_active_users,0)/active_users) * 100 AS > highly_active_pct
FROM act_tot