Back to SQL Practice: 331 Real Interview Queries
Q5Question 5 of 9

CTEs: WITH Clause and Common Table Expressions

Answer: A CTE (WITH clause) creates a temporary named result set that makes complex queries readable by breaking them into logical steps, and is required for recursive queries.

Key Concepts
  • A CTE (Common Table Expression) creates a temporary named result using WITH. It exists only for the single query that follows it.
  • CTEs improve readability by breaking complex queries into named steps. Each step can be referenced in later steps or the main query.
  • A CTE can reference a previous CTE: WITH step1 AS (...), step2 AS (SELECT ... FROM step1) SELECT * FROM step2.
  • CTEs are usually inlined by the optimizer (treated like subqueries). They do NOT always materialize as temp tables.
  • Recursive CTEs reference themselves. They need an anchor (base case) + recursive member connected by UNION ALL.
  • Use CTEs instead of deeply nested subqueries. They make the query readable and maintainable.
  • CTEs cannot be indexed. For very large intermediate results used multiple times, a temp table may perform better.

Definition

A CTE (WITH clause) creates a temporary named result set that makes complex queries readable by breaking them into logical steps, and is required for recursive queries.
  • 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.

Pseudo query (plain English first):

TEXT
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...

SQL Solution:

SQL
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:

  1. WITH (CTE): Materializes avg_salaries as temporary result set(s).
  2. FROM: Reads table employees.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. GROUP BY: Collapses rows into groups. One output row per unique group value.
  5. 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:

  1. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
  2. What condition filters the rows I need? Write the WHERE clause for that condition.

Pseudo query (plain English first):

TEXT
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...

SQL Solution:

SQL
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:

  1. WITH (CTE): Materializes primes as temporary result set(s).
  2. FROM: Reads table employees.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Which tables do I need? How are they related? What column connects them?
  4. Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
  5. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.

Pseudo query (plain English first):

TEXT
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...

SQL Solution:

SQL
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:

  1. WITH (CTE): Materializes monthly_sales as temporary result set(s).
  2. FROM: Reads table sales.
  3. LEFT JOIN: Combines rows from joined tables using the ON condition.
  4. GROUP BY: Collapses rows into groups. One output row per unique group value.
  5. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Do I need to filter groups after aggregation? That means HAVING, not WHERE.
  4. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
  5. Are there duplicates I need to remove? Use DISTINCT.

Pseudo query (plain English first):

TEXT
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...

SQL Solution:

SQL
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:

  1. WITH (CTE): Materializes months, customer_months as temporary result set(s).
  2. FROM: Reads table purchase_date.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. GROUP BY: Collapses rows into groups. One output row per unique group value.
  5. HAVING: Filters groups. Only groups passing the condition remain.
  6. SELECT: Projects the final columns and evaluates expressions/aliases.
  7. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Which tables do I need? How are they related? What column connects them?
  4. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.

Pseudo query (plain English first):

TEXT
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...

SQL Solution:

SQL
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:

  1. WITH (CTE): Materializes monthly_sales, total_monthly_sales as temporary result set(s).
  2. FROM: Reads table sales.
  3. INNER JOIN: Combines rows from joined tables using the ON condition.
  4. GROUP BY: Collapses rows into groups. One output row per unique group value.
  5. 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:

  1. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
  2. What condition filters the rows I need? Write the WHERE clause for that condition.
  3. Are there duplicates I need to remove? Use DISTINCT.

Pseudo query (plain English first):

TEXT
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...

SQL Solution:

SQL
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:

  1. WITH (CTE): Materializes jan_orders, feb_orders as temporary result set(s).
  2. FROM: Reads table orders.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. SELECT: Projects the final columns and evaluates expressions/aliases.
  5. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Which tables do I need? How are they related? What column connects them?
  4. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.

Pseudo query (plain English first):

TEXT
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...

SQL Solution:

SQL
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:

  1. WITH (CTE): Materializes dept_max as temporary result set(s).
  2. FROM: Reads table employees.
  3. INNER JOIN: Combines rows from joined tables using the ON condition.
  4. GROUP BY: Collapses rows into groups. One output row per unique group value.
  5. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Which tables do I need? How are they related? What column connects them?
  4. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.

Pseudo query (plain English first):

TEXT
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...

SQL Solution:

SQL
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:

  1. WITH (CTE): Materializes dept_max as temporary result set(s).
  2. FROM: Reads table employees.
  3. INNER JOIN: Combines rows from joined tables using the ON condition.
  4. WHERE: Filters individual rows. Rows failing the condition are discarded.
  5. GROUP BY: Collapses rows into groups. One output row per unique group value.
  6. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Which tables do I need? How are they related? What column connects them?
  4. Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
  5. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.

