WordPress Custom Query for Most Commented Posts in the Past 7 Days

I'm trying to do a query on wpdb to get the posts commented on most in the past week... Any ideas what I'm doing wrong?

$querystr = "SELECT comment_count, ID, post_title
        FROM $wpdb->posts wposts开发者_开发问答, $wpdb->comments wcomments
        WHERE wposts.ID = wcomments.comment_post_ID
        AND wcomments.comment_date >= CURDATE() - 7 
        GROUP BY wposts.ID
        ORDER BY comment_count DESC
        LIMIT 0 ,  10
 ";

 $pageposts = $wpdb->get_results($querystr);

The query seems to get top commented posts of all time, instead of the top commented posts that have been commented on in the past week.

Thanks in advance.

Lonut, that takes care of the date range problem and most commented, but it wont select only the most commented posts that were commented on in the past X time interval.

This should do it:

$querystr = "SELECT comment_count, ID, post_title
        FROM $wpdb->posts wposts, $wpdb->comments wcomments
        WHERE wposts.ID = wcomments.comment_post_ID
        AND wposts.post_status='publish'
        AND wcomments.comment_approved='1'
        AND wcomments.comment_date > DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 1 WEEK) 
        GROUP BY wposts.ID
        ORDER BY comment_count DESC
        LIMIT 0 ,  10
 ";

EDIT: Filter on date changed from YEAR to WEEK

This should work:

global $wpdb;
$querystr = $wpdb->get_results("SELECT comment_count, ID,post_title 
    FROM $wpdb->posts 
    WHERE 
    post_status='publish' AND 
    post_date > DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 1 WEEK) 
    ORDER BY comment_count DESC 
    LIMIT 0 , 10");

You're ordering by the comment_count field of the posts table. Try ordering on the number of comments matched for the group:

ORDER BY count(*) desc