← Pinterest Interview Insights

Pinterest·Data Scientist·Technical Phone Screen·Intermediate

IntermediatePrefer not to say
Jun 2026Remote

Summary

Pinterest DS interview with two SQL problems back to back. Both were heavier than I expected, especially the second one which had multiple moving parts. No behavioral, just pure SQL the whole time.

Questions Asked (2)

Q1

Given impression, user, and pin_info tables, find the pin category with the most impressions today for each country. Return all tied categories if there's a tie.

Product Analytics & MetricsData Modeling
Author's notes

Knew right away I needed a rank or dense_rank over a count grouped by country and category.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the schema and defining 'today' and 'impressions' precisely. Then write a SQL query that joins the tables, filters for today's impressions, aggregates counts by country and pin category, and uses a window function to rank categories within each country, returning all top-ranked categories to handle ties.

Pro tip: Explicitly state your assumptions about the data model (e.g., whether pin_info contains category, how impressions link to pins) and mention that you would validate the results with a quick sanity check, such as ensuring the sum of impressions per country matches the total. This shows you think about data quality and edge cases.

1. Clarify schema and definitions

Confirm the columns in each table, how they join (e.g., impression.pin_id = pin_info.pin_id), and define 'today' (e.g., date = CURRENT_DATE) and 'impression' (e.g., event_type = 'impression').

2. Filter and aggregate

Filter impressions for today, join with pin_info to get category, and group by country and category to count impressions.

3. Rank categories per country

Use a window function like RANK() or DENSE_RANK() over (PARTITION BY country ORDER BY impression_count DESC) to assign ranks.

4. Select top categories including ties

Filter for rank = 1 to return all categories with the highest impression count per country, ensuring ties are included.

5. Validate and discuss edge cases

Check for nulls, countries with no impressions, and verify that the sum of impressions per country matches the total; discuss how to handle ties in the output.

Key Points to Mention

  • Use of window functions (RANK/DENSE_RANK) to handle ties efficiently.
  • Proper join conditions between impression, user, and pin_info tables.
  • Definition of 'today' and 'impression' based on business logic (e.g., timezone, event type).
  • Handling of ties: returning all categories with the maximum count.
  • Data quality checks: nulls, missing categories, and validation of results.
  • Performance considerations: indexing, partitioning, and avoiding unnecessary joins.

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

Q2

Using the same tables, for each country compute the percentage of active users (at least 1 impression in the last 7 days) who qualify as highly active, where highly active means active on 4 or more distinct days AND used 3 or more distinct surfaces on at least one of those days.

Product Analytics & MetricsData ModelingRoot Cause Analysis
Author's notes

This one took me longer than it should have.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by defining the base population: users with at least one impression in the last 7 days per country. Then, for each user, compute two metrics: distinct active days and, for each active day, the number of distinct surfaces used. Identify highly active users as those with >=4 active days and at least one day with >=3 distinct surfaces. Finally, calculate the percentage of highly active users within the active user base for each country.

Pro tip: Clarify the time window: 'last 7 days' should be relative to the analysis date, and ensure you handle users with no activity correctly. Also, consider using a single SQL query with CTEs for efficiency and readability.

1. Define Active Users

Filter the impressions table to the last 7 days and identify users with at least one impression per country. This forms the denominator.

2. Compute Daily Surface Counts

For each user and each active day, count the number of distinct surfaces they used. This will be used to check the '3 or more surfaces on at least one day' condition.

3. Identify Highly Active Users

For each user, count distinct active days (must be >=4) and check if any day has >=3 distinct surfaces. Flag users meeting both criteria as highly active.

4. Calculate Percentage per Country

For each country, compute the ratio of highly active users to total active users, and multiply by 100 to get the percentage.

Key Points to Mention

  • Clearly define 'active user' as having at least one impression in the last 7 days.
  • Specify that 'distinct days' means unique dates with at least one impression.
  • Explain that 'distinct surfaces' refers to unique surface IDs or names on a given day.
  • Mention the need to handle users with multiple impressions on the same day and surface (deduplication).
  • Discuss the importance of using the correct date range and timezone considerations.
  • Suggest using SQL window functions or CTEs to compute the metrics efficiently.

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