Common SQL Window Functions: Positional Functions

Window Functions, analytics, SQL for advanced, aggregate functions, SQL clauses, positional functions

Positional SQL window functions deal with data’s location in the set. In this post, we explain LEAD, LAG, and other positional functions. SQL window functions allow us to aggregate data while still using individual row values. We’ve already dealt with ranking functions and the use of partitions. In this post, we’ll examine positional window functions, which are extremely helpful in reporting and summarizing data. Specifically, we’ll look at LAG, LEAD, FIRST_VALUE and LAST_VALUE. It is worthwhile mentioning that LEAD mirrors

Continue Reading

Common SQL Window Functions: Using Partitions with Ranking Functions

You’ve started your mastery of SQL window functions by learning RANK, NTILE, and other basic functions. In this article, we will explain how to use partitions with ranking functions. Mastering SQL window (or analytical) functions is a bumpy road, but it helps to break the journey into logical stages that build on each other. In the previous Common SQL Functions article, you learned about the various ranking functions, which are the most basic form of window functions. In this article, we

Continue Reading

Common SQL Window Functions: Ranking Functions

Want to learn how to use SQL window functions?  Ranking functions are a good place to start! Learning about SQL window functions usually comes after you’ve built a foundation in the language, but these powerful functions take your skills up a level. As you master them, you’ll find better ways to solve query problems. When used for business intelligence applications, SQL queries combine data retrieval and advanced computations. These operations are more complex than those used in OLTP systems. In

Continue Reading

Useful SQL Patterns: Date Generator

As you start coding in SQL, you will use some statements and techniques over and over again. We call these “SQL patterns”. This series will look at the most common SQL patterns and consider how to use them. SQL patterns, such as the pivot pattern we discussed last week, can save you a lot of time and effort. Suppose you are asked to get a range of days in a financial quarter, but the only records you have are for

Continue Reading

Useful SQL Patterns: Pivoting

As you start coding in SQL, you will use some statements and techniques over and over again. We call these “SQL patterns”. This series will look at the most common SQL patterns and consider how to use them. Pivoting in SQL refers to taking the data in table rows and making that data into columns. This is very important in reporting, and it’s easy to do when you use the CASE statement. In fact, you will see that pivoting data

Continue Reading

Useful SQL Patterns: Conditional Summarization with CASE

As you start coding in SQL, you will use some statements and techniques over and over again. We call these “SQL patterns”. This series will look at the most common SQL patterns and consider how to use them. Previously, we looked at the SQL pattern of matching NULLs. This is important when you are comparing columns containing NULL values. Today, we’re going to consider another SQL practice: conditional summarization with CASE operator. What is Conditional Summarization? When you are using

Continue Reading

GET ACCESS TO EXPERT SQL CONTENT!