# This is a query to get statistics on what days a specific user is playing games, on average of the past 4 weeks

SELECT
    DATE_FORMAT(startDate, '%W') AS day,
    SUM(TIMESTAMPDIFF(SECOND, startDate, endDate)) / 4 AS time
  FROM playtime
    WHERE userID=?
    AND startDate > NOW() - INTERVAL 4 WEEK
  GROUP BY DATE_FORMAT(startDate, '%W')
  ORDER BY time DESC

# This is a query to get the top played games on a server

SELECT
    game,
    SUM(TIMESTAMPDIFF(SECOND,startDate, IFNULL(endDate, NOW()))) AS time,
    COUNT(DISTINCT userID) AS count
  FROM
      playtime
  WHERE
      userID IN (SELECT userID FROM userGuilds WHERE guildID=?)

      # This is optional, but may be useful if you would add a filter for games/software/blocked/unknown presences
      AND game IN (SELECT game FROM knownGames WHERE type=?)

  GROUP BY game
  ORDER BY time DESC

# This is a query to get a playtime chart for a specific server and a specific game

SELECT
  DATE_FORMAT(startDate, '%M %d %Y') AS day,
    SUM(TIMESTAMPDIFF(MINUTE, startDate, IFNULL(endDate, NOW()))) AS time
  FROM playtime
  WHERE
    game=?
    AND userID IN (SELECT userID FROM userGuilds WHERE guildID=?)
  GROUP BY DATE_FORMAT(startDate, '%M %d %Y')
  ORDER BY startDate