Find user who has given the lowest average rating in '%Y-%m'

Question

There are three tables: Movies, Users, and Rating having the below structure:

Movies(id int, title text);
Users(id int, name text);
Rating(movie_id int, user_id int, rating int, created_at date);

I'm trying to find the name the user(s) who has given the lowest average rating in May 2019, with the below query:

select name 
from users 
where id = 
(
    select user_id from
    (
        select user_id, min(avgrating)
        from 
        (
            select user_id, avg(rating) as avgrating 
            from Rating 
            where strftime('%Y-%m', created_at) = '2019-05'
            group by user_id
        )
    )
);

There are two users Ben and Nicole, but the above query only returns Ben.

Contents of Movies:

id  title
1, 'Avengers:Endgame'
2, 'Aladdin'
3, 'Aquaman'

Contents of Users:

id  name
1, 'Ben'
2, 'Nicole'
3, 'James'
4, 'Tara'

Contents of Rating:

movie_id, user_id, rating, created_at
1,        1,       3,      '2019-05-07 00:10:00'
1,        2,       4,      '2019-04-28 09:00:00'
1,        3,       5,      '2019-05-11 10:40:00'
1,        4,       4,      '2019-05-02 09:30:00'
2,        1,       4,      '2019-06-16 08:00:00'
2,        2,       3,      '2019-05-30 09:00:00'
2,        3,       4,      '2019-06-01 13:50:00'
3,        1,       5,      '2019-01-05 09:55:00'
3,        2,       4,      '2019-01-28 10:00:00'

score 1 · Accepted Answer · answered Sep 02 '20 at 22:55

In SQLite 3.25 or higher, you could use rank():

select name
from (
    select u.name, rank() over(order by r.rating) rn
    from rating r
    inner join users u on u.id = r.user_id
    where r.created_date >= '2019-05-01' and r.created_date < '2019-06-01'
) t
where rn = 1

In earlier versions, I would go for a subquery that retrieves the least rating of the month, and then a join on ratings and users:

select u.name, rank() over(order by r.rating) rn
from rating r
inner join users u on u.id = r.user_id
where 
    r.created_date >= '2019-05-01' and r.created_date < '2019-06-01'
    and r.rating = (
        select min(r1.rating)
        from rating r1
        where r1.created_date >= '2019-05-01' and r1.created_date < '2019-06-01'
    )

Find user who has given the lowest average rating in '%Y-%m'

1 Answers1