Pseudo query (plain English first):

TEXT
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...

SQL Solution:

SQL
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:

  1. WITH (CTE): Materializes dates as temporary result set(s).
  2. FROM: Reads table orders.
  3. LEFT JOIN: Combines rows from joined tables using the ON condition.
  4. GROUP BY: Collapses rows into groups. One output row per unique group value.
  5. SELECT: Projects the final columns and evaluates expressions/aliases.
  6. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Do I need to filter groups after aggregation? That means HAVING, not WHERE.
  4. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.

Pseudo query (plain English first):

TEXT
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...

SQL Solution:

SQL
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:

  1. WITH (CTE): Materializes avg_sales as temporary result set(s).
  2. FROM: Reads table sales.
  3. GROUP BY: Collapses rows into groups. One output row per unique group value.
  4. HAVING: Filters groups. Only groups passing the condition remain.
  5. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Do I need to filter groups after aggregation? That means HAVING, not WHERE.
  4. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.

Pseudo query (plain English first):

TEXT
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...

SQL Solution:

SQL
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:

  1. WITH (CTE): Materializes monthly_sales as temporary result set(s).
  2. FROM: Reads table sales.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. GROUP BY: Collapses rows into groups. One output row per unique group value.
  5. HAVING: Filters groups. Only groups passing the condition remain.
  6. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Do I need to filter groups after aggregation? That means HAVING, not WHERE.
  4. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.

Pseudo query (plain English first):

TEXT
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...

SQL Solution:

SQL
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:

  1. WITH (CTE): Materializes monthly_orders as temporary result set(s).
  2. FROM: Reads table orders.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. GROUP BY: Collapses rows into groups. One output row per unique group value.
  5. HAVING: Filters groups. Only groups passing the condition remain.
  6. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Which tables do I need? How are they related? What column connects them?
  4. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.

Pseudo query (plain English first):

TEXT
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...

SQL Solution:

SQL
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:

  1. WITH (CTE): Materializes dept_max as temporary result set(s).
  2. FROM: Reads table employees.
  3. INNER JOIN: Combines rows from joined tables using the ON condition.
  4. GROUP BY: Collapses rows into groups. One output row per unique group value.
  5. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Do I need to filter groups after aggregation? That means HAVING, not WHERE.
  4. Which tables do I need? How are they related? What column connects them?
  5. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.

Pseudo query (plain English first):

TEXT
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...

SQL Solution:

SQL
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:

  1. WITH (CTE): Materializes days as temporary result set(s).
  2. FROM: Reads table orders.
  3. INNER JOIN: Combines rows from joined tables using the ON condition.
  4. GROUP BY: Collapses rows into groups. One output row per unique group value.
  5. HAVING: Filters groups. Only groups passing the condition remain.
  6. SELECT: Projects the final columns and evaluates expressions/aliases.
  7. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Which tables do I need? How are they related? What column connects them?
  4. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.

Pseudo query (plain English first):

TEXT
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...

SQL Solution:

SQL
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:

  1. WITH (CTE): Materializes company_avg, dept_avg as temporary result set(s).
  2. FROM: Reads table employees.
  3. INNER JOIN: Combines rows from joined tables using the ON condition.
  4. WHERE: Filters individual rows. Rows failing the condition are discarded.
  5. GROUP BY: Collapses rows into groups. One output row per unique group value.
  6. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Do I need to filter groups after aggregation? That means HAVING, not WHERE.
  4. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.

Pseudo query (plain English first):

TEXT
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...

SQL Solution:

SQL
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:

  1. WITH (CTE): Materializes avg_order as temporary result set(s).
  2. FROM: Reads table orders.
  3. GROUP BY: Collapses rows into groups. One output row per unique group value.
  4. HAVING: Filters groups. Only groups passing the condition remain.
  5. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.

Pseudo query (plain English first):

TEXT
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...

SQL Solution:

SQL
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:

  1. WITH (CTE): Materializes order_counts as temporary result set(s).
  2. FROM: Reads table sales.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. GROUP BY: Collapses rows into groups. One output row per unique group value.
  5. 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:

  1. Which tables do I need? How are they related? What column connects them?
  2. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
  3. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

TEXT
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...

SQL Solution:

SQL
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:

  1. WITH (CTE): Materializes salary_last_year, salary_this_year as temporary result set(s).
  2. FROM: Reads table salaries.
  3. INNER JOIN: Combines rows from joined tables using the ON condition.
  4. WHERE: Filters individual rows. Rows failing the condition are discarded.
  5. SELECT: Projects the final columns and evaluates expressions/aliases.
  6. ORDER BY: Sorts the final result set.
  7. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Do I need to filter groups after aggregation? That means HAVING, not WHERE.
  4. Which tables do I need? How are they related? What column connects them?
  5. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.

