Skip to content

Instantly share code, notes, and snippets.

@oleander
Created September 9, 2013 22:40
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save oleander/6502454 to your computer and use it in GitHub Desktop.
Save oleander/6502454 to your computer and use it in GitHub Desktop.
SELECT AVG(i.total), MAX(i.total)
FROM(
SELECT COALESCE(SUM(a.count) + SUM(b.count), 0) as total
FROM users
LEFT JOIN (
SELECT
COUNT(*) as count,
user_id
FROM "quick_answers"
GROUP BY user_id
) a ON a.user_id = users.id
LEFT JOIN (
SELECT
COUNT(*) as count,
taken_exams.user_id
FROM "answers"
INNER JOIN taken_exams ON taken_exams.id = answers.taken_exam_id
GROUP BY taken_exams.user_id
) b ON b.user_id = users.id
GROUP BY users.id
) i
WHERE i.total != 0;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment