ROW REFERENCE FUNCTIONS

To the surprise of many learning SQL, the methods for referencing a specific attribute value within a specific row are not as straightforward as those afforded by the popular spreadsheet software, Microsoft Excel. Using Excel, a user can simply click on an intersection created by a row and column (i.e., a cell) or manually type in the desired cell’s reference value (e.g., C10 for column C, row 10) to make use of the value. While not as simple, SQL users can do the same using SELF JOINS or window/analytic functions. The window functions, LAG and LEAD, were introduced specifically to allow users to achieve the same outcome produced by the somewhat tricky SELF JOIN operation. LAG allows users to access rows prior to the position of the current row while LEAD allows users to access rows beyond the position of the current row. The functions allow a user to specify the offset or number of rows to look back (LAG) or forward (LEAD) relative to the current row along with a default value to be captured in those rows where a row does not exist prior (LAG) to or after (LEAD) the current row. The functions operate on sorted rows with the sorting specified by the user in the OVER clause. If you are unfamiliar with the general components of window functions, I strongly recommend first reading my page that provides a high-level overview here. Let us walk through two real-world examples highlighting the usefulness of these intuitive functions.

The data used for these two examples can be found in the table below. This fictional data set can be generated using the code found in the Data Set Generation Code section. To review the code, click on the icon to the left of the section’s header. To hide the code, click the icon again. To follow along and practice using this data set, you can copy and paste this code into livesql.oracle.com or your preferred query editor. Keep in mind, if you are not using an Oracle product, then slight changes to the code may be required for it to run. The data set contains daily sales for software, hardware, and office supplies product categories within the year 2023. There is one row per product category and day value combination.

EXEC DBMS_RANDOM.SEED(42);
 
CREATE TABLE north_america_sale AS
    WITH north_america_sale (report_date, product_category, total_sale) AS (
        SELECT
            daily_sale.report_date,
            data_template.product_category,
            daily_sale.total_sale
        FROM (
            SELECT
               'Software' AS category_1,
               'Hardware' AS category_2,
               'Office Supplies' AS category_3
            FROM
                dual
        )
        UNPIVOT (
            product_category FOR category_column IN (
                "CATEGORY_1", "CATEGORY_2", "CATEGORY_3"
            )
        ) data_template
        CROSS JOIN (
        SELECT
            TO_DATE('2023-01-01', 'YYYY-MM-DD') AS report_date,
            FLOOR(DBMS_RANDOM.VALUE(100000, 500000)) AS total_sale
        FROM
            dual
        ) daily_sale
        UNION ALL
        SELECT
            north_america_sale.report_date + INTERVAL '1' DAY,
            north_america_sale.product_category,
            FLOOR(DBMS_RANDOM.VALUE(100000, 500000))
        FROM
            north_america_sale
        WHERE
            north_america_sale.report_date < TO_DATE('2023-12-31', 'YYYY-MM-DD')
    )
    SELECT
        north_america_sale.report_date,
        north_america_sale.product_category,
        north_america_sale.total_sale
    FROM
        north_america_sale;

North American Sales
Suppose you are a reporting analyst for a large company focused on the sale of various software, hardware, and office supply products. Today, you were assigned a ticket in your Service Now queue requesting two reports to be developed intended to answer the following questions:

Report 1
– How much sales were generated within each month?
– For each month, how much more or less sales were generated in the prior month?
– For each month, what was the percent change, rounded to two decimal places, in sales relative to the previous month?

Report 2
– For each product category, how much sales were generated within each month?
– For each product category, how much more or less sales were generated in the prior month?
– For each product category, what was the percent change, rounded to two decimal places, in sales relative to the previous month?

Generating Report 1
Based on the questions listed for report 1, you will need to develop a query that will return a result set containing the total sales by month. To do so, the SUM function can be used in the SELECT clause along with an attribute derived based on the truncation of each report date value. Using the TRUNC function with the report date attribute and MONTH as arguments results in each original report date’s day value being the first of the corresponding month. A GROUP BY clause consisting of the same derived attribute results in the total sales by month. The current query can be seen below along with the corresponding result set. While not entirely necessarily, I have sorted the result set by reporting month values for ease of use.

SELECT
    TRUNC(north_america_sale.report_date, 'MONTH') AS reporting_month,
    SUM(north_america_sale.total_sale) AS total_sale
FROM
    north_america_sale
GROUP BY
    TRUNC(north_america_sale.report_date, 'MONTH')
ORDER BY
    reporting_month;

