Back to SQL Practice: 331 Real Interview Queries
Q2Question 2 of 9

Aggregation: GROUP BY, HAVING, COUNT, SUM, AVG

Answer: GROUP BY collapses rows into groups, aggregate functions (COUNT, SUM, AVG, MAX, MIN) compute one value per group, and HAVING filters groups after aggregation.

Key Concepts
  • GROUP BY collapses multiple rows into one row per unique value. Every column in SELECT that is not inside an aggregate function must appear in GROUP BY.
  • HAVING filters groups after aggregation. WHERE filters rows before grouping. Example: WHERE salary > 50000 filters rows, HAVING COUNT(*) > 3 filters groups.
  • COUNT(*) counts all rows including NULLs. COUNT(column) counts only non-NULL values. COUNT(DISTINCT column) counts unique non-NULL values.
  • SUM adds values, AVG computes the average, MAX/MIN find the largest/smallest. All ignore NULL values except COUNT(*).
  • You cannot use an aggregate in WHERE. Use HAVING instead. WHERE runs before GROUP BY, HAVING runs after.
  • Conditional aggregation with CASE: SUM(CASE WHEN status = 'Active' THEN 1 ELSE 0 END) counts active rows without a WHERE filter.
  • GROUP BY with ROLLUP adds subtotal and grand total rows. GROUP BY with CUBE adds all possible subtotal combinations.

Definition

GROUP BY collapses rows into groups, aggregate functions (COUNT, SUM, AVG, MAX, MIN) compute one value per group, and HAVING filters groups after aggregation.
  • GROUP BY: Collapses rows into groups by unique column values. One output row per group.
  • HAVING: Filters groups after aggregation. Unlike WHERE which filters individual rows before grouping.
  • **COUNT(*)**: Counts all rows including NULLs. COUNT(col) ignores NULLs. COUNT(DISTINCT col) counts unique non-NULL values.
  • SUM/AVG/MAX/MIN: Aggregate functions that ignore NULLs. AVG = SUM/COUNT(non-null).
  • CASE in aggregation: SUM(CASE WHEN ... THEN 1 ELSE 0 END) for conditional counting.
  • Rule: Every non-aggregated column in SELECT must be in GROUP BY.

This section is a focused tutorial on Aggregation. 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

## Aggregation: GROUP BY, HAVING, COUNT, SUM, AVG

GROUP BY collapses multiple rows into one row per unique value. Aggregate functions (COUNT, SUM, AVG, MAX, MIN) compute one value per group. HAVING filters groups after aggregation. WHERE filters rows before grouping, HAVING filters groups after.

Sample Tables

employees table (shared across all queries in this section):

idnamesalarydepartment_idmanager_idjoin_datebonusrating
1Rajesh90000101NULL2020-01-1510000A
2Priya7500010112021-03-208000B
3Amit9500010212019-06-1012000A
4Sneha6000010232022-08-015000C
5Vikram9000010312020-11-2510000A
6Meena7500010352023-02-147000B

departments table:

department_iddepartment_namelocation
101Retail BankingMumbai
102CorporateDelhi
103TreasuryChennai
104ITPune

---

Q1. Count employees in each department having more than 5 employees

Problem: Count employees in each department having more than 5 employees.

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.

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
KEEP ONLY groups that satisfy the group condition

SQL Solution:

SQL
SELECT department_id, COUNT(*) AS 
num_employees 
FROM employees 
GROUP BY department_id 
HAVING COUNT(*) > 5;

Step-by-step how the database executes this:

  1. FROM: Reads table employees.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. HAVING: Filters groups. Only groups passing the condition remain.
  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.

---

Q2. Write a query to perform a conditional aggregation (count males and females in each department)

