1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for Drupal developers and administrators

DECLARE @now DATETIME = (SELECT TOP 1 CreationDate FROM Votes ORDER BY Id DESC);
DECLARE @from DATETIME = DATEADD(DAY, -##LastNDays:int?365##, @now);

CREATE TABLE #Raw (
  Day DATETIME NOT NULL,
  Count INT NOT NULL,
  Type INT NOT NULL,
  PRIMARY KEY(Day, Type)
);

INSERT INTO #Raw
SELECT CreationDate AS Day, COUNT(*) AS Count, 1 AS Type
FROM Votes
WHERE CreationDate >= @from
GROUP BY CreationDate

UNION

SELECT
  DATEFROMPARTS(YEAR(CreationDate), MONTH(CreationDate), DAY(CreationDate))
    AS Day,
  COUNT(*) AS Count,
  2 AS Type
FROM PostFeedback
WHERE CreationDate >= @from
GROUP BY
  DATEFROMPARTS(YEAR(CreationDate), MONTH(CreationDate), DAY(CreationDate));

INSERT INTO #Raw
SELECT Day, SUM(Count) AS Count, 3 AS Type
FROM #Raw
WHERE Type BETWEEN 1 AND 2
GROUP BY Day;

WITH Raw2 AS (
  SELECT
    A.Day,
    B.Count,
    ##SmoothingDays## - ABS(DATEDIFF(DAY, A.Day, B.Day))
      AS Weight,
    A.Type
  FROM #Raw A, #Raw B
  WHERE
    A.Day
      BETWEEN B.Day - ##SmoothingDays:int?7## + 1
        AND B.Day + ##SmoothingDays## - 1
    AND
    A.Type = B.Type
)
SELECT
  Day,
  CASE
    WHEN Type = 1 THEN 'Vote'
    WHEN Type = 2 THEN 'Feedback'
    WHEN Type = 3 THEN 'Both'
  END AS Type,
  1.0 * SUM(Count * Weight) / SUM(Weight) AS Count
FROM Raw2
GROUP BY Day, Raw2.Type
ORDER BY Day DESC, Raw2.Type

fork query

edited jan 25

created jan 25 peterh - PLEASE DELETE THIS ACCOUNT