Skip to main content

Explore Problems

Certifications Quiz Pricing

⌘K

Advanced Queries - SQL

Course Contents

Intro - What is Data Aggregation

Aggregate Functions

Window Functions vs Group By

Syntax of Window Functions

Window Partition By

Window Order By

Window Function Rules

Window Basic Summary

Introduction to Window Aggregate Functions

Window MIN & MAX

Use Case: Rolling & Running Total

Use Case: Moving Average

Window Aggregate Functions Summary

Introduction to Ranking Functions

Window ROW_NUMBER

Window DENSE_RANK

ROW_NUMBER vs RANK vs DENSE_RANK

Use Cases: Top/Bottom N Analysis

Use Cases: Assign Unique IDs

Use Cases: Identify Duplicates

Window CUME_DIST

Window PERCENT_RANK

Window Ranking Function Summary

Introduction to Window Value Functions

Window MIN & MAX

Use Case: Month-Over-Month

Use Case: Customer Retention

Use Case: Time Gap Analysis

Window FIRST & LAST

Window Value Functions Summary

Challenges in Complex Projects

What is Subquery?

Subquery Categories

Subquery Result Types

Subquery in FROM

How DB Executes Subquery

Subquery in SELECT

Subquery in JOIN

Subquery in WHERE

Subquery using IN Operator

Subquery using ALL & ANY Operators

Correlated Subquery

Subquery using Exists

Subquery Summary

Introduction to CTE

How DB Executes CTE

Multiple Standalone CTE

CTE Best Practices

Database Structure & Views

3 Levels Architecture

Views vs Tables

Use Case: Central Logic

CREATE, ALTER, DROP Views

How DB Executes View

Use Case: Hide Complexity

Use Case: Data Security

Use Case: Flexibility

Use Case: Multi-Language

Use Case: Data Warehouse

Window Aggregate Functions Summary

Review the five SQL window aggregate functions — OVER clause options, a window frame reference, quick references for COUNT, SUM, AVG, MIN and MAX, common patterns, and GROUP BY versus window functions.

Loading...

Loading content...