SQL Date Queries

MySQL is a powerful tool.

Lately I have been using a lot of the powerful date related functions.

How many years ago did they take the test?

SELECT
 FLOOR(PERIOD_DIFF(Test.taken, NOW())/12)) AS "Years Ago"
FROM
 tests Test 

How many people took the test per "years ago"

SELECT
 COUNT(*),FLOOR(PERIOD_DIFF(Test.taken, NOW())/12)) AS "Years Ago"
FROM
 tests Test 
GROUP BY
 FLOOR(PERIOD_DIFF(Test.taken, NOW())/12))

How many people took the test between now and 2 years ago?

SELECT
 COUNT(*)
FROM
 tests Test
WHERE
 Test.taken >= DATE_SUB(NOW(), 'INTERVAL 2 YEAR')

How many people took the test between 2 and 3 years ago?

SELECT
 COUNT(*)
FROM
 tests Test 
INNER JOIN
 users User
ON
 User.id = Test.user_id
WHERE
 Test.taken <= DATE_SUB(NOW(), 'INTERVAL 2 YEAR')
AND
 Test.taken >= DATE_SUB(NOW(), 'INTERVAL 3 YEAR')
GROUP BY
 User.id

What if a user could take the test multiple times in one year

SELECT
 COUNT(*),FLOOR(PERIOD_DIFF(Test.taken, NOW())/12)) AS "Years Ago"
FROM
 tests Test 
INNER JOIN 
 users User
ON
 User.id = Test.user_id
GROUP BY
 FLOOR(PERIOD_DIFF(Test.taken, NOW())/12)), User.id 

How many times each user took a test each year

SELECT
 User.id,
 User.name,
 COUNT(*),
 FLOOR(PERIOD_DIFF(Test.taken, NOW())/12)) AS "Years Ago"
FROM
 tests Test 
INNER JOIN 
 users User
ON
 User.id = Test.user_id
GROUP BY
 FLOOR(PERIOD_DIFF(Test.taken, NOW())/12)), User.id

Explanation:
FLOOR - Essentially takes off the extra digits after the division
PERIOD_DIFF - The number of months between dates