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 GROUP BY with Multiple JOINs: Aggregating Across Three or More Related Tables
Most real business questions can't be answered from a single table — they require joining three or more tables together and then summarizing the result. This lesson teaches you how to plan, write, and debug multi-table aggregation queries that actually hold up under scrutiny.
17 min read
SQLExpert
Translating Outer Query Results into Inner Query Filters: Writing Correlated Subqueries That Reference Parent Scope
Correlated subqueries are the key to answering row-level questions that depend on the surrounding data context — category averages, prior-period comparisons, existence checks. This deep-dive lesson teaches you the execution model, aliasing rules, and performance trade-offs through real analytical scenarios.
27 min read
SQLPractitioner
Writing SQL to Identify Top-N Records Per Group: Filtering with Subqueries and Row Ranking Without Window Functions
Identifying the top-N records within each group is one of the most common — and most misunderstood — SQL challenges. This lesson teaches two production-ready techniques using correlated subqueries and self-joins, so you can solve this problem confidently on any database without window functions.
23 min read
SQLFoundation
Writing SQL Aggregate Expressions Without GROUP BY: Using COUNT, SUM, and AVG Across an Entire Result Set
Learn how to use COUNT, SUM, and AVG to summarize an entire dataset in a single SQL query — no GROUP BY required. This lesson covers NULL behavior, combining multiple aggregates, and practical business reporting patterns from first principles.
16 min read
SQLExpert
Querying Hierarchical Data Without Recursion: Using Self-Joins and Subqueries to Traverse Parent-Child Relationships
Most tutorials jump straight to recursive CTEs for hierarchical data — but expert SQL practitioners know there's a richer toolkit. This lesson teaches you to traverse org charts, category trees, and bill-of-materials structures using self-joins, correlated subqueries, and alternative storage models, with full indexing and performance guidance.
26 min read
SQLPractitioner
Calculating Running Totals and Cumulative Sums with GROUP BY and Subqueries
Running totals are one of the most requested patterns in analytical SQL — and GROUP BY alone can't produce them. Learn two reliable techniques using correlated subqueries and self-joins, with real-world examples covering revenue, user growth, and multi-category breakdowns.
20 min read
SQLFoundation
Filtering Joined Results: Applying WHERE and HAVING Conditions Across Multiple Tables
Most SQL writers know WHERE filters rows and HAVING filters groups — but combining both across multiple joined tables is where things get tricky. This lesson builds your mental model from the ground up so you always know exactly where each condition belongs.
18 min read
SQLExpert
Nested Subqueries as JOIN Replacements: When to Swap EXISTS, IN, and Derived Tables for Cleaner, Faster Filtering
Most developers reach for JOINs out of habit, even when EXISTS, IN, or derived tables would produce cleaner results and better performance. This lesson teaches you exactly when and why to swap each approach — with the internals, edge cases, and real query examples to back it up.
28 min read
SQLPractitioner
Aggregating Data from Multiple Subqueries: Combining Scalar and Derived Table Patterns in a Single SELECT
Learn how to build powerful single-query dashboards by combining scalar subqueries and derived tables in one SELECT statement. This lesson teaches you when to use each pattern, how to assemble them together, and the common mistakes that produce silently wrong results.
22 min read
SQLFoundation
Counting and Grouping with DISTINCT vs GROUP BY: When to Use Each and Why It Matters
`DISTINCT` and `GROUP BY` both eliminate duplicates, but they're built for completely different jobs — and mixing them up leads to subtly wrong answers. This lesson teaches you exactly when to use each, including the powerful `COUNT(DISTINCT ...)` pattern that every analyst needs to master.
15 min read
SQLExpert
Writing Reusable SQL Column Logic: Applying Expressions, Calculations, and CASE Statements Inside SELECT Without Repeating Yourself
Learn how to write SQL SELECT expressions — arithmetic, string transformations, date calculations, and CASE statements — with a discipline that keeps your logic defined once and reused everywhere. This lesson covers the architecture of CTE-based expression layering, conditional aggregation, and the performance traps that turn elegant queries into slow disasters.
26 min read
SQLPractitioner
Using SQL to Compare Current and Prior Period Results: Date Filtering, Self-Joins, and Conditional Aggregation in Practice
Period-over-period comparison is one of the most requested analytical tasks in SQL — and one of the trickiest to get right. This lesson teaches three production-ready techniques: self-joins with CTEs, conditional aggregation with CASE WHEN, and subquery-based lookups, with complete copy-paste-ready code and a hands-on exercise.
21 min read