Q&A about the site for researchers and developers who explore the principles of a system through analysis of its structure, function, and operation
-- Chunk Start -- The date to start at -- input : date in yyyymmdd format -- default : today's date -- -- Chunk Type -- The time unit to chunk by -- input : 1. Days -- 2. Months -- 3. Years -- default : 1 -- -- Chunk Size -- The size of each chunk -- input : Group n @chunkType into one chunk -- default : 1 -- -- Chunk Amount Type -- The time unit to determine the size of -- input : 0. Same as @chunkType -- 1. Days -- 2. Months -- 3. Years -- default : 0 -- -- Chunk Amount Size -- The amount of chunks to take in each chunk group -- input : Group n @chunkAmountType into a chunk group -- default : 100 -- -- Chunk Skip Type -- The time unit to determine the size of -- input : 0. Same as @chunkAmountType -- 1. Days -- 2. Months -- 3. Years -- default : 0 -- -- Chunk Skip -- The amount of chunks to skip between chunk groups -- input : Skip n @chunkSkipType between the first of each chunk group -- default : 12 -- -- Chunk Groups -- The amount of chunk groups -- input : The amount of chunk groups desired -- default : 0 -- -- Date Normalize -- The normalized graph view of the data -- input : 2. Month view -- 3. Year view -- 4. Infinate view -- default : 4 -- -- Good Values -- Quarterly (days) -- ChunkStart 20210400 -- ChunkType 1 -- ChunkAmountType 2 -- ChunkSkipType 0 -- ChunkSize 1 -- ChunkAmountSize 6 -- ChunkSkip 12 -- ChunkGroups 2 -- -- Quarterly (months) -- ChunkStart 20210100 -- ChunkSize 7 -- ChunkAmountSize 3 -- ChunkSkip 12 -- ChunkGroups 5 -- ChunkType 1 -- ChunkAmountType 2 -- ChunkSkipType 0 -- dateNormalize 3 -- -- Continuous (days) -- ChunkStart 20210400 -- ChunkType 1 -- ChunkAmountType 0 -- ChunkSkipType 0 -- ChunkSize 1 -- ChunkAmountSize 100 -- ChunkSkip 12 -- ChunkGroups 1 -- -- Continuous (months) -- ChunkStart 20210400 -- ChunkType 2 -- ChunkAmountType 0 -- ChunkSkipType 0 -- ChunkSize 1 -- ChunkAmountSize 100 -- ChunkSkip 12 -- ChunkGroups 1 -- -- Test date-normalize 3 leap years -- ChunkStart 20210100 -- ChunkSize 7 -- ChunkAmountSize 6 -- ChunkSkip 15 -- ChunkGroups 5 -- ChunkType 1 -- ChunkAmountType 2 -- ChunkSkipType 0 -- dateNormalize 3 ------------ -- Input -- ------------ DECLARE @_chunkStart AS int = ##ChunkStart:int?0##; DECLARE @chunkStart AS date = DateAdd( day, 1, CASE WHEN @_chunkStart = 0 THEN Convert(date, CURRENT_TIMESTAMP) ELSE CASE WHEN @_chunkStart % 100 = 0 THEN DateAdd(day, -1, cast(cast(@_chunkStart + 1 AS varchar(255)) AS date)) ELSE cast(cast(@_chunkStart AS varchar(255)) AS date) END END ); DECLARE @chunkSize AS int = ##ChunkSize:int?1##; DECLARE @chunkAmountSize AS int = ##ChunkAmountSize:int?100##; DECLARE @_chunkSkip AS int = ##ChunkSkip:int?0##; DECLARE @chunkSkip AS int = CASE WHEN @_chunkSkip = 0 THEN @chunkAmountSize ELSE @_chunkSkip END; DECLARE @chunkGroups AS int = ##ChunkGroups:int?1##; DECLARE @chunkType AS int = ##ChunkType:int?1##; DECLARE @_chunkAmountType AS int = ##ChunkAmountType:int?0##; DECLARE @chunkAmountType AS int = CASE WHEN @_chunkAmountType = 0 THEN @chunkType ELSE @_chunkAmountType END; DECLARE @_chunkSkipType AS int = ##ChunkSkipType:int?0##; DECLARE @chunkSkipType AS int = CASE WHEN @_chunkSkipType = 0 THEN @chunkAmountType ELSE @_chunkSkipType END; DECLARE @dateNormalize AS int = ##DateNormalize:int?4##; DECLARE @leftHandSide AS nvarchar(max) = '##LeftHandSide?Q##'; DECLARE @rightHandSide AS nvarchar(max) = '##RightHandSide##'; -------------------- -- Input Sources -- -------------------- SELECT ROW_NUMBER() OVER (ORDER BY Id Asc) - 1 AS Id INTO #ChunkNumbers FROM Posts ; WITH _ChunksEnd AS ( SELECT n.Id AS Id, CASE WHEN @chunkSkipType = 1 THEN DateAdd(day, -n.Id * @chunkSkip, @chunkStart) WHEN @chunkSkipType = 2 THEN DateAdd(month, -n.Id * @chunkSkip, @chunkStart) WHEN @chunkSkipType = 3 THEN DateAdd(year, -n.Id * @chunkSkip, @chunkStart) ELSE Null END AS ChunkEnd FROM #ChunkNumbers AS n WHERE n.Id < @chunkGroups ), _ChunksStart AS ( SELECT c.Id, CASE WHEN @chunkAmountType = 1 THEN DateAdd(day, -@chunkAmountSize, c.ChunkEnd) WHEN @chunkAmountType = 2 THEN DateAdd(month, -@chunkAmountSize, c.ChunkEnd) WHEN @chunkAmountType = 3 THEN DateAdd(year, -@chunkAmountSize, c.ChunkEnd) ELSE Null END AS ChunkStart, c.ChunkEnd, DateAdd(day, -1, c.ChunkEnd) AS _ChunkEnd FROM _ChunksEnd AS c ), _ChunksGroups AS ( SELECT c.Id, CASE WHEN year(c.ChunkStart) != year(c._ChunkEnd) THEN CASE WHEN day(c.ChunkStart) = day(c._ChunkEnd) AND month(c.ChunkStart) = month(c._ChunkEnd) THEN cast(day(c.ChunkStart) AS varchar) + CASE WHEN day(c.ChunkStart) % 10 = 1 THEN 'st' WHEN day(c.ChunkStart) % 10 = 2 THEN 'nd' WHEN day(c.ChunkStart) % 10 = 3 THEN 'rd' ELSE 'th' END + ' ' + CASE WHEN month(c.ChunkStart) = 1 THEN 'Jan' WHEN month(c.ChunkStart) = 2 THEN 'Feb' WHEN month(c.ChunkStart) = 3 THEN 'Mar' WHEN month(c.ChunkStart) = 4 THEN 'Apr' WHEN month(c.ChunkStart) = 5 THEN 'May' WHEN month(c.ChunkStart) = 6 THEN 'Jun' WHEN month(c.ChunkStart) = 7 THEN 'Jul' WHEN month(c.ChunkStart) = 8 THEN 'Aug' WHEN month(c.ChunkStart) = 9 THEN 'Sep' WHEN month(c.ChunkStart) = 10 THEN 'Oct' WHEN month(c.ChunkStart) = 11 THEN 'Nov' WHEN month(c.ChunkStart) = 12 THEN 'Dec' END + ', ' + cast(year(c.ChunkStart) AS varchar) + ' - ' + cast(year(c.ChunkStart) AS varchar) ELSE cast(day(c.ChunkStart) AS varchar) + CASE WHEN day(c.ChunkStart) % 10 = 1 THEN 'st' WHEN day(c.ChunkStart) % 10 = 2 THEN 'nd' WHEN day(c.ChunkStart) % 10 = 3 THEN 'rd' ELSE 'th' END + ' ' + CASE WHEN month(c.ChunkStart) = 1 THEN 'Jan' WHEN month(c.ChunkStart) = 2 THEN 'Feb' WHEN month(c.ChunkStart) = 3 THEN 'Mar' WHEN month(c.ChunkStart) = 4 THEN 'Apr' WHEN month(c.ChunkStart) = 5 THEN 'May' WHEN month(c.ChunkStart) = 6 THEN 'Jun' WHEN month(c.ChunkStart) = 7 THEN 'Jul' WHEN month(c.ChunkStart) = 8 THEN 'Aug' WHEN month(c.ChunkStart) = 9 THEN 'Sep' WHEN month(c.ChunkStart) = 10 THEN 'Oct' WHEN month(c.ChunkStart) = 11 THEN 'Nov' WHEN month(c.ChunkStart) = 12 THEN 'Dec' END + ' ' + cast(year(c.ChunkStart) AS varchar) + ' - ' + cast(day(c._ChunkEnd) AS varchar) + CASE WHEN day(c._ChunkEnd) % 10 = 1 THEN 'st' WHEN day(c._ChunkEnd) % 10 = 2 THEN 'nd' WHEN day(c._ChunkEnd) % 10 = 3 THEN 'rd' ELSE 'th' END + ' ' + CASE WHEN month(c._ChunkEnd) = 1 THEN 'Jan' WHEN month(c._ChunkEnd) = 2 THEN 'Feb' WHEN month(c._ChunkEnd) = 3 THEN 'Mar' WHEN month(c._ChunkEnd) = 4 THEN 'Apr' WHEN month(c._ChunkEnd) = 5 THEN 'May' WHEN month(c._ChunkEnd) = 6 THEN 'Jun' WHEN month(c._ChunkEnd) = 7 THEN 'Jul' WHEN month(c._ChunkEnd) = 8 THEN 'Aug' WHEN month(c._ChunkEnd) = 9 THEN 'Sep' WHEN month(c._ChunkEnd) = 10 THEN 'Oct' WHEN month(c._ChunkEnd) = 11 THEN 'Nov' WHEN month(c._ChunkEnd) = 12 THEN 'Dec' END + ' ' + cast(year(c._ChunkEnd) AS varchar) END WHEN month(c.ChunkStart) != month(c._ChunkEnd) THEN cast(day(c.ChunkStart) AS varchar) + CASE WHEN day(c.ChunkStart) % 10 = 1 THEN 'st' WHEN day(c.ChunkStart) % 10 = 2 THEN 'nd' WHEN day(c.ChunkStart) % 10 = 3 THEN 'rd' ELSE 'th' END + ' ' + CASE WHEN month(c.ChunkStart) = 1 THEN 'Jan' WHEN month(c.ChunkStart) = 2 THEN 'Feb' WHEN month(c.ChunkStart) = 3 THEN 'Mar' WHEN month(c.ChunkStart) = 4 THEN 'Apr' WHEN month(c.ChunkStart) = 5 THEN 'May' WHEN month(c.ChunkStart) = 6 THEN 'Jun' WHEN month(c.ChunkStart) = 7 THEN 'Jul' WHEN month(c.ChunkStart) = 8 THEN 'Aug' WHEN month(c.ChunkStart) = 9 THEN 'Sep' WHEN month(c.ChunkStart) = 10 THEN 'Oct' WHEN month(c.ChunkStart) = 11 THEN 'Nov' WHEN month(c.ChunkStart) = 12 THEN 'Dec' END + ' - ' + cast(day(c._ChunkEnd) AS varchar) + CASE WHEN day(c._ChunkEnd) % 10 = 1 THEN 'st' WHEN day(c._ChunkEnd) % 10 = 2 THEN 'nd' WHEN day(c._ChunkEnd) % 10 = 3 THEN 'rd' ELSE 'th' END + ' ' + CASE WHEN month(c._ChunkEnd) = 1 THEN 'Jan' WHEN month(c._ChunkEnd) = 2 THEN 'Feb' WHEN month(c._ChunkEnd) = 3 THEN 'Mar' WHEN month(c._ChunkEnd) = 4 THEN 'Apr' WHEN month(c._ChunkEnd) = 5 THEN 'May' WHEN month(c._ChunkEnd) = 6 THEN 'Jun' WHEN month(c._ChunkEnd) = 7 THEN 'Jul' WHEN month(c._ChunkEnd) = 8 THEN 'Aug' WHEN month(c._ChunkEnd) = 9 THEN 'Sep' WHEN month(c._ChunkEnd) = 10 THEN 'Oct' WHEN month(c._ChunkEnd) = 11 THEN 'Nov' WHEN month(c._ChunkEnd) = 12 THEN 'Dec' END + ', ' + cast(year(c.ChunkStart) AS varchar) WHEN day(c.ChunkStart) != day(c._ChunkEnd) THEN cast(day(c.ChunkStart) AS varchar) + CASE WHEN day(c.ChunkStart) % 10 = 1 THEN 'st' WHEN day(c.ChunkStart) % 10 = 2 THEN 'nd' WHEN day(c.ChunkStart) % 10 = 3 THEN 'rd' ELSE 'th' END + ' to ' + cast(day(c._ChunkEnd) AS varchar) + CASE WHEN day(c._ChunkEnd) % 10 = 1 THEN 'st' WHEN day(c._ChunkEnd) % 10 = 2 THEN 'nd' WHEN day(c._ChunkEnd) % 10 = 3 THEN 'rd' ELSE 'th' END + ' ' + CASE WHEN month(c.ChunkStart) = 1 THEN 'Jan' WHEN month(c.ChunkStart) = 2 THEN 'Feb' WHEN month(c.ChunkStart) = 3 THEN 'Mar' WHEN month(c.ChunkStart) = 4 THEN 'Apr' WHEN month(c.ChunkStart) = 5 THEN 'May' WHEN month(c.ChunkStart) = 6 THEN 'Jun' WHEN month(c.ChunkStart) = 7 THEN 'Jul' WHEN month(c.ChunkStart) = 8 THEN 'Aug' WHEN month(c.ChunkStart) = 9 THEN 'Sep' WHEN month(c.ChunkStart) = 10 THEN 'Oct' WHEN month(c.ChunkStart) = 11 THEN 'Nov' WHEN month(c.ChunkStart) = 12 THEN 'Dec' END + ' ' + cast(year(c.ChunkStart) AS varchar) ELSE cast(day(c.ChunkStart) AS varchar) + CASE WHEN day(c.ChunkStart) % 10 = 1 THEN 'st' WHEN day(c.ChunkStart) % 10 = 2 THEN 'nd' WHEN day(c.ChunkStart) % 10 = 3 THEN 'rd' ELSE 'th' END + ' ' + CASE WHEN month(c.ChunkStart) = 1 THEN 'Jan' WHEN month(c.ChunkStart) = 2 THEN 'Feb' WHEN month(c.ChunkStart) = 3 THEN 'Mar' WHEN month(c.ChunkStart) = 4 THEN 'Apr' WHEN month(c.ChunkStart) = 5 THEN 'May' WHEN month(c.ChunkStart) = 6 THEN 'Jun' WHEN month(c.ChunkStart) = 7 THEN 'Jul' WHEN month(c.ChunkStart) = 8 THEN 'Aug' WHEN month(c.ChunkStart) = 9 THEN 'Sep' WHEN month(c.ChunkStart) = 10 THEN 'Oct' WHEN month(c.ChunkStart) = 11 THEN 'Nov' WHEN month(c.ChunkStart) = 12 THEN 'Dec' END + ' ' + cast(year(c.ChunkStart) AS varchar) END AS ChunkName, c.ChunkStart, c.ChunkEnd, CAST(CASE WHEN @chunkType = 1 THEN DateDiff(day, c.ChunkStart, c.ChunkEnd) WHEN @chunkType = 2 THEN DateDiff(month, c.ChunkStart, c.ChunkEnd) WHEN @chunkType = 3 THEN DateDiff(year, c.ChunkStart, c.ChunkEnd) ELSE Null END AS FLOAT) / CAST(@chunkSize AS FLOAT) AS Amount FROM _ChunksStart AS c ) SELECT c.* INTO #ChunkGroups FROM _ChunksGroups AS c ; WITH _ChunksRaw AS ( SELECT n.Id AS ChunkIndex, g.Id AS ChunkGroupId, CASE WHEN @chunkType = 1 THEN DateAdd(day, (n.Id + 0) * @chunkSize, g.ChunkStart) WHEN @chunkType = 2 THEN DateAdd(month, (n.Id + 0) * @chunkSize, g.ChunkStart) WHEN @chunkType = 3 THEN DateAdd(year, (n.Id + 0) * @chunkSize, g.ChunkStart) ELSE Null END AS StartDate, CASE WHEN @chunkType = 1 THEN DateAdd(day, (n.Id + 1) * @chunkSize, g.ChunkStart) WHEN @chunkType = 2 THEN DateAdd(month, (n.Id + 1) * @chunkSize, g.ChunkStart) WHEN @chunkType = 3 THEN DateAdd(year, (n.Id + 1) * @chunkSize, g.ChunkStart) ELSE Null END AS EndDate, g.ChunkName AS ChunkName, CASE WHEN @dateNormalize in (1, 2, 3) THEN CASE WHEN @chunkType = 1 THEN DateAdd(day, n.Id * @chunkSize, datefromparts(2000, month(g.ChunkStart), day(g.ChunkStart))) WHEN @chunkType = 2 THEN DateAdd(month, n.Id * @chunkSize, datefromparts(2000, month(g.ChunkStart), day(g.ChunkStart))) WHEN @chunkType = 3 THEN DateAdd(year, n.Id * @chunkSize, datefromparts(2000, month(g.ChunkStart), day(g.ChunkStart))) ELSE Null END WHEN @dateNormalize = 4 THEN CASE WHEN @chunkType = 1 THEN DateAdd(day, (n.Id + 0) * @chunkSize, g.ChunkStart) WHEN @chunkType = 2 THEN DateAdd(month, (n.Id + 0) * @chunkSize, g.ChunkStart) WHEN @chunkType = 3 THEN DateAdd(year, (n.Id + 0) * @chunkSize, g.ChunkStart) ELSE Null END ELSE Null END AS ChunkDate FROM #ChunkGroups AS g INNER JOIN #ChunkNumbers AS n ON n.Id < g.Amount ), _ChunksCorrected AS ( SELECT c.ChunkIndex, c.ChunkGroupId, c.StartDate, CASE WHEN year(c.EndDate) * 10000 + month(c.EndDate) * 100 + day(c.EndDate) <= year(g.ChunkEnd) * 10000 + month(g.ChunkEnd) * 100 + day(g.ChunkEnd) THEN c.EndDate ELSE g.ChunkEnd END AS EndDate, c.ChunkName, CASE WHEN @dateNormalize = 1 THEN datefromparts(2000, 1, 1) WHEN @dateNormalize = 2 THEN DateAdd(day, 7 - datepart(dayofyear, datefromparts(2000, 1, day(c.ChunkDate))) % @chunkSize, datefromparts(2000, 1, day(c.ChunkDate))) WHEN @dateNormalize = 3 THEN DateAdd(day, 7 - datepart(dayofyear, datefromparts(2000, month(c.ChunkDate), day(c.ChunkDate))) % @chunkSize, datefromparts(2000, month(c.ChunkDate), day(c.ChunkDate))) ELSE c.ChunkDate END AS ChunkDate FROM _ChunksRaw AS c INNER JOIN #ChunkGroups AS g ON c.ChunkGroupId = g.Id ) SELECT c.* INTO #Chunks FROM _ChunksCorrected AS c ; ------------- -- Sources -- ------------- IF @leftHandSide = 'A' OR @rightHandSide = 'A' -- Answers SELECT p.Id AS Id, 1 AS Value, 1 AS Amount, p.OwnerUserId AS UserId, c.ChunkName AS ChunkName, c.ChunkDate AS ChunkDate INTO #Answers FROM #Chunks AS c INNER JOIN Posts AS p ON c.StartDate <= p.CreationDate AND p.CreationDate < c.EndDate WHERE p.PostTypeId = 2 ; IF @leftHandSide = 'P' -- Posts SELECT p.Id AS Id, 1 AS Value, 1 AS Amount, p.OwnerUserId AS UserId, c.ChunkName AS ChunkName, c.ChunkDate AS ChunkDate INTO #Posts FROM #Chunks AS c INNER JOIN Posts AS p ON c.StartDate <= p.CreationDate AND p.CreationDate < c.EndDate ; IF @leftHandSide = 'Q' -- Questions SELECT p.Id AS Id, 1 AS Value, 1 AS Amount, p.OwnerUserId AS UserId, c.ChunkName AS ChunkName, c.ChunkDate AS ChunkDate INTO #Questions FROM #Chunks AS c INNER JOIN Posts AS p ON c.StartDate <= p.CreationDate AND p.CreationDate < c.EndDate WHERE p.PostTypeId = 1 ; IF @leftHandSide = 'R' -- Reputation SELECT v.PostId AS Id, CASE WHEN v.VoteTypeId = 1 THEN 15 WHEN v.VoteTypeId = 2 THEN 10 WHEN v.VoteTypeId = 3 THEN -2 WHEN v.VoteTypeId = 8 THEN -v.BountyAmount WHEN v.VoteTypeId = 9 THEN v.BountyAmount WHEN v.VoteTypeId = 16 THEN 2 ELSE 0 END AS Value, 1 AS Amount, v.UserId AS UserId, c.ChunkName AS ChunkName, c.ChunkDate AS ChunkDate INTO #Reputation FROM #Chunks AS c INNER JOIN Votes AS v ON c.StartDate <= v.CreationDate AND v.CreationDate < c.EndDate WHERE ( v.VoteTypeId = 16 AND v.BountyAmount IS NOT Null ) OR v.VoteTypeId in (1, 2, 3, 8, 9) ; IF @leftHandSide = 'V' -- Votes SELECT v.PostId AS Id, CASE WHEN v.VoteTypeId = 2 THEN 1 ELSE -1 END AS Value, 1 AS Amount, v.UserId AS UserId, c.ChunkName AS ChunkName, c.ChunkDate AS ChunkDate INTO #Votes FROM #Chunks AS c INNER JOIN Votes AS v ON c.StartDate <= v.CreationDate AND v.CreationDate < c.EndDate WHERE v.VoteTypeId in (2, 3) ; IF @leftHandSide = 'Z' -- Zombies SELECT p.Id AS Id, 1 AS Value, 1 AS Amount, p.OwnerUserId AS UserId, c.ChunkName AS ChunkName, c.ChunkDate AS ChunkDate INTO #Zombies FROM #Chunks AS c INNER JOIN Posts AS p ON c.StartDate <= p.CreationDate AND p.CreationDate < c.EndDate WHERE p.PostTypeId = 1 AND p.AcceptedAnswerId IS Null AND 0 = ( SELECT count(*) FROM Posts AS a WHERE a.ParentId = p.Id AND 0 < a.Score ) ; ------------- -- Grouped -- ------------- SELECT sum(a.Value) AS Value, sum(a.Amount) AS Amount, a.ChunkName AS ChunkName, a.ChunkDate AS ChunkDate INTO #ChunkAnswers FROM #Answers AS a GROUP BY a.ChunkName, a.ChunkDate ; SELECT sum(q.Value) AS Value, sum(q.Amount) AS Amount, q.ChunkName AS ChunkName, q.ChunkDate AS ChunkDate INTO #ChunkQuestions FROM #Questions AS q GROUP BY q.ChunkName, q.ChunkDate ; -------------- -- Equation -- -------------- SELECT cast(a.Value as float) / cast(q.Value as float) AS Value, a.Amount + q.Amount AS Amount, a.ChunkName AS ChunkName, a.ChunkDate AS ChunkDate INTO #Metric FROM #ChunkAnswers AS a INNER JOIN #ChunkQuestions AS q ON a.ChunkName = q.ChunkName AND a.ChunkDate = q.ChunkDate ; ------------- -- Cleaned -- ------------- SELECT s.Value AS Value, s.ChunkName AS ChunkName, s.ChunkDate AS ChunkDate INTO #MetricClean FROM #Metric AS s ORDER BY s.ChunkDate, s.ChunkName ; ------------- -- Display -- ------------- DECLARE @pivot AS nvarchar(max) = STUFF( ( SELECT ',' + QUOTENAME(ChunkName) FROM #ChunkGroups ORDER BY Id ASC FOR XML PATH(''), TYPE ).value( '.', 'NVARCHAR(MAX)' ), 1, 1, '' ); PRINT @pivot; DECLARE @query AS nvarchar(max) = 'SELECT * FROM #MetricClean PIVOT ( sum(Value) FOR ChunkName IN (' + @pivot + ') ) AS Pivoted'; execute(@query);