Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,360,149
Community Members
 
Community Events
168
Community Groups

End of year metrics: top 5 page creators

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;

 

0 comments

Comment

Log in or Sign up to comment
TAGS
Community showcase
Published in Confluence

Watch 4 Confluence apps compete for Best App Demo in September's Appy Hours

Calling all collaborators! Appy Hours on the Atlassian Community is a monthly event where 4 Partner and app vendor presenters go head-to-head with 5-minute demos for the title of Best App Demo. I...

524 views 0 12
Read article

Atlassian Community Events