Jellyfin Playback Reporting queries

Couple of handy queries you can use to find popular Movies and TV Shows on your Jellyfin instance.

Unique users that watched a TV Show

Here we will do some SQL magic to split ItemName with - sign. Based on my short observation i noticed that the ItemName pattern is TV Show Name - Episode Name. So by splitting at - and picking the first part, we can focus on only on TV Show not per episode.

Another important factor is PlayDuration > 20 where we count only playbacks that lasted at least 20minutes. To avoid counting any short playbacks.

SELECT ItemName, COUNT(*) as UniqueUsers
FROM (
  SELECT DISTINCT SUBSTR(ItemName, 1, INSTR(ItemName, '-')-1) AS ItemName, ItemType, COUNT(DISTINCT UserId), UserId
  FROM PlaybackActivity 
  WHERE ItemType is "Episode" and PlayDuration > 20
  GROUP BY ItemName, ItemType, UserId
)
GROUP BY ItemName
ORDER BY UniqueUsers DESC
LIMIT 100

Unique users that watched a Movie

The naming here is simpler, so no magic needed. And same as before, we limit PlayDuration > 50 to focus on playbacks longer than 50 minutes.

SELECT ItemName, COUNT(*) as UniqueUsers
FROM (
  SELECT DISTINCT ItemName, ItemType, COUNT(DISTINCT UserId), UserId
  FROM PlaybackActivity 
  WHERE ItemType is "Movie" and PlayDuration > 50
  GROUP BY ItemName, ItemType, UserId
)
GROUP BY ItemName
ORDER BY UniqueUsers DESC
LIMIT 100