Voting stats and most recent voters. (Stats exclude users who've never voted.)

1

Please login or register to vote for this query.

(click on this box to dismiss)

Physics

Q&A for active researchers, academics and students of physics

-- UserId: Enter ID of user to report (magic for logged in SEDE user)
WITH Voters AS (
    SELECT
            u.Id
            , u.DisplayName
            , u.UpVotes                     * 1.0                   AS UpVotes
            , u.DownVotes                   * 1.0                   AS DownVotes
            , (u.UpVotes + u.DownVotes)     * 1.0                   AS TotalVotes
            , u.Reputation                  * 1.0                   AS Reputation
            , DATEDIFF (day, u.CreationDate, GETDATE() )   *1.0     AS UserAge
            , DATEDIFF (day, u.LastAccessDate, GETDATE() ) *1.0     AS vDaysAgo
    FROM    Users u
    WHERE   (u.UpVotes > 0  OR  u.DownVotes > 0)
)
, AvgUser AS (
    SELECT
              AVG (v.UpVotes)           AS UpVotes
            , AVG (v.DownVotes)         AS DownVotes
            , AVG (v.TotalVotes)        AS TotalVotes
            , AVG (v.Reputation)        AS Reputation
            , AVG (v.UserAge)           AS UserAge
            , AVG (v.vDaysAgo)          AS vDaysAgo
    FROM    Voters v
)
, CombinedUsers AS (
    SELECT      '=> Average <='     AS DisplayName
                , 1                 AS sortOrder
                , 0                 AS Id
                , au.UpVotes
                , au.DownVotes
                , au.TotalVotes
                , au.Reputation
                , au.UserAge
                , au.vDaysAgo
    FROM        AvgUser au
    UNION ALL
    SELECT      v.DisplayName
                , 2                 AS sortOrder
                , v.Id
                , v.UpVotes
                , v.DownVotes
                , v.TotalVotes
                , v.Reputation
                , v.UserAge
                , v.vDaysAgo
    FROM        Voters v
    WHERE       v.Id = -1   -- Include the community user especially
    UNION ALL
    SELECT      '** You => ' + v.DisplayName    AS DisplayName
                , 3                             AS sortOrder
                , v.Id
                , v.UpVotes
                , v.DownVotes
                , v.TotalVotes
                , v.Reputation
                , v.UserAge
                , v.vDaysAgo
    FROM        Voters v
    WHERE       v.Id = ##UserId:INT?0##  -- defaults to no rows (there is no user 0)
    UNION ALL
    SELECT      v.DisplayName
                , 4                 AS sortOrder
                , v.Id
                , v.UpVotes
                , v.DownVotes
                , v.TotalVotes
                , v.Reputation
                , v.UserAge
                , v.vDaysAgo
    FROM        Voters v
    WHERE       v.Id > 0   -- Community user only once
    AND         v.Id <> ##UserId:INT?0## 
)
SELECT TOP 5000  -- At 10K, SEDE doesn't add sort controls? (But sort not working on formatted numbers anyway (sorts as string).)
            (   CASE  WHEN c.Id = 0  THEN c.DisplayName
                    ELSE 'site://u/' + CAST(c.Id AS NVARCHAR) + '|' + c.DisplayName
                END
            )                                                           AS [User]
            , ROUND (c.vDaysAgo, 0)                                     AS [Lst Vst (days)]
            , FORMAT (c.TotalVotes / c.UserAge, 'G3')                   AS [Vts / day]
            , FORMAT ( (c.TotalVotes / c.UserAge) / c.vDaysAgo, 'G3')   AS [Predictor]
            , ROUND (c.TotalVotes, 0)                                   AS [Ttl vts]
            , ROUND (c.UpVotes, 0)                                      AS [Up v]
            , ROUND (c.DownVotes, 0)                                    AS [Dwn v]
            , ( CASE  WHEN c.DownVotes = 0  THEN FORMAT (c.UpVotes, 'G3')
                    ELSE FORMAT (c.UpVotes / c.DownVotes, 'G3')
                END
            )                                                           AS [Ratio]
            , ROUND (c.Reputation, 0)                                   AS [Reputation]
            , FORMAT (c.Reputation / c.TotalVotes, 'G3')                AS [Rep / vt]
FROM        CombinedUsers c
ORDER BY    c.sortOrder
            , ( (c.TotalVotes / c.UserAge) / c.vDaysAgo) DESC
            , c.vDaysAgo
            , (c.TotalVotes / c.UserAge) DESC
            , c.Reputation DESC
            , c.DisplayName

Enter Parameters

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