EssentialSQL: SQL Window Functions for Business Analytics

Focused View

Kris Wenzel

3:02:35

133 View

1. Introduction

1. Welcome to the Course.mp4

02:22

2. Why You Should Learn Windows Functions.mp4

03:33

3. What Flavor of SQL Should I Choose.mp4

05:03

4. How to Take the Course.mp4

04:11

5.1 Azure Data Studio.html

5.2 MySQL Downloads.html

5.3 PostgreSQL Download.html

5.4 PostgreSQL Extension for Azure Data Studio.html

5.5 Sample Database Scripts.html

5.6 SQL Server Downloads.html

5.7 SQLite.html

5. Tools Used and Database Setup.mp4

06:38

2. Warmup

1.1 01 What is a Window Function.pdf

1.2 01 what is a window function.zip

1. What is a Window Function.mp4

06:29

2.1 02 Group By versus Window Function.pdf

2.2 02 group by versus window function.zip

2. GROUP BY versus Window Function Results.mp4

12:09

3. Getting to Know Window Functions.html

4.1 03 running total.zip

4. Create Running Totals using SQL Joins versus Window Functions.mp4

12:49

5. Write Your First Windows Function.html

3. Windows and Partitions

1.1 01 Introduction to Windows and Partitions.pdf

1. Introduction.mp4

01:02

2.1 02 Window Function Order of Operations.pdf

2.2 02 window function order of operations.zip

2. Window Function Order of Operations.mp4

07:10

3.1 03 Using Over to Create a Window.pdf

3.2 03 using over to create a window.zip

3. Using OVER to create a window.mp4

09:33

4.1 04 Using PARTITION BY to group rows.pdf

4.2 04 using partition by to group rows.zip

4. Using PARTITION BY to group rows.mp4

16:27

5. Introduction to Partition By.html

6. Using Multiple Window Functions.html

7. Replacing Subquery with Window Function.html

8.1 05 overcome limitations.zip

8.2 05 Overcome Window Function Limitations.pdf

8. Overcome Window Function Limitations.mp4

10:29

9. Overcome Window Function Limitations.html

10. Case Study in using Window Functions to Summarize Data.html

4. Defining Window Function Frames with ROW and RANGE

1.1 01 Introduction to Ordering Windows and Defining Boundaries.pdf

1. Introduction to Window Frames.mp4

01:31

2.1 02 over and order by.zip

2.2 02 Using OVER with ORDER BY.pdf

2. Using Over and Order by with Window Functions.mp4

08:20

3. Coding Exercise Over and Order By #1.html

4. Coding Exercise Over and Order By #2.html

5. Coding Exercise Over and Order By #3.html

6.1 03 Defining Window Frames Rows and Ranges Introduction.pdf

6. Introduction to Defining Window Frames using ROWS and RANGES.mp4

03:39

7.1 04 Using ROWS to define the Frame.pdf

7. Using ROWS to Define the Window Frame..mp4

05:52

8.1 04 using rows to define the frame.zip

8. Demonstration on Using ROWS to Define the Window Frame.mp4

07:39

9. Using ROWS to Define the Window Frame #1.html

10. Using ROWS to Define the Window Frame #2.html

11. Using ROWS to Define the Window Frame #3.html

12.1 05 Using RANGE to define the Frame.pdf

12. Using RANGE to define the Window Frame.mp4

03:06

13.1 05 using range to define the frame.zip

13. Demonstration on Using RANGE to Define a Windows Frame.mp4

08:09

14. Using Range to Define the Frame #2.html

15. Using Range to Define the Frame #1.html

16. Section Review (Evolution 1 of 3).html

17. Section Review (Evolution 2 of 3).html

18. Section Review (Evolution 3 of 3).html

5. Aggregate Functions

1.1 01 Introduction to Aggregrate Functions.pdf

1. Introduction to Aggregate Functions with OVER.mp4

01:40

2.1 02 Aggregrate Functions - Common.pdf

2. Commonly Used Aggregate Functions.mp4

02:02

3.1 03 Aggregrate Functions - Uncommom.pdf

3. Uncommonly Used Aggregate Functions.mp4

04:01

4.1 04 coding exercise - aggregate functions.zip

4. Coding Exercise - Aggregate Functions and OVER().html

6. Ranking Functions

1.1 01 Introduction to Ranking Functions.pdf

1. Introduction and Review of Functions.mp4

00:46

2.1 02 Comparison of Ranking Functions.pdf

2.2 02 comparison of ranking functions.zip

2. Comparison of Ranking Functions.mp4