The result set above answers the first question, but does not contain the necessary data to easily answer the remaining two questions. Ideally, each row in the result set above should contain the corresponding previous month’s sales, the difference between the current month and previous month, and the associated month-over-month percent change. This will minimize the amount of mental or cognitive effort required of your report’s consumers. To compute and return the previous month’s sales relative to each reporting month value, the LAG function can be used. Because the goal is to capture the previous month’s sales within each row, the expression specified or passed into the function should be the sum of sales and the offset should be 1. When computing the previous month’s sales for the first month in the ordered data, the default value of NULL will be returned. See the augmented query and corresponding result set below.

SELECT
    TRUNC(north_america_sale.report_date, 'MONTH') AS reporting_month,
    SUM(north_america_sale.total_sale) AS total_sale,
    LAG(SUM(north_america_sale.total_sale), 1, NULL) OVER (
    	ORDER BY TRUNC(north_america_sale.report_date, 'MONTH')
    ) AS previous_month_total_sale
FROM
    north_america_sale
GROUP BY
	TRUNC(north_america_sale.report_date, 'MONTH')
ORDER BY
    reporting_month;

In the result set above, each month’s total sale value along with the previous month’s total sale value is captured. For example, the total sales occurring in February 2023 along with January 2023 is returned within a single row. As expected, this is the case for every month aside for January 2023 where only the total sale value is available due to December 2022 not being available in the fictional data set.

Given the result set, updating the query to also include an attribute capturing the difference between the current month’s sales and the previous month’s is straightforward. This can be achieved by simply using the subtract operator or minus sign. See the updated query and corresponding result set below.

SELECT
    TRUNC(north_america_sale.report_date, 'MONTH') AS reporting_month,
    SUM(north_america_sale.total_sale) AS total_sale,
    LAG(SUM(north_america_sale.total_sale), 1, NULL) OVER (
    	ORDER BY TRUNC(north_america_sale.report_date, 'MONTH')
    ) AS previous_month_total_sale,
    (
        SUM(north_america_sale.total_sale)
    	- LAG(SUM(north_america_sale.total_sale), 1, NULL) OVER (
        	ORDER BY TRUNC(north_america_sale.report_date, 'MONTH')
        )
    ) AS difference
FROM
    north_america_sale
GROUP BY
	TRUNC(north_america_sale.report_date, 'MONTH')
ORDER BY
    reporting_month;

Now, the result set is only missing an attribute capturing the month-over-month percent change. To compute this measure for each month, the resulting difference between the current month’s sales and previous month’s sales can be divided by the current month’s sales and subsequently multiplied by 100. Each percent change value can then be rounded to two decimal places using the ROUND function. For more information on the ROUND function along with other numeric functions, see my page dedicated to them here. The formula, query, and corresponding output can be found below.

Formula
((current month’s sales – previous month’s sales) / current month’s sales)) * 100

SELECT
    TRUNC(north_america_sale.report_date, 'MONTH') AS reporting_month,
    SUM(north_america_sale.total_sale) AS total_sale,
    LAG(SUM(north_america_sale.total_sale), 1, NULL) OVER (
    	ORDER BY TRUNC(north_america_sale.report_date, 'MONTH')
    ) AS previous_month_total_sale,
    (
        SUM(north_america_sale.total_sale)
    	- LAG(SUM(north_america_sale.total_sale), 1, NULL) OVER (
        	ORDER BY TRUNC(north_america_sale.report_date, 'MONTH')
        )
    ) AS difference,
    ROUND(
        (
            (
                SUM(north_america_sale.total_sale)
            	- LAG(SUM(north_america_sale.total_sale), 1, NULL) OVER (
                	ORDER BY TRUNC(north_america_sale.report_date, 'MONTH')
                )
            ) / SUM(north_america_sale.total_sale)
        ) * 100,
    	2
    ) AS percent_change
FROM
    north_america_sale
GROUP BY
	TRUNC(north_america_sale.report_date, 'MONTH')
ORDER BY
    reporting_month;

Having generated the result set above, report 1 is ready. At the beginning of this discussion, I mentioned there were two common strategies used for solving problems requiring the ability to reference values within other rows. The one utilized above uses window functions and is aligned with the focus of this page. For those curious readers, I have also written a SELECT statement that generates the same result set but with the use of a SELF JOIN. To review the code, click on the icon to the left of the section header below. To hide the code, click the icon again.

