AGGREGATES WITH WINDOWS

Structured query language provides users the ability to compute aggregate values based on sequences of rows and the values the rows contain. A sequence of rows is commonly referred to as the window. The rows belonging to each window and the ordering of rows within each window are based on user-specified instructions. The significance of being able to combine window and aggregate functions is it allows users to easily calculate period-based measures such as running totals and moving averages. Additionally, if desired, the combination allows aggregate values to be computed without changing the level of detail or granularity of the data. Let us explore a few of real-world problems and their corresponding solutions to highlight the power of this feature.

Cupcake Shop
Suppose you have recently joined an analytics team at a world-renowned cupcake shop with several locations across the globe. The company recently opened up a new location and management has asked you to provide answers or a report addressing the following:

– On which date did the new location sell its 10,000th cupcake?

– Can a report be provided containing 7-day moving averages for each of our cupcake flavors in terms of quantity sold? The report should contain the same level of detail as how the data is currently stored (i.e., one row per report date and flavor along with the corresponding quantity sold). An attribute containing the 7-day moving average values should be included.

– Can a daily cupcake flavor performance report be provided containing a comparison between each day’s cupcake sales and the all-time high in terms of number of cupcakes sold per flavor? The report should contain the report date, flavor, quantity sold, the maximum quantity ever sold for the flavor, and the difference between those two values.

The data you are working with 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.

EXEC DBMS_RANDOM.SEED(42);

CREATE TABLE daily_report AS 
    WITH daily_report (report_date, flavor, quantity_sold) AS (
        SELECT
    		daily_sale.report_date,
    		data_template.flavor_value,
    		daily_sale.quantity_sold
    	FROM (
    		SELECT
    		   'Chocolate' AS flavor_1,
    		   'Vanilla' AS flavor_2,
    		   'Red Velvet' AS flavor_3,
    		   'Lemon' AS flavor_4
    		FROM
    			dual
    	)
    	UNPIVOT (
    		flavor_value FOR flavor_column IN (
    			"FLAVOR_1", "FLAVOR_2",
    			"FLAVOR_3", "FLAVOR_4")
    	) data_template
    	CROSS JOIN (
    	SELECT
    		TRUNC(SYSDATE) AS report_date,
    		FLOOR(DBMS_RANDOM.VALUE(0, 101)) AS quantity_sold
    	FROM
    		dual
    	) daily_sale
        UNION ALL
        SELECT
            daily_report.report_date - INTERVAL '1' DAY,
         	daily_report.flavor,
            FLOOR(DBMS_RANDOM.VALUE(0, 101))
        FROM
            daily_report
        WHERE
            daily_report.report_date > TRUNC(SYSDATE) - INTERVAL '90' DAY
    )
    SELECT
    	daily_report.report_date,
    	daily_report.flavor,
    	daily_report.quantity_sold
    FROM
    	daily_report;

Getting back to management’s questions, how can SQL be used to generate the answers? Let tackle each question in turn, starting with determining the date for which the 10,000th cupcake was sold.

On which date did the new location sell its 10,000th cupcake?
To determine the date for which the 10,000th cupcake was sold, the number of cupcakes sold per day requires computing. This can be achieved by using the aggregate function, SUM, with a GROUP BY clause specifying to group the rows by report date values. The query described so far can be found below and corresponding output can be found below.

SELECT
    daily_report.report_date,
    SUM(daily_report.quantity_sold) AS quantity_sold
FROM
    daily_report
GROUP BY
    daily_report.report_date;

After computing the quantity sold value for each day the cupcake shop has been open, a running total needs to be computed across the report date values. To compute the running total, the summation of quantity sold values needs to be wrapped within another SUM and an OVER clause specifying one implicit partition (i.e., the entire data set) is needed. Within the OVER clause, a window clause stating to compute each day’s running daily total value based on the sum of the quantity sold values for all days (i.e., rows) prior to the current row’s day value and the current row’s day value is required. When computing the running total, it is important to order the report date values in ascending order within the OVER clause. This will ensure the running total starts with the first day the shop was open. For clarity, see the augmented query and corresponding result set below.

