SQL — From Intermediate to Superhero

Eyal Trabelsi
Dec 12, 2019 · 3 min read

Practical SQL For Advanced Analytics

Source: https://www.maximizemarketresearch.com/advance-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.
Source: https://en.wikipedia.org/wiki/The_Starry_Night

Hierarchical Traversal Patterns

source: https://www.flickr.com/photos/torley/2361164281

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.
Source: gigabitmagazine.com/ai/report-predicts-ai-spending-reach-almost-98bn-2023

Last Words

This article is somewhat of advance SQL pattern couldn’t be possible without:

I hope you found it interesting and useful. I am open to any kind of constructive feedback.

Towards AI

Towards AI, is the world’s fastest-growing AI community for learning, programming, building and implementing AI.

Thanks to Zack Shapiro

Eyal Trabelsi

Written by

Towards AI

Towards AI, is the world’s fastest-growing AI community for learning, programming, building and implementing AI.

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade