SQL Query Help for Automatic Point Calculation

1

Hey Guys,

I have a quick SQL query question and not really sure where to get some help, so i thought i would post here and see if anyone can help make a query for me (my sql knowledge does not extend that far to make such complex query). If this breaks the community rules, please let me know and i will remove it.

So, what i have is this. 3 tables:

Users:
+----+-------+-----+
| id | name  | ... |
+----+-------+-----+
| 1  | bob   | ... |
| 2  | bill  | ... |
+----+-------+-----+
Questions:
+----+------------+-----------+
| id | difficulty | txt       |
+----+------------+-----------+
| 1  | 5          | 1 + 1 = 2 |
| 2  | -5         | 1 + 1 = 3 |
| 3  | 9          | 4 * 0 = 0 |
+----+------------+-----------+
Answers:
+----+---------+-------------+--------+
| id | user_id | question_id | answer |
+----+---------+-------------+--------+
| 1  | 1       | 1           | 1      |
| 2  | 1       | 2           | -1     |
| 3  | 1       | 3           | 1      |
| 4  | 2       | 1           | 1      |
| 5  | 2       | 2           | 1      |
| 6  | 2       | 3           | -1     |
+----+---------+-------------+--------+

Just to give a rough explanation, this is stripped down example of users answrring yes/no questions to accumulate points. Each question (questions table) has a difficulty (number between +10 to -10 where positive means the answer is YES and negative means the answer is NO) which determines the amount of points the user gets if they guess correct, and amount of points they loose if they guess wrong. Each answer (answers table) is a list of answers by users where the answer column states the yes (+1) and no (-1).

I could use the language to get the list of rows for the given user and do the calculations manually, but it would be ideal if i can do this automatically using SQL.

I expect these tables to be really long and points calculation needs to happen often. So im looking for creating a sql query where i can call with a given user id, and it gives me the amount of points the user has (only need to do per one user at a time).

for example, bobs points = (1 * 5) + (-1 * -5) + (1 * 9) = 19; bills points = (1 * 5) + (1 * -5) + (-1 * 9) = -9;

Any suggestions/help is very appreciated. :smiley:

Also, if there is a better way to structure the database table to get the calculations easier, please let me know. I have full freedom to re-structure this, so long as it gets the end result: users can vote for given list of questions (yes/no), and each question has sort of a difficulty that determines the amount of points that the user gains/looses depending on the answer.

Future extensions: i will likely add some feature on the future where it not only records the answer +1/-1, but also records the time (seconds) it took for the user to answer, and this number then also needs to be deducted from the total points (lets say every second the user took would reduce 0.1 points).

2 
select u.name
      ,sum(a.answer*q.difficulty) as points
  from users as u
       inner join answers as a
               on a.user_id=u.id
       inner join questions as q
               on q.id=a.question_id
 where u.name='bob'
;

If you want several users:

select u.name
      ,sum(a.answer*q.difficulty) as points
  from users as u
       inner join answers as a
               on a.user_id=u.id
       inner join questions as q
               on q.id=a.question_id
 where u.name in ('bob'
                 ,'bill'
                 ,'john'
                 )
 group by u.name
 order by points desc
         ,u.name
;

If you want all users, remove the entire where statement.

When you implement the "time factor", add two time or datetime fields to the answer table, and call them starttime and endtime. You can then do:

select u.name
      ,sum(a.answer
          *q.difficulty
          +datediff(seconds,a.endtime,a.starttime)*.01
          ) as points
  from users as u
       inner join answers as a
               on a.user_id=u.id
       inner join questions as q
               on q.id=a.question_id
 group by u.name
 order by points desc
         ,u.name
;

As to redesigning your tables, that really depends on your plans for functionality.

  • do you want the students to have more choices than true/false
  • in "more choice" situations, should false answers be "punished" differently
  • should "time penalty" start after certain time
  • should "time penalty" factor be adjustable per question
  • should "time penalty" have a max
  • etc.