Lessons › SQL

137 free lessons

SQL lessons

SQL lessons for data analysts, from your first SELECT to window functions, data cleaning and advanced analytics.

Chapter 1: Reading data

• SELECT all columnsViewing a Full Retail Product Table with SELECT *
• SELECT specific columnsPicking the columns you actually need
• Column aliases with ASRenaming Retail Columns with AS
• DISTINCT for unique valuesFinding the unique values
• ORDER BY with single column and multiple columnsSorting Retail Orders with ORDER BY
• LIMITPreviewing a Few Retail Orders with LIMIT

Chapter 2: Filtering

• FILTERING with WHEREPicking only the rows you want
• Combining WHERE with AND and ORFiltering Retail Orders with WHERE, AND, and OR
• NOT operatorExcluding Retail Orders with NOT
• INFiltering Retail Products with IN
• BETWEENFiltering Retail Orders Within a Range Using BETWEEN
• LIKE and wildcardsSearching Retail Product Names with LIKE and Wildcards
• IS NULL and IS NOT NULLFinding Missing Retail Order Values with IS NULL and IS NOT NULL

Chapter 3: Summarising

• COUNT(*), COUNT(column), COUNT(DISTINCT)Counting Retail Orders, Discounts, and Unique Customers
• SUM()Adding Retail Revenue and Quantity with SUM()
• AVG()Finding Typical Retail Values with AVG()
• MIN() and MAX()Finding Lowest and Highest Retail Values with MIN() and MAX()
• GROUP BYSummarising Retail Orders with GROUP BY
• GROUP BY with multiple columnsBreaking Down Retail Results with GROUP BY on Multiple Columns
• HAVINGFiltering Grouped Retail Results with HAVING

Chapter 4: Shaping data

• ROUND()Rounding Retail Numbers with ROUND()
• CASE WHENCreating Retail Labels with CASE WHEN
• Pivoting data with CASE WHENPivoting Retail Revenue with CASE WHEN
• COALESCE()Replacing Missing Retail Discounts with COALESCE()
• CAST() and type conversionConverting Retail Text Values with CAST()
• EXTRACT DATEExtracting Date Parts from Retail Orders
• DATE_TRUNC() for rounding dates to year, month, week or dayBucketing Retail Orders with DATE_TRUNC()
• Date difference functionsHow many days between two dates

Chapter 5: Combining tables

• INNER JOINMatching Retail Orders to Customers with INNER JOIN
• LEFT JOINKeeping All Retail Orders with LEFT JOIN
• Anti-join with LEFT JOIN and IS NULLFinding rows that don't match
• Multi-table joins across three or more tablesJoining three tables in one query
• SubqueriesAnswering Benchmark Questions with Subqueries
• CTEs with WITHBreaking Retail Queries into Steps with CTEs
• UNION and UNION ALLCombining Retail Sales Rows with UNION and UNION ALL

Chapter 6: Window Functions

• ROW_NUMBER() / RANK() / DENSE_RANK()Ranking Retail Orders with ROW_NUMBER(), RANK(), and DENSE_RANK()
• SUM() OVER (PARTITION BY) - partition totalPartition Total with SUM() OVER (PARTITION BY)
• Running totals with SUM() OVERTracking Retail Running Revenue with SUM() OVER
• LAG() and LEAD()Comparing Retail Orders with LAG() and LEAD()
• Top N analysisFinding Top Retail Products with Ranking Functions
• Comparison vs previous periodComparing Retail Revenue to the Previous Period

Deep Dive: Data Cleaning

