In this post we are going to cover GROUP BY and aggregation in PostgreSQL: WHERE versus HAVING, the three kinds of COUNT, the FILTER clause, the GROUP BY error everybody hits, string_agg, ROLLUP, and the median. These are 7 real PostgreSQL interview questions, asked the way they come up on the job.
Aggregation questions look easy, and that is why interviewers like them. Small details decide the answer: when a filter runs, what a NULL does to a count, and which columns you are allowed to select. We answer each question with the short answer first, then a picture of what happens to the rows, then the real query and its output.
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 2 of the Real Time PostgreSQL Interview Questions and Answers series on TechBrothersIT.
- Let's create some sample data
- Question 1: Sales wants the customers whose delivered orders add up to more than 500. One filter is on the status and one is on the total. Where does each filter go?
- Question 2: A dashboard shows 7 customers, a second tile shows 6, and a third shows 2. All three use COUNT on the same customers table. How is that possible?
- Question 3: The operations manager wants one row per customer, with the number of delivered, shipped and cancelled orders side by side. How do you write it in PostgreSQL?
- Question 4: Your query fails with: column must appear in the GROUP BY clause or be used in an aggregate function. Why does Postgres say that, and why does the same query work when you group by the primary key?
- Question 5: Support wants one line per order that lists its products, separated by commas, in alphabetical order. How do you do it in PostgreSQL?
- Question 6: Finance wants sales by category and product, with a subtotal for each category and a grand total at the bottom, all from one query. How do you write it?
- Question 7: The manager asks for the typical order amount. The average looks too high, because two big orders pull it up. What do you report, and how do you calculate it in PostgreSQL?
- Key points to remember
- 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. The script below creates everything this post needs. It was written for and run on PostgreSQL 16.
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);Question 1: Sales wants the customers whose delivered orders add up to more than 500. One filter is on the status and one is on the total. Where does each filter go?
WHERE filters rows before they are grouped, so the status goes there. HAVING filters groups after the aggregate is calculated, so the total goes there.
Follow the rows through the query. We start with all 12 orders. WHERE runs first and keeps the 8 delivered rows. GROUP BY turns those rows into 6 groups, one per customer, and SUM is calculated for each group. Only now can HAVING run, and it keeps the 3 groups with a total above 500.
Here is the query. The status filter is in WHERE, and the total is in HAVING. Three customers come back.
SELECT customer_id, SUM(amount) AS total
FROM orders
WHERE status = 'Delivered'
GROUP BY customer_id
HAVING SUM(amount) > 500
ORDER BY total DESC;| customer_id | total |
|---|---|
| 1 | 1250.00 |
| 3 | 1200.00 |
| 4 | 900.00 |
If you put the aggregate in WHERE, Postgres stops you with a clear error: aggregate functions are not allowed in WHERE.
SELECT customer_id, SUM(amount) AS total
FROM orders
WHERE SUM(amount) > 500
GROUP BY customer_id;ERROR: aggregate functions are not allowed in WHEREOne more tip. A filter on a plain column works in HAVING too, but put it in WHERE. Then Postgres removes those rows early, before it does the grouping work.
Plain column filters belong in WHERE: fewer rows to group, and an index can be used.
Question 2: A dashboard shows 7 customers, a second tile shows 6, and a third shows 2. All three use COUNT on the same customers table. How is that possible?
They are three different counts. COUNT(*) counts rows. COUNT(column) counts rows where that column is not NULL. COUNT(DISTINCT column) counts the unique non-NULL values.
Look at the region column. COUNT star counts every row, so it returns 7. COUNT of region skips Ravi's NULL, so it returns 6. And COUNT DISTINCT region finds only two different values, East and West, so it returns 2.
| customer_id | region | |
|---|---|---|
| 1 | East | sara@mail.com |
| 2 | West | omar@mail.com |
| 3 | West | lina@mail.com |
| 4 | East | mei@mail.com |
| 5 | NULL | ravi@mail.com |
| 6 | West | chen@mail.com |
| 7 | East | sara@mail.com |
COUNT(*)every rowCOUNT(region)skips the NULLCOUNT(DISTINCT region)East, WestHere are all the counts in one query. 7, 6 and 2, exactly as we saw. And COUNT DISTINCT email returns 6, because two customers share the same email address.
SELECT COUNT(*) AS all_rows,
COUNT(region) AS with_region,
COUNT(DISTINCT region) AS regions,
COUNT(DISTINCT email) AS emails
FROM customers;| all_rows | with_region | regions | emails |
|---|---|---|---|
| 7 | 6 | 2 | 6 |
A related trap. When no rows match, COUNT returns zero, but SUM returns NULL. So wrap SUM in COALESCE when the report must show a zero.
SELECT COUNT(*) AS orders,
SUM(amount) AS total,
COALESCE(SUM(amount), 0) AS total_safe
FROM orders
WHERE status = 'Returned';| orders | total | total_safe |
|---|---|---|
| 0 | NULL | 0 |
In an interview, say which count you mean. Rows, non-NULL values, or unique values. That one sentence shows you know the difference.
Say which one you mean: rows, non-NULL values or unique values.
Question 3: The operations manager wants one row per customer, with the number of delivered, shipped and cancelled orders side by side. How do you write it in PostgreSQL?
Use an aggregate with the FILTER clause. Each COUNT(*) FILTER (WHERE ...) counts only the rows that pass its own condition, so one GROUP BY builds all the columns in one pass.
Take Sara Khan's three orders. Each aggregate looks at the same group of rows, but through its own filter. The delivered filter lets one row in. The shipped filter lets one row in. And the cancelled filter lets one row in. Three counts, one scan of the table.
| order_id | status |
|---|---|
| 1001 | Delivered |
| 1004 | Cancelled |
| 1010 | Shipped |
FILTER (WHERE status = 'Delivered')FILTER (WHERE status = 'Shipped')FILTER (WHERE status = 'Cancelled')Here is the full query. Three counts, each with its own FILTER. Every customer gets one row, and Chen Wu, who never ordered, shows zeros.
SELECT c.name,
COUNT(*) FILTER (WHERE o.status = 'Delivered') AS delivered,
COUNT(*) FILTER (WHERE o.status = 'Shipped') AS shipped,
COUNT(*) FILTER (WHERE o.status = 'Cancelled') AS cancelled
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name
ORDER BY c.customer_id;| name | delivered | shipped | cancelled |
|---|---|---|---|
| Sara Khan | 1 | 1 | 1 |
| Omar Ali | 2 | 0 | 0 |
| Lina Park | 1 | 0 | 1 |
| Mei Chen | 2 | 1 | 0 |
| Ravi Patel | 1 | 0 | 0 |
| Chen Wu | 0 | 0 | 0 |
| Sara K. | 1 | 0 | 0 |
FILTER is standard SQL, but not every database has it. The portable version is SUM of a CASE expression. In Postgres, FILTER is shorter and easier to read.
Portable version:SUM(CASE WHEN status = 'Shipped' THEN 1 ELSE 0 END)
Question 4: Your query fails with: column must appear in the GROUP BY clause or be used in an aggregate function. Why does Postgres say that, and why does the same query work when you group by the primary key?
A group has many rows, but the result shows one. For a column that is not grouped and not aggregated, Postgres cannot know which value to show. Grouping by the primary key is the exception, because then every other column has exactly one value.
Group the customers by city. Toronto has two rows: Sara Khan and Sara K. The count is easy: two. But which name should Postgres print for Toronto? There are two candidates, and no rule to pick one, so it refuses.
| name | city |
|---|---|
| Sara Khan | Toronto |
| Sara K. | Toronto |
| Omar Ali | Dallas |
| Mei Chen | Boston |
| city | COUNT(*) | name |
|---|---|---|
| Toronto | 2 | ??? |
| Dallas | 1 | Omar Ali |
| Boston | 1 | Mei Chen |
Here is the failing query. Name is selected, but it is not in GROUP BY and not inside an aggregate. And this is the real error.
SELECT city, name, COUNT(*) AS customers
FROM customers
GROUP BY city;ERROR: column "customers.name" must appear in the GROUP BY clause or be used in an aggregate functionNow group by customer ID, the primary key. Name and city are not in GROUP BY, but the query runs. Postgres knows one customer ID means exactly one name and one city. This is called functional dependency.
SELECT c.customer_id, c.name, c.city,
SUM(o.amount) AS total
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id
ORDER BY c.customer_id;| customer_id | name | city | total |
|---|---|---|---|
| 1 | Sara Khan | Toronto | 1600.00 |
| 2 | Omar Ali | Dallas | 375.00 |
| 3 | Lina Park | Seattle | 1600.00 |
| 4 | Mei Chen | Boston | 1175.00 |
| 5 | Ravi Patel | Austin | 400.00 |
| 7 | Sara K. | Toronto | 50.00 |
So you have three legal choices: add the column to GROUP BY, wrap it in an aggregate like MIN or string_agg, or group by the table's primary key.
Fix it: group the column, aggregate it, or group by the primary key.
Question 5: Support wants one line per order that lists its products, separated by commas, in alphabetical order. How do you do it in PostgreSQL?
Use string_agg with GROUP BY. It joins the values of a group into one string, and an ORDER BY inside the aggregate controls the order of the list.
Order 1001 has two item rows. A laptop and a keyboard. string_agg collects the product names of the group, sorts them because we asked it to, and glues them together with a comma. Two rows become one line.
| order_id | product |
|---|---|
| 1001 | Laptop |
| 1001 | Keyboard |
| 1003 | Desk |
| 1003 | Chair |
string_agg(product, ', ' ORDER BY product)| order_id | products |
|---|---|
| 1001 | Keyboard, Laptop |
| 1003 | Chair, Desk |
Here is the query on all the orders. The ORDER BY sits inside the parentheses of string_agg. Order 1001 shows Keyboard, then Laptop, in alphabetical order.
SELECT oi.order_id,
string_agg(p.product, ', ' ORDER BY p.product) AS products,
COUNT(*) AS items
FROM order_items oi
JOIN products p ON p.product_id = oi.product_id
GROUP BY oi.order_id
ORDER BY oi.order_id;| order_id | products | items |
|---|---|---|
| 1001 | Keyboard, Laptop | 2 |
| 1002 | Monitor | 1 |
| 1003 | Chair, Desk | 2 |
| 1004 | Keyboard | 1 |
| 1005 | Laptop | 1 |
| 1006 | Chair, Mouse | 2 |
| 1007 | Desk | 1 |
| 1008 | Mouse | 1 |
| 1009 | Chair | 1 |
| 1010 | Monitor | 1 |
| 1011 | Desk | 1 |
| 1012 | Keyboard | 1 |
Without that inner ORDER BY, the order of the list is not guaranteed, and it can change from one run to the next. The same pattern works with array_agg for an array, and jsonb_agg for JSON.
No inner ORDER BY = no guaranteed order. Also: array_agg, jsonb_agg.
Question 6: Finance wants sales by category and product, with a subtotal for each category and a grand total at the bottom, all from one query. How do you write it?
Use GROUP BY ROLLUP. It runs the same aggregate at several levels: each product, each category, and the whole table. The subtotal rows show NULL in the columns that were rolled up.
ROLLUP of category and product means three levels of grouping. First, category and product: the detail rows. Then category alone: one subtotal per category. Then nothing at all: one grand total. Postgres stacks all three results into one answer.
GROUP BY ROLLUP (category, product)| category | product | sales |
|---|---|---|
| Furniture | Chair | 750.00 |
| Furniture | Desk | 1200.00 |
| category | product | sales |
|---|---|---|
| Furniture | NULL | 1950.00 |
| category | product | sales |
|---|---|---|
| NULL | NULL | 5200.00 |
Here is the real query. The detail rows come first. A row with a NULL product is the subtotal of its category. And the last row, with both columns NULL, is the grand total.
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 ROLLUP (p.category, p.product)
ORDER BY p.category, p.product;| category | product | sales |
|---|---|---|
| Accessories | Keyboard | 150.00 |
| Accessories | Mouse | 100.00 |
| Accessories | NULL | 250.00 |
| Electronics | Laptop | 2400.00 |
| Electronics | Monitor | 600.00 |
| Electronics | NULL | 3000.00 |
| Furniture | Chair | 750.00 |
| Furniture | Desk | 1200.00 |
| Furniture | NULL | 1950.00 |
| NULL | NULL | 5200.00 |
One caution. If the data itself can contain NULL, you cannot tell a real NULL from a subtotal. Use the GROUPING function for that. It returns 1 when the column was rolled up.
GROUPING(product) = 1 marks a subtotal row. Do not rely on NULL alone.
Question 7: The manager asks for the typical order amount. The average looks too high, because two big orders pull it up. What do you report, and how do you calculate it in PostgreSQL?
Report the median, the middle value. Postgres has no MEDIAN function, so use percentile_cont(0.5) WITHIN GROUP (ORDER BY amount).
Sort the 12 order amounts from small to large. Here they are. The median is the middle of the line. With 12 values, that is halfway between the sixth and the seventh, so 300. The average is different. The two large orders on the right pull it up to 433.
Here is the query. percentile_cont with 0.5 is the median. It is an ordered-set aggregate, so the ORDER BY goes inside WITHIN GROUP. The average is 433.33, but the median is only 300.
SELECT ROUND(AVG(amount), 2) AS average,
percentile_cont(0.5) WITHIN GROUP (ORDER BY amount) AS median
FROM orders;| average | median |
|---|---|
| 433.33 | 300 |
It works with GROUP BY as well. Here is the median per status. Notice the 287.5 for shipped orders. percentile_cont can return a value that is not in the table, because it interpolates between the two middle rows. If you need a real value from the data, use percentile_disc.
SELECT status, COUNT(*) AS orders,
percentile_cont(0.5) WITHIN GROUP (ORDER BY amount) AS median
FROM orders
GROUP BY status
ORDER BY status;| status | orders | median |
|---|---|---|
| Cancelled | 2 | 225 |
| Delivered | 8 | 350 |
| Shipped | 2 | 287.5 |
So for skewed numbers like order amounts, salaries or response times, report the median, or a percentile, next to the average.
Skewed data: report the median or a percentile, not only the average.
Key points to remember
- WHERE filters rows before grouping, HAVING filters groups after it
- COUNT(*) counts rows, COUNT(column) skips NULLs, COUNT(DISTINCT) counts unique values
- FILTER (WHERE ...) gives conditional aggregates in one pass
- Every selected column must be grouped, aggregated, or depend on a grouped primary key
- ROLLUP adds subtotals, and percentile_cont(0.5) gives the median
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)
-- ========================================================================
-- PostgreSQL Interview Q&A #2 - GROUP BY, HAVING and FILTER
-- 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);
-- Step 1 (question 1): where and having
SELECT customer_id, SUM(amount) AS total
FROM orders
WHERE status = 'Delivered'
GROUP BY customer_id
HAVING SUM(amount) > 500
ORDER BY total DESC;
-- Step 2 (question 1): sum in where
SELECT customer_id, SUM(amount) AS total
FROM orders
WHERE SUM(amount) > 500
GROUP BY customer_id;
-- Step 3 (question 2): three counts
SELECT COUNT(*) AS all_rows,
COUNT(region) AS with_region,
COUNT(DISTINCT region) AS regions,
COUNT(DISTINCT email) AS emails
FROM customers;
-- Step 4 (question 2): empty set
SELECT COUNT(*) AS orders,
SUM(amount) AS total,
COALESCE(SUM(amount), 0) AS total_safe
FROM orders
WHERE status = 'Returned';
-- Step 5 (question 3): status counts
SELECT c.name,
COUNT(*) FILTER (WHERE o.status = 'Delivered') AS delivered,
COUNT(*) FILTER (WHERE o.status = 'Shipped') AS shipped,
COUNT(*) FILTER (WHERE o.status = 'Cancelled') AS cancelled
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name
ORDER BY c.customer_id;
-- Step 6 (question 4): group by error
SELECT city, name, COUNT(*) AS customers
FROM customers
GROUP BY city;
-- Step 7 (question 4): group by pk
SELECT c.customer_id, c.name, c.city,
SUM(o.amount) AS total
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id
ORDER BY c.customer_id;
-- Step 8 (question 5): products per order
SELECT oi.order_id,
string_agg(p.product, ', ' ORDER BY p.product) AS products,
COUNT(*) AS items
FROM order_items oi
JOIN products p ON p.product_id = oi.product_id
GROUP BY oi.order_id
ORDER BY oi.order_id;
-- Step 9 (question 6): sales rollup
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 ROLLUP (p.category, p.product)
ORDER BY p.category, p.product;
-- Step 10 (question 7): avg vs median
SELECT ROUND(AVG(amount), 2) AS average,
percentile_cont(0.5) WITHIN GROUP (ORDER BY amount) AS median
FROM orders;
-- Step 11 (question 7): median by status
SELECT status, COUNT(*) AS orders,
percentile_cont(0.5) WITHIN GROUP (ORDER BY amount) AS median
FROM orders
GROUP BY status
ORDER BY status;
Part 3: Window functions
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.