SQL Answers

There are often multiple different ways to answer a question. If you feel you have a different answer that's valid, feel free to leave a comment or to email me.

Section 1

Question: How would you select when and what games user 2435649 logged into during November?

Answer:

SELECT
     login_time,
     game_id
FROM logins
WHERE
     user_id=2435649
     AND login_time::date BETWEEN '11/1/2012'  AND'11/30/2012';

Section 2

Question: Given the char_info table outlined here, calculate the average number of quests that have been completed by a level 90 character. Hint: remember that an average is calculated as the sum of the values divided by how many values there are. Where count() counts how many non-NULL entries are in a selection, the sum() function will add up the values.

Answer:

SELECT
     sum(quests_completed)/count(*)
FROM char_info
WHERE level=90;

There are a couple other ways to do this. count(*) could be replaced with count(char_id) or count(quests_completed) since neither of those fields should ever be null in this table. The other fields should work as well.

Additionally, there is yet another function avg() which calculates the average of numerical values. So we could have just done

SELECT
     avg(quests_completed)
FROM char_info
WHERE level=90;