SQL · Intermediate · 5 h

CTEs, dates, strings and analysis patterns

Readable CTE pipelines, date and time functions, cohorts, string cleaning, pivots and funnels: the queries analysts write every week.

Why employers care: Real analysis queries are 50 to 200 lines. Teams hire people whose SQL can be reviewed and reused.

Lessons, in order

SQL CTE (WITH clause): write readable queriesBreak long SQL into named steps with WITH, debug each step on its own, know when a CTE is inlined or materialised, and write queries a reviewer can follow.12 min
SQL date functions: DATE_TRUNC, EXTRACT and intervalsGroup by week and month, measure durations, handle ISO weeks, Indian fiscal years, time zones and ambiguous date strings, and fill missing days with a calendar.16 min
SQL cohort analysis and customer retentionBuild a monthly cohort retention table in SQL on real orders: cohorts, month numbers, retention with FIRST_VALUE, the cohort triangle, revenue retention, and the biases that fool most first attempts.21 min
SQL string functions: TRIM, SUBSTRING, REPLACE and regexClean messy text in SQL on real data: TRIM and hidden spaces, SUBSTRING and POSITION, REPLACE and regex, SPLIT_PART, CONCAT with NULLs and STRING_AGG, with the dialect differences that trip people up.20 min
SQL pivot: rows to columns and unpivotPivot rows to columns in SQL with conditional aggregation, the PIVOT keyword, dynamic pivots and crosstab, then unpivot back and prove nothing was lost, on real sales and payments data.18 min
SQL funnel analysis: conversion rates step by stepBuild funnels in SQL the careful way on real orders: strict vs any-order steps, time windows, step and overall conversion, segments, sessions with LAG, and the dirty events that inflate funnels.17 min