CODEWITHSUNDEEP
sql
Chuck
Chuck

Reputation: 4892

How can I perform this search with SQL?

I have three tables with the following (relevant) columns:

Users are presented with the questions and when they answer them by selecting one of the available answers, this creates a choices record and assigns the answer_id the appropriate value. Users can answer a question multiple times.

I need a query that will tell me, taking into account only the latest choice for each answered question, how many were answered correctly. I know enough SQL to be dangerous, but this one is above my head.

As an example:

questions

id level answer_id
------------------
1    1     101
2    1     102
3    1     103
4    2     110

choices

question_id answer_id user_id date_answered
--------------------------------------------
1           101       201     2019-01-24
1           104       201     2019-01-25
2           105       201     2019-01-25
2           102       201     2019-01-26
3           103       201     2019-01-26

So user 201 answered the first question correctly and then incorrectly. Then they answered the second question incorrectly and then correctly. Finally, they answered question 3 once and it was correct.

Note that this is all within a question level. If the user is at level 1, I'd only be considering choices to level 1 questions. If at level 2, only choice to level 2 question.

Although they've answered all the level 1 questions correctly, they've only answered level 1 most recently correctly twice. 2 would be the result I'm looking for, therefore.

Is there a SQL query that can do this or must I use a loop within the programming?

Upvotes: 1

Views: 77

Answers (1)

Arne Saupe
Arne Saupe

Reputation: 111

I don't currently have SQL Server setup so there might be some typos in my answer. This should get you questions answered correctly only if it is the most recent answer by that user.

SELECT * 
FROM
    questions a INNER JOIN
    choices b on (a.question_id = b.question_id) INNER JOIN
    (   SELECT question_id, user_id, max(date_answered) as date_answered 
        FROM choices 
        GROUP BY question_id, user_id
    ) c
    on (b.question_id = c.question_id and b.user_id = c.user_id and b.date_answered = c.date_answered)

Answer to updated question:

SELECT * 
FROM
    questions a INNER JOIN
    choices b on (a.id = b.question_id) INNER JOIN
    (   SELECT question_id, user_id, max(date_answered) as date_answered 
        FROM choices 
        GROUP BY question_id, user_id
    ) c INNER JOIN
    on (b.question_id = c.question_id and b.user_id = c.user_id and b.date_answered = c.date_answered) INNER JOIN
    users d on (d.id = b.user_id and d.level = a.level)

Upvotes: 1

Related Questions