Fix bi-21.sql
-select
- zombielikes.ps_creatorid,
- zombielikes.cnt as zmb,
- reallikes.cnt as real,
- zombielikes.cnt::float/reallikes.cnt as score
-from
- (
- select post.ps_creatorid as ps_creatorid, count(likes.l_creationdate) as cnt
- from (
- select p_personid
- from person, post
- where ps_creatorid = p_personid
- group by p_personid, p_creationdate
- having count(*) < extract(day from ($endDate - p_creationdate))/30
- ) noproducer, post, likes
- where likes.l_personid = noproducer.p_personid
- and likes.l_postid = post.ps_postid
- group by post.ps_creatorid
- ) zombielikes,
- (
- select post.ps_creatorid as ps_creatorid, count(likes.l_creationdate) as cnt
- from post, likes
- where likes.l_postid = post.ps_postid
- group by post.ps_creatorid
- ) reallikes, country, person p
-where zombielikes.ps_creatorid = reallikes.ps_creatorid
- and p.p_placeid = ctry_city
- and ctry_name = '$country'
- and zombielikes.ps_creatorid = p.p_personid
-order by
- zombielikes.cnt::float/reallikes.cnt desc,
- zombielikes.ps_creatorid asc
-limit 100;
+/* Q21. Zombies in a country
+\set country '\'Belarus\''
+\set endDate '\'2013-01-01T00:00:00.000+00:00\''::timestamp
+ */
+WITH zombies AS (
+ SELECT p.p_personid AS zombieid
+ FROM place co -- country
+ , place ci -- city
+ , person p
+ LEFT JOIN post m ON (p.p_personid = m.ps_creatorid AND m.ps_creationdate BETWEEN p.p_creationdate AND :endDate)
+ WHERE 1=1
+ -- join
+ AND co.pl_placeid = ci.pl_containerplaceid
+ AND ci.pl_placeid = p.p_placeid
+ -- filter
+ AND co.pl_name = :country
+ AND p.p_creationdate < :endDate
+ GROUP BY p.p_personid
+ -- average of [0, 1) messages per month is equivalent with having less messages than the month span between person creationDate and parameter :endDate
+ HAVING count(ps_postid) < 12*extract(YEAR FROM :endDate)+extract(MONTH FROM :endDate) - (12*extract(YEAR FROM p.p_creationdate) + extract(MONTH FROM p.p_creationdate)) + 1
+)
+SELECT z.zombieid AS "zombie.id"
+ , count(zl.zombieid) AS zombieLikeCount
+ , count(l.l_personid) AS totalLikeCount
+ , CASE WHEN count(l.l_personid) = 0 THEN 0 ELSE count(zl.zombieid)::float/count(l.l_personid) END AS zombieScore
+ FROM post m
+ INNER JOIN likes l ON (m.ps_postid = l.l_postid)
+ INNER JOIN person p ON (l.l_personid = p.p_personid AND p.p_creationdate < :endDate)
+ LEFT JOIN zombies zl ON (p.p_personid = zl.zombieid) -- see if the like was given by a zombie
+ RIGHT JOIN zombies z ON (z.zombieid = m.ps_creatorid)
+ GROUP BY z.zombieid
+ ORDER BY zombieScore DESC, z.zombieid
+ LIMIT 100
+;
