Exploring SQL without Window Functions (Part II) - Examples

Today we will explore different techniques of how we might approach common SQL challenges, but without Window Functions! We’d have to get creative.

SQL DDL

You can use the following DDL statement to create the table used in the below examples. To play and experiment with queries for free and without downloading anything, visit db-fiddle.

-- Create a new table named 'sales'
CREATE TABLE WIND_DATA (
    sale_id INT PRIMARY KEY,
    sale_amount DECIMAL(5, 2)
);

-- Insert some data into the 'sales' table
INSERT INTO WIND_DATA (sale_id, sale_amount)
VALUES 
    (1, 200.00),
    (2, 500.00),
    (3, 200.00),
    (4, 800.00),
    (5, 500.00),
    (6, 300.00),
    (7, 900.00),
    (8, 300.00),
    (9, 900.00),
    (10, 800.00);        

Row Numbering:

In the absence of ROW_NUMBER(), we could use a self-join on the table. The joined table would include rows with a lower or equal id, and then we’d count the number of id’s to simulate the row number.

/* With WINDOW Function */
SELECT 
    sale_id
    , sale_amount
    , ROW_NUMBER() OVER(ORDER BY sale_id) AS row_num
FROM WIND_DATA;

/* Without WINDOW Function */
SELECT 
    s1.sale_id
    , s1.sale_amount
    , COUNT(s1.sale_id) AS row_num
FROM WIND_DATA AS s1
INNER JOIN WIND_DATA AS s2 
   ON s1.sale_id >= s2.sale_id
GROUP BY s1.sale_id, s1.sale_amount
ORDER BY s1.sale_id;        

Lead/Lag Analysis:

Without LAG() or LEAD(), we would again have to rely on self-joins. For example, to get the previous value (LAG), we could join the table on itself where the main table’s row id is 1 less than the joined table’s row id.

/* With WINDOW Function */
SELECT 
    sale_id
    , sale_amount
    , LAG(sale_amount) OVER(ORDER BY sale_id) AS prev_sale_amount
FROM WIND_DATA
ORDER BY sale_id;

/* Without WINDOW Function */
SELECT 
    s1.sale_id
    , s1.sale_amount
    , s2.sale_amount AS prev_sale_amount
FROM WIND_DATA AS s1
LEFT JOIN WIND_DATA AS s2 
    ON s1.sale_id = s2.sale_id + 1
ORDER BY s1.sale_id;        

Running Total

Calculating a running total without a window function would be cumbersome, requiring a self-join to all previous rows and then summing up the column value.

/* With WINDOW Function */
SELECT 
    sale_id
    , sale_amount
    , SUM(sale_amount) OVER(ORDER BY sale_id) AS running_total
FROM WIND_DATA;

/* Without WINDOW Function */
SELECT 
    s1.sale_id
    , s1.sale_amount
    , SUM(s2.sale_amount) AS running_total
FROM WIND_DATA AS s1
JOIN WIND_DATA AS s2 
    ON s1.sale_id >= s2.sale_id
GROUP BY s1.sale_id, s1.sale_amount
ORDER BY s1.sale_id;        

Ranking — RANK()

/* With WINDOW Function */
SELECT 
    sale_id
    , sale_amount
    , RANK() OVER(ORDER BY sale_amount DESC) AS sale_rank
FROM WIND_DATA;

/* Without WINDOW Function */
SELECT 
    s1.sale_id
    , s1.sale_amount
    , 1 + COUNT(s2.sale_amount) AS sale_rank
FROM WIND_DATA AS s1
LEFT JOIN WIND_DATA AS s2 
    ON s1.sale_amount < s2.sale_amount
GROUP BY s1.sale_id, s1.sale_amount
ORDER BY sale_rank;        

Ranking — DENSE_RANK()

/* With WINDOW Function */
SELECT 
    sale_id
    , sale_amount
    , DENSE_RANK() OVER(ORDER BY sale_amount DESC) AS sale_rank
FROM WIND_DATA;

/* Without WINDOW Function */
SELECT 
    s1.sale_id
    , s1.sale_amount
    , 1+ COUNT(DISTINCT s2.sale_amount) AS sale_rank
FROM WIND_DATA AS s1
LEFT JOIN WIND_DATA AS s2 
    ON s1.sale_amount < s2.sale_amount
GROUP BY s1.sale_id, s1.sale_amount
ORDER BY sale_rank;        

And what if we want to use ROW_NUMBER(), RANK() and DENSE_RANK() in one query? The answer is - Correlated Subquery.

/* With WINDOW Function */
SELECT 
    sale_id
    , sale_amount
    , ROW_NUMBER() 
         OVER(ORDER BY sale_amount DESC, sale_id DESC) AS sale_row_number
    , RANK() 
         OVER(ORDER BY sale_amount DESC) AS sale_rank
    , DENSE_RANK() 
         OVER(ORDER BY sale_amount DESC) AS sale_dense_rank
FROM WIND_DATA
ORDER BY sale_row_number;


/* Without WINDOW Function */
SELECT 
    s1.sale_id
    , s1.sale_amount
    , (SELECT COUNT(*) FROM WIND_DATA s2 
     WHERE s1.sale_amount < s2.sale_amount
           OR s1.sale_amount = s2.sale_amount
           AND s1.sale_id <= s2.sale_id) AS sale_row_number
    , (SELECT 1 + COUNT(sale_amount) FROM WIND_DATA s2 
     WHERE s1.sale_amount < s2.sale_amount) AS sale_rank
    , (SELECT 1 + COUNT(DISTINCT sale_amount) FROM WIND_DATA s2 
     WHERE s1.sale_amount < s2.sale_amount) AS sale_rank
FROM WIND_DATA AS s1
ORDER BY sale_row_number;        

A world without SQL window functions would indeed be a daunting one. We would be left with complex subqueries and self-joins, leading to less readable and less performant queries. It’s clear that window functions are a crucial part of SQL, allowing us to perform complex analyses efficiently. So, let’s take a moment to appreciate the power of window functions and the sophistication they bring to our SQL queries!

?? Feel free to ask any questions and remember to ???????? ?????????????????? ??????????????! ?? Engage, Share, & Follow | Lasha Dolenjashvili