0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

SELECT
  Input.Percent_Rep_UpOrDown_B,
  COUNT(1)
  
FROM (
  SELECT
    Input.Id [User Link],
    Input.Id,
    u.Reputation,
    Bounties.Bounties,
    CASE WHEN Bounties.Bounties IS NULL THEN u.Reputation ELSE (Bounties.Bounties + u.Reputation) END Adj_Rep_By_B,
    SUM(Input.Daily_Score) Sum_UpOrDown,
    CAST(SUM(Input.Daily_Score) AS FLOAT) / CAST(u.Reputation AS FLOAT) Percent_Rep_UpOrDown,
    CAST(SUM(Input.Daily_Score) AS FLOAT) / (CASE WHEN Bounties.Bounties IS NULL THEN u.Reputation ELSE (Bounties.Bounties + u.Reputation) END) Percent_Rep_UpOrDown_B
  FROM
    (
    SELECT
      u.Id,
      DATEADD(day, DATEDIFF(day, 0,v.CreationDate), 0) AS TimeStamp,
        CASE
          WHEN 
            SUM(CASE 
              WHEN v.VoteTypeId = 2 THEN 10
              WHEN v.VoteTypeId = 3 THEN -2
              ELSE NULL
              END) > 200 THEN 200 
          ELSE 
            SUM(CASE 
              WHEN v.VoteTypeId = 2 THEN 10
              WHEN v.VoteTypeId = 3 THEN -2 
              ELSE NULL
              END)
          END
          AS Daily_Score
    FROM 
      Users u
    JOIN Posts p ON p.OwnerUserId = u.Id
    JOIN Votes v ON v.PostId = p.Id
    WHERE u.Reputation > 1 AND u.CreationDate > '2018-01-01'
    GROUP BY u.id, DATEDIFF(day, 0,v.CreationDate)
    ) AS Input

  JOIN Users u ON u.Id = Input.Id
  JOIN (
    Select
      UserId,
      SUM(BountyAmount) Bounties
    FROM Votes
    GROUP BY UserId
    ) Bounties ON Bounties.UserId = u.Id

  GROUP BY Input.Id, u.Reputation, Bounties.Bounties
) AS Input
    
GROUP BY Input.Percent_Rep_UpOrDown_B
ORDER BY Input.Percent_Rep_UpOrDown_B DESC

fork query

edited jan 31

created jan 31 LasseG