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. 

• Understanding SQL Join 

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

• Best practices for writing SQL queries

• SQL Best Practices - How to type code cleanly and perfectly organized

• Top 5 SQL Functions

• Snowflake Analytics – Lag

• Bucketing in SQL - grouping, bucketing, and segmenting users, dates, and events in SQL, from simple to complex cases. 

• A Practical Use of the SQL COALESCE Function 

• Snowflake Analytics – Moving Difference

• Introduction to Temporary Tables in SQL Server

• SQL Workshop: SQL Patterns Every Analyst Should Know with Ergest Xheblati (so you know it is good). 

• SQL Cheat-Sheet for Data Science

• 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

  Chetan Sharma
  shares SQL for calculating metrics, experiment windows, excluding bias, etc. 

• Using SQL to Summarize A/B Experiment Results

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

• SQL DROP TABLE Statement and Various Use Cases 

🚀 Advance and become an expert 

• How to Parse Strings Like a Pro Using SQL SUBSTRING() Function?

• Passing Data table as Parameter to Stored Procedures

• The Best SQL Template for Customer Lifetime Value - you’re welcome.

• SQL for getting cohorted user retention - my guest post for

  Lenny Rachitsky
  .

• JSON IN POSTGRESQL: HOW TO USE IT RIGHT

• Postgres Full-Text Search

• 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 ⭐: 

• Practical SQL for Data Analysis

• How to Get the First or Last Value in a Group Using Group By in SQL

• Simple Anomaly Detection Using Plain SQL

• Stop Using datetime.now!

• 12 Common Mistakes and Missed Optimization Opportunities in SQL

• Fastest Way to Load Data Into PostgreSQL Using Python

Some of my SQL publications (available for paid subscribers): 

• How To Install A Database 

• Joining two tables that are not related by a foreign key relationship

• User Segmentation and Power User Analysis in SQL

• Day Of The Week Analysis in SQL

• Getting Date Functions in SQL

• Advanced SQL Functions For Data Analysis

• How To Measure Your Adjacent Users

• How To Get Randomly Distributed Users in SQL

• When Simple Becomes Tricky: SQL CASE Statement

• How To Create Date Series in SQL

• Ranking The Most Used Features in Your Product Using SQL

• How To Get Paid Subscriptions In SQL

🔨 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

• 50+ SQL Interview Questions and Answers (Updated for 2021)

• SQL Interview Questions 

• Mastering the SQL Technical Interview from

  Madison Mae
  (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!