4

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for students, teachers, and linguists wanting to discuss the finer points of the Italian language

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