The Library · Insights
Deep dives across data, automation & AI
70 in-depth articles — from Excel foundations to production machine learning. Filter by topic or difficulty to find exactly what you need.
SQLPractitioner
Writing SQL Self-Joins: Query the Same Table Twice to Compare Rows and Find Relationships
Self-joins are one of SQL's most versatile — and most misunderstood — patterns. Learn how to query a table against itself to navigate hierarchies, compare rows, detect duplicates, and find sequential relationships, with realistic examples and clear guidance on when to use this technique versus window functions.
21 min read
SQLFoundation
Selecting Distinct Values and Eliminating Duplicates in SQL: DISTINCT, GROUP BY, and COUNT Explained
Duplicate rows in SQL results are one of the most common — and most fixable — problems in data work. This lesson teaches you exactly how DISTINCT, GROUP BY, and COUNT work, when to use each, and how to audit your data for duplicates like a pro.
15 min read
SQLExpert
Writing SQL FROM Scratch: Structuring Multi-Step Analytical Queries with Derived Tables and Inline Views
Most complex analytical questions can't be answered in a single flat query — they require computing something, and then computing something on top of that. This lesson teaches you how to structure multi-step SQL using derived tables and inline views, with realistic examples, optimizer internals, and a complete hands-on exercise.
26 min read
SQLPractitioner
Combining Aggregates with Conditional Logic: GROUP BY, HAVING, and CASE WHEN in Practice
Learn how to write production-quality SQL that combines GROUP BY, HAVING, and CASE WHEN in a single query. This lesson goes beyond the basics to cover conditional counts, rate calculations, performance tier classification, and the subtle bugs that cause silently wrong results.
21 min read
SQLFoundation
Understanding SQL Subqueries: Filtering and Looking Up Data with Nested SELECT Statements
Learn how to write SQL subqueries that dynamically filter rows, look up calculated values, and check for related data — all inside a single query. This hands-on lesson builds from scalar subqueries to correlated subqueries with realistic examples and exercises.
15 min read
SQLExpert
Multi-Table Reporting with JOIN and GROUP BY: Aggregating Across Relationships in a Single Query
Learn how to combine JOIN and GROUP BY to aggregate data across multiple related tables in a single SQL query. This expert-level lesson covers fan-out inflation, LEFT JOIN with aggregation, conditional aggregation, ROLLUP, and performance strategies for production-scale data.
27 min read
SQLPractitioner
Writing SQL CASE Expressions: Conditional Logic Inside SELECT, WHERE, and GROUP BY
CASE expressions let you embed if-then-else logic directly inside SQL queries — not just in SELECT, but in WHERE filters, GROUP BY buckets, and aggregate functions. This lesson teaches every form with production-realistic examples so you can write smarter queries without touching application code.
22 min read
SQLFoundation
Grouping and Summarizing Data: COUNT, SUM, AVG, and GROUP BY for Beginners
Learn how to use SQL's most essential aggregate functions — COUNT, SUM, and AVG — combined with GROUP BY to transform raw data into meaningful summaries. This hands-on lesson walks you from first principles to writing real analytical queries with confidence.
15 min read
SQLExpert
Columnar Storage and Vectorized Execution: Writing SQL That Aligns with How Analytical Databases Actually Process Your Queries
Most SQL practitioners write queries without understanding how columnar engines physically store and process data — and pay for it in two-minute queries that should take eight seconds. This lesson teaches you the internals of columnar storage, vectorized execution, and zone map pruning so you can write SQL that works with these mechanics rather than against them.
29 min read
SQLPractitioner
Geospatial Queries in SQL: Using PostGIS and Spatial Functions to Filter, Measure, and Join Location-Based Data
PostGIS turns PostgreSQL into a full-featured spatial database. Learn how to store, index, filter, measure, and join location data using geometry types, spatial predicates, and real-world radius and polygon queries — with production-grade performance patterns included.
22 min read
SQLFoundation
Subquery Factoring and Inline Views: When to Use Derived Tables, Scalar Subqueries, and EXISTS for Readable and Efficient Filtering
Mastering subquery patterns is the inflection point between writing SQL that works and SQL that scales. This lesson teaches you when to use derived tables, scalar subqueries, and EXISTS filters — with real examples and the performance reasoning behind each choice.
16 min read
SQLExpert
Deterministic and Nondeterministic Functions in SQL: Understanding Volatility Categories and Their Impact on Query Planning, Caching, and Index Usage
Most SQL developers treat functions as black boxes — but the database treats them as contracts. Learn how PostgreSQL's IMMUTABLE, STABLE, and VOLATILE classifications shape query planning, index eligibility, and caching behavior, and how getting this wrong silently destroys performance or corrupts results.
30 min read