Update bi-9.sql
--- Q9. Forum with related Tags
--- tagClass1: BaseballPlayer
--- tagClass2: ChristianBishop
--- threshold: 200
-select
- f_forumid,
- sum(competing) as comp,
- sum (ours) as ours2
-from (
- select
- f_forumid, (
- select count(*)
- from post
- where ps_forumid = f_forumid
- and exists (
- select 1
- from post_tag
- where pst_postid = ps_postid
- and pst_tagid in (
- select ttc_tagid
- from tagclass, tag_tagclass
- where ttc_tagclassid = tc_tagclassid
- and tc_name = '$tagClass1'
+/* Q9. Forum with related Tags
+\set tagClass1 '\'BaseballPlayer\''
+\set tagClass2 '\'ChristianBishop\''
+\set threshold 200
+ */
+WITH popular_forums AS (
+ SELECT fp_forumid as forumid
+ FROM forum_person
+ GROUP BY fp_forumid
+ HAVING count(*) > :threshold
)
- )
- ) as competing, (
- select count(*)
- from post
- where ps_forumid = f_forumid
- and exists (
- select 1
- from post_tag
- where pst_postid = ps_postid
- and pst_tagid in (
- select ttc_tagid
- from tagclass, tag_tagclass
- where ttc_tagclassid = tc_tagclassid
- and tc_name = '$tagClass2'
- )
- )
- ) as ours
- from forum
- where (select count(*) from forum_person where fp_forumid = f_forumid) > $threshold
-) mindshare
-where competing > 0
- and ours > 0
-group by f_forumid
-order by
- abs(sum(ours) - sum(competing)) desc,
- f_forumid asc
-limit 100;
+SELECT f.f_forumid AS "forum.id"
+ , count(DISTINCT p1.ps_postid) AS count1
+ , count(DISTINCT p2.ps_postid) AS count2
+ FROM tagclass tc1
+ , tag_tagClass ttc1
+ -- tag skipped because tagid connects tag_tagClass ans post_tag directly
+ --, tag t1
+ , post_tag pt1
+ , post p1
+ , tagclass tc2
+ , tag_tagClass ttc2
+ --, tag t2
+ , post_tag pt2
+ , post p2
+ , forum f
+ , popular_forums pf
+ WHERE 1=1
+ -- join
+ -- tagClass1 to forum
+ AND tc1.tc_tagclassid = ttc1.ttc_tagclassid
+ AND ttc1.ttc_tagid = pt1.pst_tagid
+ AND pt1.pst_postid = p1.ps_postid
+ AND p1.ps_forumid = f.f_forumid
+ AND f.f_forumid = pf.forumid
+ -- tagClass2 to forum
+ AND tc2.tc_tagclassid = ttc2.ttc_tagclassid
+ AND ttc2.ttc_tagid = pt2.pst_tagid
+ AND pt2.pst_postid = p2.ps_postid
+ AND p2.ps_forumid = f.f_forumid
+ -- filter
+ AND tc1.tc_name = :tagClass1
+ AND tc2.tc_name = :tagClass2
+ AND p1.ps_replyof IS NULL
+ AND p2.ps_replyof IS NULL
+ GROUP BY f.f_forumid
+ ORDER BY abs(count(DISTINCT p2.ps_postid) - count(DISTINCT p1.ps_postid) ) DESC, f.f_forumid
+ LIMIT 100
+;
