Window Functions for Row-Level Analytics

1. What Are Window Functions?

At its core, a window function calculates a value for each row based on a "window" of related rows. Unlike an aggregate function with GROUP BY, which collapses multiple rows into a single output row, a window function returns a value for every single row.

The key to using a window function is the OVER() clause, which defines this window. The OVER() clause has two main components:

• PARTITION BY: This divides the rows into partitions (groups). The window function is applied independently to each partition. It's similar to GROUP BY but doesn't collapse the rows.
• ORDER BY: This orders the rows within each partition. The order is crucial for functions like RANK() or LAG().

Let's start with a video that clearly explains this core concept and contrasts it with standard aggregate functions.

The fundamental syntax you saw is:

SELECT
  column_1,
  column_2,
  FUNCTION_NAME() OVER (PARTITION BY partition_column ORDER BY order_column) AS new_column
FROM
  my_table;

2. Ranking Functions: ROW_NUMBER, RANK, and DENSE_RANK

A common use for window functions is to rank rows within a group. For this, SQL provides three main functions, and the difference lies in how they handle ties (rows with the same value in the ORDER BY clause).

• ROW_NUMBER(): Assigns a unique, sequential integer to each row within the partition. Ties are given different numbers arbitrarily.
• RANK(): Assigns the same rank to rows with the same value (ties). It then skips the next rank(s) before resuming. For example: 1, 2, 2, 4.
• DENSE_RANK(): Also assigns the same rank to ties, but it does not skip the next rank. For example: 1, 2, 2, 3.

Let's continue with the same video to see these functions in action.

As the video demonstrated, you cannot use the result of a window function directly in a WHERE clause like this:

-- This query will fail!
SELECT name, salary, RANK() OVER(...) AS rnk
FROM employees
WHERE rnk <= 3;

This is because the WHERE clause is processed before the window functions are computed. To filter on the result, you must use a subquery or, more readably, a CTE:

-- The correct way using a CTE
WITH EmployeeRanks AS (
  SELECT
    name,
    salary,
    dept,
    RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk
  FROM employees
)
SELECT name, salary, dept, rnk
FROM EmployeeRanks
WHERE rnk <= 3;

This image provides a great visual summary of how the three functions treat ties.

3. Positional Functions: LAG and LEAD

Another powerful type of window function allows you to access data from other rows relative to the current row. LAG and LEAD are essential for comparing values across time or sequence.

• LAG(column, offset, default_value): Accesses data from a previous row within the same partition.
• LEAD(column, offset, default_value): Accesses data from a succeeding row within the same partition.

The offset and default_value arguments are optional. The offset defaults to 1 (the immediately preceding/succeeding row), and the default value is NULL.

Let's finish our video to see a practical use case.

For a more detailed look at the practicalities of using LAG and LEAD, the following reading is excellent. It covers common use cases and pitfalls that are often the subject of interview questions.

This image illustrates the concept of LEAD and LAG using stock market data.

4. Hands-On Practice

Now it's time to apply these functions in your Databricks workspace.

Instructions:

1. Navigate to the SQL Editor in your Databricks workspace.
2. Make sure a SQL Warehouse is running and selected.
3. Run the following query to create a temporary view with some sample sales data.

CREATE OR REPLACE TEMP VIEW customer_sales (
    customer_id, sale_id, sale_date, sale_amount
) AS VALUES
    (1, 101, '2023-01-15', 80.00),
    (1, 102, '2023-01-20', 120.00),
    (2, 103, '2023-01-16', 200.00),
    (1, 104, '2023-02-05', 50.00),
    (2, 105, '2023-01-25', 150.00),
    (2, 106, '2023-02-10', 150.00),
    (3, 107, '2023-02-12', 300.00),
    (1, 108, '2023-02-15', 120.00);

-- You can query the view to check its contents
-- SELECT * FROM customer_sales ORDER BY customer_id, sale_date;

Now, write SQL queries to solve the following challenges.

Practice Questions:

1. Sequential Numbering: For each customer, assign a chronological order number to their sales. The first sale for a customer should be 1, the second 2, and so on. Your output should include customer_id, sale_date, sale_amount, and the new sale_order_number.

   • Hint: Use ROW_NUMBER() partitioned by customer_id.

2. Ranking Sales: For each customer, rank their sales from most expensive to least expensive. If two sales have the same amount, they should receive the same rank, and there should be no gaps in the ranking sequence.

   • Hint: Use DENSE_RANK(). Your ORDER BY clause should be DESC.

3. Top 2 Sales per Customer: Using a CTE, find the top 2 most expensive sales for each customer.

   • Hint: Build on your query from question #2 and filter where the rank is less than or equal to 2.

4. Time Between Purchases: For each sale, calculate the number of days since that customer's previous sale. Call the new column days_since_last_sale. For a customer's first sale, the value should be NULL.

   • Hint: Use LAG() on the sale_date column and the DATEDIFF() function: DATEDIFF(current_date, previous_date).

Conclusion

Congratulations on completing this lesson and the "Querying Data with Databricks SQL" module! You have now progressed from writing basic SELECT statements to using sophisticated analytical tools like window functions.

Key Takeaways

• Window functions perform calculations on a set of rows (a "window") but return a value for each row, unlike GROUP BY.
• The OVER() clause is the key, using PARTITION BY to define groups and ORDER BY to sort rows within them.
• ROW_NUMBER, RANK, and DENSE_RANK are used for ranking, each with a different way of handling tied values.
• LAG and LEAD are powerful functions for comparing a row to its neighbors, which is essential for analyzing trends and sequences.
• To filter on the results of a window function, you must use a subquery or a CTE.

Next Steps

You've built a strong foundation in SQL for data analysis and transformation. In the next module, "Managing Tables with Delta Lake," we will shift our focus to the storage layer of the Databricks Lakehouse. You will learn how to use your SQL skills to create and manage robust, reliable, and high-performance tables using the Delta Lake format. This is where you'll see how ACID transactions, time travel, and schema enforcement come to life, forming the bedrock of modern data engineering pipelines.