Podcast Search Relevance and Weak-Hit Rate

Problem

Table: `SearchHits`

```text
+-------------+----------+
| Column | Type |
+-------------+----------+
| search_term | varchar |
| episode | varchar |
| slot | int |
| stars | int |
+-------------+----------+
Each row is one episode that appeared in the results for a search term, the
slot (1 = top) it appeared in, and the star rating (1 to 5) a listener gave
that result. A search_term can have many rows. There is no single primary key.
```

For each `search_term`, compute two numbers:

- `relevance`: the average of `stars / slot` across that term's rows, rounded to 2 decimals.
- `weak_hit_pct`: the percentage of that term's rows whose `stars` is below 3, rounded to 2 decimals.

Return `search_term`, `relevance`, `weak_hit_pct`.

Tables

Example rows — the live problem includes the full dataset.

Expected output

Your answer should return 2 rows with the columns search_term, relevance, weak_hit_pct.

Starter code (SQL)

SELECT *
FROM SearchHits;

Related SQL questions

• Above-Average CampusesEasy
• Adjacent Open Parking SpotsEasy
• App Ad Click-Through RateEasy
• Asset Labels With a Three-Digit CodeEasy
• Average Listening Sessions per ListenerEasy
• Average Nightly Rate Earned per RoomEasy