Downsample to a weekly grain

Management would like to see how well people have utilized the spa in 2020. They would like to see results by week, reviewing the total number of minutes of amenity usage, the number of attendees, and the customer with the largest customer ID that week to see if new customers are coming in.

We can use functions in SQL Server to downsample to a fixed grain like this. One such function is DATEPART().

Deze oefening maakt deel uit van de cursus

Time Series Analysis in SQL Server

Cursus bekijken

Oefeninstructies

Downsample the day spa visit data to a weekly grain using the DATEPART() function.
Find the customer with the largest customer ID for a given week.
Fill in the GROUP BY clause with any non-aggregated values in the SELECT clause (but without aliases like AS Week).

Praktische interactieve oefening

Probeer deze oefening eens door deze voorbeeldcode in te vullen.

SELECT
	-- Downsample to a weekly grain
	___(___, dsv.CustomerVisitStart) AS Week,
	SUM(dsv.AmenityUseInMinutes) AS AmenityUseInMinutes,
	-- Find the customer with the largest customer ID for that week
	___(dsv.___) AS HighestCustomerID,
	COUNT(1) AS NumberOfAttendees
FROM dbo.DaySpaVisit dsv
WHERE
	dsv.CustomerVisitStart >= '2020-01-01'
	AND dsv.CustomerVisitStart < '2021-01-01'
GROUP BY
	-- When we use aggregation functions like SUM or COUNT,
    -- we need to GROUP BY the non-aggregated columns
	___(___, dsv.CustomerVisitStart)
ORDER BY
	Week;

Code bewerken en uitvoeren