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'
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'
)