Pseudo query (plain English first):

TEXT
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...

SQL Solution:

SQL
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:

  1. WITH (CTE): Materializes months, customer_months as temporary result set(s).
  2. FROM: Reads table orders.
  3. INNER JOIN: Combines rows from joined tables using the ON condition.
  4. WHERE: Filters individual rows. Rows failing the condition are discarded.
  5. GROUP BY: Collapses rows into groups. One output row per unique group value.
  6. HAVING: Filters groups. Only groups passing the condition remain.
  7. SELECT: Projects the final columns and evaluates expressions/aliases.
  8. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.

Pseudo query (plain English first):

TEXT
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...

SQL Solution:

SQL
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:

  1. WITH (CTE): Materializes total_employees as temporary result set(s).
  2. FROM: Reads table employees.
  3. GROUP BY: Collapses rows into groups. One output row per unique group value.
  4. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Which tables do I need? How are they related? What column connects them?
  4. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.

Pseudo query (plain English first):

TEXT
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...

SQL Solution:

SQL
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:

  1. WITH (CTE): Materializes max_salary_per_job as temporary result set(s).
  2. FROM: Reads table employees.
  3. INNER JOIN: Combines rows from joined tables using the ON condition.
  4. GROUP BY: Collapses rows into groups. One output row per unique group value.
  5. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Do I need to filter groups after aggregation? That means HAVING, not WHERE.
  4. Which tables do I need? How are they related? What column connects them?
  5. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.

Pseudo query (plain English first):

TEXT
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...

SQL Solution:

SQL
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:

  1. WITH (CTE): Materializes avg_spent_per_country as temporary result set(s).
  2. FROM: Reads table customers.
  3. INNER JOIN: Combines rows from joined tables using the ON condition.
  4. GROUP BY: Collapses rows into groups. One output row per unique group value.
  5. HAVING: Filters groups. Only groups passing the condition remain.
  6. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Do I need to filter groups after aggregation? That means HAVING, not WHERE.
  4. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
  5. Are there duplicates I need to remove? Use DISTINCT.

Pseudo query (plain English first):

TEXT
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...

SQL Solution:

SQL
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:

  1. WITH (CTE): Materializes category_products, customer_products as temporary result set(s).
  2. FROM: Reads table products.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. GROUP BY: Collapses rows into groups. One output row per unique group value.
  5. HAVING: Filters groups. Only groups passing the condition remain.
  6. SELECT: Projects the final columns and evaluates expressions/aliases.
  7. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Which tables do I need? How are they related? What column connects them?
  4. Do I need to compare rows within the same table? That means a self-join with aliases.
  5. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.

Pseudo query (plain English first):

TEXT
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...

SQL Solution:

SQL
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:

  1. WITH (CTE): Materializes monthly_customers, retention as temporary result set(s).
  2. FROM: Reads table orders.
  3. INNER JOIN: Combines rows from joined tables using the ON condition.
  4. WHERE: Filters individual rows. Rows failing the condition are discarded.
  5. GROUP BY: Collapses rows into groups. One output row per unique group value.
  6. SELECT: Projects the final columns and evaluates expressions/aliases.
  7. DISTINCT: Removes duplicate rows by comparing all selected columns.
  8. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Do I need to filter groups after aggregation? That means HAVING, not WHERE.
  4. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
  5. Are there duplicates I need to remove? Use DISTINCT.

Pseudo query (plain English first):

TEXT
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...

SQL Solution:

SQL
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:

  1. WITH (CTE): Materializes quarterly_sales as temporary result set(s).
  2. FROM: Reads table sales.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. GROUP BY: Collapses rows into groups. One output row per unique group value.
  5. HAVING: Filters groups. Only groups passing the condition remain.
  6. SELECT: Projects the final columns and evaluates expressions/aliases.
  7. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Which tables do I need? How are they related? What column connects them?
  4. Do I need to compare rows within the same table? That means a self-join with aliases.
  5. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.

Pseudo query (plain English first):

TEXT
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...

SQL Solution:

SQL
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:

  1. WITH (CTE): Materializes order_pairs as temporary result set(s).
  2. FROM: Reads table order_items.
  3. INNER JOIN: Combines rows from joined tables using the ON condition.
  4. GROUP BY: Collapses rows into groups. One output row per unique group value.
  5. SELECT: Projects the final columns and evaluates expressions/aliases.
  6. ORDER BY: Sorts the final result set.
  7. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.

Pseudo query (plain English first):

TEXT
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...

SQL Solution:

