The 10 mightiest SQL queries in the world

Some years ago SQL was considered “dead”, but today it’s more alive than ever. If you don’t master SQL, you’re finished.

But SQL is much more than SELECT, GROUP BY, JOIN and WHERE. That is the SQL from Windows 3.11 era, the SQL from 1992, the past. If you don’t know what happened after, don’t miss out this talk.

SQL is one of the most advanced and powerful languages out there. And today we are proving it with 10 queries that will blow your mind. 10 examples to use in your daily work and demonstrate your SQL superpowers.

Transcript

1. The 10 mightiest SQL
   queries in the world
   Pablo Gonzalez Doval
   

   View Slide

2. PGCONF.EU 2017
   

   View Slide

3. PGCONF.EU 2017
   

   View Slide

4. DEV
   Working @ OnGres (www.ongres.com)
   Software Developer
   PostgreSQL support
   @dovaleac
   pgdoval
   PABLO GONZALEZ
   

   View Slide

5. PGCONF.EU 2017
   1. generate_series()
   2. WITH RECURSIVE
   3. LATERAL
   4. Window functions
   5. Grouping sets
   6. Moving data atomically
   7. Run WHATEVER you want
   Post-92
   

   View Slide

6. PGCONF.EU 2017
   PGCONF.EU 2017
   [EX1] Premium users
   

   View Slide

7. user position
   3 1
   4 2
   2 3
   1 4
   scoring premium
   user position
   4 1
   3 3
   user position
   4 1
   2 2
   3 3
   1 4
   PGCONF.EU 2017
   [EX1] Premium users
   

   View Slide

8. PGCONF.EU 2017
   PGCONF.EU 2017
   1.
   generate_series()
   

   View Slide

9. generate_series
   1
   2
   3
   4
   5
   6
   7
   8
   PGCONF.EU 2017
   1. generate_series()
   

   View Slide

10. generate_series
    1
    3
    5
    PGCONF.EU 2017
    1. generate_series()
    

    View Slide

11. Generate count of events per
    hour
    id t
    1 12:23
    2 12:55
    3 15:06
    4 22:47
    PGCONF.EU 2017
    1. generate_series()
    

    View Slide

12. PGCONF.EU 2017
    1. generate_series()
    

    View Slide

13. PGCONF.EU 2017
    1. generate_series()
    Does it work?
    

    View Slide

14. Generate event count per hour
    id t
    1 12:23
    2 12:55
    3 15:06
    4 22:47
    PGCONF.EU 2017
    1. generate_series()
    

    View Slide

15. PGCONF.EU 2017
    1. generate_series()
    

    View Slide

16. PGCONF.EU 2017
    PGCONF.EU 2017
    2. WITH RECURSIVE
    

    View Slide

17. PGCONF.EU 2017
    sum
    5050
    2. WITH RECURSIVE
    

    View Slide

18. id name parent_id
    1 good1 -
    2 wrong1 -
    3 good2 1
    4 good3 2
    5 wrong2 1
    6 good4 3
    7 good5 4
    id name parent_id
    1 good1 -
    3 good2 1
    6 good4 3
    name NOT LIKE ‘wrong%’
    PGCONF.EU 2017
    2. WITH RECURSIVE
    

    View Slide

19. PGCONF.EU 2017
    2. WITH RECURSIVE
    

    View Slide

20. PGCONF.EU 2017
    PGCONF.EU 2017
    [EX1] Premium users
    

    View Slide

21. user position
    3 1
    4 2
    2 3
    1 4
    scoring premium
    user position
    4 1
    3 3
    user position
    4 1
    2 2
    3 3
    1 4
    PGCONF.EU 2017
    [EX1] Premium users
    

    View Slide

22. PGCONF.EU 2017
    [EX1] Premium users
    

    View Slide

23. PGCONF.EU 2017
    [EX1] Premium users
    

    View Slide

24. PGCONF.EU 2017
    [EX1] Premium users
    

    View Slide

25. PGCONF.EU 2017
    [EX1] Premium users
    

    View Slide

26. PGCONF.EU 2017
    PGCONF.EU 2017
    [EX2] User sessions
    

    View Slide

