Getting median of column values in each group

Question

I have a table containing user_id, movie_id, rating. These are all INT, and ratings range from 1-5.

I want to get the median rating and group it by user_id, but I'm having some trouble doing this.

My code at the moment is:

SELECT AVG(rating)
FROM (SELECT rating
      FROM movie_data
      ORDER BY rating
      LIMIT 2 - (SELECT COUNT(*) FROM movie_data) % 2
      OFFSET (SELECT (COUNT(*) - 1) / 2
              FROM movie_data));

However, this seems to return the median value of all the ratings. How can I group this by user_id, so I can see the median rating per user?

Answer 1

The following gives the required median:

DROP TABLE IF EXISTS movie_data2;
CREATE TEMPORARY TABLE movie_data2 AS
SELECT user_id, rating FROM movie_data order by user_id, rating;

SELECT a.user_id, a.rating FROM (
SELECT user_id, rowid, rating
FROM movie_data2) a JOIN (
SELECT user_id, cast(((min(rowid)+max(rowid))/2) as int) as midrow FROM movie_data2 b
GROUP BY user_id
) c ON a.rowid = c.midrow
;

The logic is straightforward but the code is not beautified. Given encouragement or comments I will improve it. In a nutshell, the trick is to use rowid of SQLite.