As part of their assessment and curriculum review, our curriculum team wanted to know the most commonly selected wrong answer for each question on our district assessments. This data allows them to identify questions that may be unclear or common misconceptions that need to be addressed in the classroom.
The DENSE_RANK function makes this task easy. I specifically chose DENSE_RANK as I wanted to allow for ties. So, if 2 incorrect responses have the same number of selections, both will be returned in my query.
I use DENSE_RANK to assign a rank to each wrong answer choice in descending order (highest count first). Rankings are assigned for each question on each district assessment.
,DENSE_RANK() OVER (PARTITION BY Test, [Question Number] ORDER BY [Student Count] DESC) AS ‘MostCommonWrongAnswer’
To return only the most common wrong answer, I restrict results to those answers with a ranking of 1.
WHERE MostCommonWrongAnswer = 1
The query is attached and can also be found in our GitHub repository.
