Compute CTR by pin_format and date

Q: Compute CTR by pin_format and date

This is a Data Manipulation (SQL/Python) interview question from Pinterest for Data Scientist roles. View the full question and solution on PracHub.

Q: How do I approach Data Manipulation (SQL/Python) interview questions?

Data Manipulation (SQL/Python) questions require understanding of core concepts and practice. PracHub provides solutions with explanations to help you master data manipulation (sql/python) interviews.

Question

PostgreSQL-compatible SQL required. Given these tables, write a single query that returns, for each event_date and pin_format, the Click-Through-Rate (CTR), total_clicks, and total_impressions restricted to new US users. Definitions: CTR = total_clicks / total_impressions. A user is new on a given event_date if (event_date - signup_date) < 30 days and signup_date <= event_date. Filter users with users_d.country = 'US'. Treat division-by-zero safely (CTR NULL when impressions = 0).

Schema

events(event_date DATE, event_type TEXT CHECK (event_type IN ('impression','click')), user_id BIGINT, pin_id BIGINT, platform TEXT)
users_d(user_id BIGINT PRIMARY KEY, signup_date DATE, country TEXT)
pins_classification(pin_id BIGINT PRIMARY KEY, pin_format TEXT, category TEXT)

Sample data (minimal) users_d user_id | signup_date | country 1 | 2025-08-10 | US 2 | 2025-06-01 | US 3 | 2025-08-25 | CA 4 | 2025-08-20 | US

events event_date | event_type | user_id | pin_id | platform 2025-09-01 | impression | 1 | 10 | ios 2025-09-01 | click | 1 | 10 | ios 2025-09-01 | impression | 2 | 10 | web 2025-09-01 | impression | 4 | 12 | android 2025-09-01 | click | 4 | 12 | android 2025-08-31 | impression | 3 | 10 | ios 2025-08-31 | click | 3 | 10 | ios 2025-09-01 | impression | 2 | 11 | web

Output columns (sorted): event_date, pin_format, total_clicks, total_impressions, ctr. Make sure only users_d.country = 'US' and new users contribute to counts.

Compute CTR by pin_format and date

Comments (0)