| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130 |
- \set startDate '\'2012-08-29\''::timestamp
- \set endDate '\'2012-11-24\''::timestamp
- -- materialize=> \i q09.sql
- -- person.id | person.firstName | person.lastName | threadcount | messagecount
- -- ----------------+-------------------+-----------------+-------------+--------------
- -- 8796093032299 | Robert | Brown | 144 | 1984
- -- 26388279073672 | Andrei | Condariuc | 185 | 1314
- -- 24189255815615 | Dan | Andersson | 125 | 1182
- -- 10995116284458 | Mark | Coetzee | 97 | 1112
- -- 24189255811663 | Chris | Hall | 197 | 1067
- -- 28587302329167 | Samir | Al-Fayez | 267 | 1033
- -- 6597069774064 | Ayesha | Ahmed | 117 | 961
- -- 15393162797132 | Shweta | Kapoor | 109 | 829
- -- 30786325579605 | Jorge | Gonzalez | 69 | 816
- -- 28587302323380 | Alexander | Hleb | 96 | 735
- -- 10995116284554 | Ali | Lo | 63 | 732
- -- 4398046519825 | Ahmad Rafiq | Akbar | 103 | 711
- -- 13194139535893 | Benedict | Wang | 55 | 697
- -- 15393162792602 | Francisco | Gonzalez | 92 | 697
- -- 28587302326380 | Sam | Carter | 77 | 687
- -- 17592186045301 | Rudolf | Engel | 51 | 674
- -- 8796093030850 | Monja | Tsiranana | 95 | 662
- -- 32985348835002 | Bingbing | Liu | 88 | 643
- -- 19791209304449 | Peng | Li | 44 | 642
- -- 19791209306158 | Bobby | Garcia | 58 | 641
- -- 2199023265458 | Peter | Hall | 74 | 632
- -- 8796093030826 | K. | Sharma | 92 | 618
- -- 21990232559659 | Baruch | Dego | 108 | 594
- -- 26388279070147 | George | Nagy | 74 | 594
- -- 24189255814889 | Jun | Zhang | 78 | 585
- -- 8478 | James | Moore | 44 | 535
- -- 24189255812073 | Anh | Pham | 38 | 510
- -- 28587302329901 | Shweta | Khan | 60 | 510
- -- 24189255819865 | Sombat | Amarttayakul | 69 | 500
- -- 8796093023472 | K. | Bose | 82 | 497
- -- 21990232563252 | Avraham | Alhouthi | 100 | 480
- -- 10995116283619 | Maria | Kibona | 101 | 474
- -- 24189255812627 | Edward | Ouma | 43 | 474
- -- 26388279066798 | Helen | Carr | 54 | 462
- -- 2199023258646 | Abdul Rahman | Budjana | 63 | 455
- -- 28587302326092 | John | Sen | 47 | 434
- -- 8796093032304 | Eli | Chihab | 62 | 433
- -- 19791209308287 | Sam | Huang | 33 | 432
- -- 15393162789959 | Alfred | Hoffmann | 81 | 422
- -- 24189255814904 | Abdulwahab | Gillett | 56 | 422
- -- 2199023263408 | Piotr | Dobrowolski | 57 | 419
- -- 15393162795695 | Robert | Bratianu | 32 | 419
- -- 26388279074683 | Adrian | Popescu | 59 | 419
- -- 8796093023326 | Joseph | Sharma | 30 | 414
- -- 15393162791403 | Aaron | Cruz | 70 | 409
- -- 26388279073393 | Rajiv | Singh | 36 | 409
- -- 19791209308584 | Huu | Ho | 29 | 402
- -- 21990232563874 | Howard | Ha | 29 | 397
- -- 24189255820411 | Jose | Sotto | 33 | 396
- -- 17592186054998 | Manuel | Cosio | 43 | 395
- -- 32985348843490 | Yang | Li | 38 | 394
- -- 24189255811542 | Fatmir | Mojsov | 73 | 388
- -- 17592186052709 | Babar | Hussain | 80 | 379
- -- 10995116283889 | Joe | Wong | 26 | 373
- -- 21990232557614 | Hoang Yen | Nguyen | 69 | 370
- -- 21990232561742 | Abhishek | Roy | 59 | 370
- -- 28587302327742 | Jun | Li | 33 | 359
- -- 17592186052516 | Yang | Yang | 26 | 358
- -- 2199023260815 | Chen | Li | 32 | 352
- -- 19791209309084 | Adriaan | Dijk | 48 | 345
- -- 17592186049517 | Natalia Barbu | Goma | 105 | 343
- -- 28587302326226 | Mohamed | Achiou | 70 | 338
- -- 17592186046498 | Andrea | Blanco | 141 | 330
- -- 19791209302061 | Manuel | Martins | 81 | 330
- -- 2199023256462 | Emperor of Brazil | Dom Pedro II | 69 | 329
- -- 17592186050645 | Ning | Zhang | 76 | 329
- -- 35184372092691 | Carlos | Escobar | 41 | 327
- -- 10995116279521 | Rahul | Nair | 54 | 326
- -- 15393162796126 | Agustiar | Irama | 29 | 313
- -- 15393162799448 | Fritz | Muller | 47 | 310
- -- 6597069770739 | Abdul Haris | Gallagher | 28 | 302
- -- 28587302326788 | Carlos | Rodriguez | 65 | 301
- -- 26388279072682 | Abderrahmane | Ferrer | 96 | 300
- -- 6597069767028 | Grigore | Bologan | 65 | 299
- -- 26388279067483 | Aditya | Khan | 26 | 298
- -- 10995116285747 | Koji | Tanaka | 25 | 292
- -- 26388279068136 | Koji | Kato | 20 | 289
- -- 8796093023499 | Bruna | Costa | 93 | 285
- -- 17592186049899 | Ivan Ignatyevich | Aleksandrov | 42 | 279
- -- 15393162789362 | K. | Kumar | 24 | 278
- -- 17592186050809 | Pablo | Cosio | 29 | 278
- -- 10995116281358 | Kunal | Kumar | 20 | 277
- -- 17592186051871 | Ashok | Sharma | 56 | 276
- -- 19791209305198 | Martin | Muller | 39 | 275
- -- 19791209300230 | Benhalima | Ferrer | 42 | 273
- -- 4398046520667 | Charles | Bona | 51 | 272
- -- 32985348835600 | Isabel | Diaz | 45 | 272
- -- 24189255815184 | K. | Kumar | 31 | 270
- -- 8796093028247 | Jie | Wang | 19 | 268
- -- 2199023260487 | Bing | Yang | 50 | 266
- -- 17592186054199 | Fritz | Fischer | 61 | 264
- -- 2662 | Priyanka | Nair | 43 | 260
- -- 2783 | Rafael | Alonso | 65 | 260
- -- 24189255815100 | Albert | Jong | 51 | 260
- -- 17592186047024 | Wei | Zhu | 62 | 259
- -- 21990232563483 | Zhong | Wang | 89 | 253
- -- 24189255817213 | Álvaro | Rojas | 48 | 252
- -- 10349 | Ahmad | Arief | 45 | 250
- -- 2199023265231 | Wei | Wang | 82 | 249
- -- 19791209301504 | Jose | Costa | 16 | 241
- -- (100 rows)
- --
- -- Time: 3765.033 ms (00:03.765)
- /* Q9. Top thread initiators
- \set startDate '\'2011-10-01\''::timestamp
- \set endDate '\'2011-10-15\''::timestamp
- */
- WITH
- MPP AS (SELECT RootPostId, count(*) as MessageCount FROM Message WHERE Message.creationDate BETWEEN :startDate AND :endDate GROUP BY RootPostId)
- SELECT Person.id AS "person.id"
- , Person.firstName AS "person.firstName"
- , Person.lastName AS "person.lastName"
- , count(Post.id) AS threadCount
- , sum(MPP.MessageCount) AS messageCount
- FROM Person
- JOIN Post_View Post
- ON Person.id = Post.CreatorPersonId
- JOIN MPP
- ON Post.id = MPP.RootPostId
- WHERE Post.creationDate BETWEEN :startDate AND :endDate
- GROUP BY Person.id, Person.firstName, Person.lastName
- ORDER BY messageCount DESC, Person.id
- LIMIT 100
- ;
|
