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

Este exercício faz parte do curso

Time Series Analysis in SQL Server

Ver curso

Instruções do exercício

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

Exercício interativo prático

Experimente este exercício completando este código de exemplo.

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;

Editar e executar o código