11:52

3.1 03 Named Windows.pdf

3.2 03 named windows.zip

3. Using the WINDOWS clause..mp4

03:17

4. Ranking Weekly Radio Sales.html

5. Ranking All Product Sales.html

6. Ranking the top 10% Product Sales.html

7. Analytic Functions

1.1 01 Introuction to Analytic Functions.pdf

1. Introduction to Analytic Functions.mp4

00:49

2.1 02 Comparison of LEAD^J LAG^J FIRST ROW^J LAST ROW.pdf

2. Comparison of LEAD, LAG, FIRST ROW and LAST ROW functions.mp4

04:42

3.1 02 demo lead and lag.zip

3. LEAD and LAG Demo.mp4

05:44

4.1 02 demo first value last value.zip

4. FIRST VALUE and LAST VALUE Demo.mp4

05:02

5. Lead and Lag #1.html

6. Lead and Lag #2.html

7. Lead and Lag #3.html

8.1 03 Comparison PERCENTILE RANK and CUME DIST.pdf

8.2 03 demo percent rank cume dist.zip

8. Calculating Percentiles using Window Functions.mp4

06:29

9. Working with Percent Ranks.html

10. Working with Cumulative Distributions.html

8. Window Function Recipies

1. How Do I Calculate a Running Total Using SQL.html

2. How Do I Calculate a Moving Average using SQL.html

3. How Do I Find Duplicates Using Windows Functions.html

Description

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.

Who is this for?

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!

More details

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 than
Being 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.
Who this course is for:
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!

User Reviews

Rating

average 0

Total votes0

Focused display

If you're interested in learning SQL, you're in the right place.I'm here to help you build a strong foundation in all aspects of SQL: from the very first steps to complex data architectures and designs. Many people struggle with where to begin learning SQL, but you don’t have to!! Essential SQL is my platform for bringing all aspects of SQL to you.With more than 30 years of experience in SQL and a degree in Computer Engineering from the University of Michigan, I am passionate about helping others learn this valuable skill. As a blogger, I share SQL tips and how-to articles with over a million readers each year on my blog, Essential SQL. In addition, I have been teaching online for over eight years to people from various industries, including accounting, purchasing, and human resources.In my professional life, I work with large corporations to design data warehouses that support business intelligence and data science activities. I believe that SQL is a valuable skill that is within reach for anyone, and my courses are designed to help you learn and grow with the technology.So, what will you get out of my courses?You will learn SQL from the ground up and progress to advanced concepts. Whether you are a beginner with no prior experience in SQL or looking to expand your knowledge and skills, these courses are tailored to meet your needs.One of the key features of my courses is the hands-on experience and exercises that I provide. Instead of just talking about concepts and theories, I believe that the best way to learn is by doing. That's why my courses are packed with practical exercises that allow you to apply what they have learned and see the results for yourself.As you work through my courses, I will be with you every step of the way to provide guidance and support. I will walk you through the very basics of SQL, such as selecting data from tables and filtering results, and then progress to more advanced topics like creating and modifying tables, working with data types, and using built-in functions. By the end of the course, you will have a solid understanding of SQL and be able to apply what you have learned to real-world situations.In short, my Essential SQL courses offer a comprehensive and practical learning experience that will take you from the very basics to advanced concepts in SQL. Whether you are looking to improve your career prospects, solve problems more efficiently, or just want to learn more about this powerful language, my courses have you covered.What are Students Saying?For the lessons I have covered so far, the instructor is doing a great job in explaining key concepts of stored procedure with several examples of SQL statements to refine business logic. Pretty cool -- Billandre O.Teaching style is really practical. It takes a depth of topical understanding to reduce the topic to an accessible form. He’s got those skillz – Mathew CI like the way Kris teaches delicate subjects in simple English. This course helps students and beginners to better understand Database Design and Data Modeling. I highly recommend it to start your journey in the relational database world. – Radouane BSo far this course teach me in a very short of time how to get confidence with Stored Procedures. I need to learn the subject quick for a job interview . But it's not only basic staff. The lecture gave important example and teach us to use it in the most advantage way for SQL. – Yael M.Excellent course with clear explanations. The instructor had an easy and likeable approach that comprehensively answered how to wield the power of the subquery. Highly recommended. – Andre MSo what are you waiting for? Enroll in one of my courses today.See you in class.Kris

Udemy

View courses Udemy

Students take courses primarily to improve job-related skills.Some courses generate credit toward technical certification. Udemy has made a special effort to attract corporate trainers seeking to create coursework for employees of their company.