Simon Willison’s Weblog
Owned by simonw, visibility: Public
SELECT "tag" AS wordcloud_word, COUNT(*) AS wordcloud_count FROM ( SELECT blog_tag.tag FROM blog_entry_tags JOIN blog_tag ON blog_entry_tags.tag_id = blog_tag.id JOIN blog_entry ON blog_entry_tags.entry_id = blog_entry.id WHERE EXTRACT(YEAR FROM blog_entry.created) = %(year)s UNION ALL SELECT blog_tag.tag FROM blog_blogmark_tags JOIN blog_tag ON blog_blogmark_tags.tag_id = blog_tag.id JOIN blog_blogmark ON blog_blogmark_tags.blogmark_id = blog_blogmark.id WHERE EXTRACT(YEAR FROM blog_blogmark.created) = %(year)s UNION ALL SELECT blog_tag.tag FROM blog_quotation_tags JOIN blog_tag ON blog_quotation_tags.tag_id = blog_tag.id JOIN blog_quotation ON blog_quotation_tags.quotation_id = blog_quotation.id WHERE EXTRACT(YEAR FROM blog_quotation.created) = %(year)s ) AS results WHERE tag not in ('quora', 'recovered') GROUP BY "tag" ORDER BY wordcloud_count DESC