Tag cloud by year

Owned by simonw, visibility: Public

Query parameters

SQL query
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