27. id user_id time
    1 1 12:40
    2 1 13:05
    3 2 13:40
    4 1 13:45
    5 2 14:02
    6 1 18:23
    events
    user_id events
    1 2
    1 1
    1 1
    2 2
    *Two consecutive events by the same user belong to the same session iff t2-t1<= 30 minutes
    sessions
    PGCONF.EU 2017
    [EX2] User sessions
    

    View Slide

28. PGCONF.EU 2017
    PGCONF.EU 2017
    3. LATERAL
    

    View Slide

29. PGCONF.EU 2017
    3. LATERAL
    

    View Slide

30. PGCONF.EU 2017
    3. LATERAL
    

    View Slide

31. id name
    1 Deportivo
    2 Barcelona
    3 Real Madrid
    4 Atlético Madrid
    id team_id name value country
    1 2 Messi 150 ARG
    2 2 Iniesta 100 ESP
    3 2 Umtiti 70 FRA
    4 3 Modric 100 CRO
    5 3 Cristiano
    Ronaldo
    95 POR
    6 4 Griezmann 120 FRA
    teams players
    PGCONF.EU 2017
    3. LATERAL
    

    View Slide

32. id name value
    1 Deportivo 80
    2 Barcelona 1000
    3 Real Madrid 1000
    4 Atlético Madrid 700
    PGCONF.EU 2017
    3. LATERAL
    

    View Slide

33. team best_player value country
    Deportivo Emre Çolak 15 TUR
    Barcelona Messi 150 ARG
    Real Madrid Modric 100 CRO
    Atlético Griezmann 120 FRA
    PGCONF.EU 2017
    3. LATERAL
    

    View Slide

34. PGCONF.EU 2017
    3. LATERAL
    

    View Slide

35. PGCONF.EU 2017
    

    View Slide

36. PGCONF.EU 2017
    

    View Slide

37. PGCONF.EU 2017
    3. LATERAL
    

    View Slide

38. PGCONF.EU 2017
    

    View Slide

39. PGCONF.EU 2017
    PGCONF.EU 2017
    4. Window functions
    

    View Slide

40. team_id name value
    1 Emre Çolak 15
    2 Messi 150
    3 Modric 100
    4 Griezmann 120
    PGCONF.EU 2017
    4. Window functions
    

    View Slide

41. id team_id name value country
    1 2 Messi 150 ARG
    2 2 Iniesta 100 ESP
    3 2 Umtiti 70 FRA
    4 3 Modric 100 CRO
    5 3 Cristiano Ronaldo 95 POR
    6 4 Griezmann 120 FRA
    PGCONF.EU 2017
    4. Window functions
    

    View Slide

42. position id team_id name value country
    1 1 2 Messi 150 ARG
    2 2 2 Iniesta 100 ESP
    3 3 2 Umtiti 70 FRA
    1 4 3 Modric 100 CRO
    2 5 3 Cristiano Ronaldo 95 POR
    1 6 4 Griezmann 120 FRA
    PGCONF.EU 2017
    4. Window functions
    

    View Slide

43. PGCONF.EU 2017
    4. Window functions
    

    View Slide

44. PGCONF.EU 2017
    4. Window functions
    

    View Slide

45. row_number()
    rank()
    dense_rank()
    percent_rank()
    cume_dist()
    ntile(num_buckets integer)
    lag(value anyelement [, offset
    integer [,default anyelement ]])
    lead(value anyelement [, offset
    integer [,default anyelement ]])
    first_value(value any)
    last_value(value any)
    nth_value(value any, nth
    integer)
    PGCONF.EU 2017
    4. Window functions
    

    View Slide

46. PGCONF.EU 2017
    PGCONF.EU 2017
    [EX2] User sessions
    

    View Slide

47. id user_id time
    1 1 12:40
    2 1 13:05
    3 2 13:40
    4 1 13:45
    5 2 14:02
    6 1 18:23
    events
    user_id events
    1 2
    1 1
    1 1
    2 2
    *Two consecutive events by the same user belong to the same session iff t2-t1<= 30 minutes
    sessions
    PGCONF.EU 2017
    [EX2] User sessions
    

    View Slide

