Inspire ideas by # of endorsements

Last run at 21:06 PDT on March 15, 2015. Copy/paste the code below into a new query and click "submit" to get updated counts.

SQL

SELECT SUBSTRING_INDEX(page_title, "/", -1) AS idea_title, COUNT(rev_comment) as endorsements
FROM metawiki_p.page AS p 
JOIN metawiki_p.categorylinks AS cl 
ON p.page_id = cl.cl_from 
JOIN metawiki_p.revision AS r
ON p.page_id = r.rev_page
WHERE cl.cl_to ="IdeaLab/Ideas/Inspire"
AND cl.cl_type = "page"
AND p.page_namespace = 200
AND p.page_title LIKE "IdeaLab/%"
AND p.page_title NOT LIKE "%/%/%"
AND (r.rev_comment LIKE ("%Endorse%") OR r.rev_comment LIKE ("%endorse%")) GROUP BY idea_title ORDER BY endorsements DESC;

SQL

Checking query status...