I have an SQL problem to solve, although it appears simple, I am not getting the correct output. here is the question and my query let me know what is that I am missing
For each customer, rank the cuisines based on the average overall rating given by the customer (with rank 1 being the highest) and classify their favorite and least favorite cuisine on the basis of the given rank. Assign the same rank to the same value, and the rank should be skipped. Consider cuisine_preference_level as follows:
• Favorite: Rank between 1 to 10.
• Least Favorite: Rank between 11 and 20.
• Neutral: Rank greater than 20.
Expected Output:
consumer_id, cuisine_id, avg_rating, cuisine_preference_rank, cuisine_preference_level in the same sequence.
Sort the consumer_id in ascending order.
tables and columns: Customers: consumer_id; Cuisines: cuisine_id; Ratings: consumer_id, overall_rating;consumer_cuisine_preferences: consumer_id, preferred_cuisine
QUERY:
WITH AvgRatings AS (
SELECT
r.consumer_id,
ccp.preferred_cuisine AS cuisine_id,
AVG(r.overall_rating) AS avg_rating
FROM
Ratings r
INNER JOIN
consumer_cuisine_preferences ccp ON r.consumer_id = ccp.consumer_id
GROUP BY
r.consumer_id, ccp.preferred_cuisine
),
Ranking AS (
SELECT
consumer_id,
cuisine_id,
avg_rating,
RANK() OVER (PARTITION BY consumer_id ORDER BY avg_rating DESC) AS cuisine_preference_rank
FROM
AvgRatings
),
Categorization AS (
SELECT
consumer_id,
cuisine_id,
avg_rating,
cuisine_preference_rank,
CASE
WHEN cuisine_preference_rank BETWEEN 1 AND 10 THEN 'Favorite'
WHEN cuisine_preference_rank BETWEEN 11 AND 20 THEN 'Least Favorite'
ELSE 'Neutral'
END AS cuisine_preference_level
FROM
Ranking
)
SELECT
consumer_id,
cuisine_id,
avg_rating,
cuisine_preference_rank,
cuisine_preference_level
FROM
Categorization
ORDER BY
consumer_id ASC,