skip to Main Content

I am working with the ml100k dataset to write a query that can fetch me the most rated movie for every age.
Here’s how my tables are defined.
users
id | age | gender | occupation | zipcode
ratings
userid | movieid | rating | ts

What I’ve done so far

  1. Since the count wasn’t explicitly defined, I wrote a query to find it.
   SELECT age, movieid, COUNT(*) AS mcount
   FROM ratings
   JOIN users ON id = userid
   GROUP BY age, movieid

This gave me the count for every movie for every age.

10  1   1
11  1   1
13  1   3
14  1   1
15  1   2
16  1   4
17  1   4
18  1   6
19  1   15
20  1   22
21  1   14
  1. To find the maximum for each age
    SELECT age, MAX(mcount) AS mc
    FROM (
        SELECT age, movieid, COUNT(*) AS mcount
        FROM ratings
        JOIN users ON id = userid
        GROUP BY age, movieid
    ) t1
    GROUP BY age
7   1
10  1
11  1
13  5
14  3
15  5
16  5
17  11
18  16
19  21
20  25
21  23

This gave me the age and the max count. However, I also want the corresponding movie ID and this where I have been stuck. My thought process is to join these results with the first table but it is not working. Are there any other alternatives I can try?
This is the query I used.


SELECT users.age, ratings.movieid, count(*) as mc2
FROM ratings JOIN users ON id = userid 
INNER JOIN
(
    SELECT age, MAX(mcount) AS mc
    FROM (
        SELECT age, movieid, COUNT(*) AS mcount
        FROM ratings
        JOIN users ON id = userid
        GROUP BY age, movieid
    ) t1
    GROUP BY age
)t2
ON t2.age = users.age
WHERE mc2=t2.mc
GROUP BY users.age, ratings.movieid;

2

Answers


  1. You can do it like this:

    SELECT t.age, t.movieid, t.mcount
    FROM (
       SELECT age, movieid, COUNT(*) AS mcount
       FROM ratings
       JOIN users ON id = userid
       GROUP BY age, movieid
    ) t
    LEFT JOIN (
       SELECT age, movieid, COUNT(*) AS mcount
       FROM ratings
       JOIN users ON id = userid
       GROUP BY age, movieid
    ) t2
    ON t.age = t2.age AND
       t.movieid <> t2.movieid AND
       t.mcount < t2.mcount
    WHERE t2.age IS NULL
    

    Explanation:

    • first subselect
      • joins ratings and users
      • on the id of users
      • groups by age and movieid
      • to find the groups
      • selects the fields you need
      • yields all groups
    • second subselect
      • is the same as the first
    • we LEFT JOIN the two groups by
      • matching age
      • different movieid (so we will have sensible count comparisons)
      • and the first mcount being smaller than the second
      • because we want to find items of the first group
      • which do not have higher count matches
      • so we search for higher count matches
    • and in the WHERE clause we exclude the scenario when we found higher count matches in the second group than in the first group

    Fiddle: http://sqlfiddle.com/#!9/f8defe/6

    Login or Signup to reply.
  2. It is 8.2.0 – vnk

    WITH cte AS (
            SELECT age, 
                   movieid, 
                   COUNT(*) AS mcount, 
                   RANK() OVER (PARTITION BY age, ORDER BY COUNT(*) DESC) rnk
            FROM ratings
            JOIN users ON id = userid
            GROUP BY 1, 2
            )
    SELECT age, 
           GROUP_CONCAT(movieid) movie_ids, 
           mcount
    FROM cte
    WHERE rnk = 1
    GROUP BY 1, 3
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search