Calculate total score in SQL query from multiple tables

Question

I have the following tables for a competition:

User:

• Id
• Name
• Email
• EntryId

Entry:

• Id
• Age
• Gender
• State

GameResult:

• Id
• EntryId
• Duration
• Score

QuestionResult:

• Id
• EntryId
• Correct
• UsersAnswer

Each entry will have multiple games, and multiple questions. I need to perform a query that will find a list of the highest scores, and then also break down highest score by demographic, i.e. Age, Gender, State.

The calculations are as follows:

Each correct question will have a score value assigned, e.g. 10 points for each correct answer. Each game will have the score already defined in its column.

So, for an entry, the total score will be:

(Count(Qn.Correct) * QuestionScore) + SUM(G1.Score, G2.Score, Gn.Score)

Not sure where to start in figuring this query out.

Answer 1

note sure individual answer score would be relevant here since you already have final score on the GameResult table.

select e.Age, max(gr.Score) as Score
from Entry e
inner join GameResult gr on(gr.EntryID=e.EntryID)
group by e.Age

then repeat the same thing for the gender and state.

edit: ok, I am not sure I am following your high level design here, but whatever... You need to group your results by the entry ID first, and then by Age/Gender/State. An extra complexity level, but otherwise exactly the same task.

with GameResultScore as (
select EntryID, sum(Score) as Score
from GameResult
group by EntryID
),
QuestionResultScore as (
select EntryID, count(*) CorrectAnswers
from QuestionResult
where Correct=1
group by EntryID
)

select e.Age, max(isnull(grs.Score,0)+isnull(qrs.CorrectAnswers,0) * QuestionScore) as Score
from Entry e
left join GameResultScore  grs on(grs.EntryID=e.EntryID)
left join QuestionResultScore qrs on(qrs.EntryID=e.EntryID)
group by e.Age