48. PGCONF.EU 2017
    [EX2] User sessions
    

    View Slide

49. PGCONF.EU 2017
    [EX2] User sessions
    

    View Slide

50. PGCONF.EU 2017
    [EX2] User sessions
    

    View Slide

51. PGCONF.EU 2017
    PGCONF.EU 2017
    [EX3] Palindromes
    

    View Slide

52. words
    user_id events
    racard r ACA rd
    racard RACAR d
    ifisi IFI si
    ifisi If ISI
    palindromes
    pneumonoultramicroscopicsilicovolc
    anoconiosis
    pseudopseudohypoparathyroidism
    floccinaucinihilipilification
    antidisestablishmentarianism
    supercalifragilisticexpialidocious
    incomprehensibilities
    honorificabilitudinitatibus
    tattarrattat
    PGCONF.EU 2017
    [EX3] Palindromes
    

    View Slide

53. PGCONF.EU 2017
    PGCONF.EU 2017
    5. Grouping sets
    

    View Slide

54. country team_id avg
    ARG 150
    CRO 100
    ESP 100
    FRA 95
    POR 95
    1 15
    2 106.6667
    3 97.5
    4 120
    105.8333
    PGCONF.EU 2017
    5. Grouping sets
    

    View Slide

55. PGCONF.EU 2017
    5. Grouping sets
    

    View Slide

56. PGCONF.EU 2017
    5. Grouping sets
    

    View Slide

57. groupin
    g
    country team_i
    d
    avg
    1 ARG 150
    1 CRO 100
    1 ESP 100
    1 FRA 95
    1 POR 95
    2 1 15
    2 2 106.6667
    2 3 97.5
    2 4 120
    3 105.8333
    PGCONF.EU 2017
    5. Grouping sets
    

    View Slide

58. PGCONF.EU 2017
    PGCONF.EU 2017
    6. Moving data atomically
    

    View Slide

59. stay id
    t 1
    t 2
    f 3
    f 4
    t1
    id
    5
    t2
    stay id
    t 1
    t 2
    t1
    id
    3
    4
    5
    t2
    PGCONF.EU 2017
    6. Moving data atomically
    

    View Slide

60. PGCONF.EU 2017
    6. Moving data atomically
    

    View Slide

61. PGCONF.EU 2017
    PGCONF.EU 2017
    7. Run WHATEVER you want
    

    View Slide

62. DO allows you to run whatever you want:
    - No parameters
    - No result
    - You can run any PostgreSQL compatible procedure
    - pl/pgsql
    - perl
    - python
    - The procedure is not stored anywhere
    PGCONF.EU 2017
    7. Run WHATEVER you want
    

    View Slide

63. PGCONF.EU 2017
    PGCONF.EU 2017
    [EX3] Palindromes
    

    View Slide

64. words
    user_id events
    racard r ACA rd
    racard RACAR d
    ifisi IFI si
    ifisi If ISI
    https://blog.jooq.org/2017/08/22/finding-all-palindromes-contained-in-st
    rings-with-sql/
    palindromes
    pneumonoultramicroscopicsilicovolc
    anoconiosis
    pseudopseudohypoparathyroidism
    floccinaucinihilipilification
    antidisestablishmentarianism
    supercalifragilisticexpialidocious
    incomprehensibilities
    honorificabilitudinitatibus
    tattarrattat
    PGCONF.EU 2017
    [EX3] Palindromes
    By @lukaseder
    

    View Slide

65. PGCONF.EU 2017
    [EX3] Palindromes
    

    View Slide

66. PGCONF.EU 2017
    [EX3] Palindromes
    

    View Slide

67. PGCONF.EU 2017
    [EX3] Palindromes
    

    View Slide

68. PGCONF.EU 2017
    [EX3] Palindromes
    

    View Slide

69. PGCONF.EU 2017
    PGCONF.EU 2017
    Extra!
    

    View Slide

70. PGCONF.EU 2017
    

    View Slide

71. PGCONF.EU 2017
    Fractals!
    

    View Slide

72. PGCONF.EU 2017
    Questions?
    Thank you
    

    View Slide