Category Click-Through Rates
Amazon● MediumFULLTIMEONSITE INTERVIEWProblem statement
You are given two tables describing widget impressions and server-side clicks. The pair (request_id, widget_index) identifies one impression, whose category is stored in impressions. Each row in clicks refers to one of those impressions.
Return one row for every category present in impressions, with these columns:
category: the exact category label.clicks: the number of its impressions that have a matching click.impressions: the total number of its impressions.ctr:clicks / impressions, rounded to twelve decimal places.
Keep categories with no clicks and assign them a rate of 0. Compare all text values case sensitively. Return rows in any order; an empty impression table produces no rows.
Table schema
Use the same input data with any supported language. Open the Schema tab in the editor to see the generated SQL setup or Pandas DataFrames.
impressions
Flattened widget impressions; one row per request and widget pair.
| Column | Type | Nullable | Description |
|---|---|---|---|
| request_id | Text | No | — |
| widget_index | Text | No | — |
| category | Text | No | — |
clicks
At most one click for each impression.
| Column | Type | Nullable | Description |
|---|---|---|---|
| request_id | Text | No | — |
| widget_index | Text | No | — |
Expected result
Your query or function must return these columns.
| Column | Type | Nullable | Description |
|---|---|---|---|
| category | Text | No | — |
| clicks | Integer | No | — |
| impressions | Integer | No | — |
| ctr | Decimal | No | — |
Row order: any order is accepted. Numeric tolerance: 1e-9.
Constraints
- Each table contains at most
10000rows. - All columns are non-null. Text values contain
1to40ASCII letters, digits, or underscores. - The pair
(request_id, widget_index)is unique in each table. - Every click pair exists in the impression table.
- Categories are exact, case-sensitive labels; the same widget index may occur in different requests.