SQL query to find number of WordPress posts per week

I wanted to know how many posts I have been creating each week. Here is a quick MySQL query to find out:

SELECT DISTINCT extract(week from date(post_date)) AS WeekNumber, count( * ) AS Posts 
FROM wp_posts where post_type = 'post' AND post_status = 'publish' AND post_date like '%2012-%' 
GROUP BY WeekNumber;

Returns:

+------------+-------+
| WeekNumber | Posts |
+------------+-------+
|          1 |     4 | 
|          2 |     3 | 
|          3 |     3 | 
|          4 |     3 | 
|          5 |     3 | 
|          6 |     2 | 
|          7 |     3 | 
|          8 |     1 | 
|          9 |     2 | 
|         10 |     2 | 
|         11 |     3 | 
|         12 |     2 | 
|         13 |     2 | 
|         14 |     3 | 
+------------+-------+

,

Comments are closed.