PostgreSQL Interview Questions and Answers Part 3 - Window functions

PostgreSQL Interview Q&A · Part 3 of 10

In this post we are going to cover window functions in PostgreSQL: ROW_NUMBER, RANK and DENSE_RANK, the top N rows per group, running totals, LAG and LEAD, LAST_VALUE, percent of total, and the gaps and islands pattern. These are 7 real PostgreSQL interview questions, asked the way they come up on the job.

A window function calculates across a set of rows that are related to the current row, but it does not collapse them like GROUP BY does. Every row stays, and gets an extra value. Most interview questions are about the three parts of the OVER clause: PARTITION BY, ORDER BY and the frame.

Every query below is complete, so you can copy it, run it on the sample data and get exactly the output shown under it.

VIDEO COMING SOON

Part 3 of the Real Time PostgreSQL Interview Questions and Answers series on TechBrothersIT.

On this page
  1. Let's create some sample data
  2. Question 1: HR wants to rank the Engineering team by salary. Lina and Ravi earn exactly the same. What is the difference between ROW_NUMBER, RANK and DENSE_RANK, and which one do you use?
  3. Question 2: The CFO wants the two highest paid employees in each department. How do you write it, and why can't you just filter on the row number in WHERE?
  4. Question 3: Finance wants a running total of sales by date. Two sales on June 2nd both show the same running total, 350. Is that a bug?
  5. Question 4: The sales director wants each month's sales next to the previous month's sales, and the change between them. How do you do it without a self join?
  6. Question 5: You used FIRST_VALUE to show the top earner of the department on every row, and it works. Then you used LAST_VALUE for the lowest earner, and every row just shows its own name. Why?
  7. Question 6: A product manager wants each product's sales, its share of its category, and its share of total sales, all in the same row. How do you do it without joining the table to itself?
  8. Question 7: The product team wants every streak of consecutive login days for each user, with its start, its end and its length. How do you find the streaks in PostgreSQL?
  9. Key points to remember
  10. The full SQL script

Let's create some sample data

We use a small shop database called TB Shop: departments, employees, customers, products, orders, order items and payments. For this post we add two small tables: daily_sales, with two sales on the same day, and logins, with one row per user per login day. The script below creates everything this post needs. It was written for and run on PostgreSQL 16.

