Model Online / On-Site
Duration
Modules 5 modules

Course Objective & Scope

When complex analytical questions and machine learning feature tables are written with basic GROUP BY queries, they quickly become unreadable and drive up warehouse latency and cost. This programme advances analytical SQL skills so teams can express sophisticated calculations in a single pass and eliminate query bottlenecks.

Window functions (OVER, PARTITION BY, window frames), recursive CTEs, cohort and retention patterns, querying JSON and array columns, indexing strategies, partitioning and reading EXPLAIN ANALYZE plans are covered hands-on. Participants refactor a slow or convoluted reporting/feature query from their own environment for measurable gains in speed and clarity.

Who Should Attend?

  • Senior data analysts, BI developers and analytics engineers
  • Data scientists and data engineers building feature pipelines
  • Backend developers looking to improve database query performance
  • Prerequisite: hands-on SQL experience with JOINs, GROUP BY and subqueries

Key Takeaways

  • Compute moving averages, running totals and rankings with window functions and custom frames
  • Model hierarchical and sequential data using modular and recursive CTEs
  • Build cohort, churn, funnel and point-in-time feature tables in SQL
  • Read EXPLAIN ANALYZE plans to spot full table scans, suboptimal joins and missing indexes
  • Apply partitioning, clustering and cost-optimization techniques on large tables

Curriculum

Advanced Window Functions and Window Frames
  • ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG and LEAD
  • Moving and cumulative metrics with ROWS vs RANGE frames
Modular SQL, Recursive CTEs and Hierarchical Data
  • Multi-layered CTE design and avoiding repetition
  • Querying trees, org charts and graphs with recursive CTEs
Analytical Patterns and Point-in-Time Feature Engineering
  • Cohort, retention, sessionization and gaps-and-islands problems
  • Historical feature queries that prevent data leakage
Working with Semi-Structured Data (JSON and Arrays)
  • JSON functions and unnesting/flattening nested structures
  • Using semi-structured columns efficiently in analytical tables
Query Plan Analysis and Performance Tuning
  • Reading EXPLAIN ANALYZE: Hash Join, Merge Join, Nested Loop
  • B-Tree/GIN indexes, partitioning, clustering and SQL anti-patterns

Other Courses in This Category

  • AI-Ready Data Engineering

    End-to-end data engineering for RAG, LLM and machine learning systems: data contracts, medallion architecture, feature and embedding pipelines, lineage and metadata management.

    Data Science & AI-Ready Data Engineering Corporate
    Live Course
    5 modules
    View
  • Data Quality Testing and Data Contracts

    Automated data quality testing (dbt, Great Expectations, Soda) and enforceable producer-consumer data contracts to stop silent schema changes and corrupted data from breaking downstream analytics and AI.

    Data Science & AI-Ready Data Engineering Corporate
    Live Course
    5 modules
    View
  • Data Lineage, Catalogs and Data Observability

    Discovering data assets, tracking column-level lineage and catching pipeline delays or distribution drift early using OpenLineage, DataHub/OpenMetadata and data observability practices.

    Data Science & AI-Ready Data Engineering Corporate
    Live Course
    5 modules
    View
  • Unstructured Data Pipelines for Documents, Audio and Images

    Scalable data pipelines that turn PDFs, Office files, scanned documents, call-centre audio and images into parsed, chunked, metadata-enriched and indexed assets for RAG and multimodal AI.

    Data Science & AI-Ready Data Engineering Corporate
    Live Course
    5 modules
    View
  • The Modern Data Stack: dbt, Apache Airflow and Apache Iceberg

    Lakehouse storage on Apache Iceberg, modular tested transformations with dbt and orchestration with Apache Airflow, for engineers building scalable, open-standard data platforms.

    Data Science & AI-Ready Data Engineering Corporate
    Live Course
    5 modules
    View
  • AI-Ready Data Architecture

    Enterprise data architecture supporting analytics, classical ML, RAG and autonomous agents under one roof: designing lakehouse storage, semantic layers, feature stores, vector indexes and governance.

    Data Science & AI-Ready Data Engineering Corporate
    Live Course
    5 modules
    View

Get in touch