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().

Cet exercice fait partie du cours

Time Series Analysis in SQL Server

Afficher le cours

Instructions

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).

Exercice interactif pratique

Essayez cet exercice en complétant cet exemple de code.

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;

Modifier et exécuter le code