Graph of Votes per User, non cumulative version

1

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

-- 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]
                , v.upVotes
                , v.dwnVotes
                , SUM (a.numNewUsers)  OVER (ORDER BY a.[Date] ROWS UNBOUNDED PRECEDING)  AS  [totalUsers]
    FROM        accountsOnDate a
    LEFT JOIN   votesOnDate v   ON v.[Date] = a.[Date]
)
, linearData AS (
    SELECT
                c.[Date]
                , AVG (1.0 * c.upVotes / c.totalUsers) OVER (
                    ORDER BY c.[Date]
                    ROWS BETWEEN ##MovingAvg:int?10## PRECEDING AND CURRENT ROW
                )
                AS [upvPerUser]
                , AVG (1.0 * c.dwnVotes / c.totalUsers) OVER (
                    ORDER BY c.[Date]
                    ROWS BETWEEN ##MovingAvg:int?10## PRECEDING AND CURRENT ROW
                )
                AS [dwnvPerUser]
    FROM        cumltvVotesAndUsers c
)
SELECT
            l.[Date]
            , CASE WHEN l.upvPerUser  = 0  THEN 0 ELSE LOG (l.upvPerUser, 10)  END  AS [Daily Upvotes per user (log-10)]
            , CASE WHEN l.dwnvPerUser = 0  THEN 0 ELSE LOG (l.dwnvPerUser, 10) END  AS [Daily Downvotes per user (log-10)]
FROM        linearData l

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results