When working with sales data, looking at daily revenue alone doesn't always tell the complete story.
Businesses often need to compare today's sales with the previous day's sales to understand whether revenue is increasing or decreasing.
For example:
- Yesterday's sales: 15,000
- Today's sales: 12,000
- Difference: -3,000
How can you calculate this comparison using SQL without joining the same table with itself?
The answer is the LAG() window function.
In this SQL tutorial, let's solve a practical daily sales comparison problem using Oracle SQL.
- Understanding the Problem
Suppose you have a "daily_sales" table containing sale dates and daily sales amounts.
Your task is to write an SQL query that displays:
- Sale date
- Today's sales
- Previous day's sales
- Difference between today's and previous day's sales
The original table should remain unchanged.
- Sample Table: daily_sales
CREATE TABLE daily_sales (
sales_date DATE,
total_sales NUMBER(10)
);
Sample records:
INSERT INTO daily_sales VALUES (DATE '2026-11-16', 10000);
INSERT INTO daily_sales VALUES (DATE '2026-11-17', 15000);
INSERT INTO daily_sales VALUES (DATE '2026-11-18', 12000);
INSERT INTO daily_sales VALUES (DATE '2026-11-19', 18000);
INSERT INTO daily_sales VALUES (DATE '2026-11-20', 22000);
COMMIT;
- Your SQL Challenge
Write an SQL query to:
- Retrieve the previous day's sales using LAG().
- Compare today's sales with previous day's sales.
- Calculate the sales difference.
- Display records in chronological order.
Can you write the query before checking the solution?
- SQL Solution Using LAG() (Oracle)
SELECT
sales_date,
total_sales AS today_sales,
LAG(total_sales) OVER (
ORDER BY sales_date
) AS previous_day_sales,
total_sales - LAG(total_sales) OVER (
ORDER BY sales_date
) AS sales_difference
FROM daily_sales
ORDER BY sales_date;
- Expected Output
SALES_DATE TODAY_SALES PREVIOUS_DAY_SALES SALES_DIFFERENCE
16-Nov-2026 10000 NULL NULL
17-Nov-2026 15000 10000 5000
18-Nov-2026 12000 15000 -3000
19-Nov-2026 18000 12000 6000
20-Nov-2026 22000 18000 4000
- How Does the LAG() Function Work?
Let's understand the important parts of this query.
LAG(total_sales)
Retrieves the sales amount from the previous row.
OVER()
Defines the window over which the function operates.
ORDER BY sales_date
Arranges the rows by date and determines which row is considered previous.
total_sales - LAG(total_sales)
Calculates the difference between today's sales and the previous day's sales.
The first record returns NULL because there is no previous row.
- Understanding LAG() Syntax
The general syntax is:
LAG(column_name, offset, default_value)
OVER (
PARTITION BY column_name
ORDER BY column_name
)
- "column_name": The column whose previous value you want.
- "offset": Number of rows to look backward. Default is 1.
- "default_value": Value returned when no previous row exists.
- "PARTITION BY": Divides records into separate groups.
- "ORDER BY": Determines the order of rows.
- One Thing to Remember
LAG() is different from aggregate functions.
- "SUM()": Calculates totals.
- "LAG()": Retrieves a value from a previous row.
- "LEAD()": Retrieves a value from a following row.
- "ROW_NUMBER()": Assigns sequential numbers to rows.
The LAG() function is particularly useful for daily sales comparisons, monthly revenue analysis, customer purchase trends and business performance reports.
- Your Turn: Try This SQL Challenge
How would you modify the above query to calculate the percentage change in sales compared with the previous day?
Hint: Use the following formula:
((today_sales - previous_day_sales)
/ previous_day_sales) * 100
Try writing the query yourself before looking for the answer.
Share your SQL solution in the comments!
If you are learning SQL window functions, you can explore more practical SQL tutorials and interview challenges at "Sankalan Data Tech" (https://www.sankalandtech.com/).

