Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

T-SQL: Capture Related Data and Return By Aggregates


By:   |   Read Comments   |   Related Tips: More > T-SQL

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


Problem

We have online games that allow user input when answering questions, offer multiple choice quizzes, or challenge our users with timed practices. We want to add an increased difficulty feature to our games that track's the most commonly missed answers for specific questions from user input and uses these as options in our other quizzes with multiple choice. This will continuously make the wrong answers more challenging and make it more difficult for the user to correctly pick the right answer.

Since we're retaining the user input on both questions where the user enters information or misses information on a multiple choice quiz, we will be using SQL Server to retain this information. We want to know how to use SQL Server to present incorrect answers that most users miss on each question for our most difficult setting over randomly picking a word on the easy setting.

Solution

In this tip, we'll look at one way we can do this in SQL Server and we'll use an English learning game as the example.

In our example, we'll have a dictionary of words stored in the database with an ID field for random selection, which is the easy setting. If the game features a word in a multiple choice question on the easy setting, it generates these with a select query on the ID fields from randomly chosen numbers in the application or database layer. What we want to do is add a feature which allows us to increase the difficultly of our game.

For an example, suppose our game asks a multiple choice question on the easy setting:

Select the correct possessive for the sentence: They took ______ dog with them to New York.

The choices for the question are:

A.  there
B.  hope
C.  their
D.  to

In the question, two of those choices may not be challenging. We want to track the most commonly missed choices for the word "their" and have those be the top choices, rather than randomly selecting three words. What we're essentially solving is capturing related data to a data point by saving the related data to a table, then returning the related data associated most often to that data point.

We'll create three tables for this exercise. The first table, tblWords, is where we hold words for questions we'll ask, or for incorrect words on challenges that offer options (like multiple choice challenges). The second table, tblErrorTracking, logs the incorrect responses. Using the example question above this, if a user chose "hope" - which is incorrect - the tblErrorTracking would log the ID of the incorrect word (what was chosen by the user) and the ID of the correct word (what should have been chosen by the user). I've added a date and time field that allows us the option of time-based relevancy, such as users making different errors as they learn. Since this requires additional space and may not be necessary for the application, this column may be removed.

The tblErrorTracking table in general will consume little space, as even with 1 million users a week generating 10 errors each, that's only 10 million records per week for the table. Integers in SQL Server only consume 4 bytes of storage, and depending on the application, we may have the option of using a smallint. Because the English language has over 1 million words and a challenging application might store 25,000 or more words with a possibility for growth, we'll use integer in our example. The third table we'll use for the final demo only and populate it with one record.

Here is the code to create the tables and insert some data.

CREATE TABLE tblWords(
 WordId INT IDENTITY(1,1) PRIMARY KEY,
 Word VARCHAR(100)
)

CREATE TABLE tblErrorTracking(
 CorrectWordId INT FOREIGN KEY REFERENCES tblWords (WordId),
 ErrorWordId INT FOREIGN KEY REFERENCES tblWords (WordId),
 DateError DATETIME DEFAULT GETDATE()
)

---- FOR FINAL DEMO PURPOSES ONLY:
CREATE TABLE tblQuestion(
 QuestionID INT IDENTITY(1,1) PRIMARY KEY,
 RelatedWordID INT FOREIGN KEY REFERENCES tblWords (WordId),
 Question VARCHAR(1000)
)

INSERT INTO tblWords (Word)
VALUES ('to')
 , ('two')
 , ('too')
 , ('their')
 , ('there')
 , ('they''re')
 , ('one')
 , ('from')
 , ('of')

INSERT INTO tblErrorTracking (CorrectWordId,ErrorWordId)
VALUES (1,2)
 , (1,2)
 , (1,2)
 , (1,3)
 , (1,9)
 , (4,5)
 , (4,5)
 , (4,6)
 , (4,6)
 , (4,6)
 , (4,6)
 , (4,8)
 
---- FOR FINAL DEMO PURPOSES ONLY:
INSERT INTO tblQuestion (RelatedWordID,Question)
VALUES (4,'Select the correct plural possessive for the sentence: They took ______ dog with them to New York.')
 , (5,'What five letter adverb answers the question at, in, or where involving a place or location?')
 , (1,'Select the English two letter preposition for expressing motion in the direction of or identifying the object affected.')
 
---- if we want to retain user input: 
CREATE PROCEDURE addUserInput
@word VARCHAR(100)
AS
BEGIN
 IF NOT EXISTS (SELECT 1 FROM tblWords WHERE Word = @word)
 BEGIN
  INSERT INTO tblWords (Word) VALUES (@word)
 END
END

EXEC addUserInput 'test'

SELECT * FROM tblWords

Data from tblWords