Problem: Write a query to perform a conditional aggregation (count males and females 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. Do I need conditional logic? Use CASE WHEN ... THEN ... ELSE ... END.

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group

SQL Solution:

SQL
SELECT department_id, 
       COUNT(CASE WHEN gender = 'M' THEN 1 
END) AS male_count, 
       COUNT(CASE WHEN gender = 'F' THEN 1 
END) AS female_count 
FROM employees 
GROUP BY department_id;

Step-by-step how the database executes this:

  1. FROM: Reads table employees.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. 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.

---

Q3. Write a query to get the first and last purchase date for each customer

Problem: Write a query to get the first and last purchase date for each customer.

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.

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group

SQL Solution:

SQL
SELECT customer_id, 
       MIN(purchase_date) AS first_purchase, 
       MAX(purchase_date) AS last_purchase 
FROM sales 
GROUP BY customer_id;

Step-by-step how the database executes this:

  1. FROM: Reads table sales.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. 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.

---

Q4. Write a query to find the number of employees in each job title

Problem: Write a query to find the number of employees 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.

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group

SQL Solution:

SQL
SELECT job_title, COUNT(*) AS num_employees 
FROM employees 
GROUP BY job_title;

Step-by-step how the database executes this:

  1. FROM: Reads table employees.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. 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.

---

Q5. Identify employees who haven’t received a salary raise in more than a year

Problem: Identify employees who haven’t received a salary raise in more than a 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. Which tables do I need? How are they related? What column connects them?

Pseudo query (plain English first):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
KEEP ONLY groups that satisfy the group condition

SQL Solution:

SQL
SELECT e.name 
FROM employees e 
JOIN salary_history sh ON e.id = sh.employee_id 
GROUP BY e.id, e.name 
HAVING MAX(sh.raise_date) < CURRENT_DATE - 
INTERVAL '1 year';

Step-by-step how the database executes this:

  1. FROM: Reads table employees.
  2. INNER JOIN: Combines rows from joined tables using the ON condition.
  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 JOIN columns. Ensure both sides of ON are indexed.

---

Q6. Find the department with the lowest average salary

Problem: Find the department with the lowest 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. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SORT the result

SQL Solution:

SQL
SELECT department_id, AVG(salary) AS avg_salary 
FROM employees 
GROUP BY department_id 
ORDER BY avg_salary 
LIMIT 1;

Step-by-step how the database executes this:

  1. FROM: Reads table employees.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.
  4. ORDER BY: Sorts the final result set.
  5. LIMIT/TOP: Returns only the first N rows from the sorted result.

Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.

---

Q7. Find employees who have worked for multiple departments over time

Problem: Find employees who have worked for multiple departments over time.

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. Are there duplicates I need to remove? Use DISTINCT.

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
KEEP ONLY groups that satisfy the group condition

SQL Solution:

SQL
SELECT employee_id 
FROM employee_department_history 
GROUP BY employee_id 
HAVING COUNT(DISTINCT department_id) > 1;

Step-by-step how the database executes this:

  1. FROM: Reads table employee_department_history.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. HAVING: Filters groups. Only groups passing the condition remain.
  4. SELECT: Projects the final columns and evaluates expressions/aliases.
  5. 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.

---

Q8. Find average order value per month and product category

Problem: Find average order value per month and product 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.

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group

SQL Solution:

SQL
SELECT DATE_TRUNC('month', order_date) AS 
order_month, category_id, AVG(order_value) AS 
avg_order_value 
FROM orders 
GROUP BY order_month, category_id;

Step-by-step how the database executes this:

  1. FROM: Reads table orders.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. 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.

---

Q9. Find the average tenure of employees by department

Problem: Find the average tenure of employees by 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.

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group

SQL Solution:

SQL
SELECT department_id, AVG(DATE_PART('year', 
CURRENT_DATE - hire_date)) AS avg_tenure_years 
FROM employees 
GROUP BY department_id;

Step-by-step how the database executes this:

  1. FROM: Reads table employees.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. 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.

---

Q10. Find customers who purchased more than once in the same day

Problem: Find customers who purchased more than once in the same day.

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.

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
KEEP ONLY groups that satisfy the group condition

SQL Solution:

SQL
SELECT customer_id, purchase_date, COUNT(*) AS 
purchase_count 
FROM sales 
GROUP BY customer_id, purchase_date 
HAVING COUNT(*) > 1;

Step-by-step how the database executes this:

  1. FROM: Reads table sales.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. HAVING: Filters groups. Only groups passing the condition remain.
  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.

---

Q11. Write a query to find duplicate rows based on multiple columns

Problem: Write a query to find duplicate rows based on multiple columns.

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.

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
KEEP ONLY groups that satisfy the group condition

SQL Solution:

SQL
SELECT column1, column2, COUNT(*) 
FROM table_name 
GROUP BY column1, column2 
HAVING COUNT(*) > 1;

Step-by-step how the database executes this:

  1. FROM: Reads table table_name.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. HAVING: Filters groups. Only groups passing the condition remain.
  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.

---

Q12. Write a query to concatenate employee names in each department (string aggregation)

Problem: Write a query to concatenate employee names in each department (string aggregation).

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.

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group

SQL Solution:

SQL
SELECT department_id, STRING_AGG(name, ', ') AS 
employee_names 
FROM employees 
GROUP BY department_id;

Step-by-step how the database executes this:

  1. FROM: Reads table employees.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. 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. Show the department with the highest number of employees and the count

Problem: Show the department with the highest number of employees and the count.

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. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SORT the result

SQL Solution:

SQL
SELECT department_id, COUNT(*) AS 
employee_count 
FROM employees 
GROUP BY department_id 
ORDER BY employee_count DESC 
LIMIT 1;

Step-by-step how the database executes this:

  1. FROM: Reads table employees.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.
  4. ORDER BY: Sorts the final result set.
  5. LIMIT/TOP: Returns only the first N rows from the sorted result.

Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.

---

Q14. Calculate the total sales amount and number of orders per customer in the last year

Problem: Calculate the total sales amount and number of orders per customer in the 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.

Pseudo query (plain English first):

TEXT
GET columns FROM table
KEEP ONLY rows that satisfy the condition
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group

SQL Solution:

SQL
SELECT customer_id, COUNT(*) AS total_orders, 
SUM(amount) AS total_sales 
FROM sales 
WHERE sale_date >= CURRENT_DATE - 
INTERVAL '1 year' 
GROUP BY customer_id;

Step-by-step how the database executes this:

  1. FROM: Reads table sales.
  2. WHERE: Filters individual rows. Rows failing the condition are discarded.
  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.

---

Q15. Write a query to find the department with the highest average years of experience

Problem: Write a query to find the department with the highest average years of experience.

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. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SORT the result

SQL Solution:

SQL
SELECT department_id, AVG(EXTRACT(year FROM 
CURRENT_DATE - hire_date)) AS 
avg_experience_years 
FROM employees 
GROUP BY department_id 
ORDER BY avg_experience_years DESC 
LIMIT 1;

Step-by-step how the database executes this:

  1. FROM: Reads table current_date.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.
  4. ORDER BY: Sorts the final result set.
  5. LIMIT/TOP: Returns only the first N rows from the sorted result.

Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.

---

Q16. Find the maximum salary gap between any two employees within the same department

Problem: Find the maximum salary gap between any two employees within the same 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.

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group

SQL Solution:

SQL
SELECT department_id, MAX(salary) - MIN(salary) 
AS salary_gap 
FROM employees 
GROUP BY department_id;

Step-by-step how the database executes this:

  1. FROM: Reads table employees.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. 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 both product A and product B

Problem: Find customers who purchased both product A and product B.

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. Are there duplicates I need to remove? Use DISTINCT.

Pseudo query (plain English first):

TEXT
GET columns FROM table
KEEP ONLY rows that satisfy the condition
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
KEEP ONLY groups that satisfy the group condition

SQL Solution:

SQL
SELECT customer_id 
FROM sales 
WHERE product_id IN ('A', 'B') 
GROUP BY customer_id 
HAVING COUNT(DISTINCT product_id) = 2;

Step-by-step how the database executes this:

  1. FROM: Reads table sales.
  2. WHERE: Filters individual rows. Rows failing the condition are discarded.
  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.
  6. 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.

---

Q18. Find employees who have worked in more than 3 different departments

Problem: Find employees who have worked in more than 3 different departments.

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. Are there duplicates I need to remove? Use DISTINCT.

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
KEEP ONLY groups that satisfy the group condition

SQL Solution:

SQL
SELECT employee_id 
FROM employee_department_history 
GROUP BY employee_id 
HAVING COUNT(DISTINCT department_id) > 3;

Step-by-step how the database executes this:

  1. FROM: Reads table employee_department_history.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. HAVING: Filters groups. Only groups passing the condition remain.
  4. SELECT: Projects the final columns and evaluates expressions/aliases.
  5. 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.

---

Q19. Find the first order date for each customer

Problem: Find the first order date for each customer.

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.

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group

SQL Solution:

SQL
SELECT customer_id, MIN(order_date) AS 
first_order_date 
FROM orders 
GROUP BY customer_id;

Step-by-step how the database executes this:

  1. FROM: Reads table orders.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. 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.

---

Q20. Find employees who have been promoted more than twice

Problem: Find employees who have been promoted more than twice.

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.

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
KEEP ONLY groups that satisfy the group condition

SQL Solution:

SQL
SELECT employee_id, COUNT(*) AS 
promotion_count 
FROM promotions 
GROUP BY employee_id 
HAVING COUNT(*) > 2;

Step-by-step how the database executes this:

  1. FROM: Reads table promotions.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. HAVING: Filters groups. Only groups passing the condition remain.
  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 the difference in days between the earliest and latest orders per customer

Problem: Find the difference in days between the earliest and latest orders per customer.

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.

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group

SQL Solution:

SQL
SELECT customer_id, MAX(order_date) - 
MIN(order_date) AS days_between 
FROM orders 
GROUP BY customer_id;

Step-by-step how the database executes this:

  1. FROM: Reads table orders.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. 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.

---

Q22. Find customers who placed orders only in the last 6 months

Problem: Find customers who placed orders only in 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.

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
KEEP ONLY groups that satisfy the group condition

SQL Solution:

SQL
SELECT customer_id 
FROM orders 
GROUP BY customer_id 
HAVING MIN(order_date) >= CURRENT_DATE - 
INTERVAL '6 months';

Step-by-step how the database executes this:

  1. FROM: Reads table orders.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. HAVING: Filters groups. Only groups passing the condition remain.
  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.

---

Q23. Find the department with the most employees

Problem: Find the department with the most employees.

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. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SORT the result

SQL Solution:

SQL
SELECT department_id, COUNT(*) AS 
employee_count 
FROM employees 
GROUP BY department_id 
ORDER BY employee_count DESC 
LIMIT 1;

Step-by-step how the database executes this:

  1. FROM: Reads table employees.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.
  4. ORDER BY: Sorts the final result set.
  5. LIMIT/TOP: Returns only the first N rows from the sorted result.

Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.

---

Q24. Find employees with the highest number of dependents

Problem: Find employees with the highest number of dependents.

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. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SORT the result

SQL Solution:

SQL
SELECT employee_id, COUNT(*) AS 
dependent_count 
FROM dependents 
GROUP BY employee_id 
ORDER BY dependent_count DESC 
LIMIT 1;

Step-by-step how the database executes this:

  1. FROM: Reads table dependents.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.
  4. ORDER BY: Sorts the final result set.
  5. LIMIT/TOP: Returns only the first N rows from the sorted result.

Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.

---

Q25. Get the most recent order date per customer

Problem: Get the most recent order date per customer.

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.

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group

SQL Solution:

SQL
SELECT customer_id, MAX(order_date) AS 
last_order_date 
FROM orders 
GROUP BY customer_id;

Step-by-step how the database executes this:

  1. FROM: Reads table orders.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. 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.

---

Q26. Get the number of employees hired each year

Problem: Get the number of employees hired each 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. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SORT the result

SQL Solution:

SQL
SELECT EXTRACT(YEAR FROM hire_date) AS 
hire_year, COUNT(*) AS count 
FROM employees 
GROUP BY hire_year 
ORDER BY hire_year;

Step-by-step how the database executes this:

  1. FROM: Reads table hire_date.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.
  4. ORDER BY: Sorts the final result set.

Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.

---

Q27. Find the number of employees with the same job title per department

Problem: Find the number of employees with the same job title per 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.

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group

SQL Solution:

SQL
SELECT department_id, job_title, COUNT(*) AS 
employee_count 
FROM employees 
GROUP BY department_id, job_title;

Step-by-step how the database executes this:

  1. FROM: Reads table employees.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. 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. Calculate average salary by department and job title

Problem: Calculate average salary by department and 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.

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group

SQL Solution:

SQL
SELECT department_id, job_title, AVG(salary) AS 
avg_salary 
FROM employees 
GROUP BY department_id, job_title;

Step-by-step how the database executes this:

  1. FROM: Reads table employees.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. 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 who have been promoted more than once

Problem: Find employees who have been promoted more than once.

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.

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
KEEP ONLY groups that satisfy the group condition

SQL Solution:

SQL
SELECT employee_id, COUNT(*) AS 
promotion_count 
FROM promotions 
GROUP BY employee_id 
HAVING COUNT(*) > 1;

Step-by-step how the database executes this:

  1. FROM: Reads table promotions.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. HAVING: Filters groups. Only groups passing the condition remain.
  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.

---

Q30. Calculate total sales by product category

Problem: Calculate total sales by product 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. Which tables do I need? How are they related? What column connects them?

Pseudo query (plain English first):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group

SQL Solution:

SQL
SELECT p.category_id, SUM(s.amount) AS total_sales 
FROM sales s 
JOIN products p ON s.product_id = p.product_id 
GROUP BY p.category_id;

Step-by-step how the database executes this:

  1. FROM: Reads table sales.
  2. INNER JOIN: Combines rows from joined tables using the ON condition.
  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 JOIN columns. Ensure both sides of ON are indexed.

---

Q31. Find the top 3 products by sales amount

Problem: Find the top 3 products by 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. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SORT the result

SQL Solution:

SQL
SELECT product_id, SUM(amount) AS total_sales 
FROM sales 
GROUP BY product_id 
ORDER BY total_sales DESC 
LIMIT 3;

Step-by-step how the database executes this:

  1. FROM: Reads table sales.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.
  4. ORDER BY: Sorts the final result set.
  5. LIMIT/TOP: Returns only the first N rows from the sorted result.

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 products with sales only in the current month

Problem: Find products with sales only in the current 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. Do I need to filter groups after aggregation? That means HAVING, not WHERE.

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
KEEP ONLY groups that satisfy the group condition

SQL Solution:

SQL
SELECT product_id 
FROM sales 
GROUP BY product_id 
HAVING MAX(sale_date) >= 
DATE_TRUNC('month', CURRENT_DATE) 
AND MIN(sale_date) >= DATE_TRUNC('month', 
CURRENT_DATE);

Step-by-step how the database executes this:

  1. FROM: Reads table sales.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. HAVING: Filters groups. Only groups passing the condition remain.
  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.

---

Q33. Find employees who have worked on more than 5 projects

Problem: Find employees who have worked on more than 5 projects.

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. Are there duplicates I need to remove? Use DISTINCT.

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
KEEP ONLY groups that satisfy the group condition

SQL Solution:

SQL
SELECT employee_id, COUNT(DISTINCT project_id) 
AS project_count 
FROM project_assignments 
GROUP BY employee_id 
HAVING COUNT(DISTINCT project_id) > 5;

Step-by-step how the database executes this:

  1. FROM: Reads table project_assignments.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. HAVING: Filters groups. Only groups passing the condition remain.
  4. SELECT: Projects the final columns and evaluates expressions/aliases.
  5. 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.

---

Q34. Find the total number of products sold each day

Problem: Find the total number of products sold each day.

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. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SORT the result

SQL Solution:

SQL
SELECT sale_date, SUM(quantity) AS 
total_quantity_sold 
FROM sales 
GROUP BY sale_date 
ORDER BY sale_date;

Step-by-step how the database executes this:

  1. FROM: Reads table sales.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.
  4. ORDER BY: Sorts the final result set.

Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.

---

Q35. Find customers with orders totaling more than $10,000

Problem: Find customers with orders totaling more than $10,000.

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.

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
KEEP ONLY groups that satisfy the group condition

SQL Solution:

SQL
SELECT customer_id, SUM(amount) AS total_amount 
FROM sales 
GROUP BY customer_id 
HAVING SUM(amount) > 10000; 
 
🔷 Medium to Advanced SQL Questions (131–200)

Step-by-step how the database executes this:

  1. FROM: Reads table sales.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. HAVING: Filters groups. Only groups passing the condition remain.
  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.

---

Q36. Find the department with the lowest average salary

Problem: Find the department with the lowest 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. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SORT the result

SQL Solution:

SQL
SELECT department_id, AVG(salary) AS avg_salary 
FROM employees 
GROUP BY department_id 
ORDER BY avg_salary 
LIMIT 1;

Step-by-step how the database executes this:

  1. FROM: Reads table employees.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.
  4. ORDER BY: Sorts the final result set.
  5. LIMIT/TOP: Returns only the first N rows from the sorted result.

Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.

---

Q37. Find customers who ordered products only from one category

Problem: Find customers who ordered products only from one 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. Which tables do I need? How are they related? What column connects them?
  5. Are there duplicates I need to remove? Use DISTINCT.

Pseudo query (plain English first):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
KEEP ONLY groups that satisfy the group condition

SQL Solution:

SQL
SELECT customer_id 
FROM sales s 
JOIN products p ON s.product_id = p.product_id 
GROUP BY customer_id 
HAVING COUNT(DISTINCT p.category_id) = 1;

Step-by-step how the database executes this:

  1. FROM: Reads table sales.
  2. INNER JOIN: Combines rows from joined tables using the ON condition.
  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.
  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.

---

Q38. Find the department with the highest variance in salaries

Problem: Find the department with the highest variance in salaries.

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. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SORT the result

SQL Solution:

SQL
SELECT department_id, VAR_SAMP(salary) AS 
salary_variance 
FROM employees 
GROUP BY department_id 
ORDER BY salary_variance DESC 
LIMIT 1;

Step-by-step how the database executes this:

  1. FROM: Reads table employees.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.
  4. ORDER BY: Sorts the final result set.
  5. LIMIT/TOP: Returns only the first N rows from the sorted result.

Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.

---

Q39. Find customers who purchased both Product A and Product B

Problem: Find customers who purchased both Product A and Product B.

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. Are there duplicates I need to remove? Use DISTINCT.

Pseudo query (plain English first):

TEXT
GET columns FROM table
KEEP ONLY rows that satisfy the condition
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
KEEP ONLY groups that satisfy the group condition

SQL Solution:

SQL
SELECT customer_id 
FROM sales 
WHERE product_id IN ('A', 'B') 
GROUP BY customer_id 
HAVING COUNT(DISTINCT product_id) = 2;

Step-by-step how the database executes this:

  1. FROM: Reads table sales.
  2. WHERE: Filters individual rows. Rows failing the condition are discarded.
  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.
  6. 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.

---

Q40. Find the top N customers by total sales amount

Problem: Find the top N customers by total 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. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SORT the result

SQL Solution:

SQL
SELECT customer_id, SUM(amount) AS total_sales 
FROM sales 
GROUP BY customer_id 
ORDER BY total_sales DESC 
LIMIT N;

Step-by-step how the database executes this:

  1. FROM: Reads table sales.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.
  4. ORDER BY: Sorts the final result set.
  5. LIMIT/TOP: Returns only the first N rows from the sorted result.

Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.

---

Q41. Find the month with the highest sales in the current year

Problem: Find the month with the highest sales in 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. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

TEXT
GET columns FROM table
KEEP ONLY rows that satisfy the condition
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SORT the result

SQL Solution:

SQL
SELECT DATE_TRUNC('month', sale_date) AS 
month, SUM(amount) AS total_sales 
FROM sales 
WHERE EXTRACT(YEAR FROM sale_date) = 
EXTRACT(YEAR FROM CURRENT_DATE) 
GROUP BY month 
ORDER BY total_sales DESC 
LIMIT 1;

Step-by-step how the database executes this:

  1. FROM: Reads table sales.
  2. WHERE: Filters individual rows. Rows failing the condition are discarded.
  3. GROUP BY: Collapses rows into groups. One output row per unique group value.
  4. SELECT: Projects the final columns and evaluates expressions/aliases.
  5. ORDER BY: Sorts the final result set.
  6. LIMIT/TOP: Returns only the first N rows from the sorted result.

Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.

---

Q42. Get the average salary of employees hired each year

Problem: Get the average salary of employees hired each 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. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SORT the result

SQL Solution:

SQL
SELECT EXTRACT(YEAR FROM hire_date) AS 
year, AVG(salary) AS avg_salary 
FROM employees 
GROUP BY year 
ORDER BY year;

Step-by-step how the database executes this:

  1. FROM: Reads table hire_date.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.
  4. ORDER BY: Sorts the final result set.

Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.

---

Q43. Find the department with the most promotions

Problem: Find the department with the most promotions.

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. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SORT the result

SQL Solution:

SQL
SELECT e.department_id, COUNT(*) AS 
promotion_count 
FROM promotions p 
JOIN employees e ON p.employee_id = e.id 
GROUP BY e.department_id 
ORDER BY promotion_count DESC 
LIMIT 1;

Step-by-step how the database executes this:

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

---

Q44. Find customers who ordered products from at least 3 different categories

Problem: Find customers who ordered products from at least 3 different 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. 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. Are there duplicates I need to remove? Use DISTINCT.

Pseudo query (plain English first):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
KEEP ONLY groups that satisfy the group condition

SQL Solution:

SQL
SELECT customer_id 
FROM sales s 
JOIN products p ON s.product_id = p.product_id 
GROUP BY customer_id 
HAVING COUNT(DISTINCT p.category_id) >= 3;

Step-by-step how the database executes this:

  1. FROM: Reads table sales.
  2. INNER JOIN: Combines rows from joined tables using the ON condition.
  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.
  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.

---

Q45. Calculate total revenue and number of orders per country

Problem: Calculate total revenue and number of orders per 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. Which tables do I need? How are they related? What column connects them?

Pseudo query (plain English first):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group

SQL Solution:

SQL
SELECT c.country, COUNT(o.order_id) AS 
order_count, SUM(o.amount) AS total_revenue 
FROM customers c 
JOIN orders o ON c.customer_id = o.customer_id 
GROUP BY c.country;

Step-by-step how the database executes this:

  1. FROM: Reads table customers.
  2. INNER JOIN: Combines rows from joined tables using the ON condition.
  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 JOIN columns. Ensure both sides of ON are indexed.

---

Q46. Find the difference in days between the first and last order for each customer

Problem: Find the difference in days between the first and last order for each customer.

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.

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group

SQL Solution:

SQL
SELECT customer_id, MAX(order_date) - 
MIN(order_date) AS days_between 
FROM orders 
GROUP BY customer_id;

Step-by-step how the database executes this:

  1. FROM: Reads table orders.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. 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.

---

Q47. Find the product that has been sold in the highest quantity in a single order

Problem: Find the product that has been sold in the highest quantity in a single order.

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. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SORT the result

SQL Solution:

SQL
SELECT product_id, MAX(quantity) AS 
max_quantity_in_order 
FROM sales 
GROUP BY product_id 
ORDER BY max_quantity_in_order DESC 
LIMIT 1;

Step-by-step how the database executes this:

  1. FROM: Reads table sales.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.
  4. ORDER BY: Sorts the final result set.
  5. LIMIT/TOP: Returns only the first N rows from the sorted result.

Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.

---

Q48. Find customers with sales in at least 3 different years

Problem: Find customers with sales in at least 3 different years.

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. Are there duplicates I need to remove? Use DISTINCT.

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
KEEP ONLY groups that satisfy the group condition

SQL Solution:

SQL
SELECT customer_id 
FROM sales 
GROUP BY customer_id 
HAVING COUNT(DISTINCT EXTRACT(YEAR 
FROM sale_date)) >= 3;

Step-by-step how the database executes this:

  1. FROM: Reads table sales.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. HAVING: Filters groups. Only groups passing the condition remain.
  4. SELECT: Projects the final columns and evaluates expressions/aliases.
  5. 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.

---

Q49. Find the average order amount per customer per year

Problem: Find the average order amount per customer per 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.

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group

SQL Solution:

SQL
SELECT customer_id, EXTRACT(YEAR FROM 
order_date) AS year, AVG(amount) AS 
avg_order_amount 
FROM orders 
GROUP BY customer_id, year;

Step-by-step how the database executes this:

  1. FROM: Reads table order_date.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. 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.

---

Q50. Find the most recent promotion date per employee

Problem: Find the most recent promotion date per employee.

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.

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group

SQL Solution:

SQL
SELECT employee_id, MAX(promotion_date) AS 
last_promotion_date 
FROM promotions 
GROUP BY employee_id;

Step-by-step how the database executes this:

  1. FROM: Reads table promotions.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. 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.

---

Q51. Find the month with the lowest sales in the past year

Problem: Find the month with the lowest sales in the past 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. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

TEXT
GET columns FROM table
KEEP ONLY rows that satisfy the condition
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SORT the result

SQL Solution:

SQL
SELECT DATE_TRUNC('month', sale_date) AS 
month, SUM(amount) AS total_sales 
FROM sales 
WHERE sale_date >= CURRENT_DATE - 
INTERVAL '1 year' 
GROUP BY month 
ORDER BY total_sales 
LIMIT 1;

Step-by-step how the database executes this:

  1. FROM: Reads table sales.
  2. WHERE: Filters individual rows. Rows failing the condition are discarded.
  3. GROUP BY: Collapses rows into groups. One output row per unique group value.
  4. SELECT: Projects the final columns and evaluates expressions/aliases.
  5. ORDER BY: Sorts the final result set.
  6. LIMIT/TOP: Returns only the first N rows from the sorted result.

Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.

---

Q52. Calculate the number of employees hired each month in the last year

Problem: Calculate the number of employees hired each month in the 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. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

TEXT
GET columns FROM table
KEEP ONLY rows that satisfy the condition
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SORT the result

SQL Solution:

SQL
SELECT DATE_TRUNC('month', hire_date) AS 
month, COUNT(*) AS hires 
FROM employees 
WHERE hire_date >= CURRENT_DATE - 
INTERVAL '1 year' 
GROUP BY month 
ORDER BY month;

Step-by-step how the database executes this:

  1. FROM: Reads table employees.
  2. WHERE: Filters individual rows. Rows failing the condition are discarded.
  3. GROUP BY: Collapses rows into groups. One output row per unique group value.
  4. SELECT: Projects the final columns and evaluates expressions/aliases.
  5. ORDER BY: Sorts the final result set.

Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.

---

Q53. Find the department with the highest number of projects

Problem: Find the department with the highest number of projects.

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. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SORT the result

SQL Solution:

SQL
SELECT department_id, COUNT(*) AS project_count 
FROM projects 
GROUP BY department_id 
ORDER BY project_count DESC 
LIMIT 1;

Step-by-step how the database executes this:

  1. FROM: Reads table projects.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.
  4. ORDER BY: Sorts the final result set.
  5. LIMIT/TOP: Returns only the first N rows from the sorted result.

Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.

---

Q54. Find customers with average order amount above $500

Problem: Find customers with average order amount above $500.

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.

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
KEEP ONLY groups that satisfy the group condition

SQL Solution:

SQL
SELECT customer_id, AVG(amount) AS 
avg_order_amount 
FROM orders 
GROUP BY customer_id 
HAVING AVG(amount) > 500;

Step-by-step how the database executes this:

  1. FROM: Reads table orders.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. HAVING: Filters groups. Only groups passing the condition remain.
  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.

---

Q55. Find orders where the total quantity exceeds 100 units

Problem: Find orders where the total quantity exceeds 100 units.

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.

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
KEEP ONLY groups that satisfy the group condition

SQL Solution:

SQL
SELECT order_id, SUM(quantity) AS total_quantity 
FROM order_items 
GROUP BY order_id 
HAVING SUM(quantity) > 100;

Step-by-step how the database executes this:

  1. FROM: Reads table order_items.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. HAVING: Filters groups. Only groups passing the condition remain.
  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.

---

Q56. Write a query to find employees who worked on more than 3 projects in 2023

Problem: Write a query to find employees who worked on more than 3 projects in 2023.

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. Are there duplicates I need to remove? Use DISTINCT.

Pseudo query (plain English first):

TEXT
GET columns FROM table
KEEP ONLY rows that satisfy the condition
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
KEEP ONLY groups that satisfy the group condition

SQL Solution:

SQL
SELECT employee_id, COUNT(DISTINCT project_id) 
AS project_count 
FROM project_assignments 
WHERE assignment_date BETWEEN '2023-01-01' 
AND '2023-12-31' 
GROUP BY employee_id 
HAVING COUNT(DISTINCT project_id) > 3;

Step-by-step how the database executes this:

  1. FROM: Reads table project_assignments.
  2. WHERE: Filters individual rows. Rows failing the condition are discarded.
  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.
  6. 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.

---

Q57. Find customers whose last order was placed more than 1 year ago

Problem: Find customers whose last order was placed more than 1 year ago.

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.

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
KEEP ONLY groups that satisfy the group condition

SQL Solution:

SQL
SELECT customer_id, MAX(order_date) AS 
last_order_date 
FROM orders 
GROUP BY customer_id 
HAVING MAX(order_date) < CURRENT_DATE - 
INTERVAL '1 year';

Step-by-step how the database executes this:

  1. FROM: Reads table orders.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. HAVING: Filters groups. Only groups passing the condition remain.
  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.

---

Q58. Find the average salary increase percentage per department

Problem: Find the average salary increase percentage per 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?

Pseudo query (plain English first):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group

SQL Solution:

SQL
SELECT e.department_id, AVG((e.salary - 
p.old_salary) / p.old_salary * 100) AS avg_increase_pct 
FROM employees e 
JOIN promotions p ON e.id = p.employee_id 
GROUP BY e.department_id;

Step-by-step how the database executes this:

  1. FROM: Reads table employees.
  2. INNER JOIN: Combines rows from joined tables using the ON condition.
  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 JOIN columns. Ensure both sides of ON are indexed.

---

Q59. Find customers with orders totaling more than $5000 in the last 6 months

Problem: Find customers with orders totaling more than $5000 in 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.

Pseudo query (plain English first):

TEXT
GET columns FROM table
KEEP ONLY rows that satisfy the condition
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
KEEP ONLY groups that satisfy the group condition

SQL Solution:

SQL
SELECT customer_id, SUM(amount) AS total_amount 
FROM orders 
WHERE order_date >= CURRENT_DATE - 
INTERVAL '6 months' 
GROUP BY customer_id 
HAVING SUM(amount) > 5000;

Step-by-step how the database executes this:

  1. FROM: Reads table orders.
  2. WHERE: Filters individual rows. Rows failing the condition are discarded.
  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.

---

Q60. Find the day with the highest number of new hires

Problem: Find the day with the highest number of new hires.

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. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SORT the result

SQL Solution:

SQL
SELECT hire_date, COUNT(*) AS hires 
FROM employees 
GROUP BY hire_date 
ORDER BY hires DESC 
LIMIT 1;

Step-by-step how the database executes this:

  1. FROM: Reads table employees.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.
  4. ORDER BY: Sorts the final result set.
  5. LIMIT/TOP: Returns only the first N rows from the sorted result.

Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.

---

Q61. Find the number of employees who have worked in more than one department

Problem: Find the number of employees who have worked in more than one 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. Do I need to filter groups after aggregation? That means HAVING, not WHERE.
  4. Are there duplicates I need to remove? Use DISTINCT.

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
KEEP ONLY groups that satisfy the group condition

SQL Solution:

SQL
SELECT employee_id 
FROM employee_department_history 
GROUP BY employee_id 
HAVING COUNT(DISTINCT department_id) > 1;

Step-by-step how the database executes this:

  1. FROM: Reads table employee_department_history.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. HAVING: Filters groups. Only groups passing the condition remain.
  4. SELECT: Projects the final columns and evaluates expressions/aliases.
  5. 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.

---

Q62. SQL Query 2023

Problem: SQL Query 2023.

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. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

TEXT
GET columns FROM table
KEEP ONLY rows that satisfy the condition
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SORT the result

SQL Solution:

SQL
SELECT customer_id, SUM(quantity) AS 
total_quantity 
FROM sales 
WHERE EXTRACT(YEAR FROM sale_date) = 2023 
GROUP BY customer_id 
ORDER BY total_quantity DESC 
LIMIT 1;

Step-by-step how the database executes this:

  1. FROM: Reads table sales.
  2. WHERE: Filters individual rows. Rows failing the condition are discarded.
  3. GROUP BY: Collapses rows into groups. One output row per unique group value.
  4. SELECT: Projects the final columns and evaluates expressions/aliases.
  5. ORDER BY: Sorts the final result set.
  6. LIMIT/TOP: Returns only the first N rows from the sorted result.

Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.

---

Q63. Find the average days taken to ship orders per shipping method

Problem: Find the average days taken to ship orders per shipping method.

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.

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group

SQL Solution:

SQL
SELECT shipping_method, AVG(shipping_date - 
order_date) AS avg_shipping_days 
FROM orders 
GROUP BY shipping_method;

Step-by-step how the database executes this:

  1. FROM: Reads table orders.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. 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.

---

Q64. Find the total number of unique customers per product category

Problem: Find the total number of unique customers per product 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. Which tables do I need? How are they related? What column connects them?
  4. Are there duplicates I need to remove? Use DISTINCT.

Pseudo query (plain English first):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group

SQL Solution:

SQL
SELECT p.category_id, COUNT(DISTINCT 
s.customer_id) AS unique_customers 
FROM sales s 
JOIN products p ON s.product_id = p.product_id 
GROUP BY p.category_id;

Step-by-step how the database executes this:

  1. FROM: Reads table sales.
  2. INNER JOIN: Combines rows from joined tables using the ON condition.
  3. GROUP BY: Collapses rows into groups. One output row per unique group value.
  4. SELECT: Projects the final columns and evaluates expressions/aliases.
  5. DISTINCT: Removes duplicate rows by comparing all selected columns.

Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.

---

Q65. Find the number of employees who have changed departments more than twice

Problem: Find the number of employees who have changed departments more than twice.

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. Are there duplicates I need to remove? Use DISTINCT.

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
KEEP ONLY groups that satisfy the group condition

SQL Solution:

SQL
SELECT employee_id 
FROM employee_department_history 
GROUP BY employee_id 
HAVING COUNT(DISTINCT department_id) > 2;

Step-by-step how the database executes this:

  1. FROM: Reads table employee_department_history.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. HAVING: Filters groups. Only groups passing the condition remain.
  4. SELECT: Projects the final columns and evaluates expressions/aliases.
  5. 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.

---

Q66. Find the product with the highest average rating

Problem: Find the product with the highest average rating.

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. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SORT the result

SQL Solution:

SQL
SELECT product_id, AVG(rating) AS avg_rating 
FROM product_reviews 
GROUP BY product_id 
ORDER BY avg_rating DESC 
LIMIT 1;

Step-by-step how the database executes this:

  1. FROM: Reads table product_reviews.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.
  4. ORDER BY: Sorts the final result set.
  5. LIMIT/TOP: Returns only the first N rows from the sorted result.

Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.

---

Q67. Find the number of employees who share the same birthday

Problem: Find the number of employees who share the same birthday.

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.

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
KEEP ONLY groups that satisfy the group condition

SQL Solution:

SQL
SELECT birth_date, COUNT(*) AS count_employees 
FROM employees 
GROUP BY birth_date 
HAVING COUNT(*) > 1;

Step-by-step how the database executes this:

  1. FROM: Reads table employees.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. HAVING: Filters groups. Only groups passing the condition remain.
  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.

---

Q68. Find customers who ordered the same product multiple times in one day

Problem: Find customers who ordered the same product multiple times in one day.

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.

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
KEEP ONLY groups that satisfy the group condition

SQL Solution:

SQL
SELECT customer_id, product_id, order_date, 
COUNT(*) AS order_count 
FROM sales 
GROUP BY customer_id, product_id, order_date 
HAVING COUNT(*) > 1;

Step-by-step how the database executes this:

  1. FROM: Reads table sales.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. HAVING: Filters groups. Only groups passing the condition remain.
  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.

---

Q69. Find the day with the largest difference between maximum and minimum temperature

Problem: Find the day with the largest difference between maximum and minimum temperature.

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. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SORT the result

SQL Solution:

SQL
SELECT weather_date, MAX(temperature) - 
MIN(temperature) AS temp_diff 
FROM weather_data 
GROUP BY weather_date 
ORDER BY temp_diff DESC 
LIMIT 1;

Step-by-step how the database executes this:

  1. FROM: Reads table weather_data.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.
  4. ORDER BY: Sorts the final result set.
  5. LIMIT/TOP: Returns only the first N rows from the sorted result.

Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.

---

Q70. Find products with sales only in a specific country

Problem: Find products with sales only in a specific 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. Are there duplicates I need to remove? Use DISTINCT.

Pseudo query (plain English first):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
KEEP ONLY groups that satisfy the group condition

SQL Solution:

SQL
SELECT product_id 
FROM sales s 
JOIN customers c ON s.customer_id = c.customer_id 
GROUP BY product_id 
HAVING COUNT(DISTINCT c.country) = 1;

Step-by-step how the database executes this:

  1. FROM: Reads table sales.
  2. INNER JOIN: Combines rows from joined tables using the ON condition.
  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.
  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.

---

Q71. Find the employee who worked the most hours in a project

Problem: Find the employee who worked the most hours in a project.

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. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SORT the result

SQL Solution:

SQL
SELECT employee_id, project_id, 
MAX(hours_worked) AS max_hours 
FROM project_assignments 
GROUP BY employee_id, project_id 
ORDER BY max_hours DESC 
LIMIT 1;

Step-by-step how the database executes this:

  1. FROM: Reads table project_assignments.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.
  4. ORDER BY: Sorts the final result set.
  5. LIMIT/TOP: Returns only the first N rows from the sorted result.

Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.

---

Q72. Find the first order date for each customer

Problem: Find the first order date for each customer.

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.

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group

SQL Solution:

SQL
SELECT customer_id, MIN(order_date) AS 
first_order_date 
FROM orders 
GROUP BY customer_id;

Step-by-step how the database executes this:

  1. FROM: Reads table orders.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. 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.

---

Q73. Calculate the ratio of males to females in each department

Problem: Calculate the ratio of males to females 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. Do I need conditional logic? Use CASE WHEN ... THEN ... ELSE ... END.

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group

SQL Solution:

SQL
SELECT department_id, 
       SUM(CASE WHEN gender = 'M' THEN 1 ELSE 0 
END) * 1.0 / NULLIF(SUM(CASE WHEN gender = 'F' 
THEN 1 ELSE 0 END), 0) AS male_to_female_ratio 
FROM employees 
GROUP BY department_id;

Step-by-step how the database executes this:

  1. FROM: Reads table employees.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. 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.

---

Q74. Find employees with the highest number of direct reports

Problem: Find employees with the highest number of direct reports.

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. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

TEXT
GET columns FROM table
KEEP ONLY rows that satisfy the condition
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SORT the result

SQL Solution:

SQL
SELECT manager_id, COUNT(*) AS report_count 
FROM employees 
WHERE manager_id IS NOT NULL 
GROUP BY manager_id 
ORDER BY report_count DESC 
LIMIT 1;

Step-by-step how the database executes this:

  1. FROM: Reads table employees.
  2. WHERE: Filters individual rows. Rows failing the condition are discarded.
  3. GROUP BY: Collapses rows into groups. One output row per unique group value.
  4. SELECT: Projects the final columns and evaluates expressions/aliases.
  5. ORDER BY: Sorts the final result set.
  6. LIMIT/TOP: Returns only the first N rows from the sorted result.

Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.

---

Q75. Find the average time difference between order and delivery

Problem: Find the average time difference between order and delivery.

How to think before writing:

1. What condition filters the rows I need? Write the WHERE clause for that condition.

Pseudo query (plain English first):

TEXT
GET columns FROM table
KEEP ONLY rows that satisfy the condition

SQL Solution:

SQL
SELECT AVG(delivery_date - order_date) AS 
avg_delivery_time 
FROM orders 
WHERE delivery_date IS NOT NULL;

Step-by-step how the database executes this:

  1. FROM: Reads table orders.
  2. WHERE: Filters individual rows. Rows failing the condition are discarded.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.

Optimization: Add an index on the WHERE column(s) to avoid a full table scan.

---

Q76. Find the department with the youngest average employee age

Problem: Find the department with the youngest average employee age.

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. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SORT the result

SQL Solution:

SQL
SELECT department_id, AVG(EXTRACT(YEAR 
FROM AGE(CURRENT_DATE, birth_date))) AS 
avg_age 
FROM employees 
GROUP BY department_id 
ORDER BY avg_age 
LIMIT 1;

Step-by-step how the database executes this:

  1. FROM: Reads table age.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.
  4. ORDER BY: Sorts the final result set.
  5. LIMIT/TOP: Returns only the first N rows from the sorted result.

Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.

---

Q77. Find the employee(s) with the highest number of late arrivals

Problem: Find the employee(s) with the highest number of late arrivals.

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. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

TEXT
GET columns FROM table
KEEP ONLY rows that satisfy the condition
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SORT the result

SQL Solution:

SQL
SELECT employee_id, COUNT(*) AS late_count 
FROM attendance 
WHERE arrival_time > scheduled_start_time 
GROUP BY employee_id 
ORDER BY late_count DESC 
LIMIT 1;

Step-by-step how the database executes this:

  1. FROM: Reads table attendance.
  2. WHERE: Filters individual rows. Rows failing the condition are discarded.
  3. GROUP BY: Collapses rows into groups. One output row per unique group value.
  4. SELECT: Projects the final columns and evaluates expressions/aliases.
  5. ORDER BY: Sorts the final result set.
  6. LIMIT/TOP: Returns only the first N rows from the sorted result.

Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.

---

Q78. Find the total revenue generated per sales representative

Problem: Find the total revenue generated per sales representative.

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.

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group

SQL Solution:

SQL
SELECT sales_rep_id, SUM(amount) AS total_revenue 
FROM sales 
GROUP BY sales_rep_id;

Step-by-step how the database executes this:

  1. FROM: Reads table sales.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. 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.

---

Q79. Find customers with orders where no product quantity is less than 5

Problem: Find customers with orders where no product quantity is less than 5.

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. Are there duplicates I need to remove? Use DISTINCT.

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
KEEP ONLY groups that satisfy the group condition

SQL Solution:

SQL
SELECT DISTINCT order_id 
FROM order_items 
GROUP BY order_id 
HAVING MIN(quantity) >= 5;

Step-by-step how the database executes this:

  1. FROM: Reads table order_items.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. HAVING: Filters groups. Only groups passing the condition remain.
  4. SELECT: Projects the final columns and evaluates expressions/aliases.
  5. 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.

---

Q80. Find products ordered only by customers from one country

Problem: Find products ordered only by customers from one 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. Are there duplicates I need to remove? Use DISTINCT.

Pseudo query (plain English first):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
KEEP ONLY groups that satisfy the group condition

SQL Solution:

SQL
SELECT product_id 
FROM sales s 
JOIN customers c ON s.customer_id = c.customer_id 
GROUP BY product_id 
HAVING COUNT(DISTINCT c.country) = 1;

Step-by-step how the database executes this:

  1. FROM: Reads table sales.
  2. INNER JOIN: Combines rows from joined tables using the ON condition.
  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.
  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.

---

Q81. Find the total discount given in each month

Problem: Find the total discount given in each 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. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SORT the result

SQL Solution:

SQL
SELECT DATE_TRUNC('month', order_date) AS 
month, SUM(discount_amount) AS total_discount 
FROM orders 
GROUP BY month 
ORDER BY month;

Step-by-step how the database executes this:

  1. FROM: Reads table orders.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.
  4. ORDER BY: Sorts the final result set.

Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.

---

Q82. Find the department with the most projects completed last year

Problem: Find the department with the most projects completed 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. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

TEXT
GET columns FROM table
KEEP ONLY rows that satisfy the condition
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SORT the result

SQL Solution:

SQL
SELECT department_id, COUNT(*) AS 
completed_projects 
FROM projects 
WHERE status = 'Completed' AND completion_date 
BETWEEN DATE_TRUNC('year', 
CURRENT_DATE) - INTERVAL '1 year' AND 
DATE_TRUNC('year', CURRENT_DATE) - 
INTERVAL '1 day' 
GROUP BY department_id 
ORDER BY completed_projects DESC 
LIMIT 1;

Step-by-step how the database executes this:

  1. FROM: Reads table projects.
  2. WHERE: Filters individual rows. Rows failing the condition are discarded.
  3. GROUP BY: Collapses rows into groups. One output row per unique group value.
  4. SELECT: Projects the final columns and evaluates expressions/aliases.
  5. ORDER BY: Sorts the final result set.
  6. LIMIT/TOP: Returns only the first N rows from the sorted result.

Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.

---

Q83. Calculate the average time to close tickets per support agent

Problem: Calculate the average time to close tickets per support agent.

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.

Pseudo query (plain English first):

TEXT
GET columns FROM table
KEEP ONLY rows that satisfy the condition
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group

SQL Solution:

SQL
SELECT support_agent_id, AVG(closed_date - 
opened_date) AS avg_close_time 
FROM support_tickets 
WHERE closed_date IS NOT NULL 
GROUP BY support_agent_id;

Step-by-step how the database executes this:

  1. FROM: Reads table support_tickets.
  2. WHERE: Filters individual rows. Rows failing the condition are discarded.
  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.

---

Q84. Find the first and last login date for each user

Problem: Find the first and last login date for each user.

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.

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group

SQL Solution:

SQL
SELECT user_id, MIN(login_date) AS first_login, 
MAX(login_date) AS last_login 
FROM user_logins 
GROUP BY user_id;

Step-by-step how the database executes this:

  1. FROM: Reads table user_logins.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. 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.

---

Q85. Find departments where more than 50% of employees have a salary above $60,000

Problem: Find departments where more than 50% of employees have a salary above $60,000.

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. Do I need conditional logic? Use CASE WHEN ... THEN ... ELSE ... END.

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
KEEP ONLY groups that satisfy the group condition

SQL Solution:

SQL
SELECT department_id 
FROM employees 
GROUP BY department_id 
HAVING AVG(CASE WHEN salary > 60000 THEN 1 
ELSE 0 END) > 0.5;

Step-by-step how the database executes this:

  1. FROM: Reads table employees.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. HAVING: Filters groups. Only groups passing the condition remain.
  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.

---

Q86. Find the average tenure of employees by department

Problem: Find the average tenure of employees by 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.

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group

SQL Solution:

SQL
SELECT department_id, AVG(DATE_PART('year', 
CURRENT_DATE - hire_date)) AS avg_tenure_years 
FROM employees 
GROUP BY department_id;

Step-by-step how the database executes this:

  1. FROM: Reads table employees.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. 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.

---

Q87. Find the number of orders placed on weekends vs weekdays

Problem: Find the number of orders placed on weekends vs weekdays.

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 conditional logic? Use CASE WHEN ... THEN ... ELSE ... END.

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group

SQL Solution:

SQL
SELECT CASE 
         WHEN EXTRACT(DOW FROM order_date) IN 
(0,6) THEN 'Weekend' 
         ELSE 'Weekday' 
       END AS day_type, 
       COUNT(*) AS order_count 
FROM orders 
GROUP BY day_type;

Step-by-step how the database executes this:

  1. FROM: Reads table order_date.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. 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.

---

Q88. Find the percentage of orders with discounts per month

Problem: Find the percentage of orders with discounts 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. How should the result be sorted? ASC (ascending) or DESC (descending)?
  4. Do I need conditional logic? Use CASE WHEN ... THEN ... ELSE ... END.

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SORT the result

SQL Solution:

SQL
SELECT DATE_TRUNC('month', order_date) AS 
month, 
       100.0 * SUM(CASE WHEN discount > 0 THEN 1 
ELSE 0 END) / COUNT(*) AS discount_percentage 
FROM orders 
GROUP BY month 
ORDER BY month;

Step-by-step how the database executes this:

  1. FROM: Reads table orders.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.
  4. ORDER BY: Sorts the final result set.

Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.

---

Q89. Find products with sales only during holiday seasons

Problem: Find products with sales only during holiday seasons.

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. Can I solve the inner part first as a subquery, then use the result in the outer query?

Pseudo query (plain English first):

TEXT
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result

SQL Solution:

SQL
SELECT product_id 
FROM sales s 
JOIN holidays h ON s.sale_date = h.holiday_date 
GROUP BY product_id 
HAVING COUNT(*) = (SELECT COUNT(*) FROM 
sales WHERE product_id = s.product_id);

Step-by-step how the database executes this:

  1. FROM: Reads table sales.
  2. INNER JOIN: Combines rows from joined tables using the ON condition.
  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 JOIN columns. Ensure both sides of ON are indexed.

---

Q90. Find the average order value per customer segment

Problem: Find the average order value per customer segment.

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?

Pseudo query (plain English first):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group

SQL Solution:

SQL
SELECT segment, AVG(o.amount) AS 
avg_order_value 
FROM customers c 
JOIN orders o ON c.customer_id = o.customer_id 
GROUP BY segment;

Step-by-step how the database executes this:

  1. FROM: Reads table customers.
  2. INNER JOIN: Combines rows from joined tables using the ON condition.
  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 JOIN columns. Ensure both sides of ON are indexed.

---

Q91. Find employees who manage more than 3 projects

Problem: Find employees who manage more than 3 projects.

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. Are there duplicates I need to remove? Use DISTINCT.

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
KEEP ONLY groups that satisfy the group condition

SQL Solution:

SQL
SELECT manager_id, COUNT(DISTINCT project_id) 
AS project_count 
FROM projects 
GROUP BY manager_id 
HAVING COUNT(DISTINCT project_id) > 3;

Step-by-step how the database executes this:

  1. FROM: Reads table projects.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. HAVING: Filters groups. Only groups passing the condition remain.
  4. SELECT: Projects the final columns and evaluates expressions/aliases.
  5. 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.

---

Q92. Find the total number of unique products sold in the last quarter

Problem: Find the total number of unique products sold in the last quarter.

How to think before writing:

  1. What condition filters the rows I need? Write the WHERE clause for that condition.
  2. Are there duplicates I need to remove? Use DISTINCT.

Pseudo query (plain English first):

TEXT
GET columns FROM table
KEEP ONLY rows that satisfy the condition

SQL Solution:

SQL
SELECT COUNT(DISTINCT product_id) AS 
unique_products_sold 
FROM sales 
WHERE sale_date >= DATE_TRUNC('quarter', 
CURRENT_DATE) - INTERVAL '3 months' 
  AND sale_date < DATE_TRUNC('quarter', 
CURRENT_DATE);

Step-by-step how the database executes this:

  1. FROM: Reads table sales.
  2. WHERE: Filters individual rows. Rows failing the condition are discarded.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.
  4. DISTINCT: Removes duplicate rows by comparing all selected columns.

Optimization: Add an index on the WHERE column(s) to avoid a full table scan.

---

Q93. Find the top 5 customers by total order value in the last year

Problem: Find the top 5 customers by total order value in the 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. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

TEXT
GET columns FROM table
KEEP ONLY rows that satisfy the condition
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SORT the result

SQL Solution:

SQL
SELECT customer_id, SUM(amount) AS 
total_order_value 
FROM orders 
WHERE order_date >= CURRENT_DATE - 
INTERVAL '1 year' 
GROUP BY customer_id 
ORDER BY total_order_value DESC 
LIMIT 5;

Step-by-step how the database executes this:

  1. FROM: Reads table orders.
  2. WHERE: Filters individual rows. Rows failing the condition are discarded.
  3. GROUP BY: Collapses rows into groups. One output row per unique group value.
  4. SELECT: Projects the final columns and evaluates expressions/aliases.
  5. ORDER BY: Sorts the final result set.
  6. LIMIT/TOP: Returns only the first N rows from the sorted result.

Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.

---

Q94. Find the number of employees who changed departments in the last year

Problem: Find the number of employees who changed departments in the last year.

How to think before writing:

  1. What condition filters the rows I need? Write the WHERE clause for that condition.
  2. Are there duplicates I need to remove? Use DISTINCT.

Pseudo query (plain English first):

TEXT
GET columns FROM table
KEEP ONLY rows that satisfy the condition

SQL Solution:

SQL
SELECT COUNT(DISTINCT employee_id) 
FROM employee_department_history 
WHERE change_date >= CURRENT_DATE - 
INTERVAL '1 year';

Step-by-step how the database executes this:

  1. FROM: Reads table employee_department_history.
  2. WHERE: Filters individual rows. Rows failing the condition are discarded.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.
  4. DISTINCT: Removes duplicate rows by comparing all selected columns.

Optimization: Add an index on the WHERE column(s) to avoid a full table scan.

---

Q95. Find the average salary for each job title within each department

Problem: Find the average salary for each job title within 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.

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group

SQL Solution:

SQL
SELECT department_id, job_title, AVG(salary) AS 
avg_salary 
FROM employees 
GROUP BY department_id, job_title;

Step-by-step how the database executes this:

  1. FROM: Reads table employees.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. 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.

---

Q96. Find customers who placed orders with multiple payment methods

Problem: Find customers who placed orders with multiple payment methods.

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. Are there duplicates I need to remove? Use DISTINCT.

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
KEEP ONLY groups that satisfy the group condition

SQL Solution:

SQL
SELECT customer_id 
FROM orders 
GROUP BY customer_id 
HAVING COUNT(DISTINCT payment_method) > 1;

Step-by-step how the database executes this:

  1. FROM: Reads table orders.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. HAVING: Filters groups. Only groups passing the condition remain.
  4. SELECT: Projects the final columns and evaluates expressions/aliases.
  5. 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.

---

Q97. Find the total number of orders placed each day in the last week

Problem: Find the total number of orders placed each 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. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

TEXT
GET columns FROM table
KEEP ONLY rows that satisfy the condition
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SORT the result

SQL Solution:

SQL
SELECT DATE(order_date) AS order_day, COUNT(*) 
AS orders_count 
FROM orders 
WHERE order_date >= CURRENT_DATE - 
INTERVAL '7 days' 
GROUP BY order_day 
ORDER BY order_day;

Step-by-step how the database executes this:

  1. FROM: Reads table orders.
  2. WHERE: Filters individual rows. Rows failing the condition are discarded.
  3. GROUP BY: Collapses rows into groups. One output row per unique group value.
  4. SELECT: Projects the final columns and evaluates expressions/aliases.
  5. ORDER BY: Sorts the final result set.

Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.

---

Q98. Find customers with orders in both online and in- store channels

Problem: Find customers with orders in both online and in- store channels.

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. Are there duplicates I need to remove? Use DISTINCT.

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
KEEP ONLY groups that satisfy the group condition

SQL Solution:

SQL
SELECT customer_id 
FROM orders 
GROUP BY customer_id 
HAVING COUNT(DISTINCT order_channel) > 1;

Step-by-step how the database executes this:

  1. FROM: Reads table orders.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. HAVING: Filters groups. Only groups passing the condition remain.
  4. SELECT: Projects the final columns and evaluates expressions/aliases.
  5. 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.

---

Q99. Find the top 3 sales reps by number of deals closed this quarter

Problem: Find the top 3 sales reps by number of deals closed this quarter.

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. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

TEXT
GET columns FROM table
KEEP ONLY rows that satisfy the condition
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SORT the result

SQL Solution:

SQL
SELECT sales_rep_id, COUNT(*) AS deals_closed 
FROM sales_deals 
WHERE deal_close_date >= DATE_TRUNC('quarter', 
CURRENT_DATE) 
GROUP BY sales_rep_id 
ORDER BY deals_closed DESC 
LIMIT 3;

Step-by-step how the database executes this:

  1. FROM: Reads table sales_deals.
  2. WHERE: Filters individual rows. Rows failing the condition are discarded.
  3. GROUP BY: Collapses rows into groups. One output row per unique group value.
  4. SELECT: Projects the final columns and evaluates expressions/aliases.
  5. ORDER BY: Sorts the final result set.
  6. LIMIT/TOP: Returns only the first N rows from the sorted result.

Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.

---

Q100. Find the average delivery time by shipping method

Problem: Find the average delivery time by shipping method.

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.

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group

SQL Solution:

SQL
SELECT shipping_method, AVG(delivery_date - 
order_date) AS avg_delivery_time 
FROM shipments 
GROUP BY shipping_method;

Step-by-step how the database executes this:

  1. FROM: Reads table shipments.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. 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.

---

Q101. Find orders where the total quantity exceeds 100

Problem: Find orders where the total quantity exceeds 100.

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.

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
KEEP ONLY groups that satisfy the group condition

SQL Solution:

SQL
SELECT order_id, SUM(quantity) AS total_quantity 
FROM order_items 
GROUP BY order_id 
HAVING SUM(quantity) > 100;

Step-by-step how the database executes this:

  1. FROM: Reads table order_items.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. HAVING: Filters groups. Only groups passing the condition remain.
  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.

---

Q102. Find employees who have worked on projects for more than 2 years

Problem: Find employees who have worked on projects for more than 2 years.

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.

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
KEEP ONLY groups that satisfy the group condition

SQL Solution:

SQL
SELECT employee_id 
FROM project_assignments 
GROUP BY employee_id 
HAVING MAX(assignment_end_date) - 
MIN(assignment_start_date) > INTERVAL '2 years';

Step-by-step how the database executes this:

  1. FROM: Reads table project_assignments.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. HAVING: Filters groups. Only groups passing the condition remain.
  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.

---

Q103. Get total working hours per employee per week

Problem: Get total working hours per employee per 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.

Pseudo query (plain English first):

TEXT
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group

SQL Solution:

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

Step-by-step how the database executes this:

  1. FROM: Reads table work_logs.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. 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.

---

Key Takeaway

This section covered 104 interview questions on Aggregation: GROUP BY, HAVING, COUNT, SUM, AVG. 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.

GROUP BY collapses multiple rows into one row per unique value. Aggregate functions (COUNT, SUM, AVG, MAX, MIN) compute one value per group. HAVING filters groups after aggregation. WHERE filters rows before grouping, HAVING filters groups after.

Practice each query on the shared sample table. If you can solve all 104 without looking at the answer, you have mastered this concept.

---

Share
Use ← → arrow keys to navigate