From your first SELECT to FAANG-level analytical SQL
Build your SQL foundations
Filter, sort, and limit rows from a single table using WHERE, IN, BETWEEN, LIKE, ORDER BY, and LIMIT.
Summarize data with aggregate functions and group results using GROUP BY and HAVING.
Combine rows from two tables with INNER JOIN and LEFT JOIN, and apply WHERE and GROUP BY on joined data.
Handle missing data with IS NULL and COALESCE, and add conditional logic with CASE WHEN expressions.
Compose complex queries
Join three or more tables, use self-joins, and apply CROSS JOIN for combinations.
Break complex queries into named steps with CTEs and use scalar, row, and correlated subqueries.
Assign rankings within groups using ROW_NUMBER, RANK, and DENSE_RANK to solve Top-N per group problems.
Compare rows across time with LAG and LEAD to compute year-over-year and month-over-month growth.
Find missing or unmatched records with anti-join patterns and combine result sets with UNION and INTERSECT.
Manipulate dates with DATE_TRUNC, EXTRACT, and DATEDIFF, and transform strings with TRIM and SUBSTRING.
Create pivot-style summaries using SUM(CASE WHEN ...) and DuckDB's FILTER clause for conditional counts and sums.
Master analytical patterns
Compute cumulative sums and sliding-window averages using window frames (ROWS BETWEEN).
Detect consecutive sequences and gaps in data using the ROW_NUMBER subtraction technique.
Assign users to cohorts, track activity over time periods, and compute retention percentages.
Model multi-step conversion funnels with chained CTEs to compute step-by-step conversion rates and drop-off.
Calculate monthly recurring revenue, churn rate, lifetime value, and net revenue retention from subscription data.
Reshape data between wide and long formats using manual CASE WHEN pivots and DuckDB's native PIVOT/UNPIVOT.
Leverage DuckDB-specific syntax like QUALIFY, GROUP BY ALL, EXCLUDE, FILTER, and ASOF JOIN for cleaner queries.
Tackle complex real-world analytics: A/B testing, engagement scoring, and multi-dimensional aggregation with GROUPING SETS.