#number of users, movies and ratings and average rating
SELECT count(DISTINCT userid) AS nbusers, count(DISTINCT itemid) AS nbitems, count(*) AS nbratings, avg(rating) AS avgrating 
FROM ratings;

#distribution of the number of ratings by user
SELECT count(userid) AS nbusers, rating 
FROM ratings 
GROUP BY rating 
ORDER BY rating DESC;

#distribution of the number of ratings by users (rounded to 10)
SELECT count(userid) AS nbusers, nbratings 
FROM (SELECT round(count(itemid)/10,0)*10 AS nbratings, userid 
      FROM ratings 
      GROUP BY userid
      ) AS nbratingsbyusers 
GROUP BY nbratings 
ORDER BY nbratings DESC;

#distribution of the number of ratings by movies (rounded to 10)
SELECT count(itemid) AS nbitems, nbratings 
FROM (SELECT round(count(userid)/10,0)*10 AS nbratings, itemid 
      FROM ratings 
      GROUP BY itemid
      ) AS nbratingsbyitems 
GROUP BY nbratings 
ORDER BY nbratings DESC;

#distribution of the average ratings by users (rounded to 0.1)
SELECT count(userid) AS nbusers , avgrating 
FROM (SELECT round(avg(rating),1) AS avgrating, userid 
      FROM ratings 
      GROUP BY userid
      ) AS avgratingbyusers 
GROUP BY avgrating 
ORDER BY avgrating DESC;

#distribution of the average ratings by movies (rounded to 0.1)
SELECT count(itemid) AS nbitems , avgrating 
FROM (SELECT round(avg(rating),1) AS avgrating, itemid 
      FROM ratings 
      GROUP BY itemid
      ) AS avgratingbyitems 
GROUP BY avgrating 
ORDER BY avgrating DESC;