Fix bi-13.sql
-with histogram(yy, mm, t_name, cnt) as (
- select
- extract(year from ps_creationdate) as yy,
- extract(month from ps_creationdate) as mm,
- t_name,
- count(*) as cnt
- from post
- join person on ps_creatorid = p_personid
- join place pcity on pcity.pl_placeid = p_placeid
- join place ctry on ctry.pl_placeid = pcity.pl_containerplaceid
- left join (select t_name, pst_postid from post_tag, tag where t_tagid = pst_tagid) tags on pst_postid = ps_postid
- where ctry.pl_name = '$country'
- group by extract(year from ps_creationdate), extract(month from ps_creationdate), t_name
- having count(*) > 1
- order by yy, mm, cnt desc
- ),
- histogram_rank as (
- select
- yy, mm, t_name, cnt,
- (
- select count(h2.cnt) as rank
- from histogram h2
- where h2.yy = h1.yy
- and h1.mm = h2.mm
- and h2.cnt >= h1.cnt
- ) as rank
- from histogram h1
+/* Q13. Popular Tags per month in a country
+\set country '\'Belarus\''
+ */
+WITH detail AS (
+SELECT extract(YEAR FROM m.ps_creationdate) as year
+ , extract(MONTH FROM m.ps_creationdate) as month
+ , t.t_name as tagName
+ , count(DISTINCT m.ps_postid) as popularity
+ , row_number() OVER (PARTITION BY extract(YEAR FROM m.ps_creationdate), extract(MONTH FROM m.ps_creationdate) ORDER BY count(DISTINCT m.ps_postid) DESC, t.t_name) as rn
+ FROM post m
+ , place c -- country
+ , post_tag pt
+ , tag t
+ WHERE 1=1
+ -- join
+ AND m.ps_locationid = c.pl_placeid
+ AND m.ps_postid = pt.pst_postid
+ AND pt.pst_tagid = t.t_tagid
+ -- filter
+ AND c.pl_name = :country
+ GROUP BY year, month, t.t_name
)
-select *
-from histogram_rank
-where rank < 5
-order by
- yy asc,
- mm asc,
- cnt desc,
- t_name asc
-limit 100;
+SELECT year, month, tagName, popularity
+ FROM detail
+ WHERE rn <= 5
+ ORDER BY year DESC, month, popularity DESC, tagName
+ LIMIT 100
+;
