How can I get the percentage of time per user in sql?

I am making an app where people can select a song and listen to it. Whenever someone plays a song it should display the percentage of the how much youve listened in an overview. I have the following tables:

Song

| SongId | SongName | SongLength |
+--------+----------+------------+
| 1      | Song 1   | 00:03:34   |
| 2      | Song 2   | 00:02:57   |
| 3      | Song 3   | 00:03:12   |

User

| UserId | UserName |
+--------+----------+
| 1      | User 1   |
| 2      | User 2   |

PlayList

| UserId | SongId | Progress |
+--------+--------+----------+
| 1      | 1      | 00:01:47 |
| 1      | 2      | 00:00:15 |
| 2      | 2      | 00:02:57 |
| 2      | 3      | 00:00:48 |

I want the result to look like this:

| UserName | SongName | PercentageListened |
+----------+----------+--------------------+
| User 1   | Song 1   | 50                 |
| User 1   | Song 2   | 8                  |
| User 2   | Song 2   | 100                |
| User 2   | Song 3   | 25                 |

I’ve looked around and found this – this almost answers my question, but not quite.

I want to know the percentage for each song and for each user.

The linked question gives the percentage for each song and for all users. How can I change this?

This is what I have so far:

SELECT 
    User.UserName AS UserName, Song.SongName AS SongName, 
    COALESCE(AvgProgress.seconds * 100.0 / DATEDIFF(second, 0, Song.SongLength), 0) AS PercentageListened
FROM 
    PlayList
INNER JOIN 
    Song ON Song.Id = PlayList.SongId
INNER JOIN 
    User ON User.Id = PlayList.UserId
LEFT JOIN
    (SELECT 
         SongId, AVG(DATEDIFF(second, 0, PlayList.Progress)) AS seconds 
     FROM 
         PlayList, User
     GROUP BY 
         PlayList.SongId) AS AvgProgress ON AvgProgress.SongId = Song.Id

How can I make this work?

Answer

This query will look like this:

SELECT 
  u.UserName,
  s.SongName,
  DATEDIFF(second, 0, p.Progress) * 100 / DATEDIFF(second, 0, s.SongLength) 
    AS PercentageListened
FROM PlayList p
JOIN Song s ON s.SongId = p.SongId
JOIN User u ON u.UserId = p.UserId

Advertisement

Answer