Top Rated Recipes
ASKED IN INTERVIEWPinterest
Problem Statement
Pinterest users want to see the best recipes for their specific diets. Find the highest-rated recipe in each category.
Rules:
- Output columns: category, recipe_name, avg_rating.
- Calculate average rating per recipe first.
- In case of a tie for the top spot in a category, return all tied recipes.
- Round average ratings to 2 decimal places.
- Results must be sorted by category in ascending order, then by avg_rating in descending order.
Tests your understanding of
Window Functions, Ranking and Subqueries
Input Tables
categories
| category_id(INTEGER) | category_name(VARCHAR) |
|---|---|
| 1 | Vegan |
| 2 | Keto |
| 3 | Paleo |
recipes
| recipe_id(INTEGER) | recipe_name(VARCHAR) | category_id(INTEGER) |
|---|---|---|
| 10 | Tofu Scramble | 1 |
| 11 | Chickpea Curry | 1 |
| 20 | Steak & Butter | 2 |
| 21 | Egg Salad | 2 |
| 30 | Nut Mix | 3 |
reviews
| review_id(INTEGER) | recipe_id(INTEGER) | rating(INTEGER) |
|---|---|---|
| 1 | 10 | 5 |
| 2 | 10 | 4 |
| 3 | 11 | 5 |
| 4 | 20 | 5 |
| 5 | 21 | 5 |
| 6 | 30 | 3 |
Expected Output
| category(VARCHAR) | recipe_name(VARCHAR) | avg_rating(DECIMAL) |
|---|---|---|
| Keto | Egg Salad | 5 |
| Keto | Steak & Butter | 5 |
| Paleo | Nut Mix | 3 |
| Vegan | Chickpea Curry | 5 |
Tags
MediumASKED IN INTERVIEWWindow FunctionsRankingSubqueries
20-25 min
45%