End of year metrics: top 5 page creators

[Rita Nygren]

I wanted to give our various managers some metrics to base some of their upcoming reviews on, so I wrote up this SQL query showing the top 5 page creators for each space  - others may be interested in this as well.  I'm sure there are ways of making this more efficient, but it got me what I needed.

With allpages as (
SELECT c.[CONTENTID]
,c.[SPACEID]
,s.SPACENAME
,u.lower_username
,cu.display_name
,cu.email_address
FROM [CONTENT] c
join SPACES s on s.SPACEID=c.SPACEID and left(s.SPACEKEY,1)<>'~' 
left join user_mapping u on u.user_key=c.CREATOR
left join cwd_user AS cu ON cu.lower_user_name = u.lower_username
where c.[CREATIONDATE]>'2020-01-01 01:01:05.000'
)
, spacepages as (
Select display_name
,email_address
,SPACENAME
,count(CONTENTID) as numpages
from allpages
group by display_name, email_address,SPACENAME

)
Select * from (
Select display_name
,email_address
,SPACENAME
,numpages
,row_number() over (partition by SPACENAME order by numpages desc) as spacerank
from spacepages) ranks
where spacerank <=5;