WITH monthly_sale AS (
    SELECT
        TRUNC(north_america_sale.report_date, 'MONTH') AS reporting_month,
        SUM(north_america_sale.total_sale) AS total_sale
    FROM
        north_america_sale
    GROUP BY
        TRUNC(north_america_sale.report_date, 'MONTH')
)
SELECT
    current_month.reporting_month,
    current_month.total_sale AS current_month_sale,
    previous_month.total_sale AS previous_month_sale,
    (
    	current_month.total_sale
    	- previous_month.total_sale
    ) AS difference,
    ROUND(
        (
            (
            	current_month.total_sale
            	- previous_month.total_sale
            ) / current_month.total_sale
        
        ) * 100,
    	2
    ) AS percent_change
FROM
    monthly_sale current_month
    LEFT JOIN
    monthly_sale previous_month
    	ON current_month.reporting_month = ADD_MONTHS(previous_month.reporting_month, 1)
ORDER BY
	current_month.reporting_month;

Generating Report 2
Developing a query to generate report 2 requires a few simple updates to report 1’s query. Let us walk through the updates together using a top-down approach.

The report requires the product category attribute to be included in the result set, so the attribute needs to be added to the SELECT clause. Additionally, the measures computed need to account for this newly added attribute in their own ways. The aggregate function, SUM, requires the GROUP BY clause to be updated to include the product category attribute while measures using the window function, LAG, require the OVER clause to include a PARTITION BY clause consisting of the product category attribute. Adding the product category attribute to the GROUP BY clause ensures the total sale measure represents the total sales corresponding to the row’s reporting month and product category value. By including a PARTITION BY clause consisting of the product category attribute, the LAG function operates on each subset of rows defined by their product category values. This ensures, when the data set is ordered by reporting month, the previous month value returned is not simply the value within the row prior to the current row, but the value within the row prior to the current row with the same product category value. At this point, all that is left is to update the ORDER BY clause to include the product category attribute to make it easier to consume the report. For clarity, see the code and corresponding output below.

SELECT
    TRUNC(north_america_sale.report_date, 'MONTH') AS reporting_month,
    north_america_sale.product_category,
    SUM(north_america_sale.total_sale) AS total_sale,
    LAG(SUM(north_america_sale.total_sale), 1, NULL) OVER (
    	PARTITION BY north_america_sale.product_category
    	ORDER BY TRUNC(north_america_sale.report_date, 'MONTH')
    ) AS previous_month_total_sale,
    (
        SUM(north_america_sale.total_sale)
    	- LAG(SUM(north_america_sale.total_sale), 1, NULL) OVER (
        	PARTITION BY north_america_sale.product_category
        	ORDER BY TRUNC(north_america_sale.report_date, 'MONTH')
        )
    ) AS difference,
    ROUND(
        (
            (
                SUM(north_america_sale.total_sale)
            	- LAG(SUM(north_america_sale.total_sale), 1, NULL) OVER (
    				PARTITION BY north_america_sale.product_category
                	ORDER BY TRUNC(north_america_sale.report_date, 'MONTH')
                )
            ) / SUM(north_america_sale.total_sale)
        ) * 100,
    	2
    ) AS percent_change
FROM
    north_america_sale
GROUP BY
	TRUNC(north_america_sale.report_date, 'MONTH'),
    north_america_sale.product_category
ORDER BY
    reporting_month,
    north_america_sale.product_category;

Advertisement Campaign Changes
Suppose the company for which you are employed has been using the same advertisement campaign to promote company products for several years. Eager to test changes, your company created slightly different advertisement campaigns for quarters two through four in year 2023. With the year having ended, management now wishes to review the effect on total sales for each quarter given the slight changes made in advertising before the start of the each subsequent quarter. You have been verbally requested to generate a report containing each quarter’s total sales along with the following quarter’s sales to allow management to easily trace the effects of the minor changes in advertising. Management mentioned the following requirements related to the structure of the report:

– Each quarter’s total sales and the following quarter’s total sales should be captured within a single row.
– Within each row, the first quarter’s total sales should be captured. This will allow management to more easily compare the sales generated with the historical ad campaign in the first quarter to those generated using the slightly modified campaigns in subsequent quarters.
– The difference between the following quarter’s total sales and the first quarter’s total sales should be included in the report along with the percent change in total sales observed when comparing the following quarter’s total sales to the first quarter’s total sales. The percent change in total sales should be rounded to two decimal places.

