SQL query to identify popular pages?

[ITops123]

I am having some trouble putting together a specific SQL query using the SQL plugin.

I'd like to run a query on the 10 pages with the highest number of comments created in the past week. This gives us a good sense of where the conversation is happening on our wiki.

I know this has to be possible, but I'm not having any luck.

Ideally, the output would be like this:

<th class="confluenceTh" style="font-size: 10pt; line-height: 13pt; color: #003366; background-color: #f0f0f0; text-align: left; vertical-align: top; min-width: 0.6em; border-width: 1px; border-color: #dddddd; border-style: solid; padding: 5px;">Page title</th><th class="confluenceTh" style="font-size: 10pt; line-height: 13pt; color: #003366; background-color: #f0f0f0; text-align: left; vertical-align: top; min-width: 0.6em; border-width: 1px; border-color: #dddddd; border-style: solid; padding: 5px;">Comments created in past week</th>

Where to find Waldo	50
Carmen Miranda - fact or fiction?	35
Oregon Trail comes to Kansas	25
etc.

Can someone help me with setting up the query? I have searched the SQL plugin pages and found lots of helpful queries, but not this.

[Bob Swift · Accepted solution]

select
(select c2.title from content as c2 where c2.contentid = c1.pageid) as "Page title",
count(*) as "Comments created in past week"
from content as c1
where c1.pageid is not null and c1.contenttype = 'COMMENT' and c1.creationdate > now() - interval '7 days'
group by c1.pageid
order by 2 desc