• TRIM() / UPPER() / LOWER() for text cleanupStandardizing Retail Text with TRIM(), UPPER(), and LOWER()
• REPLACE() for fixing inconsistent valuesFixing Retail Text Values with REPLACE()
• IS NULL and COALESCE() for missing dataFinding and Filling Missing Retail Values
• Counting NULLs per column with SUM(CASE WHEN ... IS NULL)Counting Missing Values Across Retail Order Columns
• Data profiling with MIN, MAX, COUNT DISTINCT, and AVGProfiling Retail Product Data Before Cleaning
• Detecting duplicates with GROUP BY and HAVING COUNT(*) > 1Detecting Duplicate Retail Orders
• Removing duplicates with ROW_NUMBER() and DELETERemoving Duplicate Retail Orders with ROW_NUMBER()
• Standardizing categories with CASE WHEN and LOWER()Standardizing Retail Categories with CASE WHEN and LOWER()
• Cleaning date formats with TO_DATE() and TO_CHAR()Cleaning Retail Date Formats with TO_DATE() and TO_CHAR()
• NULLIF() for converting placeholder values to NULLTurning Retail Placeholder Values into NULL with NULLIF()
• Range checks and flagging values outside expected boundsFlagging Retail Values Outside Expected Ranges
• Before-and-after validation comparing row counts and sums after cleaningValidating Retail Cleaning Results Before and After

Deep Dive: Database Admin & ETL

• CREATE TABLECreating a Retail Product Table with CREATE TABLE
• CREATE TABLE AS SELECTCreating a Safe Working Copy with CREATE TABLE AS SELECT
• INSERT INTOAdding New Retail Products with INSERT INTO
• Multi-row INSERTInserting many rows in one statement
• UPDATE SETUpdating Existing Retail Product Values with UPDATE SET
• ALTER TABLEChanging a Retail Table Structure with ALTER TABLE
• DROP TABLERemoving an Unneeded Retail Table with DROP TABLE
• TRUNCATE TABLEClearing a Retail Staging Table with TRUNCATE TABLE
• DELETE FROMDeleting Specific Retail Return Rows with DELETE FROM
• ViewsSaving Retail Reporting Logic with SQL Views
• Temporary tablesBreaking Retail Analysis into Steps with Temporary Tables
• Stored procedures vs functionsChoosing Between Stored Procedures and Functions in Retail SQL
• Writing a reusable cleaning function with CREATE FUNCTIONWriting a cleaning rule once, as a function
• Scheduling automated cleaning with pg_cronWriting the clean-up job a schedule runs
• Trigger-based validation that rejects bad insertsStopping bad rows at the door with a trigger
• Documenting cleaning rules with COMMENT ON TABLE and COMMENT ON COLUMNLeaving notes on tables and columns
• Building a cleaning log table to track changesKeeping a record of every fix
• UPDATE SET for correcting values in placeCorrecting Retail Product Values with UPDATE SET
• SCD Type 1: overwriting a changeWhen the old value doesn't matter: overwrite it
• SCD Type 2: keeping the historyWhen the old value matters: keep it
• ETL concepts in SQLExtract, transform, load: a reporting table in three steps
• Star schema basics: fact tables and dimension tablesUnderstanding Retail Star Schemas with Fact and Dimension Tables

Deep Dive: Text, Date & Number Functions

• OFFSET with LIMIT for paginationSkipping rows with OFFSET
• CURRENT_DATE for today's dateStamping rows with today's date
• CEIL() and FLOOR() for rounding up and downRounding up and down to whole numbers
• ABS()Measuring Retail Return Differences with ABS()
• NULLIF()Turning Placeholder Values into NULL with NULLIF()
• CONCAT() or || concatenationCombining Retail Text Fields with CONCAT() or ||
• ILIKE and case-insensitive searchFinding Retail Text Matches with ILIKE
• INITCAP() or equivalentCleaning Retail Name Casing with INITCAP()
• AGE() for date intervalsHow long ago something happened
• SUBSTRING() / LEFT() / RIGHT()Extracting Retail Codes with SUBSTRING(), LEFT(), and RIGHT()
• LENGTH() and CHAR_LENGTH() for text lengthMeasuring how long text is
• POSITION() and STRPOS() for substring indexFinding where a character sits inside text
• LPAD() and RPAD() for padding textPadding text to a fixed width
• Combining columnsCombining Retail Customer Columns
• MOD operator (%) for modulo arithmeticSplitting Retail Orders into Groups with MOD
• Date conversion basicsTurning timestamps into dates
• Null handling in calculationsHandling NULLs in Retail Calculations

