Fix bi-14.sql
-with recursive replies(ps_replyof, ps_postid) as (
- select ps_replyof, ps_postid from post
- where ps_creationdate >= $begin
- and ps_creationdate <= $end
- union all
- select r.ps_replyof, p.ps_postid from replies r, post p
- where r.ps_postid = p.ps_replyof
- and ps_creationdate >= $begin
- and ps_creationdate <= $end
+/* Q14. Top thread initiators
+\set startDate '\'2012-06-01T00:00:00.000+00:00\''::timestamp
+\set endDate '\'2012-07-01T00:00:00.000+00:00\''::timestamp
+ */
+WITH RECURSIVE post_all(psa_threadid
+ , psa_thread_creatorid
+ , psa_messageid
+ , psa_creationdate
+ , psa_messagetype
+ ) AS (
+ SELECT ps_postid AS psa_threadid
+ , ps_creatorid AS psa_thread_creatorid
+ , ps_postid AS psa_messageid
+ , ps_creationdate
+ , 'Post'
+ FROM post
+ WHERE 1=1
+ AND ps_replyof IS NULL -- post, not comment
+ AND ps_creationdate BETWEEN :startDate AND :endDate
+ UNION ALL
+ SELECT psa.psa_threadid AS psa_threadid
+ , psa.psa_thread_creatorid AS psa_thread_creatorid
+ , ps_postid
+ , ps_creationdate
+ , 'Comment'
+ FROM post p
+ , post_all psa
+ WHERE 1=1
+ AND p.ps_replyof = psa.psa_messageid
)
-select
- p_personid,
- p_firstname,
- p_lastname,
- count(*) as cnt,
- count(distinct org.ps_postid) as n_threads
-from person, post org, replies
-where replies.ps_replyof = org.ps_postid
- and org.ps_replyof is null
- and org.ps_creatorid = p_personid
- and org.ps_creationdate >= $begin
- and org.ps_creationdate <= $end
-group by p_personid, p_firstname, p_lastname
-order by
- cnt desc,
- p_personid asc
-limit 100;
+SELECT p.p_personid AS "person.id"
+ , p.p_firstname AS "person.firstName"
+ , p.p_lastname AS "person.lastName"
+ , count(DISTINCT psa.psa_threadid) AS threadCount
+ -- if the thread initiator message does not count as a reply
+ --, count(DISTINCT CASE WHEN psa.psa_messagetype = 'Comment' then psa.psa_messageid ELSE null END) AS messageCount
+ , count(DISTINCT psa.psa_messageid) AS messageCount
+ FROM person p left join post_all psa on (
+ 1=1
+ AND p.p_personid = psa.psa_thread_creatorid
+ AND psa_creationdate BETWEEN :startDate AND :endDate
+ )
+ GROUP BY p.p_personid, p.p_firstname, p.p_lastname
+ ORDER BY messageCount DESC, p.p_personid
+ LIMIT 100
+;
