4

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for those who study, teach, research and apply economics and econometrics

SELECT 
vdt.User_ID AS [User Link],
( 'https://stackoverflow.com/users/' + CONVERT(nvarchar(20),vdt.User_ID) + '/' + REPLACE(u.DisplayName, ' ', '-') + '?tab=reputation' ) AS [Reputation Link],
u.Reputation AS [User Reputation],
vdt.Upvote_Count AS [Upvote Count], 
CONVERT(VARCHAR(20), vdt.Upvoting_Occured, 107) AS [Date of Vote],
vdt.Post_Count AS [Number of Posts],
vdt.Reputation_Gained AS [Reputation Gained]

FROM

(
SELECT 
u.Id AS User_Id, 
COUNT(v.Id) AS Upvote_Count,
MAX(v.CreationDate) AS Upvoting_Occured,
COUNT(DISTINCT p.Id) AS Post_Count,
SUM( CASE p.PostTypeId WHEN 1 THEN 5 WHEN 2 THEN 10 ELSE 0 END ) AS Reputation_Gained

FROM Votes AS v
INNER JOIN Posts AS p ON v.PostId = p.Id
INNER JOIN Users AS u ON p.OwnerUserId = u.Id

WHERE ( v.VoteTypeId = 2 ) AND ( v.CreationDate != p.CreationDate ) AND ( v.CreationDate > DATEADD(dd, -400, GETDATE()) )

GROUP BY 
u.Id,
v.CreationDate

) AS vdt

INNER JOIN Users AS u ON vdt.User_Id = u.Id

WHERE 
( ( vdt.Upvote_Count >= 20 ) OR ( vdt.Post_Count = vdt.Upvote_Count ) ) 
AND ( u.Reputation < 2500 ) 
AND ( ( vdt.Post_Count >= 10 ) OR ( vdt.Post_Count = vdt.Upvote_Count ) )
AND ( vdt.Post_Count > 6 )
AND ( vdt.Upvote_Count > 6 )
AND ( u.Reputation != 1 )

ORDER BY vdt.Upvoting_Occured DESC

fork query

edited aug 21 17 Magisch

created aug 10 17 anonymous