Deep Dive: Advanced Joins & Subqueries

• FULL OUTER JOINAuditing Catalogue and Inventory with FULL OUTER JOIN
• RIGHT JOINKeeping All Customers with RIGHT JOIN
• CROSS JOINCreating Product-Store Planning Matrices with CROSS JOIN
• Self join basicsComparing Products with a Self Join
• Multi-column join conditionsJoining on two columns at once
• Aggregate-then-join with subquery in FROMTotal first, then join
• LEFT JOIN with WHERE vs ON filter trapThe LEFT JOIN trap with WHERE on the right side
• One-to-many vs many-to-one relationshipsUnderstanding One-to-Many Retail Relationships
• EXISTS and NOT EXISTSChecking Related Retail Records with EXISTS and NOT EXISTS
• Nested subqueriesUsing Nested Subqueries for Retail Benchmarks
• Correlated subqueriesComparing Each Retail Order with a Correlated Subquery
• INTERSECT and EXCEPTComparing Retail Customer Sets with INTERSECT and EXCEPT
• Recursive CTEsWalking Retail Product Hierarchies with Recursive CTEs
• LATERAL joins for per-row correlated subqueriesPer-row correlated subqueries with LATERAL
• RANDOM() and ORDER BY RANDOM() for random samplingTaking Retail QA Samples with ORDER BY RANDOM()

Deep Dive: Advanced Window Functions

• NTILE() for ranking into equal bucketsSplitting rows into equal-sized buckets
• AVG() / COUNT() / MIN() / MAX() OVERAdding Category Context with Aggregate Window Functions
• Partitions in window functionsResetting Retail Window Calculations with PARTITION BY
• Moving averages with AVG() OVERSmoothing Retail Sales Trends with Moving Averages
• FIRST_VALUE() / LAST_VALUE() / NTH_VALUE()Navigating Customer Order Values with FIRST_VALUE(), LAST_VALUE(), and NTH_VALUE()
• PERCENTILE_CONT() and PERCENTILE_DISC()Finding Typical Retail Order Value with Percentiles
• PERCENT_RANK() and CUME_DIST()Segmenting Retail Products with PERCENT_RANK() and CUME_DIST()
• GROUPING SETSCreating Multiple Retail Subtotals with GROUPING SETS
• ROLLUP for hierarchical totalsBuilding Retail Hierarchical Totals with ROLLUP
• CUBE for cross-tabulationCreating Retail Cross-Tab Totals with CUBE
• FILTER clause for conditional aggregationConditional aggregation with FILTER
• Window FRAME specification with ROWS BETWEENControlling the window with ROWS BETWEEN
• STRING_AGG()Combining Retail Details with STRING_AGG()

Deep Dive: Business Analytics Patterns

• GENERATE_SERIES() for date ranges and gap fillingFilling Retail Date Gaps with GENERATE_SERIES()
• Reconciliation queriesDo two systems agree?
• Correlation with CORR()Do two numbers move together?
• Trend lines with REGR_SLOPE() and REGR_INTERCEPT()How much do sales change per dollar?
• Gaps and islands patternFinding consecutive runs (gaps and islands)
• Sessionization with LAG and cumulative SUMGrouping visits into sessions
• Cohort retention analysisCohort retention
• Funnel conversion analysisFunnel conversion
• Pareto 80-20 cumulative analysisPareto analysis - finding the 80%
• Market basket analysis with self-joinMarket basket - products bought together

Deep Dive: Data Validation

• DISTINCT ON for keeping one row per duplicate groupKeeping One Retail Order per Customer with DISTINCT ON
• Detecting negative values where only positives are expectedDetecting Impossible Negative Retail Values
• Regular expressions for email and phone validationValidating Retail Customer Contact Patterns with Regex
• Cross-field validationChecking Retail Fields Against Each Other
• Unit and format inconsistenciesOne unit, one format
• Referential integrity checks across tablesChecking Broken Retail Relationships Across Tables
• Unexpected category checksChecking Retail Products for Unexpected Categories