BenchMark Quizzes by MDFT Pro

Complete The Query To Compare Average Graduation Points

You work for MDFT Pro, a well-known training agency. The company has a Fabric warehouse that contains two tables named DimDate and Graduation.

DimDate contains the following fields:

Name	DataType	Description
DateID	Date	Unique identifier for the date
IsHoliday	Bit	Used to denote that the date is a statutory holiday

Graduation contains the following fields:

Name	DataType	Description
gradID	Integer	Graduation record identifier
pointsScored	Integer	Points scored in final exam
dateID	Date	Date of graduation

You need to compare the average points scored by students who took their final exams on statutory holidays versus non-statutory holidays.

You have the following T-SQL query:

SELECT 
    d.IsHoliday,
    (sum(g.pointsScored) / count(g.gradID)) AS AvgPoints
FROM Graduation g
INNER JOIN DimDate d on g.dateID = d.DateID
_______________

How should you complete the query?

Choose the correct answer from the options below.

Explanations for each answer:

GROUP BY d.IsHoliday is correct.
GROUP BY d.DateID is incorrect. Grouping by DateID would calculate an average for each individual date, rather than comparing holiday versus non-holiday averages. This would result in too many groups and wouldn't answer the question about statutory holiday comparison.
GROUPING SETS (d.DateID) is incorrect. GROUPING SETS with DateID would only create groupings based on individual dates, not the IsHoliday flag we need to compare. Additionally, GROUPING SETS typically creates multiple grouping combinations including NULL groups, which isn't what we need for this holiday vs non-holiday comparison.
HAVING GROUPING (d.IsHoliday) is incorrect. The GROUPING function is used with CUBE, ROLLUP, or GROUPING SETS to distinguish between regular grouped values and subtotals/totals. This is not needed for the simple holiday vs non-holiday comparison we want to perform.

Learn more about GROUP BY queries:

SQL GROUP BY

Exam Preparation Quiz

Complete The Query To Compare Average Graduation Points