# Who has the highest score in a game (counter strike) in last seven days?

SELECT Max(Score.score)
FROM Score
INNER JOIN Game
ON Score.game_id=Game.game_id
WHERE Game.game_name=’counter strike’;

# What is the busiest time for a game (call of duty)?

SELECT game_start
FROM
(
SELECT game_start, COUNT(Score.game_start) AS counted
FROM Score
INNER JOIN Game
ON Score.game_id=Game.game_id
WHERE Game.game_name=’call of duty’;
)
HAVING counted = MAX(counted);

SELECT MAX(counted) FROM
(
SELECT COUNT(Score.game_start) AS counted
FROM Score
INNER JOIN Game
ON Score.game_id=Game.game_id
WHERE Game.game_name=’call of duty’;
) AS counts;

GROUP BY game_start
ORDER BY totalCount DESC
LIMIT 1

# Which user haven’t played any games in last 3 days?

SELECT user_name, user_id
FROM User
INNER JOIN Score
ON User.user_id=Score.user_id
WHERE user_id
NOT IN
(
Select user_id
From Score
Where Score.game_start >= ‘2012-02-23 23:59:59’
);

timestamp BETWEEN ‘2012-05-05 00:00:00’ AND ‘2012-05-05 23:59:59’
timestamp <= '2012-05-05 23:59:59'

# Which game was played how many times by which user?

Select Game.game_name, COUNT(*) TotalCount, User.user_name
From Score
INNER JOIN Game ON Score.game_id=Game.game_id
INNER JOIN User ON Score.user_id=User.user_id
GROUP BY Game.game_name, User.user_name

colour <> ‘red’

# What is the difference (performance) of a user in a game (call of duty) last week and this week?

SELECT table1.ThisWeekAverage – table2.LastWeekAverage AS PerformanceChange
FROM
(SELECT AVG(score) AS ThisWeekAverage
FROM Score
Where Score.game_start > ‘one week ago’ AND Score.game_start < 'now') AS table1, (SELECT AVG(score) AS LastWeekAverage FROM Score WHERE Score.game_start > ‘two week ago’ AND Score.game_start < 'one') AS table2