Schema
Table
Setup
| Column Name | Type | Description |
|---|---|---|
| review_id | integer | Unique review identifier. |
| review_date | date | Date the review was submitted. |
| show_id | integer | Reviewed show identifier. |
| rating | integer | Rating value. |
Monthly
Show
Rating
Calculate the average rating for each show per month.
Table
Sample Data
Input
Output
Sample Input: reviews
| review_id | review_date | show_id | rating |
|---|---|---|---|
| 1 | 2024-01-01 | 10 | 4 |
| 2 | 2024-01-02 | 10 | 5 |
| 3 | 2024-02-01 | 20 | 3 |
| 4 | 2024-02-02 | 20 | 4 |
| 5 | 2024-02-03 | 10 | 2 |
| 6 | 2024-01-05 | 20 | 5 |
Expected Output
| month | show_id | avg_rating |
|---|---|---|
| 2024-01 | 10 | 4.5 |
| 2024-01 | 20 | 5.0 |
| 2024-02 | 10 | 2.0 |
| 2024-02 | 20 | 3.5 |
SQL Editor
Run
Query
Waiting for query
| month | show_id | avg_rating |
|---|---|---|
| 2024-01 | 10 | 4.5 |
| 2024-01 | 20 | 5.0 |
| 2024-02 | 10 | 2.0 |
| 2024-02 | 20 | 3.5 |
Hints
Unlock
Clues
Hint 01: Create a month value from review_date.
Hint 02: Group by that month and show_id.
Hint 03: Use AVG(rating) as avg_rating.
Solution
Locked
Answer
Solution is locked until you decide to reveal it. Try the editor first, then open this when you want the reference answer.
SELECT TO_CHAR(review_date, 'YYYY-MM') AS month, show_id, AVG(rating) AS avg_rating FROM reviews GROUP BY TO_CHAR(review_date, 'YYYY-MM'), show_id ORDER BY month, show_id;
Explanation
Step By
Step
01
Convert each review_date into a month bucket.
02
Group reviews by month and show_id.
03
Average the rating values in each group.
Related Questions