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
2. PGCONF.EU 2017
3. PGCONF.EU 2017
4. DEV Working @ OnGres (www.ongres.com) Software Developer PostgreSQL support @dovaleac pgdoval PABLO GONZALEZ
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
6. PGCONF.EU 2017 PGCONF.EU 2017 [EX1] Premium users
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
8. PGCONF.EU 2017 PGCONF.EU 2017 1. generate_series()
9. generate_series 1 2 3 4 5 6 7 8 PGCONF.EU 2017 1. generate_series()
10. generate_series 1 3 5 PGCONF.EU 2017 1. generate_series()
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()
12. PGCONF.EU 2017 1. generate_series()
13. PGCONF.EU 2017 1. generate_series() Does it work?
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()
15. PGCONF.EU 2017 1. generate_series()
16. PGCONF.EU 2017 PGCONF.EU 2017 2. WITH RECURSIVE
17. PGCONF.EU 2017 sum 5050 2. WITH RECURSIVE
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
19. PGCONF.EU 2017 2. WITH RECURSIVE
20. PGCONF.EU 2017 PGCONF.EU 2017 [EX1] Premium users
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
22. PGCONF.EU 2017 [EX1] Premium users
23. PGCONF.EU 2017 [EX1] Premium users
24. PGCONF.EU 2017 [EX1] Premium users
25. PGCONF.EU 2017 [EX1] Premium users
26. PGCONF.EU 2017 PGCONF.EU 2017 [EX2] User sessions
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
28. PGCONF.EU 2017 PGCONF.EU 2017 3. LATERAL
29. PGCONF.EU 2017 3. LATERAL
30. PGCONF.EU 2017 3. LATERAL
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
32. id name value 1 Deportivo 80 2 Barcelona 1000 3 Real Madrid 1000 4 Atlético Madrid 700 PGCONF.EU 2017 3. LATERAL
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
34. PGCONF.EU 2017 3. LATERAL
35. PGCONF.EU 2017
36. PGCONF.EU 2017
37. PGCONF.EU 2017 3. LATERAL
38. PGCONF.EU 2017
39. PGCONF.EU 2017 PGCONF.EU 2017 4. Window functions
40. team_id name value 1 Emre Çolak 15 2 Messi 150 3 Modric 100 4 Griezmann 120 PGCONF.EU 2017 4. Window functions
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
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
43. PGCONF.EU 2017 4. Window functions
44. PGCONF.EU 2017 4. Window functions
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
46. PGCONF.EU 2017 PGCONF.EU 2017 [EX2] User sessions
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
48. PGCONF.EU 2017 [EX2] User sessions
49. PGCONF.EU 2017 [EX2] User sessions
50. PGCONF.EU 2017 [EX2] User sessions
51. PGCONF.EU 2017 PGCONF.EU 2017 [EX3] Palindromes
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
53. PGCONF.EU 2017 PGCONF.EU 2017 5. Grouping sets
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
55. PGCONF.EU 2017 5. Grouping sets
56. PGCONF.EU 2017 5. Grouping sets
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
58. PGCONF.EU 2017 PGCONF.EU 2017 6. Moving data atomically
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
60. PGCONF.EU 2017 6. Moving data atomically
61. PGCONF.EU 2017 PGCONF.EU 2017 7. Run WHATEVER you want
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
63. PGCONF.EU 2017 PGCONF.EU 2017 [EX3] Palindromes
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
65. PGCONF.EU 2017 [EX3] Palindromes
66. PGCONF.EU 2017 [EX3] Palindromes
67. PGCONF.EU 2017 [EX3] Palindromes
68. PGCONF.EU 2017 [EX3] Palindromes
69. PGCONF.EU 2017 PGCONF.EU 2017 Extra!
70. PGCONF.EU 2017
71. PGCONF.EU 2017 Fractals!
72. PGCONF.EU 2017 Questions? Thank you