This question evaluates proficiency in SQL-based data manipulation and analytics, including joins, aggregations, date/time handling, grouping, tie-handling for top-N results, and percentage calculations to produce per-country category rankings and user activity metrics.
You are given three tables:
impressionEvent-level table of user impressions.
impression_id
BIGINT (PK)
user_id
BIGINT (FK →
user.user_id
)
pin_id
BIGINT (FK →
pin_info.pin_id
)
surface
VARCHAR (e.g., 'home', 'search', 'profile', etc.)
impression_ts
TIMESTAMP (when the impression happened)
country_code
VARCHAR (ISO country code; assume it is populated for the event)
useruser_id
BIGINT (PK)
created_ts
TIMESTAMP
pin_infopin_id
BIGINT (PK)
category
VARCHAR
Assumptions:
DATE(impression_ts) = CURRENT_DATE
.
For each country, find the pin category with the highest number of impressions today.
Requirements:
country_code
category
impression_cnt
For each country, compute the percent of active users who are highly active.
Definitions (within the last 7 days, inclusive of today):
DATE(impression_ts)
), AND
surface
values that day).
Requirements:
country_code
active_users
highly_active_users
pct_highly_active
(as a decimal or percent; specify which in your answer)