Practical SQL For Advanced Analytics
SQL is a programming language used for querying and managing data in databases and has become the backbone of modern analytics.
“SQL is the worst form of database querying,
except for all the other forms.” — Lukas Eder
There are great resources some advanced topics in SQL like performance optimization.
Yet, there is no one go-to place for advance analytics using SQL. In this article, I collected resources (listed in the bottom) about advance patterns for analytics using SQL.
It's important to note that, many databases lack some of the capabilities and thus you will gain the most by understanding the way of thoughts.
- Counting Patterns: SQL Design Patterns has dedicated 2 chapters that can be found 1 and 2.
- Window Functions Patterns: presentation regarding advance usages of window functions can be found here.
- Writing sane SQL: presentation regarding SQL oddities, best practices, and execution order can be found here.
- Working With Strings: presentation regarding patterns on how to work correctly with strings can be found here.
Data Science Patterns
It's important to note that there are better ways to do some of these (using python for example).
This article is somewhat of advance SQL pattern couldn’t be possible without:
- SQL Design Patterns: Expert Guide to SQL Programming (an awesome book!) the author uploaded some chapters to his blog.
- Silota blog has a compendium of SQL tricks.
- periscope-data blog simply a great blog.
I hope you found it interesting and useful. I am open to any kind of constructive feedback.