1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for people interested in the history and origins of science and mathematics

-- MovingAvg: Enter number of data points to use in the moving average
WITH accountsOnDate AS (
    SELECT      CAST (u.CreationDate AS DATE) as [Date]
                , COUNT (*) as numNewUsers
    FROM        Users u
    GROUP BY    CAST (u.CreationDate AS DATE)
)
, votesOnDate AS (
    SELECT      CAST (v.CreationDate AS DATE) as [Date]
                , SUM (CASE WHEN v.VoteTypeId = 2  THEN 1 ELSE 0 END) AS upVotes
                , SUM (CASE WHEN v.VoteTypeId = 3  THEN 1 ELSE 0 END) AS dwnVotes
    FROM        Votes v
    WHERE       v.VoteTypeId IN (2, 3)
    GROUP BY    CAST (v.CreationDate AS DATE)
)
, cumltvVotesAndUsers AS (
    SELECT
                a.[Date]
                , SUM (a.numNewUsers)  OVER (ORDER BY a.[Date] ROWS UNBOUNDED PRECEDING)  AS  [totalUsers]
                , SUM (v.upVotes)      OVER (ORDER BY v.[Date] ROWS UNBOUNDED PRECEDING)  AS  [upVotes]
                , SUM (v.dwnVotes)     OVER (ORDER BY v.[Date] ROWS UNBOUNDED PRECEDING)  AS  [dwnVotes]
    FROM        accountsOnDate a
    LEFT JOIN   votesOnDate v   ON v.[Date] = a.[Date]
)
SELECT
            c.[Date]
            , AVG (1.0 * c.upVotes / c.totalUsers) OVER (
                ORDER BY c.[Date]
                ROWS BETWEEN ##MovingAvg:int?5## PRECEDING AND CURRENT ROW
            )
            AS [Cumulative Upvotes per user]
            , AVG (1.0 * c.dwnVotes / c.totalUsers) OVER (
                ORDER BY c.[Date]
                ROWS BETWEEN ##MovingAvg:int?5## PRECEDING AND CURRENT ROW
            )
            AS [Cumulative Downvotes per user]
FROM        cumltvVotesAndUsers c

fork query

edited jul 28 18

created jul 28 18 Brock Adams