Compute CTR by format for new US users
Company: Pinterest
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
You are given three tables. Write a single SQL query to compute click-through rate (CTR) by pin_format for NEW users in the US, where NEW users are those whose sign_up_date is within 30 days (inclusive) of the action's event_date.
Schemas:
- events(event_date DATE, user_id BIGINT, pin_id BIGINT, event_type STRING CHECK IN ('impression','click'), event_count INT)
- users(user_id BIGINT, country STRING, sign_up_date DATE)
- pin_classification(pin_id BIGINT, pin_format STRING)
Sample data:
Table: events
event_date | user_id | pin_id | event_type | event_count
2025-08-15 | 101 | 10 | impression | 100
2025-08-15 | 101 | 10 | click | 5
2025-08-20 | 102 | 11 | impression | 50
2025-08-22 | 103 | 12 | impression | 80
2025-08-22 | 103 | 12 | click | 8
2025-08-25 | 104 | 11 | impression | 200
2025-08-25 | 104 | 11 | click | 40
Table: users
user_id | country | sign_up_date
101 | US | 2025-08-01
102 | US | 2025-07-10
103 | CA | 2025-08-10
104 | US | 2025-08-21
Table: pin_classification
pin_id | pin_format
10 | video
11 | static
12 | video
Requirements:
- Consider only events where users.country = 'US'.
- Define NEW users as 0 <= DATEDIFF(event_date, sign_up_date) <= 30.
- CTR per pin_format = SUM(click event_count) / SUM(impression event_count) across all NEW US users' events.
- Return columns: pin_format, impressions, clicks, ctr where
impressions = SUM(CASE WHEN event_type = 'impression' THEN event_count ELSE 0 END),
clicks = SUM(CASE WHEN event_type = 'click' THEN event_count ELSE 0 END),
ctr = clicks / NULLIF(impressions, 0).
- Exclude pin_formats with zero impressions after filtering.
- Round ctr to 4 decimal places and order by ctr DESC; ties broken by pin_format ASC.
- Your query must be a single SELECT (CTEs allowed), handle duplicate rows safely, and not double-count events.
Overview: This question evaluates a candidate's competency in SQL data manipulation and aggregation, covering joins, date-based filtering for user cohorts, deduplication, conditional summing, null-safe arithmetic, rounding, and ordering to compute a business metric (CTR).
## Compute CTR by `pin_format` for new US users
You are given three tables that capture pin engagement on a Pinterest-style product:
- **`events`** — one row per pin engagement event: `event_date`, `user_id`, `pin_id`, `event_type` (either `'impression'` or `'click'`), and `event_count` (how many of that event occurred). The table may contain fully duplicated rows that must **not** be double-counted.
- **`users`** — one row per user: `user_id`, `country`, `sign_up_date`.
- **`pin_classification`** — maps each `pin_id` to a `pin_format` (e.g. `'video'`, `'static'`).
Write a **single** PostgreSQL `SELECT` statement (CTEs are allowed) that computes the click-through rate (CTR) per `pin_format`, restricted to **new** users in the **US**.
### Definitions & requirements
- A user's event is from a **NEW** user when `0 <= (event_date - sign_up_date) <= 30`, i.e. the event happens between the sign-up day and 30 days after, inclusive. (In PostgreSQL, subtracting two `DATE` values yields the number of days as an integer.)
- Consider only events whose user has `country = 'US'`.
- Before aggregating, **de-duplicate** the `events` table so that fully identical rows are counted once.
- For each `pin_format`, compute:
- `impressions` = `SUM(event_count)` over rows where `event_type = 'impression'`,
- `clicks` = `SUM(event_count)` over rows where `event_type = 'click'`,
- `ctr` = `clicks / impressions`, computed in floating/decimal arithmetic and **rounded to 4 decimal places**.
- **Exclude** any `pin_format` whose total `impressions` is 0 after filtering.
- Return exactly these columns, in this order: `pin_format`, `impressions`, `clicks`, `ctr`.
- Order the result by `ctr` **descending**; break ties by `pin_format` **ascending**.
Tables
events(event_date DATE, user_id BIGINT, pin_id BIGINT, event_type VARCHAR(20), event_count INT)
users(user_id BIGINT, country VARCHAR(10), sign_up_date DATE)
pin_classification(pin_id BIGINT, pin_format VARCHAR(50))
Hints
- PostgreSQL has no DATEDIFF; subtracting two DATE values (event_date - sign_up_date) already gives an integer number of days, so use BETWEEN 0 AND 30 on that difference.
- De-duplicate the events table first (SELECT DISTINCT over all columns) so repeated raw rows don't inflate event_count.