SQL — From Intermediate to Superhero

Author(s): Eyal Trabelsi

Originally published on Towards AI.

Programming

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.

General Patterns

• 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.

Hierarchical Traversal Patterns

• Recursion: There is a great video explaining it here.
• Trees Search Patterns: SQL Design Patterns has a dedicated chapter that can be found here.
• Graph search patterns: SQL Design Patterns has a dedicated chapter that 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).

• NLP: Some basic NLP can be found 1 and 2.
• Regression: Example of linear regression example can be found 1 and 2.
• Time decay: a great example can be found here.
• Others: periscope data seemed to implement some machine learning methods using SQL, for example, Nearest neighbors and K-mean clustering.

Last Words

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.

Join thousands of data leaders on the AI newsletter. Join over 80,000 subscribers and keep up to date with the latest developments in AI. From research to projects and ideas. If you are building an AI startup, an AI-related product, or a service, we invite you to consider becoming a sponsor.

Published via Towards AI