In this post we are going to cover GROUP BY and aggregation: HAVING, conditional aggregation with SUM(CASE WHEN ...), pivots, how aggregates treat NULLs, and percent of total. These are 7 real SQL interview questions, asked the way they come up on the job, and the last one is a scenario where Sales wants the customers who bought both a Laptop and a Monitor, and the first query returns nothing.
GROUP BY is the heart of every report, so interviewers use it to see how you think about groups instead of single rows. Duplicates, dashboard counts, pivots and averages all come from the same few patterns, and each one has a small trap that changes the number.
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 3 of the SQL Server RealTime Questions Answers series on TechBrothersIT.
- The short answer
- Let's create some sample data
- Question 1: The CRM has the same person registered twice under one email, so every customer count is one too high. How do you find duplicate emails and show the customer rows behind them?
- Question 2: A dashboard tile needs total, delivered, shipped and cancelled order counts in a single row. How do you write it in one query?
- Question 3: A product manager wants one row per category with each month's sales as its own column. How do you pivot rows into columns in SQL?
- Question 4: Four of ten employees have no bonus value yet (NULL). Should the average bonus be AVG(bonus) or AVG(COALESCE(bonus, 0)), and why do they give different answers?
- Question 5: Leadership asks which departments pay above the company average salary. How do you write it, and where does the filter go?
- Question 6: Finance wants each category's share of total revenue as a percentage. How do you calculate percent of total, and what are the two common traps?
- Scenario 7: Sales wants the customers who bought BOTH a Laptop and a Monitor. Why does product = 'Laptop' AND product = 'Monitor' return nothing, and what is the right query?
- The full SQL script
The short answer
GROUP BY collapses rows into groups and HAVING filters those groups after aggregation. SUM(CASE WHEN ...) turns one pass over the data into counts, pivots and filtered totals, and aggregates ignore NULLs, so AVG(col) and AVG(COALESCE(col, 0)) answer different business questions.
Keep that in mind. Every question below is one use of 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, plus an emp_bonus table with this year's bonuses that we use in question 4. The script is written for PostgreSQL, and it runs on SQL Server, MySQL 8 or Snowflake with very small changes.
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);
DROP TABLE IF EXISTS emp_bonus;
-- this year's bonus per employee; NULL = no bonus value entered yet
CREATE TABLE emp_bonus (emp_id INT PRIMARY KEY, bonus INT);
INSERT INTO emp_bonus VALUES
(1, 20000), (2, 10000), (3, NULL), (4, 5000), (5, NULL),
(6, 8000), (7, 12000), (8, NULL), (9, 5000), (10, NULL);Here are the employees. Notice that Aisha and Zara have no department. That matters in question 5, when we compare department averages with the company average.
| emp_id | name | dept_id | manager_id | salary | hire_date |
|---|---|---|---|---|---|
| 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 |
And here are the orders: 12 of them, and two are Cancelled, 1004 and 1011. Most queries below leave the cancelled orders out.
| 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 |
Question 1: The CRM has the same person registered twice under one email, so every customer count is one too high. How do you find duplicate emails and show the customer rows behind them?
Group by the email and keep only the groups with more than one row. That is GROUP BY with HAVING COUNT(*) > 1. HAVING filters groups, after the aggregation. Here are the customers and their emails.
| customer_id | name | |
|---|---|---|
| 1 | Sara Khan | sara@mail.com |
| 2 | Omar Ali | omar@mail.com |
| 3 | Lina Park | lina@mail.com |
| 4 | Mei Chen | mei@mail.com |
| 5 | Ravi Patel | ravi@mail.com |
| 6 | Chen Wu | chen@mail.com |
| 7 | Sara K. | sara@mail.com |
In a real project, emails arrive with different case and stray spaces, so we group by LOWER(TRIM(email)), not the raw column.
SELECT LOWER(TRIM(email)) AS email_key,
COUNT(*) AS copies
FROM customers
GROUP BY LOWER(TRIM(email))
HAVING COUNT(*) > 1
ORDER BY email_key;| email_key | copies |
|---|---|
| sara@mail.com | 2 |
One email comes back, sara@mail.com, with 2 copies. But the business does not want the email, it wants the customer rows behind it. So let's join the duplicate keys back to the customers table.
SELECT c.customer_id, c.name, c.city, c.email
FROM customers c
JOIN (SELECT LOWER(TRIM(email)) AS email_key
FROM customers
GROUP BY LOWER(TRIM(email))
HAVING COUNT(*) > 1) d
ON d.email_key = LOWER(TRIM(c.email))
ORDER BY d.email_key, c.customer_id;| customer_id | name | city | |
|---|---|---|---|
| 1 | Sara Khan | Toronto | sara@mail.com |
| 7 | Sara K. | Toronto | sara@mail.com |
Customers 1 and 7, Sara Khan and Sara K., both in Toronto, are the same person registered twice.
These rows need a merge rule before they reach a report, otherwise every customer count is one too high.
Question 2: A dashboard tile needs total, delivered, shipped and cancelled order counts in a single row. How do you write it in one query?
The first instinct is GROUP BY status. Let's try it.
SELECT status, COUNT(*) AS orders
FROM orders
GROUP BY status
ORDER BY status;| status | orders |
|---|---|
| Cancelled | 2 |
| Delivered | 8 |
| Shipped | 2 |
It is correct, but it returns one row per status, and the dashboard tile wants one row with a column for each status.
So we use conditional aggregation: SUM(CASE WHEN status = 'Delivered' THEN 1 ELSE 0 END). One CASE expression per column, all calculated in the same pass over the table.
SELECT
COUNT(*) AS total_orders,
SUM(CASE WHEN status = 'Delivered' THEN 1 ELSE 0 END) AS delivered,
SUM(CASE WHEN status = 'Shipped' THEN 1 ELSE 0 END) AS shipped,
SUM(CASE WHEN status = 'Cancelled' THEN 1 ELSE 0 END) AS cancelled,
SUM(CASE WHEN status = 'Delivered' THEN amount ELSE 0 END)
AS delivered_revenue
FROM orders;| total_orders | delivered | shipped | cancelled | delivered_revenue |
|---|---|---|---|---|
| 12 | 8 | 2 | 2 | 4175.00 |
Twelve orders: 8 delivered, 2 shipped and 2 cancelled. And the same trick works for amounts: delivered revenue is 4175.00.
Two details interviewers like:
- Keep the
ELSE 0. Without it, a status with no rows gives NULL instead of 0. - PostgreSQL also has
COUNT(*) FILTER (WHERE ...), but the CASE version runs on every database.
Question 3: A product manager wants one row per category with each month's sales as its own column. How do you pivot rows into columns in SQL?
The tool is the same conditional aggregation. First, a CTE prepares the rows: category, month number and line amount, without cancelled orders. Then one SUM(CASE ...) per month: when the month is 1, add the amount, else 0. Group by category, and each CASE expression becomes a column.
WITH s AS (
SELECT p.category, EXTRACT(MONTH FROM o.order_date) AS m,
oi.qty * oi.unit_price AS amt
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id
WHERE o.status <> 'Cancelled'
)
SELECT category,
SUM(CASE WHEN m = 1 THEN amt ELSE 0 END) AS jan,
SUM(CASE WHEN m = 2 THEN amt ELSE 0 END) AS feb,
SUM(CASE WHEN m = 3 THEN amt ELSE 0 END) AS mar,
SUM(CASE WHEN m = 4 THEN amt ELSE 0 END) AS apr,
SUM(CASE WHEN m = 5 THEN amt ELSE 0 END) AS may,
SUM(CASE WHEN m = 6 THEN amt ELSE 0 END) AS jun,
SUM(amt) AS total
FROM s
GROUP BY category
ORDER BY category;| category | jan | feb | mar | apr | may | jun | total |
|---|---|---|---|---|---|---|---|
| Accessories | 50.00 | 0 | 25.00 | 75.00 | 0 | 50.00 | 200.00 |
| Electronics | 1500.00 | 1200.00 | 0 | 0 | 300.00 | 0 | 3000.00 |
| Furniture | 0 | 650.00 | 650.00 | 250.00 | 0 | 0 | 1550.00 |
Electronics sold 1500.00 in January and nothing in March or April. Furniture sold 650.00 in both February and March. The total column, 3000.00 for Electronics, lets you check that the pivot adds up.
SQL Server also has a PIVOT operator, but the CASE version runs everywhere and can pivot several measures at once. On SQL Server, use MONTH(order_date) instead of EXTRACT.
In both versions the months are fixed in the query. For a dynamic list of columns you need dynamic SQL, or you let the BI tool pivot.
Question 4: Four of ten employees have no bonus value yet (NULL). Should the average bonus be AVG(bonus) or AVG(COALESCE(bonus, 0)), and why do they give different answers?
Aggregate functions ignore NULLs, so the two averages divide by different numbers. Here are this year's bonuses for our ten employees. Lina, Mei, Fatima and Zara have no bonus value yet.
| name | bonus |
|---|---|
| Aisha | 20000 |
| Omar | 10000 |
| Lina | NULL |
| Ravi | 5000 |
| Mei | NULL |
| Sara | 8000 |
| Daniel | 12000 |
| Fatima | NULL |
| John | 5000 |
| Zara | NULL |
Let's calculate both averages side by side, with the counts and the total.
SELECT COUNT(*) AS employees,
COUNT(bonus) AS with_bonus,
SUM(bonus) AS total_bonus,
CAST(AVG(bonus) AS DECIMAL(10,2)) AS avg_bonus,
CAST(AVG(COALESCE(bonus, 0)) AS DECIMAL(10,2)) AS avg_per_head
FROM emp_bonus;| employees | with_bonus | total_bonus | avg_bonus | avg_per_head |
|---|---|---|---|---|
| 10 | 6 | 60000 | 10000.00 | 6000.00 |
COUNT(*) counts 10 rows, COUNT(bonus) counts only 6. So AVG(bonus) is 60000 divided by 6: 10000.00. COALESCE turns NULL into 0 first, so the second average is 60000 divided by 10: 6000.00. Same data, two very different answers.
Which one is right? Ask what NULL means.
| NULL is | Right when NULL means | |
|---|---|---|
| AVG(bonus) | skipped | not decided yet |
| AVG(COALESCE(bonus, 0)) | counted as 0 | no bonus: a real zero |
If it means not decided yet, ignore it: 10000.00 per employee who got one. If it means no bonus, it is a real zero, and the budget per head is 6000.00. Ask that question before you write the query.
AVG of an integer column returns an integer, so cast to DECIMAL first when the values don't divide evenly.
Question 5: Leadership asks which departments pay above the company average salary. How do you write it, and where does the filter go?
Group by department, then compare each group's average with one company average from a subquery. Let's start with the average per department.
SELECT d.dept_name, COUNT(*) AS employees,
CAST(AVG(1.0 * e.salary) AS DECIMAL(10,2)) AS avg_salary
FROM employees e
JOIN departments d ON d.dept_id = e.dept_id
GROUP BY d.dept_name
ORDER BY avg_salary DESC;| dept_name | employees | avg_salary |
|---|---|---|
| Engineering | 4 | 93750.00 |
| Sales | 3 | 86666.67 |
| HR | 1 | 70000.00 |
Engineering is 93750.00, Sales 86666.67 and HR 70000.00. The inner join leaves out Aisha and Zara, who have no department.
Now filter the groups. You can't put an aggregate in WHERE, because WHERE runs before GROUP BY. So the filter goes in HAVING, and the subquery calculates the company average once, over all employees.
SELECT d.dept_name, COUNT(*) AS employees,
CAST(AVG(1.0 * e.salary) AS DECIMAL(10,2)) AS avg_salary,
(SELECT CAST(AVG(1.0 * salary) AS DECIMAL(10,2))
FROM employees) AS company_avg
FROM employees e
JOIN departments d ON d.dept_id = e.dept_id
GROUP BY d.dept_name
HAVING AVG(1.0 * e.salary) >
(SELECT AVG(1.0 * salary) FROM employees)
ORDER BY avg_salary DESC;| dept_name | employees | avg_salary | company_avg |
|---|---|---|---|
| Engineering | 4 | 93750.00 | 91000.00 |
The company average is 91000.00, so only Engineering is above it. The 1.0 * avoids the integer AVG of SQL Server.
The company average includes Aisha and Zara, who have no department. The inner join drops them from the department list, but they still count in the company average. Agree on that definition with the business.
Question 6: Finance wants each category's share of total revenue as a percentage. How do you calculate percent of total, and what are the two common traps?
Divide each category's revenue by the grand total. A CTE calculates revenue per category once, and a scalar subquery over that same CTE gives the grand total.
WITH cat AS (
SELECT p.category, SUM(oi.qty * oi.unit_price) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id
WHERE o.status <> 'Cancelled'
GROUP BY p.category
)
SELECT category, revenue,
CAST(100.0 * revenue / (SELECT SUM(revenue) FROM cat)
AS DECIMAL(5,2)) AS pct_of_total
FROM cat
ORDER BY revenue DESC;| category | revenue | pct_of_total |
|---|---|---|
| Electronics | 3000.00 | 63.16 |
| Furniture | 1550.00 | 32.63 |
| Accessories | 200.00 | 4.21 |
Electronics brings in 3000.00, which is 63.16 percent. Furniture is 32.63 and Accessories 4.21. Together, 100 percent.
Now the two traps:
- Multiply by 100.0 first, not 100. With integer columns, SQL Server and PostgreSQL do integer division, so a small share divided by the total is simply 0.
- Use the same filter in both parts. If the total includes cancelled orders and the categories don't, the percentages won't add up to 100.
Later in this series, we do the same thing with SUM() OVER (), a window function, without the subquery.
Scenario 7: Sales wants the customers who bought BOTH a Laptop and a Monitor. Why does product = 'Laptop' AND product = 'Monitor' return nothing, and what is the right query?
Here is the first attempt everybody writes.
SELECT c.name
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id
WHERE p.product = 'Laptop' AND p.product = 'Monitor';| name |
|---|
It runs and returns no rows. WHERE looks at one row at a time, and a single order line can't be both products.
The fix is to think in groups. Filter to the two products, group by customer, and keep the customers with two distinct products.
SELECT c.customer_id, c.name
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id
WHERE p.product IN ('Laptop', 'Monitor')
AND o.status <> 'Cancelled'
GROUP BY c.customer_id, c.name
HAVING COUNT(DISTINCT p.product) = 2
ORDER BY c.customer_id;| customer_id | name |
|---|---|
| 1 | Sara Khan |
Sara Khan is the one. She bought a Laptop in January and a Monitor in May, in different orders, which is why a row-level filter could never find her.
Why COUNT(DISTINCT p.product) and not COUNT(*)? If someone bought two laptops and no monitor, COUNT(*) would also be 2, and they would slip in.
The usual follow-up: a Laptop, but never a Monitor. Same GROUP BY, and HAVING uses conditional sums: at least one Laptop, and zero Monitors.
SELECT c.customer_id, c.name
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id
WHERE o.status <> 'Cancelled'
GROUP BY c.customer_id, c.name
HAVING SUM(CASE WHEN p.product = 'Laptop' THEN 1 ELSE 0 END) > 0
AND SUM(CASE WHEN p.product = 'Monitor' THEN 1 ELSE 0 END) = 0
ORDER BY c.customer_id;| customer_id | name |
|---|---|
| 3 | Lina Park |
That is Lina Park. This one pattern answers a whole family of questions: bought all of these, any of these, or this but not that.
Key points to remember
- HAVING filters groups; WHERE filters rows before grouping
- SUM(CASE WHEN ...): counts per status and pivots in one pass
- Aggregates ignore NULLs: decide what NULL means before AVG
- Percent of total: 100.0 and the same filter in both parts
- Bought both: GROUP BY + HAVING COUNT(DISTINCT)
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 #3 - GROUP BY and aggregation
-- 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_q03; SET search_path TO sql_q03;
-- ========================================================================
-- 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);
DROP TABLE IF EXISTS emp_bonus;
-- this year's bonus per employee; NULL = no bonus value entered yet
CREATE TABLE emp_bonus (emp_id INT PRIMARY KEY, bonus INT);
INSERT INTO emp_bonus VALUES
(1, 20000), (2, 10000), (3, NULL), (4, 5000), (5, NULL),
(6, 8000), (7, 12000), (8, NULL), (9, 5000), (10, NULL);
-- Step 1: Duplicate emails: GROUP BY the cleaned email, keep groups with more than one row
SELECT LOWER(TRIM(email)) AS email_key,
COUNT(*) AS copies
FROM customers
GROUP BY LOWER(TRIM(email))
HAVING COUNT(*) > 1
ORDER BY email_key;
-- Step 2: Show the duplicate customer rows behind each duplicate email
SELECT c.customer_id, c.name, c.city, c.email
FROM customers c
JOIN (SELECT LOWER(TRIM(email)) AS email_key
FROM customers
GROUP BY LOWER(TRIM(email))
HAVING COUNT(*) > 1) d
ON d.email_key = LOWER(TRIM(c.email))
ORDER BY d.email_key, c.customer_id;
-- Step 3: GROUP BY status: correct, but one row per status
SELECT status, COUNT(*) AS orders
FROM orders
GROUP BY status
ORDER BY status;
-- Step 4: Conditional aggregation: every status as a column, in one row
SELECT
COUNT(*) AS total_orders,
SUM(CASE WHEN status = 'Delivered' THEN 1 ELSE 0 END) AS delivered,
SUM(CASE WHEN status = 'Shipped' THEN 1 ELSE 0 END) AS shipped,
SUM(CASE WHEN status = 'Cancelled' THEN 1 ELSE 0 END) AS cancelled,
SUM(CASE WHEN status = 'Delivered' THEN amount ELSE 0 END)
AS delivered_revenue
FROM orders;
-- Step 5: Pivot: one row per category, one column per month
WITH s AS (
SELECT p.category, EXTRACT(MONTH FROM o.order_date) AS m,
oi.qty * oi.unit_price AS amt
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id
WHERE o.status <> 'Cancelled'
)
SELECT category,
SUM(CASE WHEN m = 1 THEN amt ELSE 0 END) AS jan,
SUM(CASE WHEN m = 2 THEN amt ELSE 0 END) AS feb,
SUM(CASE WHEN m = 3 THEN amt ELSE 0 END) AS mar,
SUM(CASE WHEN m = 4 THEN amt ELSE 0 END) AS apr,
SUM(CASE WHEN m = 5 THEN amt ELSE 0 END) AS may,
SUM(CASE WHEN m = 6 THEN amt ELSE 0 END) AS jun,
SUM(amt) AS total
FROM s
GROUP BY category
ORDER BY category;
-- Step 6: AVG ignores NULLs; AVG(COALESCE(col, 0)) treats them as zero
SELECT COUNT(*) AS employees,
COUNT(bonus) AS with_bonus,
SUM(bonus) AS total_bonus,
CAST(AVG(bonus) AS DECIMAL(10,2)) AS avg_bonus,
CAST(AVG(COALESCE(bonus, 0)) AS DECIMAL(10,2)) AS avg_per_head
FROM emp_bonus;
-- Step 7: Average salary per department
SELECT d.dept_name, COUNT(*) AS employees,
CAST(AVG(1.0 * e.salary) AS DECIMAL(10,2)) AS avg_salary
FROM employees e
JOIN departments d ON d.dept_id = e.dept_id
GROUP BY d.dept_name
ORDER BY avg_salary DESC;
-- Step 8: Departments above the company average: HAVING with a subquery
SELECT d.dept_name, COUNT(*) AS employees,
CAST(AVG(1.0 * e.salary) AS DECIMAL(10,2)) AS avg_salary,
(SELECT CAST(AVG(1.0 * salary) AS DECIMAL(10,2))
FROM employees) AS company_avg
FROM employees e
JOIN departments d ON d.dept_id = e.dept_id
GROUP BY d.dept_name
HAVING AVG(1.0 * e.salary) >
(SELECT AVG(1.0 * salary) FROM employees)
ORDER BY avg_salary DESC;
-- Step 9: Percentage of total revenue by category (CTE + scalar subquery)
WITH cat AS (
SELECT p.category, SUM(oi.qty * oi.unit_price) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id
WHERE o.status <> 'Cancelled'
GROUP BY p.category
)
SELECT category, revenue,
CAST(100.0 * revenue / (SELECT SUM(revenue) FROM cat)
AS DECIMAL(5,2)) AS pct_of_total
FROM cat
ORDER BY revenue DESC;
-- Step 10: The trap: a single row cannot be a Laptop AND a Monitor
SELECT c.name
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id
WHERE p.product = 'Laptop' AND p.product = 'Monitor';
-- Step 11: Customers who bought BOTH products: GROUP BY + HAVING COUNT(DISTINCT)
SELECT c.customer_id, c.name
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id
WHERE p.product IN ('Laptop', 'Monitor')
AND o.status <> 'Cancelled'
GROUP BY c.customer_id, c.name
HAVING COUNT(DISTINCT p.product) = 2
ORDER BY c.customer_id;
-- Step 12: Follow-up: bought a Laptop but never a Monitor
SELECT c.customer_id, c.name
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id
WHERE o.status <> 'Cancelled'
GROUP BY c.customer_id, c.name
HAVING SUM(CASE WHEN p.product = 'Laptop' THEN 1 ELSE 0 END) > 0
AND SUM(CASE WHEN p.product = 'Monitor' THEN 1 ELSE 0 END) = 0
ORDER BY c.customer_id;
Part 4: Subqueries, CTEs and Recursive Queries
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.