SELECT
    daily_report.report_date,
    SUM(SUM(daily_report.quantity_sold)) OVER (
        ORDER BY daily_report.report_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_daily_total
FROM
    daily_report
GROUP BY
    daily_report.report_date;

By placing the query above in parenthesis and treating it as a derived table, the first date for which the running daily total value is greater than or equal to 10,000 can be retrieved using Oracle’s FETCH clause. To ensure the correct date value is fetched, the derived table’s report date values need to be ordered in ascending order. See the final query and corresponding output below.

SELECT
    augmented_daily_report.report_date
FROM (
    SELECT
    	daily_report.report_date,
        SUM(SUM(daily_report.quantity_sold)) OVER (
        	ORDER BY daily_report.report_date
    		ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS running_daily_total
    FROM
    	daily_report
    GROUP BY
        daily_report.report_date
) augmented_daily_report
WHERE
	augmented_daily_report.running_daily_total > 10000
ORDER BY
	augmented_daily_report.report_date
FETCH FIRST ROW ONLY;

The solution above makes use of Oracle’s FETCH clause. This clause should not be used when the row or rows returned can vary from execution to execution. For example, if there were multiple rows within the derived table, augmented_daily_report, with the date value of December 26th, 2023, then the specific row returned could vary. This would be noticeable with the corresponding running daily total values included in the output. As a result, the row returned may not be the one containing the lowest value meeting or exceeding the value of 10,000. For scenarios where the rows returned can vary due to ties in values, use DENSE_RANK or an ORDER BY clause resulting in consistent and predictable results.

7-day moving average
Generating a report containing 7-day moving averages for each cupcake flavor is straightforward compared to the previous problem. This can be achieved using the average function with the quantity sold attribute as its argument and a very specific OVER clause. Because the goal is to compute the 7-day moving average values for each cupcake flavor, the rows need to be partitioned by the flavor attribute. This will ensure each average is being computed using the quantity sold values corresponding to the specific flavor. Additionally, the OVER clause needs to consist of an ORDER BY clause to ensure each average value is being computed based on successive days. The final touch is to add a window clause specifying to compute each row’s average value based the quantity sold value within the row and those values within each of the six rows prior to it. Due to the way the data is structured in the daily report table and the PARTITION BY clause, each row translates to day. When the number of days (i.e., rows) existing prior to the current row’s day value is less than six, then the average is computed based on the current row’s quantity sold value and the available rows, if any. For example, the first date value in the daily report table is November 8, 2023. Each of the flavor’s 7-day moving average value is computed based on only the current row since none exist prior to it. Due to the nature of averages, the 7-day moving value and the quantity sold value will match. The solution described and the corresponding output can be found below. I have rounded the 7-day moving average values to two decimal places.

SELECT
    daily_report.report_date,
    daily_report.flavor,
    daily_report.quantity_sold,
    ROUND(
        AVG(daily_report.quantity_sold) OVER (
    		PARTITION BY daily_report.flavor
            ORDER BY daily_report.report_date
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ),
        2
    ) AS seven_day_moving_average
FROM
    daily_report
ORDER BY
    daily_report.report_date;

Daily Cupcake Flavor Performance
There are several possible ways to generate the requested report. The methods for doing so have in common that each allows a within-row comparison to be made between the row’s quantity sold value and the value associated with the largest number of cupcakes ever sold for the row’s flavor value. Using the MAX function as a window function, the latter of the two values can be computed by simply partitioning the data by flavor. This ensures the maximum value computed is based on the quantity sold values in the data matching the current row’s flavor value. For each row, the difference can be computed by subtracting the result of the window function from the row’s quantity sold value. For clarity, see the code and corresponding output below.

SELECT
    daily_report.report_date,
    daily_report.flavor,
    daily_report.quantity_sold,
    MAX(daily_report.quantity_sold) OVER (
        PARTITION BY daily_report.flavor
    ) AS all_time_high,
    (
    	daily_report.quantity_sold
        - MAX(daily_report.quantity_sold) OVER (
            PARTITION BY daily_report.flavor
        )
    ) AS difference
FROM
    daily_report
ORDER BY
    daily_report.report_date;

Knowledge Check
If you’d like to confirm your understanding on the use of aggregate functions with windows and other fundamental concepts, consider solving a handful of my practice problems here.