Calculate Ad Performance with Click-Through and Conversion Rates
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
impressions
+---------+---------+---------------------+-----------+
| user_id | ad_id | event_time | platform |
+---------+---------+---------------------+-----------+
| 101 | 9001 | 2023-07-01 10:00:00 | iOS |
| 102 | 9002 | 2023-07-01 10:00:05 | Android |
| 101 | 9003 | 2023-07-01 10:00:10 | Web |
+---------+---------+---------------------+-----------+
clicks_conversions
+---------+---------+---------------------+------------+
| user_id | ad_id | click_time | is_purchase|
+---------+---------+---------------------+------------+
| 101 | 9001 | 2023-07-01 10:00:02 | 0 |
| 102 | 9002 | 2023-07-01 10:00:06 | 1 |
| 103 | 9004 | 2023-07-01 10:00:12 | 0 |
+---------+---------+---------------------+------------+
##### Scenario
You are given two tables that log impressions, clicks and conversions for in-app ads. Build a KPI to evaluate ad performance and write the SQL needed to compute it.
##### Question
Propose a single metric that captures both click-through and conversion quality. Write a SQL query that joins the two tables and returns the metric by ad_id for the last 7 days.
##### Hints
Define join keys and timing windows; consider CTR, CVR or a composite score.
Overview: This question evaluates a candidate's competency in designing and computing ad-performance KPIs by performing data manipulation, joining event tables, applying time-window filters, and aggregating metrics using SQL or Python.
You are given two tables that log impressions, clicks, and conversions for in-app ads. Propose a single metric that captures both click-through and conversion quality, and write a SQL query that joins the two tables and returns that metric by ad_id for impressions that occurred between 2025-05-26 and 2025-06-01 (inclusive).
Tables
impressions(user_id INTEGER, ad_id INTEGER, event_time TIMESTAMP, platform VARCHAR)
clicks_conversions(user_id INTEGER, ad_id INTEGER, click_time TIMESTAMP, is_purchase INTEGER)
Hints
- Join on user_id and ad_id, and attribute clicks that occur after the impression within a reasonable window (e.g., 1 hour).
- Filter to impressions whose event_time is between 2025-05-26 and 2025-06-01.