CODEWITHSUNDEEP
mysql
Harsh Gupta
Harsh Gupta

Reputation: 4538

MySQL - Calculate rank of students on multiple conditions

I am sorry, but the title is a bit misleading. Say I have following tables

-- user_tests table
Name                    |   Type
------------------------|----------------
id                      |   int(11)
user_id                 |   int(11)
test_schedule_id        |   int(11)
device_user_test_id     |   varchar(255)
time_taken              |   int(11)
total_marks             |   int(11)
total_questions         |   int(11)
attempt_count           |   int(11)
created_at              |   datetime
updated_at              |   datetime
-- user_test_questions table
Name                    |   Type
------------------------|-------------
id                  |   int
user_test_id            |   int
time_taken              |   int
marks_obtained          |   int
question_id             |   int
is_correct              |   enum
test_section_id         |   int
created_at              |   datetime
updated_at              |   datetime

The data in user_tests table are list of students who have taken a specific exam identified by test_schedule_id. Each test_schedule_id, has one or more test_section_id.

I am trying to calculate rank of students on following conditions:

  1. Get rank based on total_marks of students for a test_schedule_id
  2. If rank for previous case is same, get rank on test_section_id = 1
  3. If rank for previous case is same, get rank on test_section_id = 2
  4. If rank for previous case is same, get rank on test_section_id = 3
  5. If rank for previous case is same, get rank on users' date of birth, which is in users table

I can do them easily in Rails (or any framework), but I want to avoid it and solve it in SQL either using a View or a Stored Procedure.

Now I know how to calculate rank individually, but I am struggling with combining these conditions. Is there a way to do this? I am just missing MS SQL Server Rank() function, I guess!

Thank you

Edit: Rank based on either total_marks in user_tests table of SUM(marks_obtained) in user_test_questions table.

Upvotes: 0

Views: 1092

Answers (1)

piotrm
piotrm

Reputation: 12356

It's a good idea to let mysql do its job - all the joins and sorting type of stuff.

I would go for something like:

SELECT u.id AS uid, 
  SUM( utq.marks_obtained ) AS total_marks,
  SUM( (utq.test_section_id = 1)*utq.marks_obtained ) AS section1_marks,
  SUM( (utq.test_section_id = 2)*utq.marks_obtained ) AS section2_marks,
  SUM( (utq.test_section_id = 3)*utq.marks_obtained ) AS section3_marks,
  u.birthdate
FROM user_test_questions utq
JOIN user_tests ut ON ut.id = utq.user_test_id
JOIN users u ON u.id = ut.user_id
WHERE ut.test_schedule_id = 1
GROUP BY utq.user_test_id, u.id, u.birthdate
ORDER BY total_marks DESC, section1_marks DESC, section2_marks DESC, section3_marks DESC, u.birthdate

The trick to count marks for each section is to multiply marks_obtained with boolean (test_section_id=1), which will be 1 for rows with test_section_id = 1 and 0 for other sections and so on.

In the GROUP BY you see three columns to comply with sql standard but you can as well try to see just utq.user_test_id there, because other columns have the same values within each group.

With highly selective condition for test_schedule_id (you should have it indexed obviously) and not many students attending each test (hundreds at most probably) this query should be instant because all the sorting will be done on a very small temporary table.

Upvotes: 1

Related Questions