// This is a query to get the playtime of a specific game of a user

SELECT
    SUM(IF(
        startDate > ?,
        TIMESTAMPDIFF(SECOND,startDate, IFNULL(endDate, NOW())),
        0)) AS time
FROM
    playtime
WHERE
    userID = ?
    AND game = ?


// This is a query to get the top played games of a user

SELECT
    game,
    SUM(TIMESTAMPDIFF(SECOND,startDate, IFNULL(endDate, NOW()))) AS time
FROM
    playtime
WHERE
    userID = ?
GROUP BY game
ORDER BY time DESC
LIMIT 10

// If you want some kind of award system here is a query for the role awards of TimePlayed
// Just to be clear: the userGuilds table contains every guild ID and member ID of everyone sharing a server with the bot,
// so for example if I am in a server my ID and the server ID would be inserted into the userGuilds table.
//If I would join another server my ID and the other server ID would also be inserted.

SELECT
  playtime.userID AS userID,
  roleAwards.guildID AS guildID,
  roleAwards.roleID AS roleID,
  IF(SUM(TIMESTAMPDIFF(SECOND,startDate, IFNULL(endDate, NOW()))) > roleAwards.time, 1, 0) AS assign

FROM
    playtime

INNER JOIN roleAwards ON playtime.userID IN (SELECT userID FROM userGuilds WHERE guildID=roleAwards.guildID)

WHERE (
    SUBSTRING_INDEX(SUBSTRING_INDEX(roleAwards.game, '|', 1), '|', -1) = playtime.game
    OR SUBSTRING_INDEX(SUBSTRING_INDEX(roleAwards.game, '|', 2), '|', -1) = playtime.game
    OR SUBSTRING_INDEX(SUBSTRING_INDEX(roleAwards.game, '|', 3), '|', -1) = playtime.game
)
AND playtime.startDate > date_sub(NOW(), INTERVAL roleAwards.per SECOND)
AND userID NOT IN (SELECT userID FROM privateUsers)

GROUP BY playtime.userID, playtime.game, roleAwards.roleID