Number of pages created per user.

[mybucket2018]

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

[Daniel Eads _unmonitored account_ · Accepted solution]

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;

[mybucket2018]

This is absolutely  spot on thank you so much for helping me with this, you have helped me out massively. I didn't realise the user_mapping table existed because it wasn't shown here.

[Shlomi Horev]

Guys, I get the below error when running the following query 

ERROR: column "user_mapping.lower_username" must appear in the GROUP BY clause or be used in an aggregate function

Query:

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;

Any ideas? 

[Shlomi Horev]

Got if fixed, just need to group by all the columns.