You have a Fabric warehouse that contains two tables named DimDate and Trips.

Comprehensive Detailed Explanation

Step 1: Requirement

We need to compare the average miles per trip for:

Statutory holidays (when IsHoliday = 1)

Non-statutory holidays (when IsHoliday = 0)

Step 2: Formula for average miles per trip

Average miles per trip = total miles ÷ number of trips

Total miles → SUM(t.tripDistance)

Number of trips → COUNT(t.tripID)

So the calculation is:

(SUM(t.tripDistance) / COUNT(t.tripID)) AS MilesPerTrip

Step 3: Grouping

We need a comparison by holiday status.

So we must group the results by:

GROUP BY d.IsHoliday

This ensures we get two rows: one for IsHoliday = 1 and one for IsHoliday = 0.

Step 4: Final Query

SELECT

d.IsHoliday,

(SUM(t.tripDistance) / COUNT(t.tripID)) AS MilesPerTrip

FROM DimDate d

INNER JOIN Trips t ON d.DateID = t.DateID

GROUP BY d.IsHoliday;

Why This is Correct

The formula ensures average miles per trip.

Grouping ensures comparison between holidays vs non-holidays.

Efficient aggregation, minimal computation.

References

Aggregate functions in T-SQL

GROUP BY clause