Essentialsql: Sql Window Functions For Business Analytics

SQL Window Functions for SQL Server, MySQL, SQLite & PostgreSQL, for Data Analysis, Business Analytics and Intelligence

What you'll learn

Learn how to incorporate window functions into your SQL queries.

Understand how partitions and windows work together within a query.

Apply window functions so you can avoid using cursors in your SQL.

Understand the difference between window function mechanics and GROUB BY summaries.

Create complex queries involving joins using window functions.

Apply aggregate, analytic, and ranking functions to perform common business tasks such as calculating running totals or comparing one row’s value to the next.

Create queries using either MySQL, PostgreSQL, or SQL Server.

Requirements

You should already be familiar with SELECT, GROUP BY, and ORDER BY clauses

Knowledge of joins and subqueries isn't required, but recommended.

If you can write a simple GROUP BY statement with HAVING, then you are ready for this course.

No special equipment needed, all exercises are done online.

Optional: Install a MySQL, PostgreSQL, or SQL Server learning lab on your own computer.

Description

In this Course you will Learn to:Write simpler queries using window functions.Know how window functions work with partition and other clauses to help you form running totals or compare values from one row to the next.Create complex queries involving joins using window functions.Learn how to use LEAD and LAG to avoid using cursors in your SQL scripts.Write window functions using MySQL, PostgreSQL, SQLite, or SQL Server – your choice!  I'm sure they will work with Oracle, I just haven't tested…  :)Understand how to set up a window frame and their affect on the window function.Know the difference between window functions and classing GROUP BY results.Work with multiple window functions within a query.Who to use results from a window functions within the same query.Learn to us the three classes of functions: Aggregate, Ranking, and Analytic functions.Nothing is worse thanBeing excited to learn something new but not knowing where to start.Wasting time learning the wrong features.You being overwhelmed with options and not knowing which to use.Imagine Having…Knowledge - Knowing how to quickly use window functions to take your SQL to the next level.Confidence - Feeling good that you’re on the right track.Accomplishment - Having a sense of accomplishment that you've learned something most cannot.By the time you’ve completed this course you’ll have an appreciation of window functions, overcome your fear, and able to use them to simplify some of your very complex queries.

Overview

Section 1: Introduction

Lecture 1 Welcome to the Course

Lecture 2 Why You Should Learn Windows Functions

Lecture 3 What Flavor of SQL Should I Choose?

Lecture 4 How to Take the Course

Lecture 5 Tools Used and Database Setup

Section 2: Warmup

Lecture 6 What is a Window Function?

Lecture 7 GROUP BY versus Window Function Results

Lecture 8 Create Running Totals using SQL Joins versus Window Functions

Section 3: Windows and Partitions

Lecture 9 Introduction

Lecture 10 Window Function Order of Operations

Lecture 11 Using OVER to create a window

Lecture 12 Using PARTITION BY to group rows

Lecture 13 Overcome Window Function Limitations

Section 4: Defining Window Function Frames with ROW and RANGE

Lecture 14 Introduction to Window Frames

Lecture 15 Using Over and Order by with Window Functions

Lecture 16 Introduction to Defining Window Frames using ROWS and RANGES

Lecture 17 Using ROWS to Define the Window Frame.

Lecture 18 Demonstration on Using ROWS to Define the Window Frame

Lecture 19 Using RANGE to define the Window Frame

Lecture 20 Demonstration on Using RANGE to Define a Windows Frame

Section 5: Aggregate Functions

Lecture 21 Introduction to Aggregate Functions with OVER

Lecture 22 Commonly Used Aggregate Functions

Lecture 23 Uncommonly Used Aggregate Functions

Lecture 24 Coding Exercise - Aggregate Functions and OVER()

Section 6: Ranking Functions

Lecture 25 Introduction and Review of Functions

Lecture 26 Comparison of Ranking Functions

Lecture 27 Using the WINDOWS clause.

Section 7: Analytic Functions

Lecture 28 Introduction to Analytic Functions

Lecture 29 Comparison of LEAD, LAG, FIRST_ROW and LAST_ROW functions

Lecture 30 LEAD and LAG Demo

Lecture 31 FIRST_VALUE and LAST_VALUE Demo

Lecture 32 Calculating Percentiles using Window Functions

Section 8: Window Function Recipies

Lecture 33 How Do I Calculate a Running Total Using SQL?

Lecture 34 How Do I Calculate a Moving Average using SQL?

Lecture 35 How Do I Find Duplicates Using Windows Functions

Advanced Beginner to Intermediate SQL query authors curious about window functions.,If you're having a hard time understanding the ins and outs of window functions, then this course is for you as I explain it to you in simple to understand English.,Business analysts looking more ways to write queries.,Data Engineers needing more tools to deal with dirty data, such as an easy means to deduplicate incoming rows.,Recent college graduates looking to hone their SQL skills.,Job applicants looking for that extra edge with their next interview!