Using SQL, on a database with user logins, report the top 10 users that have logged in the most number of times in a given duration along with a count of the items created by them in that duration.

Question :

Using SQL, on a database with user logins, report the top 10 users that have logged in the most number of times in a given duration along with a count of the items created by them in that duration.

 

Example:

Using SupportDB, report the top 10 agents that have logged in the last 'n' days and the number of ServiceRequests created by each of those agents.

 

The output should look like:

 

________________________________________________

Agent Name| Last Logged Date| Log Count | SRs Created

-----------------|------------------------|----------------|---------------------

agent name | 4/20/2013T12:0.. | 23               | 109

__________|_______________|__________|____________

Solution

select UL.FullName, UL.LastLogin, temp.CountOfSRs from UserList UL
join (select top 10 SR.AuditCreatedByPuid,COUNT(SR.id) AS CountOfSRs FROM dbo.ServiceRequest SR (nolock)
where SR.AuditCreatedByPuid in (
select UL.AuditCreatedByPuid from dbo.Userlist UL
where LastLogin >= dateadd(day,- 7,GetUTCDate())
group by SR.AuditCreatedByPuid
order by CountOfSRs desc) temp on UL.PUID = temp.AuditCreatedByPuid