Ad Campaign Click-Through Rate Ranking

Easy

A marketing analytics pipeline records user reactions to shown ads. Table: ads ad_id: advertisement identifier user_id: user identifier action: user reaction category Allowed action values: Clicked Viewed Ignored Metric definition per ad_id: clicked_count = number of Clicked rows viewed_count = number of Viewed rows denominator = clicked_count + viewed_count ctr = (clicked_count / denominator) * 100 Important behavior: Rows with action = Ignored do not contribute to denominator. If denominator is zero, ctr must be 0.00. Round ctr to 2 decimal places. Task: Return ctr for every ad_id present in ads. Output requirements: Return exactly two columns: ad_id, ctr Sort by ctr in descending order If ctr ties, sort by ad_id in ascending order Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

ads:
| ad_id | user_id | action  |
|-------|---------|---------|
| 1     | 101     | Clicked |
| 1     | 102     | Viewed  |
| 1     | 103     | Clicked |
| 2     | 201     | Viewed  |
| 2     | 202     | Ignored |
| 3     | 301     | Ignored |
| 3     | 302     | Ignored |

Output

[
  {"ad_id":1,"ctr":66.67},
  {"ad_id":2,"ctr":0.0},
  {"ad_id":3,"ctr":0.0}
]

Explanation

Ad 1 has 2 clicks and 1 view, so CTR is 66.67. Ad 2 has 0 clicks and 1 view, so CTR is 0.00. Ad 3 has no clicks/views, so denominator is zero and CTR is 0.00.

Example 2

Input

ads:
| ad_id | user_id | action  |
|-------|---------|---------|
| 10    | 1       | Clicked |
| 10    | 2       | Viewed  |
| 11    | 1       | Clicked |
| 11    | 3       | Viewed  |
| 12    | 5       | Clicked |
| 12    | 6       | Clicked |
| 12    | 7       | Viewed  |

Output

[
  {"ad_id":12,"ctr":66.67},
  {"ad_id":10,"ctr":50.0},
  {"ad_id":11,"ctr":50.0}
]

Explanation

Ads 10 and 11 tie at 50.00, so ad_id ascending order breaks the tie. Ad 12 has 2 clicks out of 3 engaged actions, so CTR is 66.67.

Example 3

Input

ads:
| ad_id | user_id | action  |
|-------|---------|---------|
| 99    | 10      | Viewed  |
| 99    | 11      | Viewed  |
| 99    | 12      | Ignored |
| 77    | 10      | Clicked |
| 77    | 11      | Ignored |
| 77    | 12      | Clicked |

Output

[
  {"ad_id":77,"ctr":100.0},
  {"ad_id":99,"ctr":0.0}
]

Explanation

Ad 77 has 2 clicks and 0 views, so CTR is 100.00. Ad 99 has 0 clicks and 2 views, so CTR is 0.00.

Constraints

0 <= number of rows in ads <= 200000
(ad_id, user_id) is unique within each dataset
1 <= ad_id <= 1000000000
1 <= user_id <= 1000000000
action is one of: Clicked, Viewed, Ignored
Return exactly two columns: ad_id, ctr
Round ctr to exactly 2 decimal places
If clicked_count + viewed_count = 0, ctr must be 0.00
Sort output by ctr DESC, then ad_id ASC
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns: ad_id, ctr

ads: | ad_id | user_id | action | |-------|---------|---------| | 1 | 101 | Clicked | | 1 | 102 | Viewed | | 1 | 103 | Clicked | | 2 | 201 | Viewed | | 2 | 202 | Ignored | | 3 | 301 | Ignored | | 3 | 302 | Ignored |

ads: | ad_id | user_id | action | |-------|---------|---------| | 10 | 1 | Clicked | | 10 | 2 | Viewed | | 11 | 1 | Clicked | | 11 | 3 | Viewed | | 12 | 5 | Clicked | | 12 | 6 | Clicked | | 12 | 7 | Viewed |

ads: | ad_id | user_id | action | |-------|---------|---------| | 99 | 10 | Viewed | | 99 | 11 | Viewed | | 99 | 12 | Ignored | | 77 | 10 | Clicked | | 77 | 11 | Ignored | | 77 | 12 | Clicked |