Definition
- Syntax: WITH cte_name AS (SELECT ...) SELECT * FROM cte_name.
- Scope: Exists only for the query that follows it. Not a permanent object.
- Chaining: Multiple CTEs can reference previous ones: WITH a AS (...), b AS (... FROM a).
- Recursive: Anchor + UNION ALL + recursive member. For hierarchies and sequences.
- Performance: Usually inlined by optimizer. Not always materialized.
- vs Subquery: Same performance, but CTEs are more readable.
- vs Temp table: Temp tables persist and can be indexed. CTEs cannot.
This section is a focused tutorial on CTEs. Each question teaches you to think through the problem before writing SQL, build the query step by step, understand how the database executes it, and optimize for performance.
Understanding the Concepts
## CTEs: WITH Clause and Common Table Expressions
A CTE (Common Table Expression) creates a temporary named result set using WITH. It exists only for the duration of the query. CTEs improve readability, can be referenced multiple times, and are required for recursive queries. Think of a CTE as a named subquery that you define once and use in the main query.
Sample Tables
employees, departments, orders, and customers tables as defined in previous sections.
All tables from earlier sections are available. CTEs break complex queries into named steps using the WITH keyword.
---
Q1. Find departments with the highest average salary
Problem: Find departments with the highest average salary.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH avg_salaries AS (
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
)
SELECT *
FROM avg_salaries
WHERE avg_salary = (SELECT MAX(avg_salary)
FROM avg_salaries);
Step-by-step how the database executes this:
- WITH (CTE): Materializes
avg_salariesas temporary result set(s). - FROM: Reads table
employees. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
Q2. Find employees whose salary is a prime number
Problem: Find employees whose salary is a prime number.
How to think before writing:
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
- What condition filters the rows I need? Write the WHERE clause for that condition.
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH primes AS (
SELECT generate_series(2, (SELECT MAX(salary)
FROM employees)) AS num
EXCEPT
SELECT num FROM (
SELECT num, UNNEST(ARRAY(
SELECT generate_series(2, FLOOR(SQRT(num)))
AS divisor
)) AS divisor
WHERE num % divisor = 0
) AS composite
)
SELECT *
FROM employees
WHERE salary IN (SELECT num FROM primes);
Note: This is a conceptual approach—some databases
may not support this syntax fully.
Step-by-step how the database executes this:
- WITH (CTE): Materializes
primesas temporary result set(s). - FROM: Reads table
employees. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: If the subquery runs once per outer row (correlated), consider rewriting as a JOIN or CTE for better performance.
---
Q3. Generate a report that shows sales and sales growth percentage compared to the same month last year
Problem: Generate a report that shows sales and sales growth percentage compared to the same month last year.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Which tables do I need? How are they related? What column connects them?
- Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH monthly_sales AS (
SELECT DATE_TRUNC('month', sale_date) AS
month, SUM(amount) AS total_sales
FROM sales
GROUP BY month
)
SELECT ms1.month, ms1.total_sales,
((ms1.total_sales - ms2.total_sales) * 100.0 /
ms2.total_sales) AS growth_pct
FROM monthly_sales ms1
LEFT JOIN monthly_sales ms2 ON ms1.month =
ms2.month + INTERVAL '1 year';
Step-by-step how the database executes this:
- WITH (CTE): Materializes
monthly_salesas temporary result set(s). - FROM: Reads table
sales. - LEFT JOIN: Combines rows from joined tables using the ON condition.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q4. Find customers who made purchases in every month of the current year
Problem: Find customers who made purchases in every month of the current year.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need to filter groups after aggregation? That means HAVING, not WHERE.
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
- Are there duplicates I need to remove? Use DISTINCT.
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH months AS (
SELECT generate_series(1, 12) AS month
),
customer_months AS (
SELECT customer_id, EXTRACT(MONTH FROM
purchase_date) AS month
FROM sales
WHERE EXTRACT(YEAR FROM purchase_date) =
EXTRACT(YEAR FROM CURRENT_DATE)
GROUP BY customer_id, EXTRACT(MONTH
FROM purchase_date)
)
SELECT customer_id
FROM customer_months
GROUP BY customer_id
HAVING COUNT(DISTINCT month) = 12;
Step-by-step how the database executes this:
- WITH (CTE): Materializes
months, customer_monthsas temporary result set(s). - FROM: Reads table
purchase_date. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- HAVING: Filters groups. Only groups passing the condition remain.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- DISTINCT: Removes duplicate rows by comparing all selected columns.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
Q5. Calculate the percentage contribution of each product’s sales to the total sales per month
Problem: Calculate the percentage contribution of each product’s sales to the total sales per month.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Which tables do I need? How are they related? What column connects them?
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH monthly_sales AS (
SELECT product_id, DATE_TRUNC('month',
sale_date) AS month, SUM(amount) AS product_sales
FROM sales
GROUP BY product_id, month
),
total_monthly_sales AS (
SELECT month, SUM(product_sales) AS total_sales
FROM monthly_sales
GROUP BY month
)
SELECT ms.product_id, ms.month, ms.product_sales,
(ms.product_sales * 100.0) / tms.total_sales AS
pct_contribution
FROM monthly_sales ms
JOIN total_monthly_sales tms ON ms.month =
tms.month;
Step-by-step how the database executes this:
- WITH (CTE): Materializes
monthly_sales, total_monthly_salesas temporary result set(s). - FROM: Reads table
sales. - INNER JOIN: Combines rows from joined tables using the ON condition.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q6. List customers who placed orders in January but not in February
Problem: List customers who placed orders in January but not in February.
How to think before writing:
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
- What condition filters the rows I need? Write the WHERE clause for that condition.
- Are there duplicates I need to remove? Use DISTINCT.
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH jan_orders AS (
SELECT DISTINCT customer_id
FROM orders
WHERE EXTRACT(MONTH FROM order_date) = 1
),
feb_orders AS (
SELECT DISTINCT customer_id
FROM orders
WHERE EXTRACT(MONTH FROM order_date) = 2
)
SELECT customer_id
FROM jan_orders
WHERE customer_id NOT IN (SELECT customer_id
FROM feb_orders);
Step-by-step how the database executes this:
- WITH (CTE): Materializes
jan_orders, feb_ordersas temporary result set(s). - FROM: Reads table
orders. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- DISTINCT: Removes duplicate rows by comparing all selected columns.
Optimization: If the subquery runs once per outer row (correlated), consider rewriting as a JOIN or CTE for better performance.
---
Q7. Find the highest salary by department and the employee(s) who earn it
Problem: Find the highest salary by department and the employee(s) who earn it.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Which tables do I need? How are they related? What column connects them?
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH dept_max AS (
SELECT department_id, MAX(salary) AS max_salary
FROM employees
GROUP BY department_id
)
SELECT e.*
FROM employees e
JOIN dept_max d ON e.department_id =
d.department_id AND e.salary = d.max_salary;
Step-by-step how the database executes this:
- WITH (CTE): Materializes
dept_maxas temporary result set(s). - FROM: Reads table
employees. - INNER JOIN: Combines rows from joined tables using the ON condition.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q8. Find employees whose salary is within 10% of the highest salary in their department
Problem: Find employees whose salary is within 10% of the highest salary in their department.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Which tables do I need? How are they related? What column connects them?
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH dept_max AS (
SELECT department_id, MAX(salary) AS max_salary
FROM employees
GROUP BY department_id
)
SELECT e.*
FROM employees e
JOIN dept_max d ON e.department_id =
d.department_id
WHERE e.salary >= 0.9 * d.max_salary;
Step-by-step how the database executes this:
- WITH (CTE): Materializes
dept_maxas temporary result set(s). - FROM: Reads table
employees. - INNER JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q9. Get the total number of orders per day, including days with zero orders
Problem: Get the total number of orders per day, including days with zero orders.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Which tables do I need? How are they related? What column connects them?
- Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH dates AS (
SELECT generate_series(MIN(order_date),
MAX(order_date), INTERVAL '1 day') AS day
FROM orders
)
SELECT d.day, COUNT(o.order_id) AS order_count
FROM dates d
LEFT JOIN orders o ON d.day = o.order_date
GROUP BY d.day
ORDER BY d.day;
Step-by-step how the database executes this:
- WITH (CTE): Materializes
datesas temporary result set(s). - FROM: Reads table
orders. - LEFT JOIN: Combines rows from joined tables using the ON condition.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q10. List products with sales above the average sales amount
Problem: List products with sales above the average sales amount.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need to filter groups after aggregation? That means HAVING, not WHERE.
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH avg_sales AS (
SELECT AVG(amount) AS avg_amount
FROM sales
)
SELECT product_id, SUM(amount) AS total_sales
FROM sales
GROUP BY product_id
HAVING SUM(amount) > (SELECT avg_amount
FROM avg_sales);
Step-by-step how the database executes this:
- WITH (CTE): Materializes
avg_salesas temporary result set(s). - FROM: Reads table
sales. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- HAVING: Filters groups. Only groups passing the condition remain.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
Q11. Find products with sales increasing every month for the last 3 months
Problem: Find products with sales increasing every month for the last 3 months.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need to filter groups after aggregation? That means HAVING, not WHERE.
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH monthly_sales AS (
SELECT product_id, DATE_TRUNC('month',
sale_date) AS month, SUM(amount) AS total_sales
FROM sales
WHERE sale_date >= CURRENT_DATE -
INTERVAL '3 months'
GROUP BY product_id, month
)
SELECT product_id
FROM monthly_sales
GROUP BY product_id
HAVING COUNT(*) = 3
AND MIN(total_sales) < MAX(total_sales)
AND total_sales[1] < total_sales[2] AND
total_sales[2] < total_sales[3]; -- depends on DB
support
Note: Exact syntax varies per RDBMS; may need
window functions.
Step-by-step how the database executes this:
- WITH (CTE): Materializes
monthly_salesas temporary result set(s). - FROM: Reads table
sales. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- HAVING: Filters groups. Only groups passing the condition remain.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
Q12. Find customers who have increased their order volume every month for the last 3 months
Problem: Find customers who have increased their order volume every month for the last 3 months.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need to filter groups after aggregation? That means HAVING, not WHERE.
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH monthly_orders AS (
SELECT customer_id, DATE_TRUNC('month',
order_date) AS month, COUNT(*) AS orders_count
FROM orders
WHERE order_date >= CURRENT_DATE -
INTERVAL '3 months'
GROUP BY customer_id, month
)
SELECT customer_id
FROM monthly_orders
GROUP BY customer_id
HAVING COUNT(*) = 3
AND MIN(orders_count) < MAX(orders_count); --
Needs detailed window check for strict increase
Step-by-step how the database executes this:
- WITH (CTE): Materializes
monthly_ordersas temporary result set(s). - FROM: Reads table
orders. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- HAVING: Filters groups. Only groups passing the condition remain.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
Q13. Find the employee with the maximum salary in each department
Problem: Find the employee with the maximum salary in each department.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Which tables do I need? How are they related? What column connects them?
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH dept_max AS (
SELECT department_id, MAX(salary) AS max_salary
FROM employees
GROUP BY department_id
)
SELECT e.*
FROM employees e
JOIN dept_max d ON e.department_id =
d.department_id AND e.salary = d.max_salary;
Step-by-step how the database executes this:
- WITH (CTE): Materializes
dept_maxas temporary result set(s). - FROM: Reads table
employees. - INNER JOIN: Combines rows from joined tables using the ON condition.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q14. Find customers with orders on every day in the last week
Problem: Find customers with orders on every day in the last week.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need to filter groups after aggregation? That means HAVING, not WHERE.
- Which tables do I need? How are they related? What column connects them?
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH days AS (
SELECT generate_series(CURRENT_DATE -
INTERVAL '6 days', CURRENT_DATE, INTERVAL
'1 day') AS day
)
SELECT customer_id
FROM orders o
JOIN days d ON o.order_date = d.day
GROUP BY customer_id
HAVING COUNT(DISTINCT o.order_date) = 7;
Step-by-step how the database executes this:
- WITH (CTE): Materializes
daysas temporary result set(s). - FROM: Reads table
orders. - INNER JOIN: Combines rows from joined tables using the ON condition.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- HAVING: Filters groups. Only groups passing the condition remain.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- DISTINCT: Removes duplicate rows by comparing all selected columns.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q15. Find employees whose salary is above the company’s average but below their department’s average
Problem: Find employees whose salary is above the company’s average but below their department’s average.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Which tables do I need? How are they related? What column connects them?
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH company_avg AS (SELECT AVG(salary) AS
avg_salary FROM employees),
dept_avg AS (
SELECT department_id, AVG(salary) AS
avg_salary
FROM employees
GROUP BY department_id
)
SELECT e.*
FROM employees e, company_avg ca
JOIN dept_avg da ON e.department_id =
da.department_id
WHERE e.salary > ca.avg_salary AND e.salary <
da.avg_salary;
Step-by-step how the database executes this:
- WITH (CTE): Materializes
company_avg, dept_avgas temporary result set(s). - FROM: Reads table
employees. - INNER JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q16. Find customers who made orders totaling more than the average order amount
Problem: Find customers who made orders totaling more than the average order amount.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need to filter groups after aggregation? That means HAVING, not WHERE.
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH avg_order AS (
SELECT AVG(amount) AS avg_amount FROM
orders
)
SELECT customer_id, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > (SELECT avg_amount
FROM avg_order);
Step-by-step how the database executes this:
- WITH (CTE): Materializes
avg_orderas temporary result set(s). - FROM: Reads table
orders. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- HAVING: Filters groups. Only groups passing the condition remain.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
Q17. Find customers who purchased a product but never reordered it
Problem: Find customers who purchased a product but never reordered it.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH order_counts AS (
SELECT customer_id, product_id, COUNT(*) AS
order_count
FROM sales
GROUP BY customer_id, product_id
)
SELECT customer_id, product_id
FROM order_counts
WHERE order_count = 1;
Step-by-step how the database executes this:
- WITH (CTE): Materializes
order_countsas temporary result set(s). - FROM: Reads table
sales. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
Q18. Find the top 3 employees with the highest salary increase over last year
Problem: Find the top 3 employees with the highest salary increase over last year.
How to think before writing:
- Which tables do I need? How are they related? What column connects them?
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
- How should the result be sorted? ASC (ascending) or DESC (descending)?
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH salary_last_year AS (
SELECT employee_id, salary AS last_year_salary
FROM salaries
WHERE year = EXTRACT(YEAR FROM
CURRENT_DATE) - 1
),
salary_this_year AS (
SELECT employee_id, salary AS this_year_salary
FROM salaries
WHERE year = EXTRACT(YEAR FROM
CURRENT_DATE)
)
SELECT t.employee_id, t.this_year_salary -
l.last_year_salary AS salary_increase
FROM salary_this_year t
JOIN salary_last_year l ON t.employee_id =
l.employee_id
ORDER BY salary_increase DESC
LIMIT 3;
Step-by-step how the database executes this:
- WITH (CTE): Materializes
salary_last_year, salary_this_yearas temporary result set(s). - FROM: Reads table
salaries. - INNER JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
- LIMIT/TOP: Returns only the first N rows from the sorted result.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q19. Find customers who placed orders every month for the last 6 months
Problem: Find customers who placed orders every month for the last 6 months.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need to filter groups after aggregation? That means HAVING, not WHERE.
- Which tables do I need? How are they related? What column connects them?
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH months AS (
SELECT generate_series(
DATE_TRUNC('month', CURRENT_DATE) -
INTERVAL '5 months',
DATE_TRUNC('month', CURRENT_DATE),
INTERVAL '1 month'
) AS month
),
customer_months AS (
SELECT customer_id, DATE_TRUNC('month',
order_date) AS month
FROM orders
WHERE order_date >= CURRENT_DATE -
INTERVAL '6 months'
GROUP BY customer_id, month
)
SELECT customer_id
FROM customer_months cm
JOIN months m ON cm.month = m.month
GROUP BY customer_id
HAVING COUNT(DISTINCT cm.month) = 6;
Step-by-step how the database executes this:
- WITH (CTE): Materializes
months, customer_monthsas temporary result set(s). - FROM: Reads table
orders. - INNER JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- HAVING: Filters groups. Only groups passing the condition remain.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- DISTINCT: Removes duplicate rows by comparing all selected columns.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q20. Find the percentage of employees in each department
Problem: Find the percentage of employees in each department.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH total_employees AS (SELECT COUNT(*) AS
total FROM employees)
SELECT department_id, COUNT(*) * 100.0 /
(SELECT total FROM total_employees) AS percentage
FROM employees
GROUP BY department_id;
Step-by-step how the database executes this:
- WITH (CTE): Materializes
total_employeesas temporary result set(s). - FROM: Reads table
employees. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
Q21. Find employees with the highest salary in each job title
Problem: Find employees with the highest salary in each job title.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Which tables do I need? How are they related? What column connects them?
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH max_salary_per_job AS (
SELECT job_title, MAX(salary) AS max_salary
FROM employees
GROUP BY job_title
)
SELECT e.*
FROM employees e
JOIN max_salary_per_job m ON e.job_title =
m.job_title AND e.salary = m.max_salary;
Step-by-step how the database executes this:
- WITH (CTE): Materializes
max_salary_per_jobas temporary result set(s). - FROM: Reads table
employees. - INNER JOIN: Combines rows from joined tables using the ON condition.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q22. Find customers who spent more than average in their country
Problem: Find customers who spent more than average in their country.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need to filter groups after aggregation? That means HAVING, not WHERE.
- Which tables do I need? How are they related? What column connects them?
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH avg_spent_per_country AS (
SELECT c.country, AVG(o.amount) AS avg_amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.country
)
SELECT c.customer_id, SUM(o.amount) AS
total_spent
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN avg_spent_per_country a ON c.country =
a.country
GROUP BY c.customer_id, c.country, a.avg_amount
HAVING SUM(o.amount) > a.avg_amount;
Step-by-step how the database executes this:
- WITH (CTE): Materializes
avg_spent_per_countryas temporary result set(s). - FROM: Reads table
customers. - INNER JOIN: Combines rows from joined tables using the ON condition.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- HAVING: Filters groups. Only groups passing the condition remain.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q23. Find customers who ordered all products from a specific category
Problem: Find customers who ordered all products from a specific category.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need to filter groups after aggregation? That means HAVING, not WHERE.
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
- Are there duplicates I need to remove? Use DISTINCT.
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH category_products AS (
SELECT product_id
FROM products
WHERE category_id = :category_id
),
customer_products AS (
SELECT customer_id, product_id
FROM sales
WHERE product_id IN (SELECT product_id FROM
category_products)
GROUP BY customer_id, product_id
)
SELECT customer_id
FROM customer_products
GROUP BY customer_id
HAVING COUNT(DISTINCT product_id) = (SELECT
COUNT(*) FROM category_products);
Step-by-step how the database executes this:
- WITH (CTE): Materializes
category_products, customer_productsas temporary result set(s). - FROM: Reads table
products. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- HAVING: Filters groups. Only groups passing the condition remain.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- DISTINCT: Removes duplicate rows by comparing all selected columns.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
Q24. Calculate the retention rate of customers month- over-month
Problem: Calculate the retention rate of customers month- over-month.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Which tables do I need? How are they related? What column connects them?
- Do I need to compare rows within the same table? That means a self-join with aliases.
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH monthly_customers AS (
SELECT customer_id, DATE_TRUNC('month',
order_date) AS month
FROM orders
GROUP BY customer_id, month
),
retention AS (
SELECT current.month AS current_month,
current.customer_id
FROM monthly_customers current
JOIN monthly_customers previous ON
current.customer_id = previous.customer_id
AND current.month = previous.month + INTERVAL
'1 month'
)
SELECT current_month, COUNT(DISTINCT
customer_id) * 100.0 /
(SELECT COUNT(DISTINCT customer_id)
FROM monthly_customers WHERE month =
current_month - INTERVAL '1 month') AS
retention_rate
FROM retention
GROUP BY current_month
ORDER BY current_month;
Step-by-step how the database executes this:
- WITH (CTE): Materializes
monthly_customers, retentionas temporary result set(s). - FROM: Reads table
orders. - INNER JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- DISTINCT: Removes duplicate rows by comparing all selected columns.
- ORDER BY: Sorts the final result set.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q25. Find products that were sold in every quarter of the current year
Problem: Find products that were sold in every quarter of the current year.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need to filter groups after aggregation? That means HAVING, not WHERE.
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
- Are there duplicates I need to remove? Use DISTINCT.
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH quarterly_sales AS (
SELECT product_id, DATE_TRUNC('quarter',
sale_date) AS quarter
FROM sales
WHERE EXTRACT(YEAR FROM sale_date) =
EXTRACT(YEAR FROM CURRENT_DATE)
GROUP BY product_id, quarter
)
SELECT product_id
FROM quarterly_sales
GROUP BY product_id
HAVING COUNT(DISTINCT quarter) = 4;
Step-by-step how the database executes this:
- WITH (CTE): Materializes
quarterly_salesas temporary result set(s). - FROM: Reads table
sales. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- HAVING: Filters groups. Only groups passing the condition remain.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- DISTINCT: Removes duplicate rows by comparing all selected columns.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
Q26. Find the most common product combinations in orders (pairs)
Problem: Find the most common product combinations in orders (pairs).
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Which tables do I need? How are they related? What column connects them?
- Do I need to compare rows within the same table? That means a self-join with aliases.
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH order_pairs AS (
SELECT o1.order_id, o1.product_id AS product1,
o2.product_id AS product2
FROM order_items o1
JOIN order_items o2 ON o1.order_id = o2.order_id
AND o1.product_id < o2.product_id
)
SELECT product1, product2, COUNT(*) AS
pair_count
FROM order_pairs
GROUP BY product1, product2
ORDER BY pair_count DESC
LIMIT 10;
Step-by-step how the database executes this:
- WITH (CTE): Materializes
order_pairsas temporary result set(s). - FROM: Reads table
order_items. - INNER JOIN: Combines rows from joined tables using the ON condition.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
- LIMIT/TOP: Returns only the first N rows from the sorted result.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q27. Find employees who have worked more than 40 hours in a week
Problem: Find employees who have worked more than 40 hours in a week.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH weekly_hours AS (
SELECT employee_id, DATE_TRUNC('week',
work_date) AS week_start, SUM(hours_worked) AS
total_hours
FROM work_logs
GROUP BY employee_id, week_start
)
SELECT employee_id, week_start, total_hours
FROM weekly_hours
WHERE total_hours > 40;
Step-by-step how the database executes this:
- WITH (CTE): Materializes
weekly_hoursas temporary result set(s). - FROM: Reads table
work_logs. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
Q28. Find departments where average salary is higher than the company average
Problem: Find departments where average salary is higher than the company average.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need to filter groups after aggregation? That means HAVING, not WHERE.
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH company_avg AS (
SELECT AVG(salary) AS avg_salary FROM
employees
)
SELECT department_id, AVG(salary) AS dept_avg
FROM employees
GROUP BY department_id
HAVING AVG(salary) > (SELECT avg_salary FROM
company_avg);
Step-by-step how the database executes this:
- WITH (CTE): Materializes
company_avgas temporary result set(s). - FROM: Reads table
employees. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- HAVING: Filters groups. Only groups passing the condition remain.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
Q29. Find employees whose salaries are within 10% of their department’s average salary
Problem: Find employees whose salaries are within 10% of their department’s average salary.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Which tables do I need? How are they related? What column connects them?
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH dept_avg AS (
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
)
SELECT e.*
FROM employees e
JOIN dept_avg d ON e.department_id =
d.department_id
WHERE e.salary BETWEEN d.avg_salary * 0.9 AND
d.avg_salary * 1.1;
Step-by-step how the database executes this:
- WITH (CTE): Materializes
dept_avgas temporary result set(s). - FROM: Reads table
employees. - INNER JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q30. Find customers who made purchases only in one month of the year
Problem: Find customers who made purchases only in one month of the year.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need to filter groups after aggregation? That means HAVING, not WHERE.
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH customer_months AS (
SELECT customer_id, DATE_TRUNC('month',
order_date) AS month
FROM orders
GROUP BY customer_id, month
)
SELECT customer_id
FROM customer_months
GROUP BY customer_id
HAVING COUNT(*) = 1;
Step-by-step how the database executes this:
- WITH (CTE): Materializes
customer_monthsas temporary result set(s). - FROM: Reads table
orders. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- HAVING: Filters groups. Only groups passing the condition remain.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
Q31. Find products with sales revenue above the average revenue per product
Problem: Find products with sales revenue above the average revenue per product.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need to filter groups after aggregation? That means HAVING, not WHERE.
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH avg_revenue AS (
SELECT AVG(total_revenue) AS avg_rev
FROM (
SELECT product_id, SUM(amount) AS
total_revenue
FROM sales
GROUP BY product_id
) sub
)
SELECT product_id, SUM(amount) AS total_revenue
FROM sales
GROUP BY product_id
HAVING SUM(amount) > (SELECT avg_rev FROM
avg_revenue);
Step-by-step how the database executes this:
- WITH (CTE): Materializes
avg_revenueas temporary result set(s). - FROM: Reads table
sales. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- HAVING: Filters groups. Only groups passing the condition remain.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
Q32. Find employees who worked on all projects in the company
Problem: Find employees who worked on all projects in the company.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Which tables do I need? How are they related? What column connects them?
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
- Are there duplicates I need to remove? Use DISTINCT.
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH total_projects AS (
SELECT COUNT(DISTINCT project_id) AS
project_count FROM projects
),
employee_projects AS (
SELECT employee_id, COUNT(DISTINCT
project_id) AS projects_worked
FROM project_assignments
GROUP BY employee_id
)
SELECT ep.employee_id
FROM employee_projects ep
JOIN total_projects tp ON 1=1
WHERE ep.projects_worked = tp.project_count;
Step-by-step how the database executes this:
- WITH (CTE): Materializes
total_projects, employee_projectsas temporary result set(s). - FROM: Reads table
projects. - INNER JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- DISTINCT: Removes duplicate rows by comparing all selected columns.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q33. Find customers who ordered products from all categories
Problem: Find customers who ordered products from all categories.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Which tables do I need? How are they related? What column connects them?
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
- Are there duplicates I need to remove? Use DISTINCT.
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH category_count AS (
SELECT COUNT(DISTINCT category_id) AS
total_categories FROM products
),
customer_categories AS (
SELECT customer_id, COUNT(DISTINCT
p.category_id) AS categories_ordered
FROM sales s
JOIN products p ON s.product_id = p.product_id
GROUP BY customer_id
)
SELECT customer_id
FROM customer_categories
JOIN category_count ON 1=1
WHERE categories_ordered = total_categories;
Step-by-step how the database executes this:
- WITH (CTE): Materializes
category_count, customer_categoriesas temporary result set(s). - FROM: Reads table
products. - INNER JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- DISTINCT: Removes duplicate rows by comparing all selected columns.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q34. Find the department with the largest increase in employee count compared to last year
Problem: Find the department with the largest increase in employee count compared to last year.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Which tables do I need? How are they related? What column connects them?
- Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH current_year AS (
SELECT department_id, COUNT(*) AS emp_count
FROM employees
WHERE hire_date <= CURRENT_DATE AND
(termination_date IS NULL OR termination_date >=
CURRENT_DATE)
GROUP BY department_id
),
last_year AS (
SELECT department_id, COUNT(*) AS emp_count
FROM employees
WHERE hire_date <= CURRENT_DATE -
INTERVAL '1 year' AND (termination_date IS NULL
OR termination_date >= CURRENT_DATE -
INTERVAL '1 year')
GROUP BY department_id
)
SELECT c.department_id, c.emp_count -
COALESCE(l.emp_count,0) AS increase
FROM current_year c
LEFT JOIN last_year l ON c.department_id =
l.department_id
ORDER BY increase DESC
LIMIT 1;
Step-by-step how the database executes this:
- WITH (CTE): Materializes
current_year, last_yearas temporary result set(s). - FROM: Reads table
employees. - LEFT JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
- LIMIT/TOP: Returns only the first N rows from the sorted result.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q35. Identify suppliers who delivered to all regions
Problem: Identify suppliers who delivered to all regions.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Which tables do I need? How are they related? What column connects them?
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
- Are there duplicates I need to remove? Use DISTINCT.
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH total_regions AS (
SELECT COUNT(DISTINCT region) AS total FROM
suppliers
),
supplier_regions AS (
SELECT supplier_id, COUNT(DISTINCT
delivery_region) AS regions_served
FROM deliveries
GROUP BY supplier_id
)
SELECT supplier_id
FROM supplier_regions
JOIN total_regions ON regions_served = total;
Step-by-step how the database executes this:
- WITH (CTE): Materializes
total_regions, supplier_regionsas temporary result set(s). - FROM: Reads table
suppliers. - INNER JOIN: Combines rows from joined tables using the ON condition.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- DISTINCT: Removes duplicate rows by comparing all selected columns.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Key Takeaway
This section covered 35 interview questions on CTEs: WITH Clause and Common Table Expressions. Each question followed the same learning pattern: understand the problem, think about the approach, write a pseudo query in plain English, translate to SQL, understand the execution order, and consider optimization.
A CTE (Common Table Expression) creates a temporary named result set using WITH. It exists only for the duration of the query. CTEs improve readability, can be referenced multiple times, and are required for recursive queries. Think of a CTE as a named subquery that you define once and use in the main query.
Practice each query on the shared sample table. If you can solve all 35 without looking at the answer, you have mastered this concept.
---