4

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for serious players and enthusiasts of chess

SELECT Users.Id, Users.Reputation, Users.DisplayName, 
       Badges.Date As BadgeDate,
       Posts.Id AS PostId, Posts.CreationDate AS PostDate,
       Votes.VoteTypeId AS VoteId, Votes.CreationDate AS VoteDate
INTO #tmp
FROM Users
     LEFT JOIN Badges ON Users.Id = Badges.UserId
     LEFT JOIN Posts ON Users.Id = Posts.OwnerUserId
     LEFT JOIN Votes ON Posts.Id = Votes.PostId
WHERE Badges.Name='Mortarboard' 
  AND Votes.VoteTypeId=2
  AND CAST(Badges.Date AS DATE)=Votes.CreationDate
  AND CAST(Posts.CreationDate AS DATE)!=Votes.CreationDate;


SELECT * FROM #tmp t1
INNER JOIN (
    SELECT t3.Id
    FROM (
        SELECT *,
        ROW_NUMBER() OVER(PARTITION BY PostDate ORDER BY Id) AS 'RowNum'
        FROM #tmp
    ) t3
    WHERE t3.RowNum = 1
    GROUP BY t3.Id HAVING COUNT(*) > 10
) t2
ON t1.Id = t2.Id
ORDER BY t1.Id DESC, t1.PostId;

fork query

edited aug 5 14

created aug 5 14 TheBanana