The Library · Insights
Deep dives across data, automation & AI
108 in-depth articles — from Excel foundations to production machine learning. Filter by topic or difficulty to find exactly what you need.
SQLFoundation
Combining Results with SQL Set Operations: UNION, UNION ALL, INTERSECT, and EXCEPT
SQL set operations let you stack and compare results from multiple queries — without a single JOIN. Learn how UNION, UNION ALL, INTERSECT, and EXCEPT work, when to use each one, and how to avoid the mistakes that trip up beginners.
15 min read
SQLExpert
Mastering SQL EXISTS and NOT EXISTS: Correlated Subquery Patterns for Filtering with Related Data
EXISTS and NOT EXISTS are among the most powerful and misunderstood filtering tools in SQL. Learn how correlated subqueries work under the hood, why NOT EXISTS beats NOT IN every time, and how to compose multi-criteria existence checks that would tie a JOIN in knots.
28 min read
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