In this post we are going to cover running totals, LAG and LEAD: aggregate window functions, window frames, and reading the previous or next row. These are 7 real SQL interview questions, asked the way they come up on the job, and the last one is a scenario where leadership wants a monthly growth report that a zero month cannot break.
Once you know ROW_NUMBER and RANK, interviewers move on to SUM, AVG, LAG and LEAD over a window. Running totals, month over month change and moving averages are in almost every finance report. The interviewer is really checking one thing: do you know what the window frame is, and what the default frame does?
Every query below is complete, so you can copy it, run it on the sample data and get exactly the output shown under it.
Part 6 of the SQL Server RealTime Questions Answers series on TechBrothersIT.
- The short answer
- Let's create some sample data
- Question 1: Finance wants a running total of revenue by order date. How do you calculate it without a self join or a subquery?
- Question 2: Your running total of payments shows the same 2,200 on two rows. What is the difference between ROWS and RANGE, and how do you fix the running total?
- Question 3: A manager wants month-over-month revenue change. How do you use LAG and LEAD, and what is the trap when a month has no sales?
- Question 4: The business wants a 3-month moving average of revenue. How do you write it, and what happens in the first two months?
- Question 5: Finance wants each category's percent of total revenue in one query, with no subquery. How do you do it with SUM() OVER ()?
- Question 6: You use LAST_VALUE to show the lowest-paid person in each department, but every row just shows itself. Why does LAST_VALUE seem not to work, and what is the fix?
- Scenario 7: Leadership wants one monthly report with revenue, the previous month, the change and the percent growth. How do you write it so a zero month can't break it?
- The full SQL script
The short answer
SUM() OVER (ORDER BY ...) gives a running total, and PARTITION BY restarts it per group. The default frame is RANGE, which adds tied rows together, so write ROWS with a unique ORDER BY. LAG and LEAD read the previous and next row, frames control moving averages and LAST_VALUE, and SUM(SUM(x)) OVER () gives percent of total without a subquery.
Keep the word frame in mind. Most of the traps below come from it.
Let's create some sample data
We use a small shop database called TB Shop: departments, employees, customers, products, orders, order items and payments. The script is written for PostgreSQL, and it runs on SQL Server, MySQL 8 or Snowflake with very small changes. Run it once and every query in this post works.
DROP TABLE IF EXISTS payments;
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS departments;
CREATE TABLE departments (dept_id INT PRIMARY KEY, dept_name VARCHAR(20));
INSERT INTO departments VALUES (10, 'Sales'), (20, 'Engineering'), (30, 'HR'), (40, 'Finance');
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
name VARCHAR(20),
dept_id INT, -- NULL = not assigned yet
manager_id INT, -- NULL = top of the company
salary INT,
hire_date DATE
);
INSERT INTO employees VALUES
(1, 'Aisha', NULL, NULL, 150000, '2018-03-01'),
(2, 'Omar', 20, 1, 120000, '2019-06-15'),
(3, 'Lina', 20, 2, 90000, '2021-01-10'),
(4, 'Ravi', 20, 2, 90000, '2022-04-05'),
(5, 'Mei', 20, 2, 75000, '2024-09-01'),
(6, 'Sara', 10, 1, 95000, '2020-02-20'),
(7, 'Daniel', 10, 6, 105000, '2021-07-01'),
(8, 'Fatima', 10, 6, 60000, '2023-11-12'),
(9, 'John', 30, 1, 70000, '2019-09-30'),
(10,'Zara', NULL, 9, 55000, '2026-01-05');
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(20),
city VARCHAR(20),
region VARCHAR(10), -- NULL = unknown
email VARCHAR(40)
);
INSERT INTO customers VALUES
(1, 'Sara Khan', 'Toronto', 'East', 'sara@mail.com'),
(2, 'Omar Ali', 'Dallas', 'West', 'omar@mail.com'),
(3, 'Lina Park', 'Seattle', 'West', 'lina@mail.com'),
(4, 'Mei Chen', 'Boston', 'East', 'mei@mail.com'),
(5, 'Ravi Patel', 'Austin', NULL, 'ravi@mail.com'),
(6, 'Chen Wu', 'Denver', 'West', 'chen@mail.com'),
(7, 'Sara K.', 'Toronto', 'East', 'sara@mail.com');
CREATE TABLE products (product_id INT PRIMARY KEY, product VARCHAR(20), category VARCHAR(15), price DECIMAL(10,2));
INSERT INTO products VALUES
(1, 'Laptop', 'Electronics', 1200.00),
(2, 'Monitor', 'Electronics', 300.00),
(3, 'Keyboard', 'Accessories', 50.00),
(4, 'Mouse', 'Accessories', 25.00),
(5, 'Desk', 'Furniture', 400.00),
(6, 'Chair', 'Furniture', 250.00);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
status VARCHAR(10),
amount DECIMAL(10,2)
);
INSERT INTO orders VALUES
(1001, 1, '2026-01-05', 'Delivered', 1250.00),
(1002, 2, '2026-01-18', 'Delivered', 300.00),
(1003, 4, '2026-02-02', 'Delivered', 650.00),
(1004, 1, '2026-02-14', 'Cancelled', 50.00),
(1005, 3, '2026-02-20', 'Delivered', 1200.00),
(1006, 4, '2026-03-03', 'Shipped', 275.00),
(1007, 5, '2026-03-15', 'Delivered', 400.00),
(1008, 2, '2026-04-01', 'Delivered', 75.00),
(1009, 4, '2026-04-22', 'Delivered', 250.00),
(1010, 1, '2026-05-09', 'Shipped', 300.00),
(1011, 3, '2026-05-30', 'Cancelled', 400.00),
(1012, 7, '2026-06-11', 'Delivered', 50.00);
CREATE TABLE order_items (order_id INT, product_id INT, qty INT, unit_price DECIMAL(10,2));
INSERT INTO order_items VALUES
(1001, 1, 1, 1200.00), (1001, 3, 1, 50.00),
(1002, 2, 1, 300.00),
(1003, 5, 1, 400.00), (1003, 6, 1, 250.00),
(1004, 3, 1, 50.00),
(1005, 1, 1, 1200.00),
(1006, 6, 1, 250.00), (1006, 4, 1, 25.00),
(1007, 5, 1, 400.00),
(1008, 4, 3, 25.00),
(1009, 6, 1, 250.00),
(1010, 2, 1, 300.00),
(1011, 5, 1, 400.00),
(1012, 3, 1, 50.00);
CREATE TABLE payments (payment_id INT PRIMARY KEY, order_id INT, paid_amount DECIMAL(10,2));
INSERT INTO payments VALUES
(1, 1001, 1250.00), (2, 1002, 300.00), (3, 1003, 400.00), (4, 1003, 250.00), (5, 1005, 1200.00);Here are the orders. Most queries in this post skip the two Cancelled orders, 1004 and 1011.
| order_id | customer_id | order_date | status | amount |
|---|---|---|---|---|
| 1001 | 1 | 2026-01-05 | Delivered | 1250.00 |
| 1002 | 2 | 2026-01-18 | Delivered | 300.00 |
| 1003 | 4 | 2026-02-02 | Delivered | 650.00 |
| 1004 | 1 | 2026-02-14 | Cancelled | 50.00 |
| 1005 | 3 | 2026-02-20 | Delivered | 1200.00 |
| 1006 | 4 | 2026-03-03 | Shipped | 275.00 |
| 1007 | 5 | 2026-03-15 | Delivered | 400.00 |
| 1008 | 2 | 2026-04-01 | Delivered | 75.00 |
| 1009 | 4 | 2026-04-22 | Delivered | 250.00 |
| 1010 | 1 | 2026-05-09 | Shipped | 300.00 |
| 1011 | 3 | 2026-05-30 | Cancelled | 400.00 |
| 1012 | 7 | 2026-06-11 | Delivered | 50.00 |
And here are the payments. Notice that order 1003 was paid in two parts, payment 3 and payment 4. That is the tie we hit in question 2.
| payment_id | order_id | paid_amount |
|---|---|---|
| 1 | 1001 | 1250.00 |
| 2 | 1002 | 300.00 |
| 3 | 1003 | 400.00 |
| 4 | 1003 | 250.00 |
| 5 | 1005 | 1200.00 |
Question 1: Finance wants a running total of revenue by order date. How do you calculate it without a self join or a subquery?
SUM as a window function, with ORDER BY inside the OVER clause. Each row gets its own amount plus every row before it. No self join, no subquery and no GROUP BY.
Let's calculate it over every order that is not cancelled.
SELECT order_date, amount,
SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders
WHERE status <> 'Cancelled'
ORDER BY order_date;| order_date | amount | running_total |
|---|---|---|
| 2026-01-05 | 1250.00 | 1250.00 |
| 2026-01-18 | 300.00 | 1550.00 |
| 2026-02-02 | 650.00 | 2200.00 |
| 2026-02-20 | 1200.00 | 3400.00 |
| 2026-03-03 | 275.00 | 3675.00 |
| 2026-03-15 | 400.00 | 4075.00 |
| 2026-04-01 | 75.00 | 4150.00 |
| 2026-04-22 | 250.00 | 4400.00 |
| 2026-05-09 | 300.00 | 4700.00 |
| 2026-06-11 | 50.00 | 4750.00 |
The first order is 1250.00. The second adds 300.00, so the running total is 1550.00, and so on down the dates.
Here is a sanity check you should always do in a real project: the last running total, 4750.00, must equal the total revenue for the period. If it does not, a filter or a join is wrong somewhere.
Remember what ORDER BY does inside OVER. It does not only sort. It also sets a frame: with ORDER BY and no frame written, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, from the first row up to the current row. That default frame is the trap in the next question.
Question 2: Your running total of payments shows the same 2,200 on two rows. What is the difference between ROWS and RANGE, and how do you fix the running total?
ROWS counts physical rows. RANGE includes every row with the same ORDER BY value as the current row, its peers. The default frame is RANGE, so tied rows are added together.
First, the per customer version, which is one more clause: PARTITION BY. The running total restarts for every customer.
SELECT c.name, o.order_date, o.amount,
SUM(o.amount) OVER (
PARTITION BY o.customer_id
ORDER BY o.order_date
) AS customer_running
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.status <> 'Cancelled'
ORDER BY c.name, o.order_date;| name | order_date | amount | customer_running |
|---|---|---|---|
| Lina Park | 2026-02-20 | 1200.00 | 1200.00 |
| Mei Chen | 2026-02-02 | 650.00 | 650.00 |
| Mei Chen | 2026-03-03 | 275.00 | 925.00 |
| Mei Chen | 2026-04-22 | 250.00 | 1175.00 |
| Omar Ali | 2026-01-18 | 300.00 | 300.00 |
| Omar Ali | 2026-04-01 | 75.00 | 375.00 |
| Ravi Patel | 2026-03-15 | 400.00 | 400.00 |
| Sara K. | 2026-06-11 | 50.00 | 50.00 |
| Sara Khan | 2026-01-05 | 1250.00 | 1250.00 |
| Sara Khan | 2026-05-09 | 300.00 | 1550.00 |
Mei Chen goes 650.00, 925.00, 1175.00. Then Omar Ali starts again from 300.00, and goes to 375.00.
Now the trap. Let's run a running total over the payments, ordered by order ID, once with the default frame and once with ROWS.
SELECT payment_id, order_id, paid_amount,
SUM(paid_amount) OVER (
ORDER BY order_id
) AS default_frame,
SUM(paid_amount) OVER (
ORDER BY order_id, payment_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS rows_frame
FROM payments
ORDER BY payment_id;| payment_id | order_id | paid_amount | default_frame | rows_frame |
|---|---|---|---|---|
| 1 | 1001 | 1250.00 | 1250.00 | 1250.00 |
| 2 | 1002 | 300.00 | 1550.00 | 1550.00 |
| 3 | 1003 | 400.00 | 2200.00 | 1950.00 |
| 4 | 1003 | 250.00 | 2200.00 | 2200.00 |
| 5 | 1005 | 1200.00 | 3400.00 | 3400.00 |
Payments 3 and 4 both belong to order 1003, so in the default RANGE frame they are peers. The frame takes both at once, and both rows show 2200.00. The rows_frame column goes 1950.00, then 2200.00, which is what a running total should do.
We made the ORDER BY unique with payment_id and wrote ROWS explicitly. ROWS alone splits the tie, but in no guaranteed order.
Question 3: A manager wants month-over-month revenue change. How do you use LAG and LEAD, and what is the trap when a month has no sales?
LAG reads a value from the previous row, and LEAD from the next row, in the order you give them. The change is simply revenue minus LAG of revenue.
First a CTE with revenue per month. DATE_TRUNC turns every date into the first day of its month. That is PostgreSQL. In SQL Server, use DATETRUNC(month, order_date) on 2022 and later, or DATEFROMPARTS on older versions.
WITH monthly AS (
SELECT CAST(DATE_TRUNC('month', order_date) AS DATE)
AS order_month,
SUM(amount) AS revenue
FROM orders
WHERE status <> 'Cancelled'
GROUP BY CAST(DATE_TRUNC('month', order_date) AS DATE)
)
SELECT order_month, revenue,
LAG(revenue) OVER (ORDER BY order_month) AS prev_revenue,
LEAD(revenue) OVER (ORDER BY order_month) AS next_revenue,
revenue - LAG(revenue) OVER (ORDER BY order_month)
AS mom_change
FROM monthly
ORDER BY order_month;| order_month | revenue | prev_revenue | next_revenue | mom_change |
|---|---|---|---|---|
| 2026-01-01 | 1550.00 | NULL | 1850.00 | NULL |
| 2026-02-01 | 1850.00 | 1550.00 | 675.00 | 300.00 |
| 2026-03-01 | 675.00 | 1850.00 | 325.00 | -1175.00 |
| 2026-04-01 | 325.00 | 675.00 | 300.00 | -350.00 |
| 2026-05-01 | 300.00 | 325.00 | 50.00 | -25.00 |
| 2026-06-01 | 50.00 | 300.00 | NULL | -250.00 |
February made 1850.00 and January 1550.00, so the change is 300.00. March dropped by 1175.00. January has no previous month, so LAG returns NULL, and June has no next month, so LEAD returns NULL. If you want 0 instead, LAG(revenue, 1, 0) takes a default.
LAG means the previous row, not the previous month. If a month had no sales, it would not exist in the CTE, and LAG would compare with two months back. In a real report, join to a calendar of months first.
Question 4: The business wants a 3-month moving average of revenue. How do you write it, and what happens in the first two months?
AVG as a window function, with an explicit frame: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW. That is this month plus the two before it.
Let's calculate it, and add a COUNT(*) over the same frame so we can see how many months each average really uses.
WITH monthly AS (
SELECT CAST(DATE_TRUNC('month', order_date) AS DATE)
AS order_month,
SUM(amount) AS revenue
FROM orders
WHERE status <> 'Cancelled'
GROUP BY CAST(DATE_TRUNC('month', order_date) AS DATE)
)
SELECT order_month, revenue,
CAST(AVG(revenue) OVER (
ORDER BY order_month
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS DECIMAL(10,2)) AS moving_avg_3m,
COUNT(*) OVER (
ORDER BY order_month
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS months_used
FROM monthly
ORDER BY order_month;| order_month | revenue | moving_avg_3m | months_used |
|---|---|---|---|
| 2026-01-01 | 1550.00 | 1550.00 | 1 |
| 2026-02-01 | 1850.00 | 1700.00 | 2 |
| 2026-03-01 | 675.00 | 1358.33 | 3 |
| 2026-04-01 | 325.00 | 950.00 | 3 |
| 2026-05-01 | 300.00 | 433.33 | 3 |
| 2026-06-01 | 50.00 | 225.00 | 3 |
March averages January, February and March: 1358.33. April drops January and adds April: 950.00.
Now look at the first two rows. January averages only one month, so it is 1550.00, and February averages two months, 1700.00. The months_used column makes that visible: 1, then 2, then 3 from March on. If the business wants a true 3-month average, return NULL until months_used is 3.
Same warning as LAG: ROWS counts rows, not calendar months, so fill the missing months first. In a real project, this is the line you put on a dashboard to smooth a noisy trend.
Question 5: Finance wants each category's percent of total revenue in one query, with no subquery. How do you do it with SUM() OVER ()?
Group by category as usual, then divide each group's revenue by SUM(SUM(...)) OVER (). The inner SUM is the normal group total. The outer SUM is a window over all the grouped rows.
SELECT p.category,
SUM(oi.qty * oi.unit_price) AS revenue,
SUM(SUM(oi.qty * oi.unit_price)) OVER () AS total_revenue,
CAST(100.0 * SUM(oi.qty * oi.unit_price)
/ SUM(SUM(oi.qty * oi.unit_price)) OVER ()
AS DECIMAL(5,2)) AS pct_of_total
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
JOIN products p ON p.product_id = oi.product_id
WHERE o.status <> 'Cancelled'
GROUP BY p.category
ORDER BY revenue DESC;| category | revenue | total_revenue | pct_of_total |
|---|---|---|---|
| Electronics | 3000.00 | 4750.00 | 63.16 |
| Furniture | 1550.00 | 4750.00 | 32.63 |
| Accessories | 200.00 | 4750.00 | 4.21 |
Every row gets the grand total, 4750.00. Electronics is 63.16 percent, Furniture 32.63 and Accessories 4.21.
Why does SUM of SUM work? Because window functions run after GROUP BY. That is the logical query order from Part 1 of this series, and interviewers like to hear you connect the two. An empty OVER () means one window over all the rows.
Multiply by 100.0, so integer columns do not do integer division. And in a real report, check that the percentages add up to 100.
Question 6: You use LAST_VALUE to show the lowest-paid person in each department, but every row just shows itself. Why does LAST_VALUE seem not to work, and what is the fix?
It is the default frame again. With ORDER BY, the frame ends at the current row, or at its last tie, so the last value in the frame is the current row itself.
For each employee in Sales and Engineering, we want the top paid and the lowest paid person in the department. Let's compute FIRST_VALUE, LAST_VALUE with the default frame, and LAST_VALUE with the full frame.
SELECT dept_id, name, salary,
FIRST_VALUE(name) OVER (
PARTITION BY dept_id ORDER BY salary DESC, emp_id
) AS top_paid,
LAST_VALUE(name) OVER (
PARTITION BY dept_id ORDER BY salary DESC, emp_id
) AS last_default,
LAST_VALUE(name) OVER (
PARTITION BY dept_id ORDER BY salary DESC, emp_id
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS lowest_paid
FROM employees
WHERE dept_id IN (10, 20)
ORDER BY dept_id, salary DESC, emp_id;| dept_id | name | salary | top_paid | last_default | lowest_paid |
|---|---|---|---|---|---|
| 10 | Daniel | 105000 | Daniel | Daniel | Fatima |
| 10 | Sara | 95000 | Daniel | Sara | Fatima |
| 10 | Fatima | 60000 | Daniel | Fatima | Fatima |
| 20 | Omar | 120000 | Omar | Omar | Mei |
| 20 | Lina | 90000 | Omar | Lina | Mei |
| 20 | Ravi | 90000 | Omar | Ravi | Mei |
| 20 | Mei | 75000 | Omar | Mei | Mei |
FIRST_VALUE, ordered by salary descending, gives the top paid: Daniel in Sales and Omar in Engineering, correct on every row. But look at last_default: every row just returns its own name. Our ORDER BY is unique because of the emp_id tie-breaker, so the frame ends exactly at the current row.
The fix is ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, the whole partition. Now lowest_paid shows Fatima on every Sales row and Mei on every Engineering row.
Use FIRST_VALUE with the order reversed, ORDER BY salary ASC. The default frame always includes the first row.
Scenario 7: Leadership wants one monthly report with revenue, the previous month, the change and the percent growth. How do you write it so a zero month can't break it?
Here is how this goes in a real project. Your manager asks for every month with the revenue, the previous month's revenue, the change and the percent growth, in one query.
Build it in layers, because it is easier to read and to debug:
- CTE one,
monthly: revenue per month. - CTE two,
with_prev: add the previous month with LAG. - The final SELECT does the math on plain columns, so LAG is written only once.
Percent growth is the change divided by the previous revenue, times 100.0. NULLIF(prev_revenue, 0) turns a zero into NULL, so a month after a zero month returns NULL instead of a divide by zero error.
WITH monthly AS (
SELECT CAST(DATE_TRUNC('month', order_date) AS DATE)
AS order_month,
SUM(amount) AS revenue
FROM orders
WHERE status <> 'Cancelled'
GROUP BY CAST(DATE_TRUNC('month', order_date) AS DATE)
), with_prev AS (
SELECT order_month, revenue,
LAG(revenue) OVER (ORDER BY order_month)
AS prev_revenue
FROM monthly
)
SELECT order_month, revenue, prev_revenue,
revenue - prev_revenue AS mom_change,
CAST(100.0 * (revenue - prev_revenue)
/ NULLIF(prev_revenue, 0) AS DECIMAL(10,2))
AS pct_growth
FROM with_prev
ORDER BY order_month;| order_month | revenue | prev_revenue | mom_change | pct_growth |
|---|---|---|---|---|
| 2026-01-01 | 1550.00 | NULL | NULL | NULL |
| 2026-02-01 | 1850.00 | 1550.00 | 300.00 | 19.35 |
| 2026-03-01 | 675.00 | 1850.00 | -1175.00 | -63.51 |
| 2026-04-01 | 325.00 | 675.00 | -350.00 | -51.85 |
| 2026-05-01 | 300.00 | 325.00 | -25.00 | -7.69 |
| 2026-06-01 | 50.00 | 300.00 | -250.00 | -83.33 |
February grew 19.35 percent. March fell 63.51 percent, and June fell 83.33 percent.
Two things to tell the manager:
- January is NULL on purpose. There is no previous month, so growth is unknown, not 0.
- If a month ever has no sales, join to a calendar first, so LAG compares the right months.
Key points to remember
- SUM() OVER (ORDER BY ...) = running total; PARTITION BY restarts it
- Default frame is RANGE: ties add up together. Write ROWS
- LAG / LEAD read the previous / next row, not month
- Frames drive moving averages and fix LAST_VALUE
- SUM(SUM(x)) OVER () = percent of total; NULLIF for growth
The full SQL script
Here is everything in one script: the sample data and every query from this post, in order. Copy it and run it top to bottom.
Show the full SQL script (sample data and every query)
-- ========================================================================
-- SQL Interview Q&A #6 - Running totals, LAG and LEAD
-- TechBrothersIT. Sample data is invented for teaching.
-- Standard SQL, tested on PostgreSQL 16 (and cross-checked on DuckDB). Runs top to bottom in one go.
-- Run it in an empty database or schema: other videos reuse table names
-- like dim_customer, and their leftover tables can block the DROPs below.
-- PostgreSQL example: CREATE SCHEMA sql_q06; SET search_path TO sql_q06;
-- ========================================================================
-- Step 0: create and load the sample data
DROP TABLE IF EXISTS payments;
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS departments;
CREATE TABLE departments (dept_id INT PRIMARY KEY, dept_name VARCHAR(20));
INSERT INTO departments VALUES (10, 'Sales'), (20, 'Engineering'), (30, 'HR'), (40, 'Finance');
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
name VARCHAR(20),
dept_id INT, -- NULL = not assigned yet
manager_id INT, -- NULL = top of the company
salary INT,
hire_date DATE
);
INSERT INTO employees VALUES
(1, 'Aisha', NULL, NULL, 150000, '2018-03-01'),
(2, 'Omar', 20, 1, 120000, '2019-06-15'),
(3, 'Lina', 20, 2, 90000, '2021-01-10'),
(4, 'Ravi', 20, 2, 90000, '2022-04-05'),
(5, 'Mei', 20, 2, 75000, '2024-09-01'),
(6, 'Sara', 10, 1, 95000, '2020-02-20'),
(7, 'Daniel', 10, 6, 105000, '2021-07-01'),
(8, 'Fatima', 10, 6, 60000, '2023-11-12'),
(9, 'John', 30, 1, 70000, '2019-09-30'),
(10,'Zara', NULL, 9, 55000, '2026-01-05');
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(20),
city VARCHAR(20),
region VARCHAR(10), -- NULL = unknown
email VARCHAR(40)
);
INSERT INTO customers VALUES
(1, 'Sara Khan', 'Toronto', 'East', 'sara@mail.com'),
(2, 'Omar Ali', 'Dallas', 'West', 'omar@mail.com'),
(3, 'Lina Park', 'Seattle', 'West', 'lina@mail.com'),
(4, 'Mei Chen', 'Boston', 'East', 'mei@mail.com'),
(5, 'Ravi Patel', 'Austin', NULL, 'ravi@mail.com'),
(6, 'Chen Wu', 'Denver', 'West', 'chen@mail.com'),
(7, 'Sara K.', 'Toronto', 'East', 'sara@mail.com');
CREATE TABLE products (product_id INT PRIMARY KEY, product VARCHAR(20), category VARCHAR(15), price DECIMAL(10,2));
INSERT INTO products VALUES
(1, 'Laptop', 'Electronics', 1200.00),
(2, 'Monitor', 'Electronics', 300.00),
(3, 'Keyboard', 'Accessories', 50.00),
(4, 'Mouse', 'Accessories', 25.00),
(5, 'Desk', 'Furniture', 400.00),
(6, 'Chair', 'Furniture', 250.00);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
status VARCHAR(10),
amount DECIMAL(10,2)
);
INSERT INTO orders VALUES
(1001, 1, '2026-01-05', 'Delivered', 1250.00),
(1002, 2, '2026-01-18', 'Delivered', 300.00),
(1003, 4, '2026-02-02', 'Delivered', 650.00),
(1004, 1, '2026-02-14', 'Cancelled', 50.00),
(1005, 3, '2026-02-20', 'Delivered', 1200.00),
(1006, 4, '2026-03-03', 'Shipped', 275.00),
(1007, 5, '2026-03-15', 'Delivered', 400.00),
(1008, 2, '2026-04-01', 'Delivered', 75.00),
(1009, 4, '2026-04-22', 'Delivered', 250.00),
(1010, 1, '2026-05-09', 'Shipped', 300.00),
(1011, 3, '2026-05-30', 'Cancelled', 400.00),
(1012, 7, '2026-06-11', 'Delivered', 50.00);
CREATE TABLE order_items (order_id INT, product_id INT, qty INT, unit_price DECIMAL(10,2));
INSERT INTO order_items VALUES
(1001, 1, 1, 1200.00), (1001, 3, 1, 50.00),
(1002, 2, 1, 300.00),
(1003, 5, 1, 400.00), (1003, 6, 1, 250.00),
(1004, 3, 1, 50.00),
(1005, 1, 1, 1200.00),
(1006, 6, 1, 250.00), (1006, 4, 1, 25.00),
(1007, 5, 1, 400.00),
(1008, 4, 3, 25.00),
(1009, 6, 1, 250.00),
(1010, 2, 1, 300.00),
(1011, 5, 1, 400.00),
(1012, 3, 1, 50.00);
CREATE TABLE payments (payment_id INT PRIMARY KEY, order_id INT, paid_amount DECIMAL(10,2));
INSERT INTO payments VALUES
(1, 1001, 1250.00), (2, 1002, 300.00), (3, 1003, 400.00), (4, 1003, 250.00), (5, 1005, 1200.00);
-- Step 1: Running total of revenue by date
SELECT order_date, amount,
SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders
WHERE status <> 'Cancelled'
ORDER BY order_date;
-- Step 2: Running total per customer (PARTITION BY)
SELECT c.name, o.order_date, o.amount,
SUM(o.amount) OVER (
PARTITION BY o.customer_id
ORDER BY o.order_date
) AS customer_running
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.status <> 'Cancelled'
ORDER BY c.name, o.order_date;
-- Step 3: The tie trap: default RANGE frame vs ROWS
SELECT payment_id, order_id, paid_amount,
SUM(paid_amount) OVER (
ORDER BY order_id
) AS default_frame,
SUM(paid_amount) OVER (
ORDER BY order_id, payment_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS rows_frame
FROM payments
ORDER BY payment_id;
-- Step 4: LAG and LEAD: month-over-month revenue change
WITH monthly AS (
SELECT CAST(DATE_TRUNC('month', order_date) AS DATE)
AS order_month,
SUM(amount) AS revenue
FROM orders
WHERE status <> 'Cancelled'
GROUP BY CAST(DATE_TRUNC('month', order_date) AS DATE)
)
SELECT order_month, revenue,
LAG(revenue) OVER (ORDER BY order_month) AS prev_revenue,
LEAD(revenue) OVER (ORDER BY order_month) AS next_revenue,
revenue - LAG(revenue) OVER (ORDER BY order_month)
AS mom_change
FROM monthly
ORDER BY order_month;
-- Step 5: 3-month moving average
WITH monthly AS (
SELECT CAST(DATE_TRUNC('month', order_date) AS DATE)
AS order_month,
SUM(amount) AS revenue
FROM orders
WHERE status <> 'Cancelled'
GROUP BY CAST(DATE_TRUNC('month', order_date) AS DATE)
)
SELECT order_month, revenue,
CAST(AVG(revenue) OVER (
ORDER BY order_month
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS DECIMAL(10,2)) AS moving_avg_3m,
COUNT(*) OVER (
ORDER BY order_month
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS months_used
FROM monthly
ORDER BY order_month;
-- Step 6: Percent of total with SUM() OVER () - no subquery
SELECT p.category,
SUM(oi.qty * oi.unit_price) AS revenue,
SUM(SUM(oi.qty * oi.unit_price)) OVER () AS total_revenue,
CAST(100.0 * SUM(oi.qty * oi.unit_price)
/ SUM(SUM(oi.qty * oi.unit_price)) OVER ()
AS DECIMAL(5,2)) AS pct_of_total
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
JOIN products p ON p.product_id = oi.product_id
WHERE o.status <> 'Cancelled'
GROUP BY p.category
ORDER BY revenue DESC;
-- Step 7: FIRST_VALUE, and why LAST_VALUE "does not work" with the default frame
SELECT dept_id, name, salary,
FIRST_VALUE(name) OVER (
PARTITION BY dept_id ORDER BY salary DESC, emp_id
) AS top_paid,
LAST_VALUE(name) OVER (
PARTITION BY dept_id ORDER BY salary DESC, emp_id
) AS last_default,
LAST_VALUE(name) OVER (
PARTITION BY dept_id ORDER BY salary DESC, emp_id
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS lowest_paid
FROM employees
WHERE dept_id IN (10, 20)
ORDER BY dept_id, salary DESC, emp_id;
-- Step 8: Scenario: monthly revenue, previous month, change and percent growth
WITH monthly AS (
SELECT CAST(DATE_TRUNC('month', order_date) AS DATE)
AS order_month,
SUM(amount) AS revenue
FROM orders
WHERE status <> 'Cancelled'
GROUP BY CAST(DATE_TRUNC('month', order_date) AS DATE)
), with_prev AS (
SELECT order_month, revenue,
LAG(revenue) OVER (ORDER BY order_month)
AS prev_revenue
FROM monthly
)
SELECT order_month, revenue, prev_revenue,
revenue - prev_revenue AS mom_change,
CAST(100.0 * (revenue - prev_revenue)
/ NULLIF(prev_revenue, 0) AS DECIMAL(10,2))
AS pct_growth
FROM with_prev
ORDER BY order_month;
Part 7: Duplicates and Data Cleaning
The sample data on this page is invented for teaching. Every output table is the real result of the query above it, run on PostgreSQL 16 over that sample data, and the portable queries were also checked on a second engine. Where SQL Server syntax is different, the post says so.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.