This article explains ten advanced SQL concepts including CTEs, recursive queries, temporary functions, pivoting with CASE WHEN, EXCEPT vs NOT IN, self-joins, ranking functions, delta calculations with LEAD/LAG, running totals, and datetime manipulation, with code examples for data science interview preparation.
The article argues that most enterprise AI agents don't need to traverse graph databases; instead, they need curated context (definitions, join keys, governance rules) to write SQL directly. It distinguishes Lookup questions (known paths) from Path questions (where the path is the answer), showing that Lookup dominates and graph traversal adds latency and cost without benefit.
This article showcases a collection of impressive SQL queries—including a PostgreSQL Tetris implemented with a recursive CTE, window‑function session analysis, a ClickHouse real‑time funnel, dynamic WHERE clause generation, and a recursive employee hierarchy—while discussing performance tips and engine choices.
This article presents ten advanced SQL concepts—including CTEs, recursive CTEs, temporary functions, CASE‑WHEN pivots, EXCEPT vs NOT IN, self‑joins, ranking window functions, delta calculations, cumulative sums, and date‑time manipulation—each explained with concrete examples and code snippets.
This article showcases a spectacular PostgreSQL query that implements the classic Tetris game using recursive CTEs, provides the full source code, highlights a known long‑run disk‑usage issue, and then presents several other impressive SQL snippets and a concise checklist for writing high‑quality SQL.
This article explains the meaning and optimization strategies for various MySQL EXPLAIN EXTRA hints—including Impossible WHERE/HAVING, No matching rows, Recursive queries, Rematerialize, and canned N databases—provides example execution plans, code snippets, and performance comparisons to help developers write more efficient SQL statements.
This article presents ten essential advanced MySQL concepts—including CTEs, recursive CTEs, temporary functions, CASE‑WHEN pivots, EXCEPT vs NOT IN, self‑joins, ranking window functions, delta calculations, running totals, and date‑time manipulation—each explained with clear examples and practical SQL snippets.
This article presents ten essential advanced SQL techniques—including CTEs, recursive CTEs, temporary functions, CASE‑WHEN pivots, EXCEPT vs NOT IN, self‑joins, ranking windows, delta calculations, cumulative totals, and date‑time manipulation—each explained with clear examples and code snippets for interview preparation.
This article explains how MySQL 8.0's WITH clause can be combined with INSERT, UPDATE, DELETE, recursive common table expressions, multiple CTEs and derived tables to generate data, create date sequences, and simplify everyday SQL programming with practical code examples.
This article explains how to extract the top N records for each group in PostgreSQL, compares the slow window‑function approach with a fast recursive‑CTE and set‑returning function solution, and shows performance results dropping from over 20 seconds to under half a second.
