Had an interesting problem land on my plate today. A corner-office type wanted to know how many posts per month each of our authors had written. Sounds like a round of layoffs are coming, eh?
I hadn’t written any SQL in a LONG time but I managed to finally figure it out. Certainly it was faster than trying to add things up by hand (we have 17 brands we need this data for). Figured I’d save the queries in case I ever need them again. I am not saying this is the most efficient way to do this — I’m no DBA — but for a 1 time thing, it worked.
SELECT t.year_month, users.user_nicename, users.user_email, count(post_author)
FROM
(
SELECT DISTINCT(EXTRACT(YEAR_MONTH FROM `post_date`)) as `year_month`
FROM wp_posts
WHERE post_date > "2021-01-01 00:00:01"
ORDER BY `year_month` ASC
) as t,
wp_users users,
wp_posts posts
WHERE
posts.post_author = users.ID AND
EXTRACT(YEAR_MONTH FROM posts.post_date) = t.year_month AND
posts.post_status = 'publish' AND
posts.post_type IN ('post', 'company', 'download', 'event', 'page', 'podcast', 'product', 'webcast')
GROUP BY posts.post_author, t.year_month
ORDER BY posts.user_nicename, t.year_month ASC
In my case the request was for the past 2 years, so we’re starting at 2021-01-01.
The EXTRACT(YEAR_MONTH FROM `post_date`) was a new syntax for me.
So the posts.post_type IN line will have to be customized for your site; it depends on what content types you want to count. For this site we have a few custom content types. For a “vanilla” wordpress site IN (‘post’, ‘page’) should work.
This query sorts by author first, so you have 24 (since I’m grabbing 24 months of data) rows for the first user, then 24 for the next, and so on.
SELECT t.year_month, users.user_nicename, users.user_email, count(post_author)
FROM
(
SELECT DISTINCT(EXTRACT(YEAR_MONTH FROM `post_date`)) as `year_month`
FROM wp_posts
WHERE post_date > "2021-01-01 00:00:01"
ORDER BY `year_month` ASC
) as t,
wp_users users,
wp_posts posts
WHERE
posts.post_author = users.ID AND
EXTRACT(YEAR_MONTH FROM posts.post_date) = t.year_month AND
posts.post_status = 'publish' AND
posts.post_type IN ('post', 'company', 'download', 'event', 'page', 'podcast', 'product', 'webcast')
GROUP BY t.year_month, posts.post_author
ORDER BY t.year_month ASC
Same data, different format. This one orders by date, so you get a row for each author for Jan 2021, then a row for each author for Jan 2022 and so on.
