How can I get a table with the number of published posts by date?

Question

I am trying to investigate the correlation between the total number of posts published on a blog and the traffic it receive. I think it's not a huge task but my experience with coding with Wordpress is almost null.

I guess it should be something like a foreach which loops all the posts and list the publishing date for each of them. Then sorts them by publishing date and then it counts the number of published post.

Is my question clear enough?

Answer 1

the fastest way imho would be to create a custom sql statement:

global $wpdb;
$rows = $wpdb->query('SELECT DATE(post_date) AS date, COUNT(*) AS count FROM ' . $wpdb->posts . ' GROUP BY DATE(post_date) ORDER BY DATE(post_date)');
foreach ($rows as $row) {
    echo $row->date . ': ' . $row->count . '<br>';
}

Answer 2

You'll want to use WP_Query, along with the data parameters

e.g. years:

$years = array( 2015,2014,2013,2012 );
echo '<table>';
foreach ( $years as $year ) {
    $q = new WP_Query( array(
        'year' => $year,
        'fields' => 'ids'
    ) );
    echo '<tr><td>'.$year.'</td><td>'. $q->found_posts.'</td></tr>';
}
echo '</table>';

Note I specified only to grab the post IDs, this is to reduce the cost of the query. Doing it this way also means you can take better advantage of caching plugins

Answer 3

This answer is going to integrate the one from jjarolim

The correct whole query is

SELECT DATE(post_date) AS date, concat(post_title) titles, COUNT(*) AS count
FROM  wp_posts 
WHERE post_status = 'publish'
AND post_parent = 0
AND post_type = 'post'
GROUP BY DATE(post_date)
ORDER BY DATE(post_date)