SQL Analytics

SQL pathway

Programming Foundations / Practice rooms / Track curriculum and enrollment

1. Querying Data

   The analyst's foundation: pull exactly the rows and columns you need. Select and filter, sort and limit, aggregate, and group, all against a live customers/orders/products database.

   • SELECT and WHERE: 5 lessons
   • Sorting and Limiting: 5 lessons
   • Aggregating Data: 5 lessons
   • Grouping Data: 5 lessons
   • First Insights: 5 lessons

2. Joins

   Real questions span tables. Combine customers, orders, and products with INNER and LEFT joins, join three tables at once, and aggregate across the joined result to answer questions no single table can.

   • Inner Joins: 5 lessons
   • Left Joins: 5 lessons
   • Joining Three Tables: 5 lessons
   • Aggregating Across Joins: 5 lessons
   • Capstone: Cross-Table Insights: 5 lessons

3. Subqueries and CTEs

   Answer questions that need a query inside a query: compare rows to an aggregate, filter by another query's results with IN and EXISTS, and structure multi-step analysis cleanly with common table expressions (WITH).

   • Scalar Subqueries: 5 lessons
   • Filtering with IN: 5 lessons
   • EXISTS and Correlated Subqueries: 5 lessons
   • Common Table Expressions: 5 lessons
   • Capstone: Layered Analysis: 5 lessons

4. Window Functions

   Compute across rows without collapsing them. Rank rows, build running totals, compare each row to its group or its neighbors, the tools behind leaderboards, cumulative charts, and period-over-period analysis.

   • Ranking Rows: 5 lessons
   • Window Aggregates: 5 lessons
   • Partitioned Windows: 5 lessons
   • LAG and LEAD: 5 lessons
   • Capstone: Analyst Reports: 5 lessons

5. Data Cleaning

   Real data is messy: missing values, inconsistent casing, stray whitespace, text dates. Use CASE, NULL handling, string functions, and date functions to turn a raw signups table into clean, analyzable data.

   • CASE Expressions: 5 lessons
   • Handling NULLs: 5 lessons
   • String Functions: 5 lessons
   • Date Functions: 5 lessons
   • Capstone: Clean the Dataset: 5 lessons

6. Funnels and Cohorts

   The analyst's signature work: measure how users flow through a funnel (visit, signup, activate, purchase), compute stage-to-stage conversion, and group users into signup cohorts to compare their behavior.

   • Event Data: 5 lessons
   • Funnel Stages: 5 lessons
   • Conversion Rates: 5 lessons
   • Cohorts: 5 lessons
   • Capstone: The Funnel Report: 5 lessons

7. Set Operations

   Combine and compare whole result sets. UNION stacks rows, INTERSECT keeps the common ones, EXCEPT subtracts, and UNION ALL builds multi-section reports with subtotal and total rows.

   • UNION: 5 lessons
   • INTERSECT: 5 lessons
   • EXCEPT: 5 lessons
   • Reports with UNION ALL: 5 lessons
   • Capstone: Segment Analysis: 5 lessons

8. Time-Series Analysis

   Track metrics over time. Build a monthly revenue series, compute running totals and moving averages with window frames, and measure month-over-month growth, the core of every trend dashboard.

   • The Monthly Series: 5 lessons
   • Running Totals and Frames: 5 lessons
   • Moving Averages: 5 lessons
   • Period-over-Period: 5 lessons
   • Capstone: The Monthly Dashboard: 5 lessons

9. Advanced Patterns

   The analyst's bag of tricks: self-joins to compare rows within a table, NTILE and percent ranks for bucketing, first/last-per-group with window ordering, and deduplication of dirty imports.

   • Self-Joins: 5 lessons
   • NTILE and Percentiles: 5 lessons
   • First and Last per Group: 5 lessons
   • Deduplication: 5 lessons
   • Capstone: Advanced Analysis: 5 lessons

10. Capstone Analytics Project

    The finale: a complete analytics project. Profile customers, analyze products, chart revenue trends, segment the base, and assemble an executive summary, applying every technique from the track to one real database.

    • Customer Analysis: 5 lessons
    • Product Analysis: 5 lessons
    • Revenue Trends: 5 lessons
    • Segmentation: 5 lessons
    • Executive Summary: 5 lessons