Data Science Fundamentals SQL III Conditional Logic, Subqueries, CTEs and Window Functions

About

An introduction to SQL, from foundational concepts to slightly more advanced techniques, using Google BigQuery as the practical platform.

This module covers conditional logic, subqueries, Common Table Expressions (CTEs), and window functions.

Learning Objectives

You will be able to:

Write SQL queries that contain conditional logic by using CASE.
Construct subqueries for multi-step operations.
Practice common table expressions (CTEs) with SELECT statements to simplify complex queries.
Build Window Frames in SQL using OVER and PARTITION BY to group subsets of data.

Content

Topic	About
Advanced SQL (SQL-III)	• Conditional Logic • Subqueries • CTEs • Window Functions

Each section includes exercises and stretch challenges for practice, using the NCAA Basketball dataset in BigQuery.

Prerequisites

Familiarity with data types (e.g., strings, integers, floats, booleans)
Able to use Google BigQuery to access data via SQL.
Write and execute a SQL query, including SELECT, FROM, LIMIT, and DISTINCT.
Familiar with ORDER BY, aliasing tables, and introductory aggregate functions (MIN, MAX, SUM, AVG, and COUNT).
Understand the order of operations in SQL.
Familiar with grouping, filtering, nulls and joing within SQL.

References

📖 Reference Materials

Important note

Please ignore the _layouts folder and the config.yml file in the root of this repo. They are needed to make sure the repo renders well; please don’t change them or delete them and your repo will work fine :)