Materialize/test/ldbc-bi/q05.sql

\set tag '\'Sikh_Empire\''
-- materialize=> \i q05.sql
--    person.id    | replycount | likecount | messagecount | score
-- ----------------+------------+-----------+--------------+-------
--   8796093023472 |          2 |       176 |            1 |  1765
--   2199023258140 |          2 |       134 |            1 |  1345
--   4398046515963 |          3 |        89 |            1 |   897
--  17592186051306 |          0 |        55 |            1 |   551
--  19791209305553 |          1 |        52 |            2 |   524
--  21990232561529 |          2 |        29 |            1 |   295
--            6574 |          0 |        18 |            1 |   181
--   8796093023990 |          7 |        16 |            1 |   175
--  13194139533461 |          2 |        16 |            1 |   165
--   8796093026421 |         15 |         0 |            2 |    32
--  21990232557614 |         12 |         0 |            3 |    27
--   8796093026744 |         11 |         0 |            1 |    23
--  24189255813538 |         10 |         0 |            2 |    22
--   8796093026743 |          5 |         1 |            1 |    21
--   6597069767028 |          9 |         0 |            2 |    20
--  21990232562293 |          8 |         0 |            2 |    18
--  26388279075120 |          3 |         1 |            2 |    18
--   2199023264490 |          8 |         0 |            1 |    17
--   6597069774335 |          8 |         0 |            1 |    17
--   8796093022752 |          3 |         1 |            1 |    17
--  28587302326226 |          7 |         0 |            2 |    16
--            3451 |          7 |         0 |            1 |    15
--            4961 |          2 |         1 |            1 |    15
--  19791209301861 |          7 |         0 |            1 |    15
--  24189255813464 |          7 |         0 |            1 |    15
--  24189255813906 |          2 |         1 |            1 |    15
--   4398046513670 |          6 |         0 |            1 |    13
--   6597069776679 |          6 |         0 |            1 |    13
--  10995116280719 |          1 |         1 |            1 |    13
--  10995116287958 |          6 |         0 |            1 |    13
--  13194139541085 |          6 |         0 |            1 |    13
--  21990232556570 |          1 |         1 |            1 |    13
--  21990232561742 |          6 |         0 |            1 |    13
--  21990232565214 |          6 |         0 |            1 |    13
--  26388279072028 |          6 |         0 |            1 |    13
--  35184372092938 |          6 |         0 |            1 |    13
--             361 |          5 |         0 |            1 |    11
--            7244 |          0 |         1 |            1 |    11
--            8892 |          5 |         0 |            1 |    11
--   4398046517444 |          5 |         0 |            1 |    11
--   8796093030183 |          5 |         0 |            1 |    11
--  10995116279521 |          5 |         0 |            1 |    11
--  13194139541181 |          0 |         1 |            1 |    11
--  13194139541221 |          5 |         0 |            1 |    11
--  13194139541373 |          5 |         0 |            1 |    11
--  15393162794711 |          5 |         0 |            1 |    11
--  21990232561531 |          0 |         1 |            1 |    11
--  32985348839886 |          5 |         0 |            1 |    11
--  35184372091084 |          5 |         0 |            1 |    11
--  35184372098900 |          5 |         0 |            1 |    11
--            5099 |          4 |         0 |            2 |    10
--  17592186045301 |          4 |         0 |            2 |    10
--   2199023259253 |          4 |         0 |            1 |     9
--   4398046520094 |          4 |         0 |            1 |     9
--   6597069767319 |          4 |         0 |            1 |     9
--   6597069767925 |          4 |         0 |            1 |     9
--   8796093027822 |          4 |         0 |            1 |     9
--   8796093030655 |          4 |         0 |            1 |     9
--  10995116283381 |          4 |         0 |            1 |     9
--  10995116283874 |          4 |         0 |            1 |     9
--  13194139535015 |          4 |         0 |            1 |     9
--  15393162797132 |          4 |         0 |            1 |     9
--  17592186046214 |          4 |         0 |            1 |     9
--  17592186053687 |          4 |         0 |            1 |     9
--  19791209306158 |          4 |         0 |            1 |     9
--  21990232556400 |          4 |         0 |            1 |     9
--  21990232561740 |          4 |         0 |            1 |     9
--  30786325581075 |          4 |         0 |            1 |     9
--  32985348837969 |          4 |         0 |            1 |     9
--  35184372098666 |          4 |         0 |            1 |     9
--   4398046519516 |          3 |         0 |            2 |     8
--            3819 |          3 |         0 |            1 |     7
--            6394 |          3 |         0 |            1 |     7
--            8726 |          3 |         0 |            1 |     7
--   8796093025681 |          3 |         0 |            1 |     7
--   8796093027283 |          3 |         0 |            1 |     7
--   8796093032299 |          3 |         0 |            1 |     7
--  10995116283026 |          3 |         0 |            1 |     7
--  17592186044948 |          3 |         0 |            1 |     7
--  17592186047799 |          3 |         0 |            1 |     7
--  17592186048641 |          3 |         0 |            1 |     7
--  19791209302281 |          3 |         0 |            1 |     7
--  21990232560723 |          3 |         0 |            1 |     7
--  24189255817882 |          3 |         0 |            1 |     7
--  26388279073672 |          3 |         0 |            1 |     7
--  26388279073849 |          3 |         0 |            1 |     7
--  26388279074975 |          3 |         0 |            1 |     7
--  26388279075052 |          3 |         0 |            1 |     7
--  28587302324452 |          3 |         0 |            1 |     7
--  30786325580327 |          3 |         0 |            1 |     7
--  32985348841715 |          3 |         0 |            1 |     7
--  35184372094081 |          3 |         0 |            1 |     7
--  35184372099063 |          3 |         0 |            1 |     7
--  24189255812191 |          2 |         0 |            2 |     6
--             217 |          2 |         0 |            1 |     5
--            2174 |          2 |         0 |            1 |     5
--            8018 |          2 |         0 |            1 |     5
--   2199023258226 |          2 |         0 |            1 |     5
--   2199023261155 |          2 |         0 |            1 |     5
--   2199023262487 |          2 |         0 |            1 |     5
-- (100 rows)
--
-- Time: 17582.355 ms (00:17.582)

/* Q5. Most active posters in a given topic
\set tag '\'Abbas_I_of_Persia\''
 */
WITH detail AS (
SELECT Message.CreatorPersonId AS CreatorPersonId
     , sum(coalesce(Cs.c, 0))  AS replyCount
     , sum(coalesce(Plm.c, 0)) AS likeCount
     , count(Message.MessageId) AS messageCount
  FROM Tag
  JOIN Message_hasTag_Tag
    ON Message_hasTag_Tag.TagId = Tag.id
  JOIN Message
    ON Message.MessageId = Message_hasTag_Tag.MessageId
  LEFT JOIN (SELECT ParentMessageId, count(*) FROM Message c WHERE ParentMessageId IS NOT NULL GROUP BY ParentMessageId) Cs(id, c) ON Cs.id = Message.MessageId
  LEFT JOIN (SELECT MessageId, count(*) FROM Person_likes_Message GROUP BY MessageId) Plm(id, c) ON Plm.id = Message.MessageId
 WHERE Tag.name = :tag
 GROUP BY Message.CreatorPersonId
)
SELECT CreatorPersonId AS "person.id"
     , replyCount
     , likeCount
     , messageCount
     , 1*messageCount + 2*replyCount + 10*likeCount AS score
  FROM detail
 ORDER BY score DESC, CreatorPersonId
 LIMIT 100
;