Tag answer vote distribution by day of month

1

Please login or register to vote for this query.

(click on this box to dismiss)

It is normalized by that not all months have 31, 30, 29th days.

Reverse Engineering Meta

Q&A about the site for researchers and developers who explore the principles of a system through analysis of its structure, function, and operation

DECLARE @TagId INT;

SELECT @TagId = Id FROM Tags WHERE TagName = ##TagName:string##;

WITH Raw AS (
  SELECT
    DAY(Votes.CreationDate) AS DoM,
    COUNT(*) AS Count
  FROM Posts, Votes, PostTags
  WHERE
    Votes.PostId = Posts.Id
    AND
    Posts.ParentId = PostTags.PostId
    AND
    Votes.VoteTypeId BETWEEN 1 AND 2
    AND
    PostTags.TagId = @TagId
  GROUP BY DAY(Votes.CreationDate)
),
Raw2 AS (
  SELECT
    DoM,
    CASE
      WHEN DoM = 31 THEN 12.0/7
      WHEN DoM = 30 THEN 12.0/11
      WHEN DoM = 29 THEN 12.0/11.25
      ELSE 1
    END * Count AS Count
  FROM Raw
),
Raw3 AS (
  SELECT SUM(Count) AS SumCount FROM Raw2
)
SELECT
  Raw2.DoM,
  CASE WHEN ##Pct:int?1## <> 0 THEN 'Pct' ELSE 'Count' END,
  1.0 * Raw2.Count
    *
    CASE
      WHEN ##Pct## <> 0 THEN 100 / Raw3.SumCount
      ELSE 1
    END AS Value
FROM Raw2, Raw3

UNION ALL

SELECT
  1,
  '',
  0
ORDER BY DoM

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results