Pinterest·Data Scientist·Technical Phone Screen·Senior

Senior

Sep 2025Remote

Summary

Pinterest DS interview with two meaty SQL problems back to back. The schema was straightforward but the questions layered on enough edge cases that I definitely stumbled on a few details. Worth knowing your window functions and indexing strategy cold before walking in.

Questions Asked (3)

Given a schema with daily impressions, users, and countries tables, find the category with the highest total impressions per country for August 2025. Return country name, top category, total impressions for that category, and its share of the country's August impressions rounded to 4 decimal places. Break ties by picking the lexicographically smallest category name.

Product Analytics & MetricsData Modeling

Author's notes

The three-table join part was fine.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, filter the daily impressions data to August 2025 and join with the countries table to get country names. Then, aggregate total impressions by country and category, rank categories within each country by total impressions descending and category name ascending, and select the top category per country. Finally, compute each top category's share of the country's total August impressions, rounding to 4 decimal places.

Pro tip: Explicitly state your tie-breaking logic (ORDER BY total_impressions DESC, category ASC) and use a window function like ROW_NUMBER() to ensure deterministic results. Also, clarify that 'share' is calculated as the top category's impressions divided by the country's total August impressions, not the overall total.

1. Filter and join data

Filter the daily impressions table to August 2025 (e.g., date >= '2025-08-01' AND date < '2025-09-01') and join with the countries table to get country names. Ensure you handle any date format or timezone issues.

2. Aggregate impressions by country and category

Group by country and category to compute total impressions for each combination. This gives the base metrics needed for ranking and share calculation.

3. Rank categories within each country

Use a window function (e.g., ROW_NUMBER() OVER (PARTITION BY country ORDER BY total_impressions DESC, category ASC)) to rank categories per country. This ensures the top category is selected with the correct tie-breaking.

4. Select top category and compute share

Filter to rank = 1 to get the top category per country. Compute the share as top_category_impressions / total_country_impressions, rounded to 4 decimal places. You can get total_country_impressions via a window sum or a separate aggregation.

5. Format and validate output

Return country name, top category, total impressions for that category, and the share. Validate that shares are between 0 and 1 and sum to 1 per country (if all categories included), and check for any data quality issues.

Key Points to Mention

Date filtering: Use half-open interval [2025-08-01, 2025-09-01) to avoid missing or double-counting boundary dates.
Join logic: Ensure the join between impressions and countries is correct (e.g., on country_id) and that no rows are dropped due to missing country mappings.
Aggregation: Sum impressions per country and category, handling NULLs appropriately (e.g., COALESCE or filtering).
Tie-breaking: Implement deterministic ranking with ORDER BY total_impressions DESC, category ASC to pick lexicographically smallest category in case of ties.
Share calculation: Compute share as top category impressions divided by total country impressions, and round to 4 decimal places using ROUND(..., 4).
Window functions: Use ROW_NUMBER() or RANK() with PARTITION BY country to efficiently rank categories without self-joins.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.

Using app_events and feature_usage tables, compute a heavy user rate per country for a 7-day window. A heavy user is someone active on at least 4 distinct days in the window AND who used 3 or more distinct features on at least one of those days. Active users are anyone with at least one app_events row in the window. Exclude users with no country mapping from both numerator and denominator.

Product Analytics & MetricsData Modeling

Author's notes

This one had more moving parts than it looked.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by defining the 7-day window and filtering app_events to active users with valid country mappings. Then, compute per-user activity days and distinct features per day, flag heavy users, and finally aggregate to country-level heavy user rate as heavy users divided by active users.

Pro tip: Clarify edge cases upfront—like users with multiple countries or events outside the window—and state your assumptions; this shows rigor and prevents silent errors in the metric.

1. Define window and filter active users

Select the 7-day window (e.g., last 7 days) and filter app_events to users with at least one event in that window. Exclude users without a country mapping from both numerator and denominator.

2. Compute per-user daily activity and feature usage

Join app_events with feature_usage on user_id and date, then aggregate to get distinct active days and distinct features per day for each user.

3. Identify heavy users

Flag users with at least 4 distinct active days AND at least one day where they used 3 or more distinct features.

4. Aggregate to country-level heavy user rate

Group by country, count heavy users and total active users, then compute heavy user rate as heavy_users / active_users. Ensure both counts exclude users without country mapping.

Key Points to Mention

Clearly define the 7-day window (e.g., rolling vs. fixed) and how it aligns with business logic.
Handle users with multiple countries by either taking the most frequent country or excluding them, and state the assumption.
Use COUNT(DISTINCT date) for active days and COUNT(DISTINCT feature) per day for feature usage.
Ensure the heavy user condition is evaluated per user before aggregation to avoid double-counting.
Exclude users with NULL or missing country from both numerator and denominator to avoid skewing the rate.
Consider performance implications and use appropriate indexing or pre-aggregation if working with large datasets.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.

How would you index the daily_impressions table (with roughly a billion rows) to make both the August category aggregation query and the heavy user rate query performant?

System DesignTechnical Trade-offs

Author's notes

Blanked for a second here.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the two query patterns: the August category aggregation likely filters on date and groups by category, while the heavy user rate query likely filters on user_id and aggregates impressions. Propose composite indexes tailored to each query's filter and sort order, and discuss trade-offs like index size, write overhead, and whether partitioning or covering indexes are needed at billion-row scale.

Pro tip: Mention that at Pinterest's scale, you'd validate index choices with EXPLAIN plans and consider partitioning by date to prune data, since a single index won't optimally serve both queries without careful column ordering.

1. Clarify query patterns

Restate the two queries: one filters by date range (August) and groups by category; the other filters by user and computes a rate. Identify the columns in WHERE, GROUP BY, and JOIN clauses.

2. Propose indexes for each query

For the August category query, suggest a composite index on (date, category) or (category, date) depending on selectivity. For the heavy user rate query, suggest an index on (user_id, date) or (user_id) with included columns.

3. Evaluate trade-offs

Discuss index size, write amplification, and maintenance cost. Consider whether a single composite index can serve both or if two separate indexes are better.

4. Consider partitioning and covering indexes

Propose partitioning the table by date (e.g., monthly) to prune August data. Suggest covering indexes that include all columns needed to avoid table lookups.

5. Validate and iterate

Recommend using EXPLAIN plans, query profiling, and A/B testing index changes in a staging environment before production rollout.

Key Points to Mention

Composite index column order matters: put the most selective/filtering column first.
Covering indexes can eliminate expensive table lookups for aggregation queries.
Partitioning by date (e.g., range partitioning) helps prune data for time-bound queries like August.
Write overhead: each additional index slows down inserts/updates, which matters for a high-volume impressions table.
Consider query rewrite or materialized views if indexes alone are insufficient.
Use EXPLAIN and monitor index usage to avoid unused indexes.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.