Ad Campaign Click-Through Rate Ranking

EASY15 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

ads =

[{"ad_id":10,"user_id":1,"action":"Clicked"},{"ad_id":10,"user_id":2,"action":"Viewed"},{"ad_id":11,"user_id":3,"action":"Clicked"},{"ad_id":11,"user_id":4,"action":"Viewed"},{"ad_id":12,"user_id":5,"action":"Clicked"},{"ad_id":12,"user_id":6,"action":"Clicked"},{"ad_id":12,"user_id":7,"action":"Viewed"}]

Ad Campaign Click-Through Rate Ranking

Hints

Ad Campaign Click-Through Rate Ranking

Hints