Mastering SQL Window Functions: Analyzing Sales Trends Over the Last 4 Quarters – Part 1
Introduction
When analyzing sales data, it's crucial to track performance trends over time. In this blog, we'll explore how to use SQL window functions to retrieve and analyze sales data for the current quarter and the last 3 quarters, providing insights such as:
✅ Current and prior quarter sales
✅ Next quarter sales (if available)
✅ Rolling total of the last 4 quarters
✅ Quarter-over-quarter sales change
We’ll break this down step by step with an easy-to-follow SQL query.
Step 1: Understanding the Dataset
Let’s use following tables from snowflake sample for this blog:
Our goal is to group the sales by quarters and analyze trends using window functions from STORE_SALES table for each individual ITEM_ID from ITEM table.
Our goal is to group the sales by quarters and analyze trends using window functions from STORE_SALES table for each individual ITEM_ID from ITEM table.
Step 2: Aggregating Sales Data by Quarter
Since sales are recorded daily, we first aggregate the total sales per year, quarter for each ITEM.
WITH QUARTERLY_SALES AS (
SELECT
IT.I_ITEM_ID AS ITEM_ID,
D_YEAR AS SALE_YEAR,
D_QOY AS SALE_QUARTER,
SUM(SS_SALES_PRICE) AS TOTAL_SALES
FROM SNOWFLAKE_SAMPLE_DATA.TPCDS_SF100TCL.STORE_SALES SS
INNER JOIN SNOWFLAKE_SAMPLE_DATA.TPCDS_SF100TCL.DATE_DIM DD ON SS_SOLD_DATE_SK = DD.D_DATE_SK
INNER JOIN SNOWFLAKE_SAMPLE_DATA.TPCDS_SF100TCL.ITEM IT ON SS.SS_ITEM_SK = IT.I_ITEM_SK
GROUP BY ALL
)
What This Does:
✅ Select year and quarter from Date Dimension.
✅ Aggregates sale_amount by item, year, and quarter.
Step 3: Using Window Functions to Analyze Sales Trends
Now, we use window functions to track quarter-over-quarter trends.
WITH QUARTERLY_SALES AS (
SELECT
IT.I_ITEM_ID AS ITEM_ID,
D_YEAR AS SALE_YEAR,
D_QOY AS SALE_QUARTER,
SUM(SS_SALES_PRICE) AS TOTAL_SALES
FROM SNOWFLAKE_SAMPLE_DATA.TPCDS_SF100TCL.STORE_SALES SS
INNER JOIN SNOWFLAKE_SAMPLE_DATA.TPCDS_SF100TCL.DATE_DIM DD ON SS_SOLD_DATE_SK = DD.D_DATE_SK
INNER JOIN SNOWFLAKE_SAMPLE_DATA.TPCDS_SF100TCL.ITEM IT ON SS.SS_ITEM_SK = IT.I_ITEM_SK
GROUP BY ALL
),
-- SELECT TOP 10 * FROM QUARTERLY_SALES
RANKED_SALES AS (
SELECT
ITEM_ID,
SALE_YEAR,
SALE_QUARTER,
TOTAL_SALES,
ROW_NUMBER() OVER (PARTITION BY ITEM_ID ORDER BY SALE_YEAR DESC, SALE_QUARTER DESC) AS RNK,
-- GET SALES FROM THE PREVIOUS QUARTER
LAG(TOTAL_SALES, 1) OVER (PARTITION BY ITEM_ID ORDER BY SALE_YEAR DESC, SALE_QUARTER DESC) AS PRIOR_QUARTER_SALES,
-- GET SALES FROM THE NEXT QUARTER (IF AVAILABLE)
LEAD(TOTAL_SALES, 1) OVER (PARTITION BY ITEM_ID ORDER BY SALE_YEAR DESC, SALE_QUARTER DESC) AS NEXT_QUARTER_SALES,
-- RUNNING TOTAL FOR THE LAST 4 QUARTERS
SUM(TOTAL_SALES) OVER (PARTITION BY ITEM_ID ORDER BY SALE_YEAR DESC, SALE_QUARTER DESC ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS RUNNING_4_QUARTERS_SALES,
-- SALES CHANGE COMPARED TO PREVIOUS QUARTER
TOTAL_SALES - LAG(TOTAL_SALES, 1) OVER (PARTITION BY ITEM_ID ORDER BY SALE_YEAR DESC, SALE_QUARTER DESC) AS QUARTER_SALES_CHANGE
FROM QUARTERLY_SALES
)
Key Window Functions Used:
✅ LAG() → Retrieves sales from the previous quarter.
✅ LEAD() → Retrieves sales from the next quarter.
✅ SUM() OVER → Computes a rolling total for the last 4 quarters.
✅ ROW_NUMBER() → Ranks quarters from most recent to oldest.
Recommended by LinkedIn
Step 4: Filtering the Last 4 Quarters
We now filter the results to only include the current quarter and the last 3 quarters.
SELECT
ITEM_ID,
SALE_YEAR,
SALE_QUARTER,
TOTAL_SALES,
PRIOR_QUARTER_SALES,
NEXT_QUARTER_SALES,
RUNNING_4_QUARTERS_SALES,
QUARTER_SALES_CHANGE
FROM RANKED_SALES
WHERE RNK <= 4 -- FETCH CURRENT AND LAST 3 QUARTERS
ORDER BY SALE_YEAR DESC, SALE_QUARTER DESC;
What This Does:
✅ Limits results to the last 4 quarters.
✅ Sorts data from the latest quarter to the oldest.
Finally wrapping all the queries into a single view
CREATE VIEW VW_RANKED_QUARTERLY_SALES AS (
WITH QUARTERLY_SALES AS (
SELECT
IT.I_ITEM_ID AS ITEM_ID,
D_YEAR AS SALE_YEAR,
D_QOY AS SALE_QUARTER,
SUM(SS_SALES_PRICE) AS TOTAL_SALES
FROM SNOWFLAKE_SAMPLE_DATA.TPCDS_SF100TCL.STORE_SALES SS
INNER JOIN SNOWFLAKE_SAMPLE_DATA.TPCDS_SF100TCL.DATE_DIM DD ON SS_SOLD_DATE_SK = DD.D_DATE_SK
INNER JOIN SNOWFLAKE_SAMPLE_DATA.TPCDS_SF100TCL.ITEM IT ON SS.SS_ITEM_SK = IT.I_ITEM_SK
GROUP BY ALL
)
,
-- SELECT TOP 10 * FROM QUARTERLY_SALES
RANKED_SALES AS (
SELECT
ITEM_ID,
SALE_YEAR,
SALE_QUARTER,
TOTAL_SALES,
ROW_NUMBER() OVER (PARTITION BY ITEM_ID ORDER BY SALE_YEAR DESC, SALE_QUARTER DESC) AS RNK,
-- GET SALES FROM THE PREVIOUS QUARTER
LAG(TOTAL_SALES, 1) OVER (PARTITION BY ITEM_ID ORDER BY SALE_YEAR DESC, SALE_QUARTER DESC) AS PRIOR_QUARTER_SALES,
-- GET SALES FROM THE NEXT QUARTER (IF AVAILABLE)
LEAD(TOTAL_SALES, 1) OVER (PARTITION BY ITEM_ID ORDER BY SALE_YEAR DESC, SALE_QUARTER DESC) AS NEXT_QUARTER_SALES,
-- RUNNING TOTAL FOR THE LAST 4 QUARTERS
SUM(TOTAL_SALES) OVER (PARTITION BY ITEM_ID ORDER BY SALE_YEAR DESC, SALE_QUARTER DESC ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS RUNNING_4_QUARTERS_SALES,
-- SALES CHANGE COMPARED TO PREVIOUS QUARTER
TOTAL_SALES - LAG(TOTAL_SALES, 1) OVER (PARTITION BY ITEM_ID ORDER BY SALE_YEAR DESC, SALE_QUARTER DESC) AS QUARTER_SALES_CHANGE
FROM QUARTERLY_SALES
)
SELECT
ITEM_ID,
SALE_YEAR,
SALE_QUARTER,
TOTAL_SALES,
PRIOR_QUARTER_SALES,
NEXT_QUARTER_SALES,
RUNNING_4_QUARTERS_SALES,
QUARTER_SALES_CHANGE
FROM RANKED_SALES
WHERE RNK <= 4 -- FETCH CURRENT AND LAST 3 QUARTERS
ORDER BY SALE_YEAR DESC, SALE_QUARTER DESC
);
!Snowflake Developer Tip!
1)Use GROUP BY ALL instead of calling all columns in GROUP BY CLAUSE. Fast and easy way!
2)Use WindowFrame functions like Preceding/Following to traverse forward/backward for ROWS/RANGES
View Lineage
Step 5: Example Output
Step 6: Key Business Insights
📌 Quarter-over-quarter trends: prior_quarter_sales and quarter_sales_change help identify growth or decline in sales.
📌 Predict future trends: next_quarter_sales provides insights into what might happen next. 📌 Rolling total performance: running_4_quarters_sales helps understand overall performance over time.
Conclusion
SQL window functions are powerful tools for analyzing sales trends over time. By leveraging LAG(), LEAD(), RANGE, ROWS and SUM() OVER, you can extract key insights for decision-making and forecasting.
Similarly, you can explore other WINDOW FUNCTIONS for advanced time series analysis. Check out the URL below for more details.
Would you like to extend this further with year-over-year comparisons or cumulative moving averages? Let me know in the comments! 🚀
