Election page visitors by election

1

Please login or register to vote for this query.

(click on this box to dismiss)

Anime and Manga

Q&A for anime and manga fans

WITH Raw3 AS (
  SELECT
    CreationDate,
    LAG(CreationDate) OVER (ORDER BY CreationDate) AS PrevCreationDate
  FROM Posts
  WHERE PostTypeId = 6 AND CreationDate >= ##ElectionsSince:string?2000-01-01##
),
Elections AS (
  SELECT
    DATEFROMPARTS(YEAR(CreationDate), MONTH(CreationDate), DAY(CreationDate))
    AS Day
  FROM Raw3
  WHERE
    PrevCreationDate IS NULL
    OR
    DATEDIFF(DAY, PrevCreationDate, CreationDate) > ##NominationLength:int?8##
),
Raw AS (
  SELECT
    Id,
    UserId,
    Date,
    LAG(Date) OVER (PARTITION BY UserId ORDER BY Date) AS PrevDate
  FROM Badges
  WHERE Name='Caucus' AND TagBased=0
),
Visits AS (
  SELECT Id, Date
  FROM Raw
  WHERE
    PrevDate IS NULL
    OR
    DATEDIFF(DAY, PrevDate, Date) > ##ElectionLength:int?30##
),
Raw2 AS (
  SELECT
    DATEDIFF(HOUR, Elections.Day, Visits.Date) AS HrNo,
    Elections.Day ElectionDay,
    COUNT(*) AS Count
  FROM Visits, Elections
  WHERE
    DATEDIFF(DAY, Elections.Day, Visits.Date) BETWEEN 0 AND ##ElectionLength##
  GROUP BY Elections.Day, DATEDIFF(HOUR, Elections.Day, Visits.Date)
)
SELECT
  A.HrNo,
  CAST(A.ElectionDay AS VARCHAR(MAX)) AS Election,
  --MIN(A.Count) AS Count,
  1.0 * SUM(B.Count * (1 + ##SmoothHours## - ABS(A.Hrno - B.HrNo)))
    / SUM(1 + ##SmoothHours## - ABS(A.Hrno - B.HrNo))
    AS SmoothCount
FROM Raw2 A, Raw2 B
WHERE
  A.ElectionDay = B.ElectionDay
  AND
  A.Hrno - B.HrNo BETWEEN 0 AND ##SmoothHours:int?4##
GROUP BY A.ElectionDay, A.HrNo
ORDER BY A.ElectionDay DESC, A.HrNo

Enter Parameters

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