Join Our 5-Week ML/AI Engineer Interview Bootcamp 🚀 led by ML Tech Leads at FAANGs
Back to Questions
2. Ad CTR Calculation
easy
Metasenior
What is the click-through rate (CTR) for each ad_name in the ads_actions table? Define CTR as 100 * SUM(clicked_ad) / COUNT(*) and return ad_name and ad_ctr rounded to 2 decimal places. Order the results by ad_ctr in descending order, then by ad_name in ascending order.
ads_actions
| Column Name | Type |
|---|---|
| ad_exp_id | int64 |
| ad_name | object |
| cpc_rate | object |
| clicked_ad | int64 |
Expected Output Schema
| Column Name | Type |
|---|---|
| ad_name | object |
| ad_ctr | float64 |
Constraints
Output columns: ad_name, ad_ctr
Round CTR to 2 decimal places
Sort: ad_ctr DESC, ad_name ASC
Hint 1
Group the table by ad_name to aggregate per ad.
Hint 2
Compute two aggregates per ad_name: SUM(clicked_ad) for clicks and COUNT(*) for total impressions/rows.
Hint 3
Create ad_ctr = ROUND(100.0 * clicks / impressions, 2) and sort by ad_ctr DESC, ad_name ASC.
Roles
Data Scientist
Data Analyst
Data Engineer
Companies
MetaLevels
senior
entry
Tags
sql
aggregation
group-by
click-through-rate
rounding
39 people are solving this problem
Python EditorLn 1, Col 1