Recent WordPress Posts by Comment Frequency

wp-recent-posts-by-comments.php

<?php
/*

@author Corey Brown https://github.com/coreyweb
This code will display the 10 most commented on posts.

Rules: 
  - published within the last month
  - at least one comment made in the last week

*/
?>

<h3>Most Discussed <span>This Week</span></h3>

<?php

$result = $wpdb->get_results("
  SELECT ID, comment_count, post_title
  FROM {$wpdb->posts}
  JOIN {$wpdb->comments} ON (comment_post_ID = ID)
  WHERE 
    post_status = 'publish'
    AND comment_approved = '1'
    AND post_date_gmt > DATE_SUB(UTC_TIMESTAMP(), INTERVAL 1 MONTH)         
    AND comment_date_gmt > DATE_SUB(UTC_TIMESTAMP(), INTERVAL 1 WEEK)
  GROUP BY ID
  ORDER BY comment_count DESC
  LIMIT 10
");

foreach ($result as $post) { 
  ?> 
    <a href="<?php echo get_permalink( $post->ID ) ?>" title="Read the article" ><?php 
      echo $post->post_title
    ?></a>
    <a href="<?php echo get_permalink( $post->ID ) ?>#comments" title="Read the comments"><?php 
      echo $post->comment_count 
    ?></a>
  <?php 
}

?>