Number of pages created per user.

Martin

Morning all,

I was hoping someone can help me with this as I'm starting to get pretty desperate.

The below code will generate a table with a list of how many pages each user has created. No matter what I try, I can't figure out how to get the actual username to appear. The User field only displays a bunch of random text and numbers.

 

---

Code:

select concat('[~', creator, ']') as "User", count(distinct CONTENT.title) as "Pages created"
    from CONTENT
    where creator IS NOT NULL
    group by creator
    order by "Pages created" desc;

Result:

pagescreated.jpg

pagescreated.jpg

Daniel Eads {unmonitored account}

Hi Martin,

The Creator column in the Content table is just a unique ID for the user. The actual usernames and name for users are in the cwd_user table. It's also worth noting that your query will count not only pages, but attachments, status updates, etc. The queries here are scoped to only include Page and Blog content types. Try this out:

SELECT lower_username AS "Username", display_name AS "Full Name", count(DISTINCT CONTENT.title) AS "Pages created"
FROM CONTENT
LEFT JOIN USER_MAPPING ON CONTENT.creator = USER_MAPPING.user_key
LEFT JOIN CWD_USER ON USER_MAPPING.lower_username = CWD_USER.lower_user_name
WHERE creator IS NOT NULL
AND CONTENTTYPE IN ('PAGE', 'BLOGPOST')
GROUP BY creator
ORDER BY "Pages created" DESC;

 

For future people reading this with Oracle databases, you'll need to change things around so Oracle actually executes the query instead of griping at you. The previous query should be MySQL/MSSQL compatible (according to posts complaining about the Oracle problem I hit), but we only have Oracle Confluence databases so I couldn't confirm. Give this one a go if the above threw an error.

SELECT DISTINCT lower_username AS "Username", display_name AS "Full Name", "Pages created"
FROM (SELECT creator, count(DISTINCT CONTENT.title) AS "Pages created"
FROM CONTENT
WHERE creator IS NOT NULL
AND CONTENTTYPE IN ('PAGE', 'BLOGPOST')
GROUP BY creator
ORDER BY "Pages created" DESC)
LEFT JOIN USER_MAPPING ON creator = USER_MAPPING.user_key
LEFT JOIN CWD_USER ON USER_MAPPING.lower_username = CWD_USER.lower_user_name
ORDER BY "Pages created" DESC;