If we want to retain user input, I've added a procedure which will add a word to tblWords. Depending on the game, I recommend against this for several reasons:

  1. In the case of this example application - English - an incorrect input may not be valid English. For an example, the typo thei4 isn't a word for "their" so by saving words entered, we would be retaining invalid English data. Do we want to do this? It depends on our business rules. I add this procedure because there may be situations where this is called for - like a biology application allowing for user input, even if that input is unrelated or wrong.
  2. If we tried to validate data, such as validating whether a word is English in this example, we'd add complexity and reduce performance of our application. It's possible that we find millions of typos over a period of time, none of which are mistaken very often.
  3. If we don't offer user input in our games, this won't matter since some challenges may be a preset selection - such as multiple choice or true or false.

Still, this is case-by-case and ultimately should comply with the business rules of the application. For this example, I include it to show how it can be done, but for the example of an English learning game, I would recommend against it.

SELECT *
FROM tblErrorTracking
Data from tblErrorTracking

We've populated some sample errors to begin solving the problem of returning related data (incorrect choices). Using the word their, we query the tblErrorTracking table to return the related errors for the word. We inner join tblWords to filter for the correct word (WHERE t2.Word = @word). We want the related data for this data point - in this case, the incorrect words that were chosen when the correct word was their. We inner join tblWords again on the ErrorWordId to get the erroneously chosen words.

The result is shown below this.

---- We want to return all the incorrect values for the word 'their'
DECLARE @word VARCHAR(100) = 'their'
SELECT 
 *
FROM tblErrorTracking t
 INNER JOIN tblWords t2 ON t.CorrectWordId = t2.WordId
 INNER JOIN tblWords t3 ON t.ErrorWordId = t3.WordId
WHERE t2.Word = @word
Data based on the error

Next, we want to aggregate these data by getting the most frequently missed words for their in order of how many times they were answered incorrectly by users. I show the counts also in the below query for demonstration purposes, but for the application and returning the word, we would only return the words.

---- Next, we want to order the incorrect words by the most frequent times they were missed
DECLARE @word VARCHAR(100) = 'their'
SELECT 
 t3.Word
 , COUNT(t3.Word) CountError  ---- this is only for demo; if the application selects, it would only return the word t3.Word
FROM tblErrorTracking t
 INNER JOIN tblWords t2 ON t.CorrectWordId = t2.WordId
 LEFT JOIN tblWords t3 ON t.ErrorWordId = t3.WordId
WHERE t2.Word = @word
GROUP BY t3.Word
ORDER BY COUNT(t3.Word) DESC
Count of the Errors

Finally, we want to execute a demo only on the back-end of populating some data to the tblErrorTracking table to simulate a user getting the incorrect selection in a question - whether through user input, multiple choice, or some other format.

In the below demo, we have a stored procedure which passes in the correct word that should have been chosen by the user with the incorrect word that was chosen by the user. The procedure validates that the incorrect word exists first, then adds the CorrectWordId and ErrorWordId to the tblErrorTracking table. This if statement may not be necessary in situations where the user chose from a pre-selected set of possible answers, but if we allow user entry and we want to prevent invalid data, this is a necessary step. From here, we execute our stored procedure a few times and query our data again looking for the word their.

CREATE PROCEDURE addErroneousResponse
@wordtrue VARCHAR(100),
@wordfalse VARCHAR(100)
AS
BEGIN
 IF EXISTS (SELECT 1 FROM tblWords WHERE Word = @wordfalse)
 BEGIN
  DECLARE @wordtrueid INT, @wordfalseid INT
  SELECT @wordtrueid = WordId FROM tblWords WHERE Word = @wordtrue
  SELECT @wordfalseid = WordId FROM tblWords WHERE Word = @wordfalse

  INSERT INTO tblErrorTracking (CorrectWordId,ErrorWordId)
  VALUES (@wordtrueid,@wordfalseid)
 END
END

EXEC addErroneousResponse 'their','they''re'
EXEC addErroneousResponse 'their','they''re'
EXEC addErroneousResponse 'there','their'
EXEC addErroneousResponse 'there','their'
EXEC addErroneousResponse 'there','their'
EXEC addErroneousResponse 'there','their'

---- Increases the count:
DECLARE @word VARCHAR(100) = 'their'
SELECT 
 t3.Word
 , COUNT(t3.Word) CountError
FROM tblErrorTracking t
 INNER JOIN tblWords t2 ON t.CorrectWordId = t2.WordId
 INNER JOIN tblWords t3 ON t.ErrorWordId = t3.WordId
WHERE t2.Word = @word
GROUP BY t3.Word
ORDER BY COUNT(t3.Word) DESC
Error Count

And we query for the word there:

---- look at the new word there
DECLARE @word VARCHAR(100) = 'there'
SELECT 
 t3.Word
 , COUNT(t3.Word) CountError
FROM tblErrorTracking t
 INNER JOIN tblWords t2 ON t.CorrectWordId = t2.WordId
 INNER JOIN tblWords t3 ON t.ErrorWordId = t3.WordId