Sample data - SQL script (PostgreSQL)
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 NUMERIC(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      NUMERIC(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 NUMERIC(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 NUMERIC(10,2));
INSERT INTO payments VALUES
  (1, 1001, 1250.00), (2, 1002, 300.00),
  (3, 1003,  400.00), (4, 1003, 250.00),   -- order 1003 was paid in two parts
  (5, 1005, 1200.00), (6, 1007, 400.00),
  (7, 1008,   75.00), (8, 1009, 250.00), (9, 1012, 50.00);

CREATE TABLE daily_sales (sale_id INT PRIMARY KEY, sale_date DATE, amount INT);
INSERT INTO daily_sales VALUES
  (1, '2026-06-01', 100), (2, '2026-06-02', 200), (3, '2026-06-02', 50),   -- two sales on June 2
  (4, '2026-06-03', 300), (5, '2026-06-04', 150);

CREATE TABLE logins (user_id INT, login_date DATE);
INSERT INTO logins VALUES
  (1, '2026-06-01'), (1, '2026-06-02'), (1, '2026-06-03'), (1, '2026-06-05'), (1, '2026-06-06'),
  (2, '2026-06-02'), (2, '2026-06-03'), (2, '2026-06-04'), (2, '2026-06-05'), (2, '2026-06-08');

Question 1: HR wants to rank the Engineering team by salary. Lina and Ravi earn exactly the same. What is the difference between ROW_NUMBER, RANK and DENSE_RANK, and which one do you use?

Say this first

They differ only on ties. ROW_NUMBER always gives 1, 2, 3, 4. RANK gives tied rows the same number and then skips: 1, 2, 2, 4. DENSE_RANK gives the same number and does not skip: 1, 2, 2, 3.

Here is Engineering, sorted by salary. ROW_NUMBER just counts: one, two, three, four. Lina and Ravi get different numbers, even though they tie. RANK gives them both a two, and then jumps to four, because two people are ahead of Mei. DENSE_RANK also gives them both a two, but Mei is three. No gap.

Engineering, ORDER BY salary DESC
namesalaryROW_NUMBERRANKDENSE_RANK
Omar120000111
Lina90000222
Ravi90000322
Mei75000443
ROW_NUMBERunique numbers, ties broken arbitrarily
RANKties share a number, then a gap
DENSE_RANKties share a number, no gap

And here is the real query, with all three functions over the same window. Look at Lina and Ravi: three different answers for the same two rows.

SQL - three rankings
SELECT name, salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
  RANK()       OVER (ORDER BY salary DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
FROM employees
WHERE dept_id = 20;
Output (PostgreSQL 16)4 rows
namesalaryrow_numrnkdense_rnk
Omar120000111
Lina90000222
Ravi90000322
Mei75000443

Which one do you use? ROW_NUMBER when you need exactly one row each, like removing duplicates. RANK for competition style ranking. DENSE_RANK for questions like the second highest salary.

One warning. With ROW_NUMBER, the order of tied rows is not guaranteed. Add a tiebreaker, like the employee ID, to the ORDER BY.

Interview tip

ROW_NUMBER: exactly one row each. RANK: competition ranking. DENSE_RANK: "second highest salary".

Ties + ROW_NUMBER = random order. Add a tiebreaker: ORDER BY salary DESC, emp_id.

Question 2: The CFO wants the two highest paid employees in each department. How do you write it, and why can't you just filter on the row number in WHERE?

Say this first

Number the rows inside each department with ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) in a CTE, then filter on that number outside. Window functions run after WHERE, so WHERE cannot see them.

PARTITION BY works like separate buckets. Postgres puts the employees into one bucket per department. Inside each bucket it sorts by salary and starts counting from one again. Then the outer query keeps only the rows numbered one and two.

Engineering
namesalaryrn
Omar1200001
Lina900002
Ravi900003
Mei750004
Sales
namesalaryrn
Daniel1050001
Sara950002
Fatima600003
HR
namesalaryrn
John700001
PARTITION BY dept_idone bucket per department
ORDER BY salary DESCthe count restarts in each bucket
WHERE rn <= 2in the outer query

Here is the query. The CTE adds the row number. The outer query filters on it. Two rows for Engineering, two for Sales, and one for HR, which has only one employee.

SQL - top2 per dept
WITH ranked AS (
  SELECT d.dept_name, e.name, e.salary,
         ROW_NUMBER() OVER (PARTITION BY e.dept_id
                            ORDER BY e.salary DESC, e.emp_id) AS rn
  FROM employees e
  JOIN departments d ON d.dept_id = e.dept_id
)
SELECT dept_name, name, salary, rn
FROM ranked
WHERE rn <= 2
ORDER BY dept_name, rn;
Output (PostgreSQL 16)5 rows
dept_namenamesalaryrn
EngineeringOmar1200001
EngineeringLina900002
HRJohn700001
SalesDaniel1050001
SalesSara950002

If you try the filter in the same SELECT, Postgres refuses. WHERE runs before the window functions are calculated.

SQL - window in where
SELECT name, salary
FROM employees
WHERE ROW_NUMBER() OVER (PARTITION BY dept_id
                         ORDER BY salary DESC) <= 2;
Error (PostgreSQL 16)ERROR: window functions are not allowed in WHERE

And think about ties. Lina and Ravi both earn 90,000, and ROW_NUMBER kept only one of them. If both should appear, use DENSE_RANK instead.

Interview tip

Should tied rows all appear? Use DENSE_RANK instead of ROW_NUMBER.

Question 3: Finance wants a running total of sales by date. Two sales on June 2nd both show the same running total, 350. Is that a bug?

Say this first

It is the default frame. With ORDER BY, the default is RANGE up to the current row, and RANGE includes every peer, every row with the same date. Write ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW to add one row at a time.

Watch the second row, the first sale on June 2nd. With ROWS, the frame is this row and everything above it: 100 plus 200 is 300. With RANGE, the frame also takes in the other June 2nd row, its peer, so the total is already 350. That is why both June 2nd rows show the same number.

ROWS: one row at a time
frame for row 2
sale_dateamount
Jun 01100
Jun 02200
Jun 0250
Jun 03300
Jun 04150
100 + 200 = 300
RANGE: peers come along
frame for row 2
sale_dateamount
Jun 01100
Jun 02200
Jun 0250
Jun 03300
Jun 04150
100 + 200 + 50 = 350
Peers = rows with the same ORDER BY value.RANGE treats them as one step

Here are both frames side by side in one query. The first column uses the default frame, and the second one uses ROWS. Only the June 2nd rows are different: 350 and 350, against 300 and 350.

SQL - running totals
SELECT sale_date, amount,
  SUM(amount) OVER (ORDER BY sale_date) AS default_frame,
  SUM(amount) OVER (ORDER BY sale_date, sale_id
                    ROWS BETWEEN UNBOUNDED PRECEDING
                             AND CURRENT ROW) AS rows_frame
FROM daily_sales
ORDER BY sale_date, sale_id;
Output (PostgreSQL 16)5 rows
sale_dateamountdefault_framerows_frame
2026-06-01100100100
2026-06-02200350300
2026-06-0250350350
2026-06-03300650650
2026-06-04150800800

So it is not a bug, it is the definition. But for a running total, always write the ROWS frame, and add a unique column to the ORDER BY, so the result is the same on every run.

Interview tip

Running total = ROWS frame + a unique tiebreaker in ORDER BY.

Question 4: The sales director wants each month's sales next to the previous month's sales, and the change between them. How do you do it without a self join?

Say this first

Use LAG. It reads a value from the previous row of the window, in the order you give it. LEAD does the same for the next row. The first row has no previous row, so it gets NULL.

Each row looks one step back. February reaches up to January and brings down 1550. March reaches up to February and brings down 1900. January has nothing above it, so its previous value is NULL.

monthly sales
monthsalesLAG(sales)
Jan1550.00NULL
Feb1900.001550.00
Mar675.001900.00
Apr325.00675.00
LAG(sales) OVER (ORDER BY month)
LAG = one row backLEAD = one row forward
First row: NULLor give a default: LAG(sales, 1, 0)

Here is the real query. A CTE sums the orders per month. Then LAG brings the previous month into the same row, and a simple subtraction gives the change.

SQL - month over month
WITH monthly AS (
  SELECT date_trunc('month', order_date)::date AS month,
         SUM(amount) AS sales
  FROM orders
  GROUP BY 1
)
SELECT month, sales,
       LAG(sales) OVER (ORDER BY month) AS prev_sales,
       sales - LAG(sales) OVER (ORDER BY month) AS change
FROM monthly
ORDER BY month;
Output (PostgreSQL 16)6 rows
monthsalesprev_saleschange
2026-01-011550.00NULLNULL
2026-02-011900.001550.00350.00
2026-03-01675.001900.00-1225.00
2026-04-01325.00675.00-350.00
2026-05-01700.00325.00375.00
2026-06-0150.00700.00-650.00

February is up 350. March is down 1225. And January shows NULL for both, because there is no earlier month. That NULL is correct. Do not replace it with zero, or the first month will look like growth from nothing.

Interview tip

The first row is NULL on purpose. A zero would look like real growth.

Question 5: You used FIRST_VALUE to show the top earner of the department on every row, and it works. Then you used LAST_VALUE for the lowest earner, and every row just shows its own name. Why?

Say this first

Again the default frame. It ends at the current row, so the last value in the frame is the current row itself. Extend the frame to the end: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

Take the Sales team, sorted by salary. For Sara, the default frame is only Daniel and Sara. FIRST_VALUE finds Daniel, which is right. But LAST_VALUE finds Sara herself, because the frame stops at her row. Open the frame to the end of the partition, and now the last value is Fatima.

default frame, current row = Sara
Sales, ORDER BY salary DESC
namesalary
Daniel105000
Sara95000
Fatima60000
FIRST_VALUE = Daniel ✓LAST_VALUE = Sara ✗
frame to UNBOUNDED FOLLOWING
Sales, ORDER BY salary DESC
namesalary
Daniel105000
Sara95000
Fatima60000
LAST_VALUE = Fatima ✓

Here it is on real data. The column with the default frame returns each employee's own name. The column with the full frame returns Fatima on every row.

SQL - last value frames
SELECT name, salary,
  FIRST_VALUE(name) OVER (ORDER BY salary DESC) AS top_earner,
  LAST_VALUE(name)  OVER (ORDER BY salary DESC) AS last_default,
  LAST_VALUE(name)  OVER (ORDER BY salary DESC
                          ROWS BETWEEN UNBOUNDED PRECEDING
                                   AND UNBOUNDED FOLLOWING) AS last_full
FROM employees
WHERE dept_id = 10
ORDER BY salary DESC;
Output (PostgreSQL 16)3 rows
namesalarytop_earnerlast_defaultlast_full
Daniel105000DanielDanielFatima
Sara95000DanielSaraFatima
Fatima60000DanielFatimaFatima

A simpler way, that many teams prefer: flip the sort order and use FIRST_VALUE. And the same frame rule applies to NTH_VALUE and to aggregates like MAX used as window functions.

Interview tip

Simpler: reverse the ORDER BY and use FIRST_VALUE.

Question 6: A product manager wants each product's sales, its share of its category, and its share of total sales, all in the same row. How do you do it without joining the table to itself?

Say this first

Use SUM as a window function. OVER (PARTITION BY category) gives the category total on every row, and an empty OVER () gives the grand total. The rows are not collapsed, so you can divide right there.

GROUP BY would squeeze each category into one row. A window does not. With PARTITION BY category, every row can see the total of its own category: 250 for Accessories, 3000 for Electronics. With an empty OVER, the window is the whole result, so every row can see the grand total. Now each share is a simple division.

sales per product
categoryproductsales
AccessoriesKeyboard150.00
AccessoriesMouse100.00
ElectronicsLaptop2400.00
ElectronicsMonitor600.00
SUM(sales) OVER (PARTITION BY category)
Accessories = 250Electronics = 3000
SUM(sales) OVER ()
all rows = 5200
Laptop: 2400 / 3000 = 80% of Electronics2400 / 5200 = 46.2% of total

Here is the real query. The CTE gets sales per product. Then two window sums, one per category and one over everything, turn into two percentages.

SQL - share of sales
WITH s AS (
  SELECT p.category, p.product,
         SUM(oi.qty * oi.unit_price) AS sales
  FROM order_items oi
  JOIN products p ON p.product_id = oi.product_id
  GROUP BY p.category, p.product
)
SELECT category, product, sales,
  ROUND(100 * sales / SUM(sales) OVER (PARTITION BY category), 1) AS pct_category,
  ROUND(100 * sales / SUM(sales) OVER (), 1) AS pct_total
FROM s
ORDER BY category, product;
Output (PostgreSQL 16)6 rows
categoryproductsalespct_categorypct_total
AccessoriesKeyboard150.0060.02.9
AccessoriesMouse100.0040.01.9
ElectronicsLaptop2400.0080.046.2
ElectronicsMonitor600.0020.011.5
FurnitureChair750.0038.514.4
FurnitureDesk1200.0061.523.1

The laptop is 80 percent of Electronics, and 46.2 percent of all sales. No self join, and the table is read only once.

Interview tip

Window = totals next to the detail rows. GROUP BY = totals instead of them.

Question 7: The product team wants every streak of consecutive login days for each user, with its start, its end and its length. How do you find the streaks in PostgreSQL?

Say this first

This is gaps and islands. Number the rows per user by date, then subtract that number from the date. Inside a streak, the date and the row number grow together, so the result is constant. Group by it.

Here are the logins of user 1. ROW_NUMBER counts them: one to five. Now subtract the row number, as days, from the date. June 1st minus 1, June 2nd minus 2, June 3rd minus 3: all give May 31st. After the gap on June 4th, the date jumped but the counter did not, so the next rows give June 1st. Two different values, two islands.

user 1
login_dateROW_NUMBERdate − rn
Jun 011May 31
Jun 022May 31
Jun 033May 31
Jun 054Jun 01
Jun 065Jun 01
Inside a streak: date +1, row number +1the difference does not move
After a gap: date +2, row number +1the difference changes
island 1: Jun 01 – Jun 03 · 3 days
island 2: Jun 05 – Jun 06 · 2 days

Here is the query. The CTE calculates the island key: login date minus the row number. The outer query groups by user and that key, and takes the first day, the last day and the count.

SQL - login streaks
WITH g AS (
  SELECT user_id, login_date,
         login_date - (ROW_NUMBER() OVER (PARTITION BY user_id
                                          ORDER BY login_date))::int AS island
  FROM logins
)
SELECT user_id,
       MIN(login_date) AS streak_start,
       MAX(login_date) AS streak_end,
       COUNT(*)        AS days
FROM g
GROUP BY user_id, island
ORDER BY user_id, streak_start;
Output (PostgreSQL 16)4 rows
user_idstreak_startstreak_enddays
12026-06-012026-06-033
12026-06-052026-06-062
22026-06-022026-06-054
22026-06-082026-06-081

User 1 has a streak of three days and a streak of two. User 2 has the longest one, four days. The same trick finds gaps in invoice numbers, machine downtime, or sensor outages. One condition: the dates must be unique per user, so remove duplicates first.

Interview tip

Needs one row per user per day. De-duplicate first, for example with SELECT DISTINCT.

Key points to remember

  • Ties: ROW_NUMBER 1,2,3,4 · RANK 1,2,2,4 · DENSE_RANK 1,2,2,3
  • Top N per group: number the rows in a CTE, then filter outside it
  • Running total: write ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  • LAG reads the previous row, and LAST_VALUE needs a frame that reaches the end
  • Streaks: date minus ROW_NUMBER is constant inside one island

The full SQL script

Here is everything in one script: the sample data and every query from this post, in order.

Show the full SQL script (sample data and every query)
Full script - copy and run top to bottom
-- ========================================================================
-- PostgreSQL Interview Q&A #3 - Window functions
-- TechBrothersIT - run on PostgreSQL 16
-- ========================================================================

-- Sample data
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 NUMERIC(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      NUMERIC(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 NUMERIC(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 NUMERIC(10,2));
INSERT INTO payments VALUES
  (1, 1001, 1250.00), (2, 1002, 300.00),
  (3, 1003,  400.00), (4, 1003, 250.00),   -- order 1003 was paid in two parts
  (5, 1005, 1200.00), (6, 1007, 400.00),
  (7, 1008,   75.00), (8, 1009, 250.00), (9, 1012, 50.00);

CREATE TABLE daily_sales (sale_id INT PRIMARY KEY, sale_date DATE, amount INT);
INSERT INTO daily_sales VALUES
  (1, '2026-06-01', 100), (2, '2026-06-02', 200), (3, '2026-06-02', 50),   -- two sales on June 2
  (4, '2026-06-03', 300), (5, '2026-06-04', 150);

CREATE TABLE logins (user_id INT, login_date DATE);
INSERT INTO logins VALUES
  (1, '2026-06-01'), (1, '2026-06-02'), (1, '2026-06-03'), (1, '2026-06-05'), (1, '2026-06-06'),
  (2, '2026-06-02'), (2, '2026-06-03'), (2, '2026-06-04'), (2, '2026-06-05'), (2, '2026-06-08');

-- Step 1 (question 1): three rankings
SELECT name, salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
  RANK()       OVER (ORDER BY salary DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
FROM employees
WHERE dept_id = 20;

-- Step 2 (question 2): top2 per dept
WITH ranked AS (
  SELECT d.dept_name, e.name, e.salary,
         ROW_NUMBER() OVER (PARTITION BY e.dept_id
                            ORDER BY e.salary DESC, e.emp_id) AS rn
  FROM employees e
  JOIN departments d ON d.dept_id = e.dept_id
)
SELECT dept_name, name, salary, rn
FROM ranked
WHERE rn <= 2
ORDER BY dept_name, rn;

-- Step 3 (question 2): window in where
SELECT name, salary
FROM employees
WHERE ROW_NUMBER() OVER (PARTITION BY dept_id
                         ORDER BY salary DESC) <= 2;

-- Step 4 (question 3): running totals
SELECT sale_date, amount,
  SUM(amount) OVER (ORDER BY sale_date) AS default_frame,
  SUM(amount) OVER (ORDER BY sale_date, sale_id
                    ROWS BETWEEN UNBOUNDED PRECEDING
                             AND CURRENT ROW) AS rows_frame
FROM daily_sales
ORDER BY sale_date, sale_id;

-- Step 5 (question 4): month over month
WITH monthly AS (
  SELECT date_trunc('month', order_date)::date AS month,
         SUM(amount) AS sales
  FROM orders
  GROUP BY 1
)
SELECT month, sales,
       LAG(sales) OVER (ORDER BY month) AS prev_sales,
       sales - LAG(sales) OVER (ORDER BY month) AS change
FROM monthly
ORDER BY month;

-- Step 6 (question 5): last value frames
SELECT name, salary,
  FIRST_VALUE(name) OVER (ORDER BY salary DESC) AS top_earner,
  LAST_VALUE(name)  OVER (ORDER BY salary DESC) AS last_default,
  LAST_VALUE(name)  OVER (ORDER BY salary DESC
                          ROWS BETWEEN UNBOUNDED PRECEDING
                                   AND UNBOUNDED FOLLOWING) AS last_full
FROM employees
WHERE dept_id = 10
ORDER BY salary DESC;

-- Step 7 (question 6): share of sales
WITH s AS (
  SELECT p.category, p.product,
         SUM(oi.qty * oi.unit_price) AS sales
  FROM order_items oi
  JOIN products p ON p.product_id = oi.product_id
  GROUP BY p.category, p.product
)
SELECT category, product, sales,
  ROUND(100 * sales / SUM(sales) OVER (PARTITION BY category), 1) AS pct_category,
  ROUND(100 * sales / SUM(sales) OVER (), 1) AS pct_total
FROM s
ORDER BY category, product;

-- Step 8 (question 7): login streaks
WITH g AS (
  SELECT user_id, login_date,
         login_date - (ROW_NUMBER() OVER (PARTITION BY user_id
                                          ORDER BY login_date))::int AS island
  FROM logins
)
SELECT user_id,
       MIN(login_date) AS streak_start,
       MAX(login_date) AS streak_end,
       COUNT(*)        AS days
FROM g
GROUP BY user_id, island
ORDER BY user_id, streak_start;
Next in this series

Part 4: CTEs and recursive queries

The sample data on this page is invented for teaching. Every output table and query plan is the real result of the query above it, run on PostgreSQL 16 over that sample data.

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Subscribe to: Post Comments (Atom)