Update bi-23.sql
/* Q23. Holiday destinations
\set country '\'Belarus\''
*/
-select
- count(*) as cnt,
- dest.pl_name,
- extract(month from ps_creationdate) as mm
-from person, post, place dest, country resid
-where resid.ctry_name = :country
- and dest.pl_name <> :country
- and p_placeid = resid.ctry_city
- and ps_locationid = dest.pl_placeid
- and p_personid = ps_creatorid
-group by dest.pl_name, extract(month from ps_creationdate)
-order by
- cnt desc,
- dest.pl_name asc,
- mm
-limit 100;
+SELECT count(*) AS messageCount
+ , dest.pl_name AS "destination.name"
+ , extract(MONTH FROM m.ps_creationdate) AS month
+ FROM place pco -- person country
+ , place pci -- person city
+ , person p
+ , post m
+ , place dest
+ WHERE 1=1
+ -- join
+ AND pco.pl_placeid = pci.pl_containerplaceid
+ AND pci.pl_placeid = p.p_placeid
+ AND p.p_personid = m.ps_creatorid
+ AND m.ps_locationid = dest.pl_placeid
+ -- filter
+ AND pco.pl_name = :country
+ AND m.ps_locationid != pco.pl_placeid
+ GROUP BY dest.pl_name, month
+ ORDER BY messageCount DESC, dest.pl_name, month
+ LIMIT 100
+;
