Pinterest·Data Scientist·Technical Phone Screen·Intermediate

Intermediate

Jul 2026

Summary

Pinterest DS interview with a fairly involved SQL question. Single problem but they packed a lot of requirements into it, more than I expected for a phone screen.

Questions Asked (1)

Given three tables (events, users, and pin_classification), write a single SQL query to compute click-through rate by pin format for new US users, where 'new' means the user signed up within 30 days before the event date.

Product Analytics & MetricsData Modeling

Author's notes

The filtering logic is where I almost tripped up.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the schema and definitions: identify the event type for clicks, the format field in pin_classification, and how to determine 'new US users' using signup date and country. Then structure the query with CTEs: one to filter events to clicks and impressions for new US users within 30 days of signup, another to join with pin_classification to get format, and finally aggregate to compute CTR as clicks divided by impressions per format.

Pro tip: Always confirm whether CTR should be computed as total clicks divided by total impressions (weighted) or as an average of per-user CTRs; the former is standard for product analytics and avoids bias from low-activity users.

1. Clarify schema and definitions

Ask about table columns, event types (e.g., 'click', 'impression'), how to identify US users, and the exact signup date field. Confirm that 'new' means signup_date >= event_date - 30 days.

2. Filter events for new US users

Use a CTE to select events where the user is from the US and the event date is within 30 days after the user's signup date. Include only click and impression events.

3. Join with pin_classification

Join the filtered events with pin_classification on pin_id to get the pin format for each event.

4. Aggregate clicks and impressions by format

Group by pin format and count clicks and impressions separately, using conditional aggregation (e.g., SUM(CASE WHEN event_type = 'click' THEN 1 ELSE 0 END)).

5. Compute CTR and handle edge cases

Calculate CTR as clicks / NULLIF(impressions, 0) to avoid division by zero, and consider filtering out formats with very few impressions.

Key Points to Mention

Use of CTEs for readability and logical separation of filtering and aggregation.
Correctly defining 'new user' with a date condition: event_date BETWEEN signup_date AND signup_date + INTERVAL '30 days'.
Filtering for US users based on a country field in the users table.
Conditional aggregation to count clicks and impressions in a single pass.
Handling division by zero with NULLIF or CASE to avoid errors.
Considering whether to include only users with at least one impression or click, and whether to weight by user.

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