Calculate Ad Performance with Click-Through and Conversion Rates

Q: How do I practice SQL interview questions?

PracHub provides an interactive SQL console where you can write and test queries against real database schemas. Get instant feedback and compare your solution with the expected output.

Q: What difficulty level is this coding question?

This is a Medium difficulty Data Manipulation (SQL/Python) question, commonly asked during Onsite rounds at Meta.

Q: What role is this question designed for?

This question is commonly asked for Data Scientist candidates at Meta during technical interviews.

Question

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.

PracHub · Accepted Answer

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.

Quick Overview

Quick Overview