Blog Posts

Jane Solves Her Business Analysis Problem

Jane has a data problem and an overdue report. Will SQL save the day? Jane works as a business analyst for a large London bank. As she walks to the station on Monday morning, she’s thinking about last week’s unfinished work. She couldn’t get the data she needed from IT, so she could not deliver her report on a financial product’s non-performing assets. She absolutely must submit the report to her manager today; she’s already delayed it for more than

Continue Reading

Common SQL Analytical Functions: Using Partitions with Ranking Functions

You’ve started your mastery of SQL analytical functions by learning RANK, NTILE, and other basic functions. In this article, we will explain how to use partitions with ranking functions. Mastering SQL 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 analytical functions. In this article, we will

Continue Reading

SQL Window Functions by Example

Interested in how SQL window functions work?  We use some simple examples to get you started. SQL window functions are a bit different; they compute their result based on a set of rows rather than on a single row. In fact, the “window” in “window function” refers to that set of rows. Window functions are similar to aggregate functions, but there is one important difference. When we use aggregate functions with the GROUP BY clause, we “lose” the individual rows.

Continue Reading

How Recursive Common Table Expressions Work

Recursive Common Table Expressions are immensely useful when you’re querying hierarchical data. Let’s explore what makes them work. Common Table Expressions (CTEs) are some of the most useful constructions in SQL. Their main purpose is improving query design, which makes queries easier to read. One of the reasons CTEs are so popular is that they let you divide longer queries into shorter subqueries. These are easier to read and edit. (For a detailed introduction to CTEs, read this article.) Another

Continue Reading

Opening the Door to SQL Window Functions

When you want to analyze data in a database, you need SQL window functions. What are they? How do they work? Read on to find out. There are two ways people use databases. One way is for the creation, modification and deletion of data. The other is analyzing the data, which means getting answers to specific questions. The more precise the questions you ask, the more tools you’ll need to answer them efficiently. That’s why SQL incorporated window functions –

Continue Reading

GET ACCESS TO EXPERT SQL CONTENT!