Toss this into a test template. Does it return the basically what you’re after? We can work on details later.
{exp:query sql="SELECT c.entry_id, c.comment_date, COUNT(c.entry_id) AS comment_count, t.title FROM exp_comments c, exp_weblog_titles t WHERE c.entry_id = t.entry_id
GROUP BY c.entry_id HAVING UNIX_TIMESTAMP() - c.comment_date <= (14*24*60*60) ORDER BY comment_count DESC, c.comment_date DESC LIMIT 100"}
<p><strong>{title}</strong> - {comment_count} comment{if comment_count != 1}s{/if} in the last 14 days</p>
{/exp:query}
The query written more legibly:
SELECT c.entry_id, c.comment_date, COUNT(c.entry_id) AS count, t.title
FROM exp_comments c, exp_weblog_titles t
WHERE c.entry_id = t.entry_id
GROUP BY c.entry_id
HAVING UNIX_TIMESTAMP() - c.comment_date <= (14*24*60*60)
ORDER BY count DESC, c.comment_date DESC
LIMIT 100
