1. Find employees who earn more than the average salary.
Table: employees (employee_id, name, salary)
SELECT name, salary
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
);
Logic: Inner query calculates average salary. Outer query filters employees above average.
2. Find employees who earn the highest salary in each department.
Table: employees (employee_id, name, department, salary)
SELECT name, department, salary
FROM employees e
WHERE salary = (
SELECT MAX(salary)
FROM employees
WHERE department = e.department
);
Logic: Subquery runs per department. Matches max salary inside same department.
3. Find departments where average salary is greater than 60,000.
SELECT department
FROM (
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
) t
WHERE avg_salary > 60000;
Logic: Inner query calculates department average. Outer query filters required departments.
4. Same query using CTE.
WITH dept_avg AS (
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
)
SELECT department
FROM dept_avg
WHERE avg_salary > 60000;
Logic: CTE stores aggregated result. Final query reads from CTE.
5. Find customers who placed more orders than the average number of orders.
Tables: customers (customer_id, name), orders (order_id, customer_id)
SELECT customer_id
FROM orders
GROUP BY customer_id
HAVING COUNT(order_id) > (
SELECT AVG(order_count)
FROM (
SELECT COUNT(order_id) AS order_count
FROM orders
GROUP BY customer_id
) x
);
Logic: Inner query calculates orders per customer. Next level gets average. HAVING filters customers above average.
6. Find top-selling product by total sales amount.
Table: sales (product_id, amount)
SELECT product_id
FROM sales
GROUP BY product_id
HAVING SUM(amount) = (
SELECT MAX(total_sales)
FROM (
SELECT SUM(amount) AS total_sales
FROM sales
GROUP BY product_id
) t
);
Logic: Inner query calculates sales per product. Outer query finds max and matches it.
7. Rewrite using CTE.
WITH product_sales AS (
SELECT product_id, SUM(amount) AS total_sales
FROM sales
GROUP BY product_id
)
SELECT product_id
FROM product_sales
WHERE total_sales = (
SELECT MAX(total_sales)
FROM product_sales
);
Logic: CTE avoids repeating aggregation. Cleaner and readable.
8. Find employees whose salary is greater than their department average.
WITH dept_avg AS (
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
)
SELECT e.name, e.salary
FROM employees e
JOIN dept_avg d ON e.department = d.department
WHERE e.salary > d.avg_salary;
Logic: First compute department averages. Join back to employees. Filter higher earners.
Double Tap ♥️ For More