SQL
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:

  1. WITH (CTE): Materializes weekly_hours as temporary result set(s).
  2. FROM: Reads table work_logs.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. GROUP BY: Collapses rows into groups. One output row per unique group value.
  5. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Do I need to filter groups after aggregation? That means HAVING, not WHERE.
  4. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.

Pseudo query (plain English first):

TEXT
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...

SQL Solution:

SQL
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:

  1. WITH (CTE): Materializes company_avg as temporary result set(s).
  2. FROM: Reads table employees.
  3. GROUP BY: Collapses rows into groups. One output row per unique group value.
  4. HAVING: Filters groups. Only groups passing the condition remain.
  5. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Which tables do I need? How are they related? What column connects them?
  4. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.

Pseudo query (plain English first):

TEXT
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...

SQL Solution:

SQL
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:

  1. WITH (CTE): Materializes dept_avg as temporary result set(s).
  2. FROM: Reads table employees.
  3. INNER JOIN: Combines rows from joined tables using the ON condition.
  4. WHERE: Filters individual rows. Rows failing the condition are discarded.
  5. GROUP BY: Collapses rows into groups. One output row per unique group value.
  6. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Do I need to filter groups after aggregation? That means HAVING, not WHERE.
  4. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.

Pseudo query (plain English first):

TEXT
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...

SQL Solution:

SQL
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:

  1. WITH (CTE): Materializes customer_months as temporary result set(s).
  2. FROM: Reads table orders.
  3. GROUP BY: Collapses rows into groups. One output row per unique group value.
  4. HAVING: Filters groups. Only groups passing the condition remain.
  5. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Do I need to filter groups after aggregation? That means HAVING, not WHERE.
  4. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.

Pseudo query (plain English first):

TEXT
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...

SQL Solution:

SQL
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:

  1. WITH (CTE): Materializes avg_revenue as temporary result set(s).
  2. FROM: Reads table sales.
  3. GROUP BY: Collapses rows into groups. One output row per unique group value.
  4. HAVING: Filters groups. Only groups passing the condition remain.
  5. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Which tables do I need? How are they related? What column connects them?
  4. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
  5. Are there duplicates I need to remove? Use DISTINCT.

Pseudo query (plain English first):

TEXT
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...

SQL Solution:

SQL
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:

  1. WITH (CTE): Materializes total_projects, employee_projects as temporary result set(s).
  2. FROM: Reads table projects.
  3. INNER JOIN: Combines rows from joined tables using the ON condition.
  4. WHERE: Filters individual rows. Rows failing the condition are discarded.
  5. GROUP BY: Collapses rows into groups. One output row per unique group value.
  6. SELECT: Projects the final columns and evaluates expressions/aliases.
  7. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Which tables do I need? How are they related? What column connects them?
  4. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
  5. Are there duplicates I need to remove? Use DISTINCT.

Pseudo query (plain English first):

TEXT
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...

SQL Solution:

SQL
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:

  1. WITH (CTE): Materializes category_count, customer_categories as temporary result set(s).
  2. FROM: Reads table products.
  3. INNER JOIN: Combines rows from joined tables using the ON condition.
  4. WHERE: Filters individual rows. Rows failing the condition are discarded.
  5. GROUP BY: Collapses rows into groups. One output row per unique group value.
  6. SELECT: Projects the final columns and evaluates expressions/aliases.
  7. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Which tables do I need? How are they related? What column connects them?
  4. Do I need all rows from one table even without matches? That means LEFT JOIN, not INNER JOIN.
  5. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.

Pseudo query (plain English first):

TEXT
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...

SQL Solution:

SQL
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:

  1. WITH (CTE): Materializes current_year, last_year as temporary result set(s).
  2. FROM: Reads table employees.
  3. LEFT JOIN: Combines rows from joined tables using the ON condition.
  4. WHERE: Filters individual rows. Rows failing the condition are discarded.
  5. GROUP BY: Collapses rows into groups. One output row per unique group value.
  6. SELECT: Projects the final columns and evaluates expressions/aliases.
  7. ORDER BY: Sorts the final result set.
  8. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Which tables do I need? How are they related? What column connects them?
  4. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
  5. Are there duplicates I need to remove? Use DISTINCT.

Pseudo query (plain English first):

TEXT
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...

SQL Solution:

SQL
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:

  1. WITH (CTE): Materializes total_regions, supplier_regions as temporary result set(s).
  2. FROM: Reads table suppliers.
  3. INNER JOIN: Combines rows from joined tables using the ON condition.
  4. GROUP BY: Collapses rows into groups. One output row per unique group value.
  5. SELECT: Projects the final columns and evaluates expressions/aliases.
  6. 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.

---

Share
Use ← → arrow keys to navigate