Discover more from Data Analysis Journal
A Selection Of SQL Tutorials - Issue 141
A roundup of my favorite free SQL tutorials and articles to either get started with or to keep your querying skills strong.
Hello, and welcome to my Data Analytics Journal, where I write about product analytics and data science.
This month paid subscribers learned about:
The Ultimate Guide On Churn Reporting (And Its Technicalities) - Everything about churn in SaaS: how to calculate churn rate, the best way to report it, how it is connected with retention, and how not to get lost in SaaS. It’s aimed at senior and higher-level analysts, as I had to go deep into the details of the complex churn reporting and subscription lifecycle.
Do You Over-Report DAU? - Yes, if you use sessions to report user activity. Learn why you might be likely to over-report DAU and MAU for mobile apps, common pitfalls with user activity events, and recommendations on how to set up proper analytics for DAU reporting.
How To Pass A SQL Interview For A Data Scientist Position - an example of a technical coding interview question for a data science position at Meta with a few solutions for solving it. I’ll walk through the steps on how to best approach the problem and solve it in SQL.
I officially declare April a SQL month.
As data becomes more accessible, data quality and cleanliness have developed into a separate discipline. To increase trust in data, we have to structure it - put constraints and relationships between tables. RDBs have naturally become more popular and efficient, which means that SQL continues to be a must-have skill within the analytical domain. You can’t progress without SQL today, regardless of which specialization in the data field you chose.
Many years ago, I started making a list of my favorite go-to videos, articles, and lessons which I still use and keep updating to this day. Below I want to share my refreshed list of mostly free SQL tutorials and practice sites.
I had to learn SQL at school, but many successful analysts I know are self-taught SQL coders. Personally, I wouldn’t recommend enrolling in bootcamps or expensive programs to learn SQL. Go through the publications and videos below, and I guarantee you will be comfortable within a few months. It’s all about practice.
🛴 Getting started
A beginner's guide to SQL - a little old, but still one of the best introductions I’ve seen.
SQL LEFT JOIN – A Beginner’s Guide - a really good read from one of the engineering champions Vlad Mihalcea. I was such a fan of his writing. Unfortunately, over the years he journeyed from data to the darkness of the Java world. People don’t come back from there.
Using ON Versus WHERE Clauses to Combine and Filter Data in PostgreSQL Joins - this is one of the common interview questions for analysts.
Learn Database Design with SQL Server - walks you through how to install a database, its terminology, fundamental concepts, usage, etc. Their walkthrough can be applied to other SQL variations besides SQL Server. If you are a MySQL user, here is a similar one - a MySQL tutorial.
🚲 Become proficient
Understanding The Self Join - this is one of a few good articles out there on self-join.
SQL Best Practices - How to type code cleanly and perfectly organized
Bucketing in SQL - grouping, bucketing, and segmenting users, dates, and events in SQL, from simple to complex cases.
SQL Workshop: SQL Patterns Every Analyst Should Know with Ergest Xheblati (so you know it is good).
Experimentation Platform in a Day - the first part of the article walks through the experimentation service setup, but the second has good handy SQL you can borrow to read the test.
How To Develop a Highly Trusted Experiment Analysis Workflow - where
shares SQL for calculating metrics, experiment windows, excluding bias, etc.Reasons why SELECT * is bad for SQL performance - this article is more targeted toward DE, but the second part explains some good query plan optimization methods. Analysts must understand optimizations, especially working at smaller companies.
🚀 Advance and become an expert
How to Parse Strings Like a Pro Using SQL SUBSTRING() Function?
The Best SQL Template for Customer Lifetime Value - you’re welcome.
SQL for getting cohorted user retention - my guest post for
.Advanced SQL - Common Table Expressions - amazing piece on CTEs
Hierarchical Structures in PostgreSQL - aimed for data engineering. You will need this if you are the only data person on a small team doing all foundational things at once.
And some of my favorite publications from one and only Haki Benita ⭐:
How to Get the First or Last Value in a Group Using Group By in SQL
12 Common Mistakes and Missed Optimization Opportunities in SQL
Some of my SQL publications (available for paid subscribers):
🔨 Practicing SQL
The best way to practice SQL is in your local database. For this, you can install the PostgreSQL database and use the DataGrip client with it. This gives you the flexibility to design the schema the way you want and practice SQL from anywhere at any time.
If you want to go a simpler route, use SQLFiddle or DB-Feddle tools. You can either paste your SQL there and polish the syntax or design your own table schema.
You can also try Simplql - an in-browser tool that lets you query data files with SQL without using a database. It does everything in the browser. Users can export the results from their queries in CSV or JSON. Easy, fast, and intuitive.
🎓 SQL Tutorials
If you are new to SQL and are looking for a quick tutorial on basics, check out SQL Zoo.
The SQL Tutorial for Data Analysis - this is from Mode. The descriptions are spot on and the structure is good. They also have another - The Intermediate SQL Tutorial, which is more focused on functions and joins.
SQLCourse2.com - an online interactive SQL tutorial, from basics to advanced topics. Very similar to W3 school SQL. Both are good if you are new to SQL.
TutorialRepublic - SQL Tutorial - I like this tutorial more, as it contains more specific examples, making it easier to understand.
Leetcode - SQL - a set of SQL problems and challenges by topic, from simple to difficult. I bet we all spent a good deal of hours there. A good source to test yourself.
WindowFunctions and PG Casts - more on an advanced side for SQL users. Postgres specific.
⁉️ Interview preparation
Mastering the SQL Technical Interview from
(yes, we both coincidentally decided to cover SQL at the same time).
I continually update this list to keep it fresh and relevant, so please send me your favorite SQL links and sources that you think I should add!
Thanks for reading, everyone. Until next Wednesday!
Subscribe to Data Analysis Journal
Where product, data science, and analytics intersect. Trusted by tens of thousands of data scientists around the world
Thanks for the shoutout Olga! This is an extensive list of resources- definitely bookmarking this for my own use! :)