Generating the Report
Based on the requirements set forth by management, you will need to develop a query that will return a result set containing the total sales by quarter. To do so, the SUM function can be used in the SELECT clause along with an attribute derived based on the quarter in which each report date falls. Using the TO_CHAR function with the report date attribute and Q as arguments results in each original report date value being the corresponding quarter number (e.g., 1, 2, 3, 4). A GROUP BY clause consisting of the same derived attribute results in the total sales by quarter. To include the next quarter’s sales value within each row, the LEAD function can be used with a value of 1 specified as the offset. The ORDER BY clause within the OVER clause is significant in that it ensures the value representing the next quarter’s total sales is accurate. The current query can be seen below along with the corresponding result set. Like the previous example, the query has been sorted. In this case, by quarter value. A next quarter total sale value does not exist for the row corresponding to quarter 4 because the fictional data set only includes year 2023.

SELECT
    TO_CHAR(north_america_sale.report_date, 'Q') AS reporting_quarter,
    SUM(north_america_sale.total_sale) AS total_sale,
        LEAD(SUM(north_america_sale.total_sale), 1, NULL) OVER (
    	ORDER BY TO_CHAR(north_america_sale.report_date, 'Q')
    ) AS next_quarter_total_sale
FROM
    north_america_sale
GROUP BY
    TO_CHAR(north_america_sale.report_date, 'Q')
ORDER BY
    TO_CHAR(north_america_sale.report_date, 'Q');

The result set produced by the query above is a good start to meeting the report’s requirements. One of the remaining requirements is to include the total sales value associated with quarter 1 in all rows. There are multiple ways to achieve this, but in the spirit of using window functions, let us use the FIRST_VALUE function. This function allows us to retrieve the value within the first row of a set of ordered rows. Using this same strategy, the difference between the first quarter’s sales and each subsequent quarter can be computed. The same applies to computing the percent change but with a little help from the ROUND function. See the code and corresponding output below.

SELECT
    TO_CHAR(north_america_sale.report_date, 'Q') AS reporting_quarter,
    SUM(north_america_sale.total_sale) AS total_sale,
    FIRST_VALUE(SUM(north_america_sale.total_sale)) OVER (
        ORDER BY TO_CHAR(north_america_sale.report_date, 'Q')
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS first_quarter_total_sale,
    LEAD(SUM(north_america_sale.total_sale), 1, NULL) OVER (
    	ORDER BY TO_CHAR(north_america_sale.report_date, 'Q')
    ) AS next_quarter_sale,
    (
        FIRST_VALUE(SUM(north_america_sale.total_sale)) OVER (
            ORDER BY TO_CHAR(north_america_sale.report_date, 'Q')
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        )
    	-  LEAD(SUM(north_america_sale.total_sale), 1, NULL) OVER (
    	ORDER BY TO_CHAR(north_america_sale.report_date, 'Q')
    	)
    ) AS difference,
    ROUND(
        (
            (	LEAD(SUM(north_america_sale.total_sale), 1, NULL) OVER (
                	ORDER BY TO_CHAR(north_america_sale.report_date, 'Q')
                )
                - FIRST_VALUE(SUM(north_america_sale.total_sale)) OVER (
    				ORDER BY TO_CHAR(north_america_sale.report_date, 'Q')
    				ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                )
            ) / LEAD(SUM(north_america_sale.total_sale), 1, NULL) OVER (
                	ORDER BY TO_CHAR(north_america_sale.report_date, 'Q')
                )
        ) * 100,
    	2
    ) AS percent_change
FROM
    north_america_sale
GROUP BY
    TO_CHAR(north_america_sale.report_date, 'Q')
ORDER BY
    TO_CHAR(north_america_sale.report_date, 'Q');

The result set above contains the necessary data management has deemed necessary to determine the impact each advertisement campaign had on total sales. How are the results read? Row one states $84,122,881 in sales were generated in quarter one and in the following quarter, $81,830,438. As a result of making a change to the historically used advertisement campaign before the start of quarter two, $2,292,443 or 2.8 percent less sales were generated. Row two states $81,830,438 in sales were generated in quarter two and in the following quarter, $82,156,171. As a result of making a change to the historically used advertisement campaign before the start of quarter three, $1,966,710 or 2.39 percent less sales were generated.

Please keep in mind this is a hypothetical scenario and is meant only to demonstrate the use of the LEAD function to solve real-world problems. Determining the efficacy of advertisement campaigns and attributing changes in sales to some event requires more involved analysis and with statistically sound methods.

Knowledge Check
Need more practice or simply wish to test your understanding? Give a few of the practice problems I’ve provided in the SQL Practice Problems section a try.