Update bi-4.sql
--- Q4. Popular topics in a country
--- tagClass: MusicalArtist
--- country: Burma
-select
- f_forumid,
- f_title,
- f_creationdate,
- f_moderatorid,
- count(*) as cnt
-from forum, person, country,
- (
- select distinct post.*
- from post, post_tag, tag_tagclass, tagclass
- where tc_name = '$tagClass'
- and ttc_tagclassid = tc_tagclassid
- and ttc_tagid = pst_tagid
- and pst_postid = ps_postid
- ) post
-where ps_forumid = f_forumid
- and f_moderatorid = p_personid
- and p_placeid = ctry_city
- and ctry_name = '$country'
-group by f_forumid, f_title, f_creationdate, f_moderatorid
-order by
- cnt desc,
- f_forumid asc
-limit 100;
+/* Q4. Popular topics in a country
+\set tagClass '\'MusicalArtist\''
+\set country '\'Burma\''
+ */
+SELECT f.f_forumid AS "forum.id"
+ , f.f_title AS "forum.title"
+ , f.f_creationdate AS "forum.creationDate"
+ , f.f_moderatorid AS "person.id"
+ , count(DISTINCT p.ps_postid) AS postCount
+ FROM tagClass tc
+ , tag_tagClass ttc
+ , tag t
+ , post_tag pt
+ , post p
+ , forum f
+ , person m -- moderator
+ , place ci -- city
+ , place co -- country
+ WHERE 1=1
+ -- join
+ AND tc.tc_tagclassid = ttc.ttc_tagclassid
+ AND ttc.ttc_tagid = t.t_tagid
+ AND t.t_tagid = pt.pst_tagid
+ AND pt.pst_postid = p.ps_postid
+ AND p.ps_forumid = f.f_forumid
+ AND f.f_moderatorid = m.p_personid
+ AND m.p_placeid = ci.pl_placeid
+ AND ci.pl_containerplaceid = co.pl_placeid
+ -- filter
+ AND tc.tc_name = :tagClass
+ AND co.pl_name = :country
+ GROUP BY f.f_forumid, f.f_title, f.f_creationdate, f.f_moderatorid
+ ORDER BY postCount DESC, f.f_forumid
+ LIMIT 20
+;
