Page Creation Count by User in Specific Groups from January to June 2023

[장부현]

"I'd like to verify the number of pages created by users in specified groups during a certain period of time. Could you please check if the following query is correct? I would really appreciate your help!"

SELECT
u.FIRST_NAME as user_name,
g.group_name,
COUNT(c.CONTENTID) as page_create_count
FROM
CWD_USER u
JOIN
USER_MAPPING um ON u.CWD_USERNAME = um.USERNAME
JOIN
CWD_MEMBERSHIP m ON u.ID = m.child_user_id
JOIN
CWD_GROUP g ON m.parent_id = g.ID
JOIN
(
SELECT
DISTINCT CONTENTID, CREATOR, CREATIONDATE
FROM
CONTENT
WHERE
VERSION ='1' AND CONTENTTYPE='page' AND CONTENT_STATUS IN ('current', 'deleted')
) c ON um.USER_KEY = c.CREATOR
WHERE
g.group_name IN (
'GROUP-A',
'GROUP-B',
'GROUP-C',
'GROUP-D',
'GROUP-E'
)
AND
c.CREATIONDATE BETWEEN TO_DATE('2023-01-01', 'YYYY-MM-DD') AND TO_DATE('2023-06-30', 'YYYY-MM-DD')
GROUP BY
u.FIRST_NAME, g.group_name
ORDER BY
page_create_count ASC;