WHERE t2.Word = @word
GROUP BY t3.Word
ORDER BY COUNT(t3.Word) DESC
Error Count related to their and there

I added the last query as an example of what might happen if we don't have enough related errors. Suppose that our question involved the word "three" and no one missed questions or challenges involving three, so we had no data with errors for that word. In this case, even in our hard mode, we would randomly select words from the table.

Finally, we'll tie all this together with a stored procedure that our test application will use to get the question, the correct answer, and 3 incorrect responses by first checking if 3 common incorrect responses exist. We'll assume our application is using XML for organization on this return output where each component is wrapped in its description. We also pass in the integer for the example - the application would either generate a random number in the application layer and pass it to the procedure, or generate a random number on the back-end and use it to return the question, correct answer, and 3 incorrect responses.

----- FINAL RETURN DEMO:
CREATE PROCEDURE getQuestion_Hard
AS
BEGIN
 
 ---- random number generator either on back-end or from application; in example, we'll use "their" or 4
 DECLARE @example INT = 4, @rowcount INT
 
 DECLARE @return TABLE (OrderID INT, ReturnQuestionDetails VARCHAR(1000))

 INSERT INTO @return
 SELECT 1, '' + Question + '' ReturnQuestionDetails FROM tblQuestion WHERE RelatedWordID = @example
 
 INSERT INTO @return
 SELECT 2, '' + Word + '' AS ReturnQuestionDetails FROM tblWords WHERE WordId = @example
 
 INSERT INTO @return
 SELECT TOP 3
  3, '' + t3.Word + '' AS ReturnQuestionDetails
 FROM tblErrorTracking t
  INNER JOIN tblWords t2 ON t.CorrectWordId = t2.WordId
  INNER JOIN tblWords t3 ON t.ErrorWordId = t3.WordId
 WHERE t2.WordId = @example
 GROUP BY t3.Word
 ORDER BY COUNT(t3.Word) DESC
 SET @rowcount = @@ROWCOUNT
 
 IF @rowcount <> 3
 BEGIN
  INSERT INTO @return
  SELECT TOP 4 4, '' + Word + '' AS ReturnQuestionDetails FROM tblWords WHERE WordId <> @example ORDER BY NEWID()
 END

 ;WITH GetNoDupeFalses AS(
  SELECT ROW_NUMBER() OVER (PARTITION BY ReturnQuestionDetails ORDER BY ReturnQuestionDetails) DupeFalseId
   , *
  FROM @return
 )
 SELECT TOP 5 ReturnQuestionDetails
 FROM GetNoDupeFalses
 WHERE DupeFalseId = 1
 ORDER BY OrderId

END

Hard Question

In the variable table, OrderId is used in case we only get 0, 1, or 2 returns from the third insert seen inside the stored procedure (the related errors to the word). Since this example is looking for the question, the correct word, and 3 possible incorrect responses, we can SELECT TOP 5 and by using an order skip any extra records, like if there were 2 records returned in the third insert and 4 records returned in the final insert - it would still only return the top 5, 2 of which are related errors. Even with 50,000 records or more in this game, it's possible we return duplicates from the third and fourth query, because the third query looks for related errors to the word, while the fourth query returns any word, except the correct word. The final query removes any duplicates.

This uses an English word game as an example of using TSQL to capture related data and return aggregates. This can help add a layer of difficulty to games by using popular incorrect answers as options in questions, but we can use it for other applications where we need related data for a data point.

Consider the following use case and how similar it is to our example game:

  • We see an error regularly in our environment involving access to a folder. This error means one of three possibilities.
    • 95% of the time, the first possibility is the problem, which is the drive was detached or the folder became unavailable.
    • 3% of the time, an AD error happened and the user lost permission.
    • 2% of the time, something else happened.

Based on the instances above this, we would want to validate the first two before troubleshooting three. If an increase in the third happened, moving it to the second position or third, we could adjust as needed - our tracking of related errors allows us to dynamically solve problems. Fundamentally, this game is a data point which has related data that may be constantly changing and the returned data set related to the data point must change based on the input from the users.

Next Steps
  • Review the business rules for your application - how do you want to track errors, do you want to include the time of the error, do you want to capture user input? There are costs and benefits of each of these routes.
  • Consider that this is one possible solution that costs little in terms of storage. For instance, storing 1,000,000 records in the tblErrorTracking table (including the date and time) consumes less than 30MB. Without date and time, that reduces by 50%.
  • Other considerations when tracking errors in games such as this example on the back-end are tracking the user information, region information, etc. Some errors may be more common in some areas or to some users instead of overall. The business use determines what is needed and what is not needed.


Last Update:


signup button

next tip button



About the author
MSSQLTips author Tim Smith Tim Smith works as a DBA and developer and also teaches Automating ETL on Udemy.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools