Thursday, 19 September 2013

SQL query - Using timestamps in database to select only recent rows

SQL query - Using timestamps in database to select only recent rows

I have a table that records users answers for a number of questions:
tableA
user_id | question_id | date answered | correct?
66 345 timestamp 1
34 654 timestamp 0
34 654 timestamp 1
Every question attempt by every user is stored in the database.
I then also have a list of categories and the question_ids that go in that
category. e.g
tableB
category_id | question_id
1 34
1 44
1 23
2 99
2 44
I am trying to write a query to work out the percentage of questions in
the category that the user has previously answered correctly (where
correct? = 1) and also the percentage of questions correct of the last 20
questions answered in the category.
So far I can do the first part, but not the second
SELECT category_id, COUNT(*), COUNT(correct?)
FROM tableA LEFT JOIN tableB USING (question_id)
WHERE user_id = 1
this gives me the number of questions in total in the category and the
number of questions the user has answered correctly in the category.

No comments:

Post a Comment