0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for system and network administrators

/*select TOP 200 row_number() over (order by u.Id),
    u.Id, u.DisplayName, u.Reputation, u.LastAccessDate
from users u
inner join posts p on p.owneruserid = u.id
    and p.creationdate > dateadd(month, -6, current_timestamp)
where u.reputation > 10000
group by u.Id, u.DisplayName, u.Reputation, u.LastAccessDate
order by u.Id
*/

/* alternate method (curious to see time comparison) 
    ... and verdict is 2nd method was slightly faster (701ms vs 715ms)
    ... that's basically a rounding error.
*/

select TOP 200 row_number() over (order by u.Id),
    u.Id, u.DisplayName, u.Reputation, u.LastAccessDate
from users u
cross apply (
    select top 1 id 
    from posts p 
    where p.owneruserid = u.id
        and p.creationdate > dateadd(month, -6, current_timestamp)
) p
where u.reputation > 10000
order by u.Id

fork query

edited jul 19 18

created jul 19 18 anonymous