CODEWITHSUNDEEP
sql
Nathan KL
Nathan KL

Reputation: 11

use sql to find average rating given by each user

The task asks me to use proc sql to find average rating given by each user that have reviewed more than 5 recipes. and datasets are below:

user_id   recipe_id   date           rating
231        245        17/02/2019      20 
098        134        31/01/2019      1
.......
.......

Could anyone show me how to get the result please?

Thank you very much

Upvotes: 1

Views: 623

Answers (3)

jarlh
jarlh

Reputation: 44805

Simply do a GROUP BY. Use HAVING to only return users with more than different 5 recipes.

SELECT user_id, AVG(rating)
FROM tablename
GROUP BY user_id
HAVING COUNT(distinct recipe_id) > 5

Do COUNT(distinct recipe_id) > 5 to return only users with more than 5 different recipes reviewed. (As suggested by Serge.)

COUNT(recipe_id) > 5 could include same recipe several times.

Upvotes: 1

Mer
Mer

Reputation: 129

try this using sql join

SELECT 
    r.user_id
    ,AVG(rating) avg
FROM recipe r
inner join (
        Select user_id
    from recipe 
    group by user_id
    having count(*) > 5
) c
on c.user_id =r.user_id
group by r.user_id

Upvotes: 0

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28423

You should use Aggregate function effectively to achieve these.

Try Below

SELECT recipe_id, AVG(rating)
FROM Table1
WHERE user_id IN (SELECT user_id, COUNT(recipe_id) FROM Table1 GROUP BY user_id HAVING COUNT(recipe_id) > 5)
GROUP BY recipe_id

Upvotes: 1

Related Questions