Fix bi-15.sql
-select
- p.p_personid,
- count(p1.p_personid) as cnt
-from person p, knows, country c, person p1, country c1
-where c.ctry_name = '$country'
- and p.p_placeid = c.ctry_city
- and k_person1id = p.p_personid
- and k_person2id = p1.p_personid
- and p1.p_placeid = c1.ctry_city
- and c1.ctry_name = c.ctry_name
-group by p.p_personid
-having count(p1.p_personid) =
- floor((
- select avg(fcnt)
- from (
- select p2.p_personid, count(p3.p_personid) as fcnt
- from person p2, knows, country c2, person p3, country c3
- where p2.p_placeid = c3.ctry_city
- and c3.ctry_name = '$country'
- and k_person1id = p2.p_personid
- and k_person2id = p3.p_personid
- and p3.p_placeid = c3.ctry_city
- and c3.ctry_name = c2.ctry_name
- group by p2.p_personid) ctavg
- ))
-order by
- p.p_personid asc
-limit 100;
+/* Q15. Social normals
+\set country '\'Belarus\''
+ */
+WITH persons_of_country AS (
+ SELECT p.p_personid AS personid
+ FROM person p
+ , place ci -- city
+ , place co -- country
+ WHERE 1=1
+ -- join
+ AND p.p_placeid = ci.pl_placeid
+ AND ci.pl_containerplaceid = co.pl_placeid
+ -- filter
+ AND co.pl_name = :country
+)
+, persons_w_friendcount AS (
+ SELECT p.personid
+ , count(f.personid) AS friendCount
+ , floor(avg(count(f.personid)) OVER ()) AS avgFriendCount
+ FROM persons_of_country p -- persons to find
+ LEFT JOIN (knows k INNER JOIN persons_of_country f -- friend
+ ON (k.k_person2id = f.personid))
+ ON (p.personid = k.k_person1id)
+ WHERE 1=1
+ -- join given in the FROM clause
+ -- filter
+ GROUP BY p.personid
+)
+SELECT personid AS "person.id"
+ , friendCount AS count
+ FROM persons_w_friendcount
+ WHERE 1=1
+ -- filter
+ AND friendCount = avgFriendCount
+ ORDER BY personid
+ LIMIT 100
+;
