Definition
- 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):
| id | name | salary | department_id | manager_id | join_date | bonus | rating |
|---|---|---|---|---|---|---|---|
| 1 | Rajesh | 90000 | 101 | NULL | 2020-01-15 | 10000 | A |
| 2 | Priya | 75000 | 101 | 1 | 2021-03-20 | 8000 | B |
| 3 | Amit | 95000 | 102 | 1 | 2019-06-10 | 12000 | A |
| 4 | Sneha | 60000 | 102 | 3 | 2022-08-01 | 5000 | C |
| 5 | Vikram | 90000 | 103 | 1 | 2020-11-25 | 10000 | A |
| 6 | Meena | 75000 | 103 | 5 | 2023-02-14 | 7000 | B |
departments table:
| department_id | department_name | location |
|---|---|---|
| 101 | Retail Banking | Mumbai |
| 102 | Corporate | Delhi |
| 103 | Treasury | Chennai |
| 104 | IT | Pune |
---
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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need to filter groups after aggregation? That means HAVING, not WHERE.
Pseudo query (plain English first):
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:
SELECT department_id, COUNT(*) AS
num_employees
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;
Step-by-step how the database executes this:
- FROM: Reads table
employees. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- HAVING: Filters groups. Only groups passing the condition remain.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need conditional logic? Use CASE WHEN ... THEN ... ELSE ... END.
Pseudo query (plain English first):
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SQL Solution:
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:
- FROM: Reads table
employees. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
Pseudo query (plain English first):
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SQL Solution:
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:
- FROM: Reads table
sales. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
Pseudo query (plain English first):
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SQL Solution:
SELECT job_title, COUNT(*) AS num_employees
FROM employees
GROUP BY job_title;
Step-by-step how the database executes this:
- FROM: Reads table
employees. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need to filter groups after aggregation? That means HAVING, not WHERE.
- Which tables do I need? How are they related? What column connects them?
Pseudo query (plain English first):
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:
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:
- FROM: Reads table
employees. - INNER JOIN: Combines rows from joined tables using the ON condition.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- HAVING: Filters groups. Only groups passing the condition remain.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q6. Find the department with the lowest average salary
Problem: Find the department with the lowest average salary.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- How should the result be sorted? ASC (ascending) or DESC (descending)?
Pseudo query (plain English first):
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SORT the result
SQL Solution:
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:
- FROM: Reads table
employees. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
- LIMIT/TOP: Returns only the first N rows from the sorted result.
Optimization: Add an index on the 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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need to filter groups after aggregation? That means HAVING, not WHERE.
- Are there duplicates I need to remove? Use DISTINCT.
Pseudo query (plain English first):
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:
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:
- FROM: Reads table
employee_department_history. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- HAVING: Filters groups. Only groups passing the condition remain.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- DISTINCT: Removes duplicate rows by comparing all selected columns.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
Pseudo query (plain English first):
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SQL Solution:
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:
- FROM: Reads table
orders. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
Q9. Find the average tenure of employees by department
Problem: Find the average tenure of employees by department.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
Pseudo query (plain English first):
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SQL Solution:
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:
- FROM: Reads table
employees. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need to filter groups after aggregation? That means HAVING, not WHERE.
Pseudo query (plain English first):
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:
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:
- FROM: Reads table
sales. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- HAVING: Filters groups. Only groups passing the condition remain.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
Q11. 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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need to filter groups after aggregation? That means HAVING, not WHERE.
Pseudo query (plain English first):
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:
SELECT column1, column2, COUNT(*)
FROM table_name
GROUP BY column1, column2
HAVING COUNT(*) > 1;
Step-by-step how the database executes this:
- FROM: Reads table
table_name. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- HAVING: Filters groups. Only groups passing the condition remain.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
Q12. 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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
Pseudo query (plain English first):
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SQL Solution:
SELECT department_id, STRING_AGG(name, ', ') AS
employee_names
FROM employees
GROUP BY department_id;
Step-by-step how the database executes this:
- FROM: Reads table
employees. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- How should the result be sorted? ASC (ascending) or DESC (descending)?
Pseudo query (plain English first):
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SORT the result
SQL Solution:
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:
- FROM: Reads table
employees. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
- LIMIT/TOP: Returns only the first N rows from the sorted result.
Optimization: Add an index on the 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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
Pseudo query (plain English first):
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:
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:
- FROM: Reads table
sales. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- How should the result be sorted? ASC (ascending) or DESC (descending)?
Pseudo query (plain English first):
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SORT the result
SQL Solution:
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:
- FROM: Reads table
current_date. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
- LIMIT/TOP: Returns only the first N rows from the sorted result.
Optimization: Add an index on the 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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
Pseudo query (plain English first):
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SQL Solution:
SELECT department_id, MAX(salary) - MIN(salary)
AS salary_gap
FROM employees
GROUP BY department_id;
Step-by-step how the database executes this:
- FROM: Reads table
employees. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need to filter groups after aggregation? That means HAVING, not WHERE.
- Are there duplicates I need to remove? Use DISTINCT.
Pseudo query (plain English first):
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:
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:
- FROM: Reads table
sales. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- HAVING: Filters groups. Only groups passing the condition remain.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- DISTINCT: Removes duplicate rows by comparing all selected columns.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need to filter groups after aggregation? That means HAVING, not WHERE.
- Are there duplicates I need to remove? Use DISTINCT.
Pseudo query (plain English first):
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:
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:
- FROM: Reads table
employee_department_history. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- HAVING: Filters groups. Only groups passing the condition remain.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- DISTINCT: Removes duplicate rows by comparing all selected columns.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
Q19. Find the first order date for each customer
Problem: Find the first order date for each customer.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
Pseudo query (plain English first):
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SQL Solution:
SELECT customer_id, MIN(order_date) AS
first_order_date
FROM orders
GROUP BY customer_id;
Step-by-step how the database executes this:
- FROM: Reads table
orders. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need to filter groups after aggregation? That means HAVING, not WHERE.
Pseudo query (plain English first):
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:
SELECT employee_id, COUNT(*) AS
promotion_count
FROM promotions
GROUP BY employee_id
HAVING COUNT(*) > 2;
Step-by-step how the database executes this:
- FROM: Reads table
promotions. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- HAVING: Filters groups. Only groups passing the condition remain.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
Pseudo query (plain English first):
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SQL Solution:
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:
- FROM: Reads table
orders. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need to filter groups after aggregation? That means HAVING, not WHERE.
Pseudo query (plain English first):
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:
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:
- FROM: Reads table
orders. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- HAVING: Filters groups. Only groups passing the condition remain.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
Q23. Find the department with the most employees
Problem: Find the department with the most employees.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- How should the result be sorted? ASC (ascending) or DESC (descending)?
Pseudo query (plain English first):
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SORT the result
SQL Solution:
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:
- FROM: Reads table
employees. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
- LIMIT/TOP: Returns only the first N rows from the sorted result.
Optimization: Add an index on the 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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- How should the result be sorted? ASC (ascending) or DESC (descending)?
Pseudo query (plain English first):
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SORT the result
SQL Solution:
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:
- FROM: Reads table
dependents. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
- LIMIT/TOP: Returns only the first N rows from the sorted result.
Optimization: Add an index on the 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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
Pseudo query (plain English first):
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SQL Solution:
SELECT customer_id, MAX(order_date) AS
last_order_date
FROM orders
GROUP BY customer_id;
Step-by-step how the database executes this:
- FROM: Reads table
orders. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
Q26. Get the number of employees hired each year
Problem: Get the number of employees hired each year.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- How should the result be sorted? ASC (ascending) or DESC (descending)?
Pseudo query (plain English first):
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SORT the result
SQL Solution:
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:
- FROM: Reads table
hire_date. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
Optimization: Add an index on the 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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
Pseudo query (plain English first):
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SQL Solution:
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:
- FROM: Reads table
employees. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
Q28. Calculate average salary by department and job title
Problem: Calculate average salary by department and job title.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
Pseudo query (plain English first):
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SQL Solution:
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:
- FROM: Reads table
employees. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need to filter groups after aggregation? That means HAVING, not WHERE.
Pseudo query (plain English first):
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:
SELECT employee_id, COUNT(*) AS
promotion_count
FROM promotions
GROUP BY employee_id
HAVING COUNT(*) > 1;
Step-by-step how the database executes this:
- FROM: Reads table
promotions. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- HAVING: Filters groups. Only groups passing the condition remain.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
Q30. Calculate total sales by product category
Problem: Calculate total sales by product category.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Which tables do I need? How are they related? What column connects them?
Pseudo query (plain English first):
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:
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:
- FROM: Reads table
sales. - INNER JOIN: Combines rows from joined tables using the ON condition.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q31. Find the top 3 products by sales amount
Problem: Find the top 3 products by sales amount.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- How should the result be sorted? ASC (ascending) or DESC (descending)?
Pseudo query (plain English first):
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SORT the result
SQL Solution:
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:
- FROM: Reads table
sales. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
- LIMIT/TOP: Returns only the first N rows from the sorted result.
Optimization: Add an index on the 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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need to filter groups after aggregation? That means HAVING, not WHERE.
Pseudo query (plain English first):
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:
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:
- FROM: Reads table
sales. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- HAVING: Filters groups. Only groups passing the condition remain.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need to filter groups after aggregation? That means HAVING, not WHERE.
- Are there duplicates I need to remove? Use DISTINCT.
Pseudo query (plain English first):
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:
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:
- FROM: Reads table
project_assignments. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- HAVING: Filters groups. Only groups passing the condition remain.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- DISTINCT: Removes duplicate rows by comparing all selected columns.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- How should the result be sorted? ASC (ascending) or DESC (descending)?
Pseudo query (plain English first):
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SORT the result
SQL Solution:
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:
- FROM: Reads table
sales. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
Optimization: Add an index on the 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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need to filter groups after aggregation? That means HAVING, not WHERE.
Pseudo query (plain English first):
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:
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:
- FROM: Reads table
sales. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- HAVING: Filters groups. Only groups passing the condition remain.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
Q36. Find the department with the lowest average salary
Problem: Find the department with the lowest average salary.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- How should the result be sorted? ASC (ascending) or DESC (descending)?
Pseudo query (plain English first):
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SORT the result
SQL Solution:
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:
- FROM: Reads table
employees. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
- LIMIT/TOP: Returns only the first N rows from the sorted result.
Optimization: Add an index on the 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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need to filter groups after aggregation? That means HAVING, not WHERE.
- Which tables do I need? How are they related? What column connects them?
- Are there duplicates I need to remove? Use DISTINCT.
Pseudo query (plain English first):
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:
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:
- FROM: Reads table
sales. - INNER JOIN: Combines rows from joined tables using the ON condition.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- HAVING: Filters groups. Only groups passing the condition remain.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- DISTINCT: Removes duplicate rows by comparing all selected columns.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- How should the result be sorted? ASC (ascending) or DESC (descending)?
Pseudo query (plain English first):
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SORT the result
SQL Solution:
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:
- FROM: Reads table
employees. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
- LIMIT/TOP: Returns only the first N rows from the sorted result.
Optimization: Add an index on the 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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need to filter groups after aggregation? That means HAVING, not WHERE.
- Are there duplicates I need to remove? Use DISTINCT.
Pseudo query (plain English first):
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:
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:
- FROM: Reads table
sales. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- HAVING: Filters groups. Only groups passing the condition remain.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- DISTINCT: Removes duplicate rows by comparing all selected columns.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- How should the result be sorted? ASC (ascending) or DESC (descending)?
Pseudo query (plain English first):
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SORT the result
SQL Solution:
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:
- FROM: Reads table
sales. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
- LIMIT/TOP: Returns only the first N rows from the sorted result.
Optimization: Add an index on the 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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- How should the result be sorted? ASC (ascending) or DESC (descending)?
Pseudo query (plain English first):
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:
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:
- FROM: Reads table
sales. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
- LIMIT/TOP: Returns only the first N rows from the sorted result.
Optimization: Add an index on the 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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- How should the result be sorted? ASC (ascending) or DESC (descending)?
Pseudo query (plain English first):
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SORT the result
SQL Solution:
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:
- FROM: Reads table
hire_date. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
Optimization: Add an index on the 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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Which tables do I need? How are they related? What column connects them?
- How should the result be sorted? ASC (ascending) or DESC (descending)?
Pseudo query (plain English first):
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:
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:
- FROM: Reads table
promotions. - INNER JOIN: Combines rows from joined tables using the ON condition.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
- LIMIT/TOP: Returns only the first N rows from the sorted result.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need to filter groups after aggregation? That means HAVING, not WHERE.
- Which tables do I need? How are they related? What column connects them?
- Are there duplicates I need to remove? Use DISTINCT.
Pseudo query (plain English first):
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:
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:
- FROM: Reads table
sales. - INNER JOIN: Combines rows from joined tables using the ON condition.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- HAVING: Filters groups. Only groups passing the condition remain.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- DISTINCT: Removes duplicate rows by comparing all selected columns.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Which tables do I need? How are they related? What column connects them?
Pseudo query (plain English first):
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:
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:
- FROM: Reads table
customers. - INNER JOIN: Combines rows from joined tables using the ON condition.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- 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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
Pseudo query (plain English first):
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SQL Solution:
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:
- FROM: Reads table
orders. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- How should the result be sorted? ASC (ascending) or DESC (descending)?
Pseudo query (plain English first):
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SORT the result
SQL Solution:
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:
- FROM: Reads table
sales. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
- LIMIT/TOP: Returns only the first N rows from the sorted result.
Optimization: Add an index on the 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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need to filter groups after aggregation? That means HAVING, not WHERE.
- Are there duplicates I need to remove? Use DISTINCT.
Pseudo query (plain English first):
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:
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:
- FROM: Reads table
sales. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- HAVING: Filters groups. Only groups passing the condition remain.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- 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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
Pseudo query (plain English first):
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SQL Solution:
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:
- FROM: Reads table
order_date. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
Q50. Find the most recent promotion date per employee
Problem: Find the most recent promotion date per employee.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
Pseudo query (plain English first):
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SQL Solution:
SELECT employee_id, MAX(promotion_date) AS
last_promotion_date
FROM promotions
GROUP BY employee_id;
Step-by-step how the database executes this:
- FROM: Reads table
promotions. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- How should the result be sorted? ASC (ascending) or DESC (descending)?
Pseudo query (plain English first):
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:
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:
- FROM: Reads table
sales. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
- LIMIT/TOP: Returns only the first N rows from the sorted result.
Optimization: Add an index on the 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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- How should the result be sorted? ASC (ascending) or DESC (descending)?
Pseudo query (plain English first):
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:
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:
- FROM: Reads table
employees. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- 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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- How should the result be sorted? ASC (ascending) or DESC (descending)?
Pseudo query (plain English first):
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SORT the result
SQL Solution:
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:
- FROM: Reads table
projects. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
- LIMIT/TOP: Returns only the first N rows from the sorted result.
Optimization: Add an index on the 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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need to filter groups after aggregation? That means HAVING, not WHERE.
Pseudo query (plain English first):
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:
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:
- FROM: Reads table
orders. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- HAVING: Filters groups. Only groups passing the condition remain.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need to filter groups after aggregation? That means HAVING, not WHERE.
Pseudo query (plain English first):
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:
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:
- FROM: Reads table
order_items. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- HAVING: Filters groups. Only groups passing the condition remain.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need to filter groups after aggregation? That means HAVING, not WHERE.
- Are there duplicates I need to remove? Use DISTINCT.
Pseudo query (plain English first):
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:
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:
- FROM: Reads table
project_assignments. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- HAVING: Filters groups. Only groups passing the condition remain.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- DISTINCT: Removes duplicate rows by comparing all selected columns.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need to filter groups after aggregation? That means HAVING, not WHERE.
Pseudo query (plain English first):
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:
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:
- FROM: Reads table
orders. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- HAVING: Filters groups. Only groups passing the condition remain.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
Q58. Find the average salary increase percentage per department
Problem: Find the average salary increase percentage per department.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Which tables do I need? How are they related? What column connects them?
Pseudo query (plain English first):
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:
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:
- FROM: Reads table
employees. - INNER JOIN: Combines rows from joined tables using the ON condition.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need to filter groups after aggregation? That means HAVING, not WHERE.
Pseudo query (plain English first):
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:
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:
- FROM: Reads table
orders. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- HAVING: Filters groups. Only groups passing the condition remain.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- How should the result be sorted? ASC (ascending) or DESC (descending)?
Pseudo query (plain English first):
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SORT the result
SQL Solution:
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:
- FROM: Reads table
employees. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
- LIMIT/TOP: Returns only the first N rows from the sorted result.
Optimization: Add an index on the 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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need to filter groups after aggregation? That means HAVING, not WHERE.
- Are there duplicates I need to remove? Use DISTINCT.
Pseudo query (plain English first):
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:
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:
- FROM: Reads table
employee_department_history. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- HAVING: Filters groups. Only groups passing the condition remain.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- DISTINCT: Removes duplicate rows by comparing all selected columns.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
Q62. SQL Query 2023
Problem: SQL Query 2023.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- How should the result be sorted? ASC (ascending) or DESC (descending)?
Pseudo query (plain English first):
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:
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:
- FROM: Reads table
sales. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
- LIMIT/TOP: Returns only the first N rows from the sorted result.
Optimization: Add an index on the 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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
Pseudo query (plain English first):
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SQL Solution:
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:
- FROM: Reads table
orders. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Which tables do I need? How are they related? What column connects them?
- Are there duplicates I need to remove? Use DISTINCT.
Pseudo query (plain English first):
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:
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:
- FROM: Reads table
sales. - INNER JOIN: Combines rows from joined tables using the ON condition.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- 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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need to filter groups after aggregation? That means HAVING, not WHERE.
- Are there duplicates I need to remove? Use DISTINCT.
Pseudo query (plain English first):
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:
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:
- FROM: Reads table
employee_department_history. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- HAVING: Filters groups. Only groups passing the condition remain.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- DISTINCT: Removes duplicate rows by comparing all selected columns.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
Q66. Find the product with the highest average rating
Problem: Find the product with the highest average rating.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- How should the result be sorted? ASC (ascending) or DESC (descending)?
Pseudo query (plain English first):
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SORT the result
SQL Solution:
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:
- FROM: Reads table
product_reviews. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
- LIMIT/TOP: Returns only the first N rows from the sorted result.
Optimization: Add an index on the 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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need to filter groups after aggregation? That means HAVING, not WHERE.
Pseudo query (plain English first):
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:
SELECT birth_date, COUNT(*) AS count_employees
FROM employees
GROUP BY birth_date
HAVING COUNT(*) > 1;
Step-by-step how the database executes this:
- FROM: Reads table
employees. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- HAVING: Filters groups. Only groups passing the condition remain.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need to filter groups after aggregation? That means HAVING, not WHERE.
Pseudo query (plain English first):
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:
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:
- FROM: Reads table
sales. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- HAVING: Filters groups. Only groups passing the condition remain.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- How should the result be sorted? ASC (ascending) or DESC (descending)?
Pseudo query (plain English first):
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SORT the result
SQL Solution:
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:
- FROM: Reads table
weather_data. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
- LIMIT/TOP: Returns only the first N rows from the sorted result.
Optimization: Add an index on the 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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need to filter groups after aggregation? That means HAVING, not WHERE.
- Which tables do I need? How are they related? What column connects them?
- Are there duplicates I need to remove? Use DISTINCT.
Pseudo query (plain English first):
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:
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:
- FROM: Reads table
sales. - INNER JOIN: Combines rows from joined tables using the ON condition.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- HAVING: Filters groups. Only groups passing the condition remain.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- DISTINCT: Removes duplicate rows by comparing all selected columns.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- How should the result be sorted? ASC (ascending) or DESC (descending)?
Pseudo query (plain English first):
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SORT the result
SQL Solution:
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:
- FROM: Reads table
project_assignments. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
- LIMIT/TOP: Returns only the first N rows from the sorted result.
Optimization: Add an index on the 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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
Pseudo query (plain English first):
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SQL Solution:
SELECT customer_id, MIN(order_date) AS
first_order_date
FROM orders
GROUP BY customer_id;
Step-by-step how the database executes this:
- FROM: Reads table
orders. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need conditional logic? Use CASE WHEN ... THEN ... ELSE ... END.
Pseudo query (plain English first):
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SQL Solution:
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:
- FROM: Reads table
employees. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- How should the result be sorted? ASC (ascending) or DESC (descending)?
Pseudo query (plain English first):
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:
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:
- FROM: Reads table
employees. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
- LIMIT/TOP: Returns only the first N rows from the sorted result.
Optimization: Add an index on the 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):
GET columns FROM table
KEEP ONLY rows that satisfy the condition
SQL Solution:
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:
- FROM: Reads table
orders. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- 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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- How should the result be sorted? ASC (ascending) or DESC (descending)?
Pseudo query (plain English first):
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SORT the result
SQL Solution:
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:
- FROM: Reads table
age. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
- LIMIT/TOP: Returns only the first N rows from the sorted result.
Optimization: Add an index on the 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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- How should the result be sorted? ASC (ascending) or DESC (descending)?
Pseudo query (plain English first):
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:
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:
- FROM: Reads table
attendance. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
- LIMIT/TOP: Returns only the first N rows from the sorted result.
Optimization: Add an index on the 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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
Pseudo query (plain English first):
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SQL Solution:
SELECT sales_rep_id, SUM(amount) AS total_revenue
FROM sales
GROUP BY sales_rep_id;
Step-by-step how the database executes this:
- FROM: Reads table
sales. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need to filter groups after aggregation? That means HAVING, not WHERE.
- Are there duplicates I need to remove? Use DISTINCT.
Pseudo query (plain English first):
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:
SELECT DISTINCT order_id
FROM order_items
GROUP BY order_id
HAVING MIN(quantity) >= 5;
Step-by-step how the database executes this:
- FROM: Reads table
order_items. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- HAVING: Filters groups. Only groups passing the condition remain.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- DISTINCT: Removes duplicate rows by comparing all selected columns.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need to filter groups after aggregation? That means HAVING, not WHERE.
- Which tables do I need? How are they related? What column connects them?
- Are there duplicates I need to remove? Use DISTINCT.
Pseudo query (plain English first):
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:
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:
- FROM: Reads table
sales. - INNER JOIN: Combines rows from joined tables using the ON condition.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- HAVING: Filters groups. Only groups passing the condition remain.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- DISTINCT: Removes duplicate rows by comparing all selected columns.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q81. Find the total discount given in each month
Problem: Find the total discount given in each month.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- How should the result be sorted? ASC (ascending) or DESC (descending)?
Pseudo query (plain English first):
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SORT the result
SQL Solution:
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:
- FROM: Reads table
orders. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
Optimization: Add an index on the 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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- How should the result be sorted? ASC (ascending) or DESC (descending)?
Pseudo query (plain English first):
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:
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:
- FROM: Reads table
projects. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
- LIMIT/TOP: Returns only the first N rows from the sorted result.
Optimization: Add an index on the 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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
Pseudo query (plain English first):
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:
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:
- FROM: Reads table
support_tickets. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
Pseudo query (plain English first):
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SQL Solution:
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:
- FROM: Reads table
user_logins. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need to filter groups after aggregation? That means HAVING, not WHERE.
- Do I need conditional logic? Use CASE WHEN ... THEN ... ELSE ... END.
Pseudo query (plain English first):
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:
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:
- FROM: Reads table
employees. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- HAVING: Filters groups. Only groups passing the condition remain.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
Q86. Find the average tenure of employees by department
Problem: Find the average tenure of employees by department.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
Pseudo query (plain English first):
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SQL Solution:
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:
- FROM: Reads table
employees. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need conditional logic? Use CASE WHEN ... THEN ... ELSE ... END.
Pseudo query (plain English first):
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SQL Solution:
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:
- FROM: Reads table
order_date. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- How should the result be sorted? ASC (ascending) or DESC (descending)?
- Do I need conditional logic? Use CASE WHEN ... THEN ... ELSE ... END.
Pseudo query (plain English first):
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SORT the result
SQL Solution:
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:
- FROM: Reads table
orders. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
Optimization: Add an index on the 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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need to filter groups after aggregation? That means HAVING, not WHERE.
- Which tables do I need? How are they related? What column connects them?
- Can I solve the inner part first as a subquery, then use the result in the outer query?
Pseudo query (plain English first):
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result
SQL Solution:
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:
- FROM: Reads table
sales. - INNER JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- HAVING: Filters groups. Only groups passing the condition remain.
- SELECT: Projects the final columns and evaluates expressions/aliases.
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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Which tables do I need? How are they related? What column connects them?
Pseudo query (plain English first):
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:
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:
- FROM: Reads table
customers. - INNER JOIN: Combines rows from joined tables using the ON condition.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- 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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need to filter groups after aggregation? That means HAVING, not WHERE.
- Are there duplicates I need to remove? Use DISTINCT.
Pseudo query (plain English first):
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:
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:
- FROM: Reads table
projects. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- HAVING: Filters groups. Only groups passing the condition remain.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- DISTINCT: Removes duplicate rows by comparing all selected columns.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
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:
- What condition filters the rows I need? Write the WHERE clause for that condition.
- Are there duplicates I need to remove? Use DISTINCT.
Pseudo query (plain English first):
GET columns FROM table
KEEP ONLY rows that satisfy the condition
SQL Solution:
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:
- FROM: Reads table
sales. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- DISTINCT: Removes duplicate rows by comparing all selected columns.
Optimization: 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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- How should the result be sorted? ASC (ascending) or DESC (descending)?
Pseudo query (plain English first):
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:
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:
- FROM: Reads table
orders. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
- LIMIT/TOP: Returns only the first N rows from the sorted result.
Optimization: Add an index on the 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:
- What condition filters the rows I need? Write the WHERE clause for that condition.
- Are there duplicates I need to remove? Use DISTINCT.
Pseudo query (plain English first):
GET columns FROM table
KEEP ONLY rows that satisfy the condition
SQL Solution:
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:
- FROM: Reads table
employee_department_history. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- DISTINCT: Removes duplicate rows by comparing all selected columns.
Optimization: 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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
Pseudo query (plain English first):
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SQL Solution:
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:
- FROM: Reads table
employees. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need to filter groups after aggregation? That means HAVING, not WHERE.
- Are there duplicates I need to remove? Use DISTINCT.
Pseudo query (plain English first):
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:
SELECT customer_id
FROM orders
GROUP BY customer_id
HAVING COUNT(DISTINCT payment_method) > 1;
Step-by-step how the database executes this:
- FROM: Reads table
orders. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- HAVING: Filters groups. Only groups passing the condition remain.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- 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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- How should the result be sorted? ASC (ascending) or DESC (descending)?
Pseudo query (plain English first):
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:
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:
- FROM: Reads table
orders. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- 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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need to filter groups after aggregation? That means HAVING, not WHERE.
- Are there duplicates I need to remove? Use DISTINCT.
Pseudo query (plain English first):
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:
SELECT customer_id
FROM orders
GROUP BY customer_id
HAVING COUNT(DISTINCT order_channel) > 1;
Step-by-step how the database executes this:
- FROM: Reads table
orders. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- HAVING: Filters groups. Only groups passing the condition remain.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- 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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- How should the result be sorted? ASC (ascending) or DESC (descending)?
Pseudo query (plain English first):
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:
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:
- FROM: Reads table
sales_deals. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
- LIMIT/TOP: Returns only the first N rows from the sorted result.
Optimization: Add an index on the 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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
Pseudo query (plain English first):
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SQL Solution:
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:
- FROM: Reads table
shipments. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
Q101. Find orders where the total quantity exceeds 100
Problem: Find orders where the total quantity exceeds 100.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need to filter groups after aggregation? That means HAVING, not WHERE.
Pseudo query (plain English first):
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:
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:
- FROM: Reads table
order_items. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- HAVING: Filters groups. Only groups passing the condition remain.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
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:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need to filter groups after aggregation? That means HAVING, not WHERE.
Pseudo query (plain English first):
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:
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:
- FROM: Reads table
project_assignments. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- HAVING: Filters groups. Only groups passing the condition remain.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
Q103. Get total working hours per employee per week
Problem: Get total working hours per employee per week.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
Pseudo query (plain English first):
GET columns FROM table
GROUP rows by the key column(s)
CALCULATE the aggregate (count/sum/avg) per group
SQL Solution:
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:
- FROM: Reads table
work_logs. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
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.
---