SQL Formula Library

JOIN Three Tables

Most real queries need data from 3+ tables. Here's the clean pattern for multi-table JOINs that actually performs well.

SQL Window Functions

Window functions let you calculate across rows without grouping. Rankings, running totals, and row comparisons — all in one query.

GROUP BY With HAVING

WHERE filters rows before grouping. HAVING filters groups after aggregation. Know when to use each.

CTE (Common Table Expression)

CTEs (WITH clause) break complex queries into named steps. Replace nested subqueries with clean, readable, maintainable SQL.

CASE WHEN Statement

CASE WHEN is SQL's IF-THEN-ELSE. Categorize data, create conditional aggregations, and build pivot-like outputs.

COALESCE Function

COALESCE returns the first non-NULL value from a list. Essential for default values, NULL-safe calculations, and clean query output.

SQL Subqueries

Subqueries let you use the result of one query inside another. Essential for filtering, comparisons, and derived tables.

Using CTEs for Clarity

CTEs allow you to break down complex SQL queries into named steps, enhancing readability and maintainability.

Using Subqueries in WHERE Clause

Discover how to efficiently filter your customer data by leveraging subqueries in the WHERE clause to identify high-value orders.

Categorize Revenue Tiers

Using SQL's CASE WHEN statement allows you to easily categorize revenue into defined tiers, enhancing data analysis and reporting.

Group Sales by Region

Grouping sales data by region allows for better analysis, and using HAVING helps filter results to meet specific criteria.

Row Number by Group

Using ROW_NUMBER() with PARTITION BY allows for effective ranking within groups, such as customers by date.

Rank Employees by Salary

Discover how to rank employees within their departments based on salary, while properly managing ties using RANK and DENSE_RANK functions.

Calculate Row Differences

The SQL LAG function allows you to access data from the previous row, making it easy to calculate differences in a time series.

Aggregate Sales by Month

Aggregating sales data by month can provide valuable insights. Use DATE_TRUNC to simplify timestamp data for monthly analysis.

Concatenate Column Values

String aggregation in SQL allows you to combine multiple row values into a single string. This guide will show you how to group and concatenate effectively.

Pivot Rows to Columns

Transform your SQL data by pivoting rows into columns using SUM and CASE WHEN, simplifying your data analysis.

Self Join on Employees

Using a self JOIN allows you to compare rows within the same table, making it easy to find employees in the same department.

Using EXISTS in SQL

Using EXISTS can enhance performance when querying for customers with at least one order. This method is often more efficient than using IN.

Recursive CTE for Hierarchies

Recursive Common Table Expressions (CTEs) allow you to efficiently traverse hierarchical data structures like organizational charts to retrieve all descendants of a specified node.

Extract JSON Field

Extracting specific fields from JSON columns can streamline data retrieval in SQL databases like PostgreSQL and MySQL. This guide will help you master the extraction process.

SQL Upsert Operations

Upsert operations allow you to insert a new row or update an existing one seamlessly, ensuring data integrity and efficiency.

Understanding SQL Joins

SQL joins allow you to combine rows from two or more tables based on a related column. Understanding each type of join helps in retrieving the right data.

Using LIKE and ILIKE in SQL

Discover how to effectively filter your SQL queries for partial text matches with LIKE and ILIKE for case-insensitivity.

Count Unique Values in SQL

Counting unique values in a column is essential for data analysis. Use COUNT(DISTINCT column_name) to easily achieve this.

Handling NULL Values in SQL

NULL values can complicate SQL queries, but using IS NULL, IS NOT NULL, COALESCE, and NULLIF can help manage them effectively.

Update Rows with JOIN

Updating specific rows in a table can be tricky, especially when you need to reference another table. This guide shows you how to do it effectively.

Insert Select SQL Statement

Inserting data from one table to another can streamline data management and enhance efficiency. This guide explains how to use the INSERT INTO ... SELECT statement.

Calculate Running Total

Running totals help analyze cumulative data over time, and SQL provides a powerful way to compute them using window functions.

Percentage of Total Calculation

Understanding how to express each row's value as a percentage of the total can simplify data analysis. This guide shows you how to achieve this using window functions.

7-Day Rolling Average

Rolling averages help smooth out data trends over time. This guide shows you how to compute a 7-day moving average using SQL.

Deduplicate Rows in SQL

Duplicate rows can clutter your data and lead to inaccurate analysis. This guide shows you how to effectively remove them using SQL.

Conditional Aggregation in SQL

Conditional aggregation allows you to compute sums based on specific criteria, enabling detailed insights from your data in a single query.

SQL String Functions Overview

Text manipulation is crucial for data accuracy; this guide covers essential SQL functions to clean and format your strings effectively.

Working with Dates in SQL

Understanding date functions in SQL is crucial for effective data analysis, allowing you to extract, calculate, and manipulate date values seamlessly.