Definition
- ROW_NUMBER(): Unique sequential number per partition. No ties.
- RANK(): Same rank for ties, skips next rank (1,1,3).
- DENSE_RANK(): Same rank for ties, no skip (1,1,2).
- LEAD(col, N)/LAG(col, N): Access the next/previous row value.
- SUM/AVG/COUNT OVER(): Running totals and moving averages.
- PARTITION BY: Defines groups (like GROUP BY but without collapsing).
- Cannot filter in WHERE: Wrap in CTE or subquery to filter by window function result.
This section is a focused tutorial on Window Functions. 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
## Window Functions: ROW_NUMBER, RANK, DENSE_RANK, LEAD, LAG
Window functions compute a value for each row using a window of related rows, without collapsing rows like GROUP BY. Syntax: FUNCTION() OVER (PARTITION BY col ORDER BY col). ROW_NUMBER gives unique sequential numbers. RANK gives same rank to ties and skips next. DENSE_RANK does not skip. LEAD/LAG access the next/previous row value. SUM/AVG/COUNT with OVER compute running totals and moving averages.
Sample Tables
sales table (shared across all queries in this section):
| sale_id | employee_id | department_id | amount | sale_date | region | product |
|---|---|---|---|---|---|---|
| S01 | 1 | 101 | 50000 | 2026-01-05 | North | Home Loan |
| S02 | 2 | 101 | 30000 | 2026-01-10 | North | Car Loan |
| S03 | 3 | 102 | 80000 | 2026-01-15 | South | Home Loan |
| S04 | 1 | 101 | 45000 | 2026-02-01 | North | Home Loan |
| S05 | 4 | 102 | 25000 | 2026-02-10 | South | Personal |
| S06 | 5 | 103 | 60000 | 2026-02-15 | East | Home Loan |
| S07 | 3 | 102 | 70000 | 2026-03-01 | South | Car Loan |
| S08 | 6 | 103 | 35000 | 2026-03-10 | East | Personal |
employees table also available from earlier sections.
---
Q1. Running total of salaries by department
Problem: Running total of salaries by department.
How to think before writing:
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
- What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
- How should the result be sorted? ASC (ascending) or DESC (descending)?
Pseudo query (plain English first):
GET columns FROM table
SORT the result
SQL Solution:
SELECT name, department_id, salary,
SUM(salary) OVER (PARTITION BY
department_id ORDER BY id) AS running_total
FROM employees;
Step-by-step how the database executes this:
- FROM: Reads table
employees. - Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
Optimization: Window functions scan the partition. Index the PARTITION BY and ORDER BY columns to speed up the sort.
---
Q2. Find the longest consecutive streak of daily logins for each user
Problem: Find the longest consecutive streak of daily logins 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.
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
- What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH login_dates AS (
SELECT user_id, login_date,
login_date - INTERVAL ROW_NUMBER()
OVER (PARTITION BY user_id ORDER BY
login_date) DAY AS grp
FROM user_logins
)
SELECT user_id, COUNT(*) AS streak_length,
MIN(login_date) AS start_date, MAX(login_date) AS
end_date
FROM login_dates
GROUP BY user_id, grp
ORDER BY streak_length DESC;
Step-by-step how the database executes this:
- WITH (CTE): Materializes
login_datesas temporary result set(s). - FROM: Reads table
user_logins. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- 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.
---
Q3. Calculate cumulative distribution (CDF) of salaries
Problem: Calculate cumulative distribution (CDF) of salaries.
How to think before writing:
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
- What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
- How should the result be sorted? ASC (ascending) or DESC (descending)?
Pseudo query (plain English first):
GET columns FROM table
SORT the result
SQL Solution:
SELECT name, salary,
CUME_DIST() OVER (ORDER BY salary) AS
salary_cdf
FROM employees;
Step-by-step how the database executes this:
- FROM: Reads table
employees. - Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
Optimization: Window functions scan the partition. Index the PARTITION BY and ORDER BY columns to speed up the sort.
---
Q4. Write a query to rank employees based on salary
Problem: Write a query to rank employees based on salary.
How to think before writing:
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
- What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
- Do I need to rank rows, handling ties? RANK skips after ties, DENSE_RANK does not.
- How should the result be sorted? ASC (ascending) or DESC (descending)?
Pseudo query (plain English first):
GET columns FROM table
SORT the result
SQL Solution:
with ties handled properly.
SELECT name, salary,
RANK() OVER (ORDER BY salary DESC) AS
salary_rank
FROM employees;
Step-by-step how the database executes this:
- FROM: Reads table
employees. - Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
Optimization: Window functions scan the partition. Index the PARTITION BY and ORDER BY columns to speed up the sort.
---
Q5. Write a query to calculate the difference between current row and previous row's salary (lag function)
Problem: Write a query to calculate the difference between current row and previous row's salary (lag function).
How to think before writing:
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
- What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
- Do I need to compare each row with the next or previous row? Use LEAD() or LAG().
- How should the result be sorted? ASC (ascending) or DESC (descending)?
Pseudo query (plain English first):
GET columns FROM table
SORT the result
SQL Solution:
SELECT name, salary,
salary - LAG(salary) OVER (ORDER BY id) AS
salary_diff
FROM employees;
Step-by-step how the database executes this:
- FROM: Reads table
employees. - Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
Optimization: Window functions scan the partition. Index the PARTITION BY and ORDER BY columns to speed up the sort.
---
Q6. Find the most recent purchase per customer using window functions
Problem: Find the most recent purchase per customer using window functions.
How to think before writing:
- Can I solve the inner part first as a subquery, then use the result in the outer query?
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
- What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
- Do I need to assign a unique number to each row within a group? Use ROW_NUMBER().
- What condition filters the rows I need? Write the WHERE clause for that condition.
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 *
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY
customer_id ORDER BY purchase_date DESC) AS rn
FROM sales
) sub
WHERE rn = 1;
Step-by-step how the database executes this:
- FROM: Reads table
sales. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
Optimization: If the subquery runs once per outer row (correlated), consider rewriting as a JOIN or CTE for better performance.
---
Q7. Write a query to rank salespeople by monthly sales, resetting the rank every month
Problem: Write a query to rank salespeople by monthly sales, resetting the rank every 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.
- Can I solve the inner part first as a subquery, then use the result in the outer query?
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
- What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
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 salesperson_id, sale_month, total_sales,
RANK() OVER (PARTITION BY sale_month
ORDER BY total_sales DESC) AS monthly_rank
FROM (
SELECT salesperson_id, DATE_TRUNC('month',
sale_date) AS sale_month, SUM(amount) AS
total_sales
FROM sales
GROUP BY salesperson_id, sale_month
) AS monthly_sales;
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.
- Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- 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.
---
Q8. Calculate the percentage change in sales compared to the previous month for each product
Problem: Calculate the percentage change in sales compared to the previous month for each product.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Can I solve the inner part first as a subquery, then use the result in the outer query?
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
- What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
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, sale_month, total_sales,
(total_sales - LAG(total_sales) OVER
(PARTITION BY product_id ORDER BY
sale_month)) * 100.0 /
LAG(total_sales) OVER (PARTITION BY
product_id ORDER BY sale_month) AS pct_change
FROM (
SELECT product_id, DATE_TRUNC('month',
sale_date) AS sale_month, SUM(amount) AS
total_sales
FROM sales
GROUP BY product_id, sale_month
) monthly_sales;
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.
- Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- 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.
---
Q9. Retrieve the last 5 orders for each customer
Problem: Retrieve the last 5 orders for each customer.
How to think before writing:
- Can I solve the inner part first as a subquery, then use the result in the outer query?
- Do I need to assign a unique number to each row within a group? Use ROW_NUMBER().
- What condition filters the rows I need? Write the WHERE clause for that condition.
- How should the result be sorted? ASC (ascending) or DESC (descending)?
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 *
FROM (
SELECT o.*, ROW_NUMBER() OVER
(PARTITION BY customer_id ORDER BY order_date
DESC) AS rn
FROM orders o
) sub
WHERE rn <= 5;
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.
- ORDER BY: Sorts the final result set.
Optimization: If the subquery runs once per outer row (correlated), consider rewriting as a JOIN or CTE for better performance.
---
Q10. Write a query to get the running total of sales per customer, ordered by sale date
Problem: Write a query to get the running total of sales per customer, ordered by sale date.
How to think before writing:
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
- What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
- How should the result be sorted? ASC (ascending) or DESC (descending)?
Pseudo query (plain English first):
GET columns FROM table
SORT the result
SQL Solution:
SELECT customer_id, sale_date, amount,
SUM(amount) OVER (PARTITION BY
customer_id ORDER BY sale_date) AS running_total
FROM sales;
Step-by-step how the database executes this:
- FROM: Reads table
sales. - Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
Optimization: Window functions scan the partition. Index the PARTITION BY and ORDER BY columns to speed up the sort.
---
Q11. Find the department-wise salary percentile (e.g., 90th percentile) using window functions
Problem: Find the department-wise salary percentile (e.g., 90th percentile) using window functions.
How to think before writing:
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
- What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
- How should the result be sorted? ASC (ascending) or DESC (descending)?
Pseudo query (plain English first):
GET columns FROM table
SORT the result
SQL Solution:
SELECT department_id, salary,
PERCENTILE_CONT(0.9) WITHIN GROUP
(ORDER BY salary) OVER (PARTITION BY
department_id) AS pct_90_salary
FROM employees;
Step-by-step how the database executes this:
- FROM: Reads table
employees. - Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
Optimization: Window functions scan the partition. Index the PARTITION BY and ORDER BY columns to speed up the sort.
---
Q12. Use window function to find the difference between current row’s sales and previous row’s sales partitioned by product
Problem: Use window function to find the difference between current row’s sales and previous row’s sales partitioned by product.
How to think before writing:
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
- What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
- Do I need to compare each row with the next or previous row? Use LEAD() or LAG().
- How should the result be sorted? ASC (ascending) or DESC (descending)?
Pseudo query (plain English first):
GET columns FROM table
SORT the result
SQL Solution:
SELECT product_id, sale_date, amount,
amount - LAG(amount) OVER (PARTITION BY
product_id ORDER BY sale_date) AS sales_diff
FROM sales;
Step-by-step how the database executes this:
- FROM: Reads table
sales. - Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
Optimization: Window functions scan the partition. Index the PARTITION BY and ORDER BY columns to speed up the sort.
---
Q13. Write a query to find the second most recent order date per customer
Problem: Write a query to find the second most recent order date per customer.
How to think before writing:
- Can I solve the inner part first as a subquery, then use the result in the outer query?
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
- What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
- Do I need to assign a unique number to each row within a group? Use ROW_NUMBER().
- What condition filters the rows I need? Write the WHERE clause for that condition.
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 customer_id, order_date
FROM (
SELECT customer_id, order_date,
ROW_NUMBER() OVER (PARTITION BY
customer_id ORDER BY order_date DESC) AS rn
FROM orders
) sub
WHERE rn = 2;
Step-by-step how the database executes this:
- FROM: Reads table
orders. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
Optimization: If the subquery runs once per outer row (correlated), consider rewriting as a JOIN or CTE for better performance.
---
Q14. Get employees with salary in the top 10% in their department
Problem: Get employees with salary in the top 10% in their department.
How to think before writing:
- Can I solve the inner part first as a subquery, then use the result in the outer query?
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
- What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
- What condition filters the rows I need? Write the WHERE clause for that condition.
- How should the result be sorted? ASC (ascending) or DESC (descending)?
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 *
FROM (
SELECT e.*, NTILE(10) OVER (PARTITION BY
department_id ORDER BY salary DESC) AS decile
FROM employees e
) sub
WHERE decile = 1;
Step-by-step how the database executes this:
- FROM: Reads table
employees. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
Optimization: If the subquery runs once per outer row (correlated), consider rewriting as a JOIN or CTE for better performance.
---
Q15. Write a query to calculate the cumulative percentage of total sales per product
Problem: Write a query to calculate the cumulative percentage of total sales per product.
How to think before writing:
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
- What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
- How should the result be sorted? ASC (ascending) or DESC (descending)?
Pseudo query (plain English first):
GET columns FROM table
SORT the result
SQL Solution:
SELECT product_id, sale_amount,
SUM(sale_amount) OVER (ORDER BY
sale_amount DESC) * 100.0 / SUM(sale_amount)
OVER () AS cumulative_pct
FROM sales;
Step-by-step how the database executes this:
- FROM: Reads table
sales. - Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
Optimization: Window functions scan the partition. Index the PARTITION BY and ORDER BY columns to speed up the sort.
---
Q16. Find gaps in date sequences for each customer (missing days)
Problem: Find gaps in date sequences for each customer (missing days).
How to think before writing:
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
- What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
- Do I need to compare each row with the next or previous row? Use LEAD() or LAG().
- What condition filters the rows I need? Write the WHERE clause for that condition.
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH dates AS (
SELECT customer_id, purchase_date,
LAG(purchase_date) OVER (PARTITION BY
customer_id ORDER BY purchase_date) AS prev_date
FROM sales
)
SELECT customer_id, prev_date + INTERVAL '1 day'
AS missing_date
FROM dates
WHERE purchase_date > prev_date + INTERVAL '1
day';
Step-by-step how the database executes this:
- WITH (CTE): Materializes
datesas temporary result set(s). - FROM: Reads table
sales. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
Optimization: If the subquery runs once per outer row (correlated), consider rewriting as a JOIN or CTE for better performance.
---
Q17. Rank employees by salary within their department, and calculate percent rank
Problem: Rank employees by salary within their department, and calculate percent rank.
How to think before writing:
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
- What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
- Do I need to rank rows, handling ties? RANK skips after ties, DENSE_RANK does not.
- How should the result be sorted? ASC (ascending) or DESC (descending)?
Pseudo query (plain English first):
GET columns FROM table
SORT the result
SQL Solution:
SELECT name, department_id, salary,
RANK() OVER (PARTITION BY department_id
ORDER BY salary DESC) AS salary_rank,
PERCENT_RANK() OVER (PARTITION BY
department_id ORDER BY salary DESC) AS
salary_percent_rank
FROM employees;
Step-by-step how the database executes this:
- FROM: Reads table
employees. - Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
Optimization: Window functions scan the partition. Index the PARTITION BY and ORDER BY columns to speed up the sort.
---
Q18. Write a query to find consecutive days where sales were above a threshold
Problem: Write a query to find consecutive days where sales were above a threshold.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
- What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH flagged_sales AS (
SELECT sale_date, amount,
CASE WHEN amount > 1000 THEN 1 ELSE 0
END AS flag
FROM sales
),
groups AS (
SELECT sale_date, amount, flag,
sale_date - INTERVAL ROW_NUMBER()
OVER (ORDER BY sale_date) DAY AS grp
FROM flagged_sales
WHERE flag = 1
)
SELECT MIN(sale_date) AS start_date,
MAX(sale_date) AS end_date, COUNT(*) AS
consecutive_days
FROM groups
GROUP BY grp
ORDER BY consecutive_days DESC;
Step-by-step how the database executes this:
- WITH (CTE): Materializes
flagged_sales, groupsas temporary result set(s). - FROM: Reads table
sales. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- 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.
---
Q19. Calculate the median salary by department using window functions
Problem: Calculate the median salary by department using window functions.
How to think before writing:
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
- What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
- How should the result be sorted? ASC (ascending) or DESC (descending)?
- Are there duplicates I need to remove? Use DISTINCT.
Pseudo query (plain English first):
GET columns FROM table
SORT the result
SQL Solution:
SELECT DISTINCT department_id,
PERCENTILE_CONT(0.5) WITHIN GROUP
(ORDER BY salary) OVER (PARTITION BY
department_id) AS median_salary
FROM employees;
Step-by-step how the database executes this:
- FROM: Reads table
employees. - Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- DISTINCT: Removes duplicate rows by comparing all selected columns.
- ORDER BY: Sorts the final result set.
Optimization: Window functions scan the partition. Index the PARTITION BY and ORDER BY columns to speed up the sort.
---
Q20. Find the percentage difference between each month’s total sales and the previous month’s total sales
Problem: Find the percentage difference between each month’s total sales and the previous month’s total sales.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
- What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH monthly_sales AS (
SELECT DATE_TRUNC('month', sale_date) AS
month, SUM(amount) AS total_sales
FROM sales
GROUP BY month
)
SELECT month, total_sales,
(total_sales - LAG(total_sales) OVER (ORDER
BY month)) * 100.0 / LAG(total_sales) OVER
(ORDER BY month) AS pct_change
FROM monthly_sales;
Step-by-step how the database executes this:
- WITH (CTE): Materializes
monthly_salesas temporary result set(s). - FROM: Reads table
sales. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- 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.
---
Q21. Write a query to find employees who have the longest tenure within their department
Problem: Write a query to find employees who have the longest tenure within their department.
How to think before writing:
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
- What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
- Do I need to rank rows, handling ties? RANK skips after ties, DENSE_RANK does not.
- What condition filters the rows I need? Write the WHERE clause for that condition.
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH tenure AS (
SELECT *,
RANK() OVER (PARTITION BY department_id
ORDER BY hire_date ASC) AS tenure_rank
FROM employees
)
SELECT *
FROM tenure
WHERE tenure_rank = 1;
Step-by-step how the database executes this:
- WITH (CTE): Materializes
tenureas temporary result set(s). - FROM: Reads table
employees. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
Optimization: If the subquery runs once per outer row (correlated), consider rewriting as a JOIN or CTE for better performance.
---
Q22. Calculate the total revenue for each customer, and rank them from highest to lowest spender
Problem: Calculate the total revenue for each customer, and rank them from highest to lowest spender.
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 a calculation per row without collapsing groups? That means a window function with OVER().
- What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
- Do I need to rank rows, handling ties? RANK skips after ties, DENSE_RANK does not.
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_revenue,
RANK() OVER (ORDER BY SUM(amount)
DESC) AS revenue_rank
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.
- Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- 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.
---
Q23. Write a query to find the top 3 products with the highest total sales amount each month
Problem: Write a query to find the top 3 products with the highest total sales amount 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.
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
- What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH monthly_product_sales AS (
SELECT product_id, DATE_TRUNC('month',
sale_date) AS month, SUM(amount) AS total_sales
FROM sales
GROUP BY product_id, month
),
ranked_sales AS (
SELECT *, RANK() OVER (PARTITION BY month
ORDER BY total_sales DESC) AS sales_rank
FROM monthly_product_sales
)
SELECT product_id, month, total_sales
FROM ranked_sales
WHERE sales_rank <= 3
ORDER BY month, sales_rank;
Step-by-step how the database executes this:
- WITH (CTE): Materializes
monthly_product_sales, ranked_salesas temporary result set(s). - FROM: Reads table
sales. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- 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.
---
Q24. List the top 5 highest-paid employees per department
Problem: List the top 5 highest-paid employees per department.
How to think before writing:
- Can I solve the inner part first as a subquery, then use the result in the outer query?
- Do I need to assign a unique number to each row within a group? Use ROW_NUMBER().
- What condition filters the rows I need? Write the WHERE clause for that condition.
- How should the result be sorted? ASC (ascending) or DESC (descending)?
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 *
FROM (
SELECT e.*, ROW_NUMBER() OVER
(PARTITION BY department_id ORDER BY salary
DESC) AS rn
FROM employees e
) sub
WHERE rn <= 5;
Step-by-step how the database executes this:
- FROM: Reads table
employees. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
Optimization: If the subquery runs once per outer row (correlated), consider rewriting as a JOIN or CTE for better performance.
---
Q25. Write a query to identify “gaps and islands” in attendance records (consecutive dates present)
Problem: Write a query to identify “gaps and islands” in attendance records (consecutive dates present).
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
- What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH attendance_groups AS (
SELECT employee_id, attendance_date,
attendance_date - INTERVAL ROW_NUMBER()
OVER (PARTITION BY employee_id ORDER BY
attendance_date) DAY AS grp
FROM attendance
)
SELECT employee_id, MIN(attendance_date) AS
start_date, MAX(attendance_date) AS end_date,
COUNT(*) AS consecutive_days
FROM attendance_groups
GROUP BY employee_id, grp
ORDER BY employee_id, start_date;
Step-by-step how the database executes this:
- WITH (CTE): Materializes
attendance_groupsas temporary result set(s). - FROM: Reads table
attendance. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- 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.
---
Q26. Calculate a 3-month moving average of monthly sales per product
Problem: Calculate a 3-month moving average of monthly sales per product.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
- What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH monthly_sales AS (
SELECT product_id, DATE_TRUNC('month',
sale_date) AS month, SUM(amount) AS total_sales
FROM sales
GROUP BY product_id, month
)
SELECT product_id, month, total_sales,
AVG(total_sales) OVER (PARTITION BY
product_id ORDER BY month ROWS BETWEEN 2
PRECEDING AND CURRENT ROW) AS
moving_avg
FROM monthly_sales;
Step-by-step how the database executes this:
- WITH (CTE): Materializes
monthly_salesas temporary result set(s). - FROM: Reads table
sales. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- 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. Write a query to find products with increasing sales over the last 3 months
Problem: Write a query to find products with increasing sales over the last 3 months.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Which tables do I need? How are they related? What column connects them?
- Do I need to compare rows within the same table? That means a self-join with aliases.
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH monthly_sales AS (
SELECT product_id, DATE_TRUNC('month',
sale_date) AS month, SUM(amount) AS total_sales
FROM sales
GROUP BY product_id, month
),
ranked_sales AS (
SELECT product_id, month, total_sales,
ROW_NUMBER() OVER (PARTITION BY
product_id ORDER BY month DESC) AS rn
FROM monthly_sales
)
SELECT ms1.product_id
FROM ranked_sales ms1
JOIN ranked_sales ms2 ON ms1.product_id =
ms2.product_id AND ms1.rn = 1 AND ms2.rn = 2
JOIN ranked_sales ms3 ON ms1.product_id =
ms3.product_id AND ms3.rn = 3
WHERE ms3.total_sales < ms2.total_sales AND
ms2.total_sales < ms1.total_sales;
Step-by-step how the database executes this:
- WITH (CTE): Materializes
monthly_sales, ranked_salesas temporary result set(s). - 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.
- Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q28. Write a query to get the nth highest salary per department
Problem: Write a query to get the nth highest salary per department.
How to think before writing:
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
- What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
- Do I need to assign a unique number to each row within a group? Use ROW_NUMBER().
- Do I need to rank rows, handling ties? RANK skips after ties, DENSE_RANK does not.
- What condition filters the rows I need? Write the WHERE clause for that condition.
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 department_id, salary
FROM (
SELECT department_id, salary, ROW_NUMBER()
OVER (PARTITION BY department_id ORDER BY
salary DESC) AS rn
FROM employees
) sub
WHERE rn = N;
(Replace N with the desired rank.)
Step-by-step how the database executes this:
- FROM: Reads table
employees. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
Optimization: If the subquery runs once per outer row (correlated), consider rewriting as a JOIN or CTE for better performance.
---
Q29. Get the product with the highest sales for each category
Problem: Get the product with the highest sales for each 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.
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
- What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH category_sales AS (
SELECT category_id, product_id, SUM(amount) AS
total_sales,
RANK() OVER (PARTITION BY category_id
ORDER BY SUM(amount) DESC) AS sales_rank
FROM sales
GROUP BY category_id, product_id
)
SELECT category_id, product_id, total_sales
FROM category_sales
WHERE sales_rank = 1;
Step-by-step how the database executes this:
- WITH (CTE): Materializes
category_salesas temporary result set(s). - FROM: Reads table
sales. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- 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.
---
Q30. Write a query to detect gaps in a sequence of invoice numbers
Problem: Write a query to detect gaps in a sequence of invoice numbers.
How to think before writing:
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
- Do I need to assign a unique number to each row within a group? Use ROW_NUMBER().
- What condition filters the rows I need? Write the WHERE clause for that condition.
- How should the result be sorted? ASC (ascending) or DESC (descending)?
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH numbered_invoices AS (
SELECT invoice_number, ROW_NUMBER() OVER
(ORDER BY invoice_number) AS rn
FROM invoices
)
SELECT invoice_number + 1 AS missing_invoice
FROM numbered_invoices ni
WHERE (invoice_number + 1) <> (
SELECT invoice_number FROM numbered_invoices
WHERE rn = ni.rn + 1
);
Step-by-step how the database executes this:
- WITH (CTE): Materializes
numbered_invoicesas temporary result set(s). - FROM: Reads table
invoices. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
Optimization: If the subquery runs once per outer row (correlated), consider rewriting as a JOIN or CTE for better performance.
---
Q31. Calculate the rank of employees by salary within their department but restart rank numbering every 10 employees
Problem: Calculate the rank of employees by salary within their department but restart rank numbering every 10 employees.
How to think before writing:
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
- What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
- Do I need to assign a unique number to each row within a group? Use ROW_NUMBER().
- Do I need to rank rows, handling ties? RANK skips after ties, DENSE_RANK does not.
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH ranked_employees AS (
SELECT e.*,
ROW_NUMBER() OVER (PARTITION BY
department_id ORDER BY salary DESC) AS rn
FROM employees e
)
SELECT *, ((rn - 1) / 10) + 1 AS rank_group
FROM ranked_employees;
Step-by-step how the database executes this:
- WITH (CTE): Materializes
ranked_employeesas temporary result set(s). - FROM: Reads table
employees. - Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
Optimization: Window functions scan the partition. Index the PARTITION BY and ORDER BY columns to speed up the sort.
---
Q32. Find the moving median of daily sales over the last 7 days for each product
Problem: Find the moving median of daily sales over the last 7 days for each product.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
- What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH daily_sales AS (
SELECT product_id, sale_date, SUM(amount) AS
total_sales
FROM sales
GROUP BY product_id, sale_date
)
SELECT product_id, sale_date,
PERCENTILE_CONT(0.5) WITHIN GROUP
(ORDER BY total_sales)
OVER (PARTITION BY product_id ORDER BY
sale_date ROWS BETWEEN 6 PRECEDING AND
CURRENT ROW) AS moving_median
FROM daily_sales;
Step-by-step how the database executes this:
- WITH (CTE): Materializes
daily_salesas temporary result set(s). - FROM: Reads table
sales. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- 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.
---
Q33. Find the 3 most recent orders per customer including order details
Problem: Find the 3 most recent orders per customer including order details.
How to think before writing:
- Can I solve the inner part first as a subquery, then use the result in the outer query?
- Do I need to assign a unique number to each row within a group? Use ROW_NUMBER().
- What condition filters the rows I need? Write the WHERE clause for that condition.
- How should the result be sorted? ASC (ascending) or DESC (descending)?
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 *
FROM (
SELECT o.*, ROW_NUMBER() OVER
(PARTITION BY customer_id ORDER BY order_date
DESC) AS rn
FROM orders o
) sub
WHERE rn <= 3;
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.
- ORDER BY: Sorts the final result set.
Optimization: If the subquery runs once per outer row (correlated), consider rewriting as a JOIN or CTE for better performance.
---
Q34. Find products that have seen a price increase in the last 6 months
Problem: Find products that have seen a price increase in the last 6 months.
How to think before writing:
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
- What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
- Do I need to compare each row with the next or previous row? Use LEAD() or LAG().
- What condition filters the rows I need? Write the WHERE clause for that condition.
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH price_changes AS (
SELECT product_id, price, effective_date,
LAG(price) OVER (PARTITION BY product_id
ORDER BY effective_date) AS prev_price
FROM product_prices
WHERE effective_date >= CURRENT_DATE -
INTERVAL '6 months'
)
SELECT DISTINCT product_id
FROM price_changes
WHERE price > prev_price;
Step-by-step how the database executes this:
- WITH (CTE): Materializes
price_changesas temporary result set(s). - FROM: Reads table
product_prices. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- DISTINCT: Removes duplicate rows by comparing all selected columns.
- ORDER BY: Sorts the final result set.
Optimization: If the subquery runs once per outer row (correlated), consider rewriting as a JOIN or CTE for better performance.
---
Q35. Find the department(s) with the second highest average salary
Problem: Find the department(s) with the second highest average salary.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
- What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH avg_salaries AS (
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
),
ranked_salaries AS (
SELECT department_id, avg_salary,
DENSE_RANK() OVER (ORDER BY avg_salary
DESC) AS rnk
FROM avg_salaries
)
SELECT department_id, avg_salary
FROM ranked_salaries
WHERE rnk = 2;
Step-by-step how the database executes this:
- WITH (CTE): Materializes
avg_salaries, ranked_salariesas temporary result set(s). - FROM: Reads table
employees. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- 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.
---
Q36. Calculate the percentage change in sales for each product comparing current month to previous month
Problem: Calculate the percentage change in sales for each product comparing current month to previous 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.
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
- What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH monthly_sales AS (
SELECT product_id, DATE_TRUNC('month',
sale_date) AS month, SUM(amount) AS total_sales
FROM sales
GROUP BY product_id, month
)
SELECT product_id, month, total_sales,
(total_sales - LAG(total_sales) OVER
(PARTITION BY product_id ORDER BY month)) *
100.0
/ LAG(total_sales) OVER (PARTITION BY
product_id ORDER BY month) AS pct_change
FROM monthly_sales;
Step-by-step how the database executes this:
- WITH (CTE): Materializes
monthly_salesas temporary result set(s). - FROM: Reads table
sales. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- 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.
---
Q37. Write a query to identify duplicate rows (all columns) in a table
Problem: Write a query to identify duplicate rows (all columns) in a table.
How to think before writing:
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
- What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
- 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 *, COUNT(*) OVER (PARTITION BY col1,
col2, col3, ...) AS cnt
FROM table_name
WHERE cnt > 1;
(Replace col1, col2, col3, ... with actual column names)
Step-by-step how the database executes this:
- FROM: Reads table
table_name. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Window functions scan the partition. Index the PARTITION BY and ORDER BY columns to speed up the sort.
---
Q38. Write a query to find customers with the highest purchase amount per year
Problem: Write a query to find customers with the highest purchase amount 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.
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
- What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH yearly_sales AS (
SELECT customer_id, EXTRACT(YEAR FROM
sale_date) AS year, SUM(amount) AS total_amount
FROM sales
GROUP BY customer_id, year
),
ranked_sales AS (
SELECT *, RANK() OVER (PARTITION BY year
ORDER BY total_amount DESC) AS rnk
FROM yearly_sales
)
SELECT customer_id, year, total_amount
FROM ranked_sales
WHERE rnk = 1;
Step-by-step how the database executes this:
- WITH (CTE): Materializes
yearly_sales, ranked_salesas temporary result set(s). - FROM: Reads table
sale_date. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- 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.
---
Q39. Find the running total of sales by date
Problem: Find the running total of sales by date.
How to think before writing:
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
- What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
- How should the result be sorted? ASC (ascending) or DESC (descending)?
Pseudo query (plain English first):
GET columns FROM table
SORT the result
SQL Solution:
SELECT sale_date, SUM(amount) OVER (ORDER BY
sale_date ROWS BETWEEN UNBOUNDED
PRECEDING AND CURRENT ROW) AS
running_total
FROM sales
ORDER BY sale_date;
Step-by-step how the database executes this:
- FROM: Reads table
sales. - Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
Optimization: Window functions scan the partition. Index the PARTITION BY and ORDER BY columns to speed up the sort.
---
Q40. Get the last 3 orders placed by each customer
Problem: Get the last 3 orders placed by each customer.
How to think before writing:
- Can I solve the inner part first as a subquery, then use the result in the outer query?
- Do I need to assign a unique number to each row within a group? Use ROW_NUMBER().
- What condition filters the rows I need? Write the WHERE clause for that condition.
- How should the result be sorted? ASC (ascending) or DESC (descending)?
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 *
FROM (
SELECT o.*, ROW_NUMBER() OVER
(PARTITION BY customer_id ORDER BY order_date
DESC) AS rn
FROM orders o
) sub
WHERE rn <= 3;
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.
- ORDER BY: Sorts the final result set.
Optimization: If the subquery runs once per outer row (correlated), consider rewriting as a JOIN or CTE for better performance.
---
Q41. Write a query to find gaps in employee IDs
Problem: Write a query to find gaps in employee IDs.
How to think before writing:
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
- What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
- Do I need to assign a unique number to each row within a group? Use ROW_NUMBER().
- What condition filters the rows I need? Write the WHERE clause for that condition.
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH numbered AS (
SELECT id, ROW_NUMBER() OVER (ORDER BY
id) AS rn
FROM employees
)
SELECT rn + 1 AS missing_id
FROM numbered
WHERE id <> rn;
Step-by-step how the database executes this:
- WITH (CTE): Materializes
numberedas temporary result set(s). - FROM: Reads table
employees. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
Optimization: If the subquery runs once per outer row (correlated), consider rewriting as a JOIN or CTE for better performance.
---
Q42. Find customers with the longest gap between two consecutive orders
Problem: Find customers with the longest gap between two consecutive orders.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
- What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH ordered_orders AS (
SELECT customer_id, order_date,
LAG(order_date) OVER (PARTITION BY
customer_id ORDER BY order_date) AS
prev_order_date
FROM orders
),
gaps AS (
SELECT customer_id, order_date - prev_order_date
AS gap_days
FROM ordered_orders
WHERE prev_order_date IS NOT NULL
)
SELECT customer_id, MAX(gap_days) AS
longest_gap
FROM gaps
GROUP BY customer_id
ORDER BY longest_gap DESC
LIMIT 1;
Step-by-step how the database executes this:
- WITH (CTE): Materializes
ordered_orders, gapsas temporary result set(s). - FROM: Reads table
orders. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- 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.
---
Q43. Find the median salary of employees
Problem: Find the median salary of employees.
How to think before writing:
1. How should the result be sorted? ASC (ascending) or DESC (descending)?
Pseudo query (plain English first):
GET columns FROM table
SORT the result
SQL Solution:
SELECT PERCENTILE_CONT(0.5) WITHIN
GROUP (ORDER BY salary) AS median_salary
FROM employees;
Step-by-step how the database executes this:
- FROM: Reads table
employees. - SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
Optimization: Run EXPLAIN ANALYZE to check the execution plan and add indexes where needed.
---
Q44. Find employees with consecutive workdays
Problem: Find employees with consecutive workdays.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need to filter groups after aggregation? That means HAVING, not WHERE.
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH attendance AS (
SELECT employee_id, work_date,
work_date - INTERVAL '1 day' *
ROW_NUMBER() OVER (PARTITION BY
employee_id ORDER BY work_date) AS grp
FROM work_log
)
SELECT employee_id, COUNT(*) AS
consecutive_days
FROM attendance
GROUP BY employee_id, grp
HAVING COUNT(*) > 1;
Step-by-step how the database executes this:
- WITH (CTE): Materializes
attendanceas temporary result set(s). - FROM: Reads table
work_log. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- HAVING: Filters groups. Only groups passing the condition remain.
- Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- 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.
---
Q45. Get cumulative count of orders per customer over time
Problem: Get cumulative count of orders per customer over time.
How to think before writing:
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
- What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
- How should the result be sorted? ASC (ascending) or DESC (descending)?
Pseudo query (plain English first):
GET columns FROM table
SORT the result
SQL Solution:
SELECT customer_id, order_date,
COUNT(*) OVER (PARTITION BY customer_id
ORDER BY order_date) AS cumulative_orders
FROM orders;
Step-by-step how the database executes this:
- FROM: Reads table
orders. - Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
Optimization: Window functions scan the partition. Index the PARTITION BY and ORDER BY columns to speed up the sort.
---
Q46. Calculate the difference between each order amount and the previous order amount per customer
Problem: Calculate the difference between each order amount and the previous order amount per customer.
How to think before writing:
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
- What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
- Do I need to compare each row with the next or previous row? Use LEAD() or LAG().
- How should the result be sorted? ASC (ascending) or DESC (descending)?
Pseudo query (plain English first):
GET columns FROM table
SORT the result
SQL Solution:
SELECT customer_id, order_date, amount,
amount - LAG(amount) OVER (PARTITION BY
customer_id ORDER BY order_date) AS diff
FROM orders;
Step-by-step how the database executes this:
- FROM: Reads table
orders. - Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
Optimization: Window functions scan the partition. Index the PARTITION BY and ORDER BY columns to speed up the sort.
---
Q47. Find employees whose salaries are between the 25th and 75th percentile
Problem: Find employees whose salaries are between the 25th and 75th percentile.
How to think before writing:
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
- How should the result be sorted? ASC (ascending) or DESC (descending)?
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH percentiles AS (
SELECT
PERCENTILE_CONT(0.25) WITHIN GROUP
(ORDER BY salary) AS p25,
PERCENTILE_CONT(0.75) WITHIN GROUP
(ORDER BY salary) AS p75
FROM employees
)
SELECT e.*
FROM employees e, percentiles p
WHERE e.salary BETWEEN p.p25 AND p.p75;
Step-by-step how the database executes this:
- WITH (CTE): Materializes
percentilesas temporary result set(s). - FROM: Reads table
employees. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
Optimization: If the subquery runs once per outer row (correlated), consider rewriting as a JOIN or CTE for better performance.
---
Q48. Find employees with salaries higher than their department average
Problem: Find employees with salaries higher than their department average.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Which tables do I need? How are they related? What column connects them?
- Can I solve the inner part first as a subquery, then use the result in the outer query?
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
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 e.*
FROM employees e
JOIN (
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
) d ON e.department_id = d.department_id
WHERE e.salary > d.avg_salary;
--148. Find the difference between each row's value and
the previous row’s value in sales.
SELECT sale_date, amount,
amount - LAG(amount) OVER (ORDER BY
sale_date) AS diff
FROM sales;
Step-by-step how the database executes this:
- FROM: Reads table
employees. - INNER JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q49. Find the average gap (in days) between orders per customer
Problem: Find the average gap (in days) between 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.
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
- What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH ordered_orders AS (
SELECT customer_id, order_date,
LAG(order_date) OVER (PARTITION BY
customer_id ORDER BY order_date) AS
prev_order_date
FROM orders
),
gaps AS (
SELECT customer_id, order_date - prev_order_date
AS gap
FROM ordered_orders
WHERE prev_order_date IS NOT NULL
)
SELECT customer_id, AVG(gap) AS avg_gap_days
FROM gaps
GROUP BY customer_id;
Step-by-step how the database executes this:
- WITH (CTE): Materializes
ordered_orders, gapsas temporary result set(s). - FROM: Reads table
orders. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- 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.
---
Q50. Find the top 3 products by quantity sold in each category
Problem: Find the top 3 products by quantity sold in each 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?
- Can I solve the inner part first as a subquery, then use the result in the outer query?
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
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 category_id, product_id, total_quantity
FROM (
SELECT p.category_id, s.product_id,
SUM(s.quantity) AS total_quantity,
ROW_NUMBER() OVER (PARTITION BY
p.category_id ORDER BY SUM(s.quantity) DESC) AS
rn
FROM sales s
JOIN products p ON s.product_id = p.product_id
GROUP BY p.category_id, s.product_id
) sub
WHERE rn <= 3;
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.
- Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q51. Find products whose sales have doubled compared to the previous month
Problem: Find products whose sales have doubled compared to the previous 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.
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
- What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH monthly_sales AS (
SELECT product_id, DATE_TRUNC('month',
sale_date) AS month, SUM(amount) AS total_sales
FROM sales
GROUP BY product_id, month
),
sales_comparison AS (
SELECT product_id, month, total_sales,
LAG(total_sales) OVER (PARTITION BY
product_id ORDER BY month) AS prev_month_sales
FROM monthly_sales
)
SELECT product_id, month
FROM sales_comparison
WHERE prev_month_sales IS NOT NULL AND
total_sales >= 2 * prev_month_sales;
Step-by-step how the database executes this:
- WITH (CTE): Materializes
monthly_sales, sales_comparisonas temporary result set(s). - FROM: Reads table
sales. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- 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.
---
Q52. Find the rank of employees based on salary within their department
Problem: Find the rank of employees based on salary within their department.
How to think before writing:
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
- What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
- Do I need to rank rows, handling ties? RANK skips after ties, DENSE_RANK does not.
- How should the result be sorted? ASC (ascending) or DESC (descending)?
Pseudo query (plain English first):
GET columns FROM table
SORT the result
SQL Solution:
SELECT *, RANK() OVER (PARTITION BY
department_id ORDER BY salary DESC) AS
salary_rank
FROM employees;
Step-by-step how the database executes this:
- FROM: Reads table
employees. - Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
Optimization: Window functions scan the partition. Index the PARTITION BY and ORDER BY columns to speed up the sort.
---
Q53. Find customers whose orders increased by at least 20% compared to the previous month
Problem: Find customers whose orders increased by at least 20% compared to the previous 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.
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
- What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH monthly_orders AS (
SELECT customer_id, DATE_TRUNC('month',
order_date) AS month, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id, month
),
orders_comparison AS (
SELECT customer_id, month, order_count,
LAG(order_count) OVER (PARTITION BY
customer_id ORDER BY month) AS prev_order_count
FROM monthly_orders
)
SELECT customer_id, month
FROM orders_comparison
WHERE prev_order_count IS NOT NULL AND
order_count >= 1.2 * prev_order_count;
Step-by-step how the database executes this:
- WITH (CTE): Materializes
monthly_orders, orders_comparisonas temporary result set(s). - FROM: Reads table
orders. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- 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.
---
Q54. Find the average order amount excluding the top 5% largest orders
Problem: Find the average order amount excluding the top 5% largest orders.
How to think before writing:
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
- What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
- What condition filters the rows I need? Write the WHERE clause for that condition.
- How should the result be sorted? ASC (ascending) or DESC (descending)?
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH ordered_orders AS (
SELECT amount,
NTILE(100) OVER (ORDER BY amount DESC)
AS percentile
FROM orders
)
SELECT AVG(amount)
FROM ordered_orders
WHERE percentile > 5;
Step-by-step how the database executes this:
- WITH (CTE): Materializes
ordered_ordersas temporary result set(s). - FROM: Reads table
orders. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
Optimization: If the subquery runs once per outer row (correlated), consider rewriting as a JOIN or CTE for better performance.
---
Q55. Find employees with the longest consecutive workdays
Problem: Find employees with the longest consecutive workdays.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
- What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH workdays AS (
SELECT employee_id, work_date,
ROW_NUMBER() OVER (PARTITION BY
employee_id ORDER BY work_date) -
ROW_NUMBER() OVER (PARTITION BY
employee_id ORDER BY work_date) AS grp
FROM attendance
),
consecutive_days AS (
SELECT employee_id, COUNT(*) AS
consecutive_days
FROM workdays
GROUP BY employee_id, grp
)
SELECT employee_id, MAX(consecutive_days) AS
max_consecutive_days
FROM consecutive_days
GROUP BY employee_id;
Step-by-step how the database executes this:
- WITH (CTE): Materializes
workdays, consecutive_daysas temporary result set(s). - FROM: Reads table
attendance. - GROUP BY: Collapses rows into groups. One output row per unique group value.
- Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- 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.
---
Q56. Find the average salary of employees hired each month
Problem: Find the average salary of employees hired 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.
- 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?
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
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 EXTRACT(YEAR FROM hire_date) AS
year, EXTRACT(MONTH FROM hire_date) AS
month, AVG(salary) AS avg_salary
FROM employees
GROUP BY year, month
ORDER BY year, month;
--207. Find the first 5 orders after a customer's
registration date.
SELECT order_id, customer_id, order_date
FROM (
SELECT order_id, customer_id, order_date,
ROW_NUMBER() OVER (PARTITION BY
customer_id ORDER BY order_date) AS rn
FROM orders
JOIN customers c ON orders.customer_id =
c.customer_id
WHERE order_date >= c.registration_date
) sub
WHERE rn <= 5;
Step-by-step how the database executes this:
- FROM: Reads table
hire_date. - 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.
- Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q57. Calculate the moving average of sales over the last 3 days
Problem: Calculate the moving average of sales over the last 3 days.
How to think before writing:
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
- What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
- How should the result be sorted? ASC (ascending) or DESC (descending)?
Pseudo query (plain English first):
GET columns FROM table
SORT the result
SQL Solution:
SELECT sale_date, product_id, amount,
AVG(amount) OVER (PARTITION BY
product_id ORDER BY sale_date ROWS BETWEEN 2
PRECEDING AND CURRENT ROW) AS
moving_avg_3_days
FROM sales;
Step-by-step how the database executes this:
- FROM: Reads table
sales. - Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
Optimization: Window functions scan the partition. Index the PARTITION BY and ORDER BY columns to speed up the sort.
---
Q58. List the top 5 employees by number of projects in each department
Problem: List the top 5 employees by number of projects in each department.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Which tables do I need? How are they related? What column connects them?
- Can I solve the inner part first as a subquery, then use the result in the outer query?
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
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 department_id, employee_id, project_count
FROM (
SELECT e.department_id, pa.employee_id,
COUNT(DISTINCT pa.project_id) AS project_count,
ROW_NUMBER() OVER (PARTITION BY
e.department_id ORDER BY COUNT(DISTINCT
pa.project_id) DESC) AS rn
FROM project_assignments pa
JOIN employees e ON pa.employee_id = e.id
GROUP BY e.department_id, pa.employee_id
) sub
WHERE rn <= 5;
Step-by-step how the database executes this:
- FROM: Reads table
project_assignments. - 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.
- Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- DISTINCT: Removes duplicate rows by comparing all selected columns.
- ORDER BY: Sorts the final result set.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q59. Find the 3 most recent orders per customer
Problem: Find the 3 most recent orders per customer.
How to think before writing:
- Can I solve the inner part first as a subquery, then use the result in the outer query?
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
- What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
- Do I need to assign a unique number to each row within a group? Use ROW_NUMBER().
- What condition filters the rows I need? Write the WHERE clause for that condition.
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 order_id, customer_id, order_date
FROM (
SELECT order_id, customer_id, order_date,
ROW_NUMBER() OVER (PARTITION BY
customer_id ORDER BY order_date DESC) AS rn
FROM orders
) sub
WHERE rn <= 3;
Step-by-step how the database executes this:
- FROM: Reads table
orders. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
Optimization: If the subquery runs once per outer row (correlated), consider rewriting as a JOIN or CTE for better performance.
---
Q60. Find the median salary per department
Problem: Find the median salary 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.
- 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,
PERCENTILE_CONT(0.5) WITHIN GROUP
(ORDER BY salary) AS median_salary
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.
- 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.
---
Q61. Find the second most expensive product per category
Problem: Find the second most expensive product per category.
How to think before writing:
- Can I solve the inner part first as a subquery, then use the result in the outer query?
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
- What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
- Do I need to assign a unique number to each row within a group? Use ROW_NUMBER().
- What condition filters the rows I need? Write the WHERE clause for that condition.
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 category_id, product_id, price
FROM (
SELECT category_id, product_id, price,
ROW_NUMBER() OVER (PARTITION BY
category_id ORDER BY price DESC) AS rn
FROM products
) sub
WHERE rn = 2;
Step-by-step how the database executes this:
- FROM: Reads table
products. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
Optimization: If the subquery runs once per outer row (correlated), consider rewriting as a JOIN or CTE for better performance.
---
Q62. Find the top 3 customers by total order amount in each region
Problem: Find the top 3 customers by total order amount in each region.
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?
- Can I solve the inner part first as a subquery, then use the result in the outer query?
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
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 region, customer_id, total_amount
FROM (
SELECT c.region, o.customer_id, SUM(o.amount) AS
total_amount,
ROW_NUMBER() OVER (PARTITION BY
c.region ORDER BY SUM(o.amount) DESC) AS rn
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.region, o.customer_id
) sub
WHERE rn <= 3;
Step-by-step how the database executes this:
- FROM: Reads table
customers. - 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.
- Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q63. Find customers whose orders decreased consecutively for 3 months
Problem: Find customers whose orders decreased consecutively for 3 months.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
- What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH monthly_orders AS (
SELECT customer_id, DATE_TRUNC('month',
order_date) AS month, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id, month
),
orders_with_lag AS (
SELECT customer_id, month, order_count,
LAG(order_count, 1) OVER (PARTITION BY
customer_id ORDER BY month) AS prev_1,
LAG(order_count, 2) OVER (PARTITION BY
customer_id ORDER BY month) AS prev_2
FROM monthly_orders
)
SELECT DISTINCT customer_id
FROM orders_with_lag
WHERE order_count < prev_1 AND prev_1 < prev_2;
Step-by-step how the database executes this:
- WITH (CTE): Materializes
monthly_orders, orders_with_lagas temporary result set(s). - FROM: Reads table
orders. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- DISTINCT: Removes duplicate rows by comparing all selected columns.
- ORDER BY: Sorts the final result set.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
Q64. Find products with an increasing sales trend over the last 3 months
Problem: Find products with an increasing sales trend over the last 3 months.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
- What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH monthly_sales AS (
SELECT product_id, DATE_TRUNC('month',
sale_date) AS month, SUM(amount) AS total_sales
FROM sales
WHERE sale_date >= CURRENT_DATE -
INTERVAL '3 months'
GROUP BY product_id, month
),
sales_ranked AS (
SELECT product_id, month, total_sales,
LAG(total_sales) OVER (PARTITION BY
product_id ORDER BY month) AS prev_month_sales,
LAG(total_sales, 2) OVER (PARTITION BY
product_id ORDER BY month) AS
prev_2_month_sales
FROM monthly_sales
)
SELECT DISTINCT product_id
FROM sales_ranked
WHERE total_sales > prev_month_sales AND
prev_month_sales > prev_2_month_sales;
Step-by-step how the database executes this:
- WITH (CTE): Materializes
monthly_sales, sales_rankedas temporary result set(s). - FROM: Reads table
sales. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- DISTINCT: Removes duplicate rows by comparing all selected columns.
- ORDER BY: Sorts the final result set.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
Q65. Find customers who ordered the most products in each category
Problem: Find customers who ordered the most products in each 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?
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH product_totals AS (
SELECT c.customer_id, p.category_id,
SUM(s.quantity) AS total_quantity,
RANK() OVER (PARTITION BY p.category_id
ORDER BY SUM(s.quantity) DESC) AS rank
FROM sales s
JOIN products p ON s.product_id = p.product_id
JOIN customers c ON s.customer_id = c.customer_id
GROUP BY c.customer_id, p.category_id
)
SELECT customer_id, category_id, total_quantity
FROM product_totals
WHERE rank = 1;
Step-by-step how the database executes this:
- WITH (CTE): Materializes
product_totalsas temporary result set(s). - 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.
- Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q66. Find customers who have increased their order frequency month-over-month for 3 consecutive months
Problem: Find customers who have increased their order frequency month-over-month for 3 consecutive 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.
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
- What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH monthly_orders AS (
SELECT customer_id, DATE_TRUNC('month',
order_date) AS month, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id, month
),
orders_with_lag AS (
SELECT customer_id, month, order_count,
LAG(order_count, 1) OVER (PARTITION BY
customer_id ORDER BY month) AS prev_1,
LAG(order_count, 2) OVER (PARTITION BY
customer_id ORDER BY month) AS prev_2
FROM monthly_orders
)
SELECT DISTINCT customer_id
FROM orders_with_lag
WHERE order_count > prev_1 AND prev_1 > prev_2;
Step-by-step how the database executes this:
- WITH (CTE): Materializes
monthly_orders, orders_with_lagas temporary result set(s). - FROM: Reads table
orders. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- DISTINCT: Removes duplicate rows by comparing all selected columns.
- ORDER BY: Sorts the final result set.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
Q67. Find employees whose salaries increased every year
Problem: Find employees whose salaries increased every year.
How to think before writing:
- What do I need to group by? Identify the column(s) that define each group.
- What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
- Do I need to filter groups after aggregation? That means HAVING, not WHERE.
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH salary_diff AS (
SELECT employee_id, year, salary,
LAG(salary) OVER (PARTITION BY
employee_id ORDER BY year) AS prev_salary
FROM salaries
)
SELECT DISTINCT employee_id
FROM salary_diff
WHERE salary > prev_salary OR prev_salary IS NULL
GROUP BY employee_id
HAVING COUNT(*) = (SELECT COUNT(*) FROM
salaries s2 WHERE s2.employee_id =
salary_diff.employee_id);
Step-by-step how the database executes this:
- WITH (CTE): Materializes
salary_diffas temporary result set(s). - FROM: Reads table
salaries. - 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.
- Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- DISTINCT: Removes duplicate rows by comparing all selected columns.
- ORDER BY: Sorts the final result set.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
Q68. Find the day with the highest sales in each month
Problem: Find the day with the highest sales 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.
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
- What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH daily_sales AS (
SELECT DATE(order_date) AS day, SUM(amount)
AS total_sales
FROM orders
GROUP BY day
),
ranked_sales AS (
SELECT day, total_sales,
RANK() OVER (PARTITION BY
DATE_TRUNC('month', day) ORDER BY total_sales
DESC) AS sales_rank
FROM daily_sales
)
SELECT day, total_sales
FROM ranked_sales
WHERE sales_rank = 1;
Step-by-step how the database executes this:
- WITH (CTE): Materializes
daily_sales, ranked_salesas temporary result set(s). - FROM: Reads table
orders. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- 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.
---
Q69. Find the products with the highest sales increase compared to the previous month
Problem: Find the products with the highest sales increase compared to the previous 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.
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
- What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH monthly_sales AS (
SELECT product_id, DATE_TRUNC('month',
sale_date) AS month, SUM(amount) AS total_sales
FROM sales
GROUP BY product_id, month
),
sales_diff AS (
SELECT product_id, month, total_sales,
LAG(total_sales) OVER (PARTITION BY
product_id ORDER BY month) AS prev_month_sales
FROM monthly_sales
)
SELECT product_id, month, total_sales -
prev_month_sales AS increase
FROM sales_diff
WHERE prev_month_sales IS NOT NULL
ORDER BY increase DESC
LIMIT 10;
Step-by-step how the database executes this:
- WITH (CTE): Materializes
monthly_sales, sales_diffas temporary result set(s). - FROM: Reads table
sales. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- 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 the lowest average rating per category
Problem: Find products with the lowest average rating per 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 a calculation per row without collapsing groups? That means a window function with OVER().
- What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
- Do I need to assign a unique number to each row within a group? Use ROW_NUMBER().
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 category_id, product_id, AVG(rating) AS
avg_rating
FROM product_reviews
GROUP BY category_id, product_id
QUALIFY ROW_NUMBER() OVER (PARTITION
BY category_id ORDER BY AVG(rating)) = 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.
- Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- 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.
---
Q71. Find the product with the highest sales for each month
Problem: Find the product with the highest sales for 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.
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
- What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH monthly_sales AS (
SELECT product_id, DATE_TRUNC('month',
sale_date) AS month, SUM(amount) AS total_sales
FROM sales
GROUP BY product_id, month
),
ranked_sales AS (
SELECT product_id, month, total_sales,
ROW_NUMBER() OVER (PARTITION BY
month ORDER BY total_sales DESC) AS rn
FROM monthly_sales
)
SELECT product_id, month, total_sales
FROM ranked_sales
WHERE rn = 1;
Step-by-step how the database executes this:
- WITH (CTE): Materializes
monthly_sales, ranked_salesas temporary result set(s). - FROM: Reads table
sales. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- 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.
---
Q72. Find customers with the highest order count in each region
Problem: Find customers with the highest order count in each region.
How to think before writing:
- Which tables do I need? How are they related? What column connects them?
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
- What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
- Do I need to assign a unique number to each row within a group? Use ROW_NUMBER().
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH customer_order_counts AS (
SELECT c.region, o.customer_id, COUNT(*) AS
order_count,
ROW_NUMBER() OVER (PARTITION BY
c.region ORDER BY COUNT(*) DESC) AS rn
FROM customers c
JOIN orders o
Step-by-step how the database executes this:
- WITH (CTE): Materializes
customer_order_countsas temporary result set(s). - FROM: Reads table
customers. - INNER JOIN: Combines rows from joined tables using the ON condition.
- Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q73. Flag customers with an increase in orders every month this year
Problem: Flag customers with an increase in orders every month this 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.
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
- What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH monthly_counts AS (
SELECT customer_id, DATE_TRUNC('month',
order_date) AS month, COUNT(*) AS cnt
FROM orders
WHERE EXTRACT(YEAR FROM order_date) =
EXTRACT(YEAR FROM CURRENT_DATE)
GROUP BY customer_id, month
),
increase_check AS (
SELECT customer_id,
LAG(cnt) OVER (PARTITION BY customer_id
ORDER BY month) AS prev_cnt,
cnt
FROM monthly_counts
)
SELECT DISTINCT customer_id
FROM increase_check
WHERE cnt > prev_cnt AND prev_cnt IS NOT NULL;
Step-by-step how the database executes this:
- WITH (CTE): Materializes
monthly_counts, increase_checkas temporary result set(s). - FROM: Reads table
orders. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- GROUP BY: Collapses rows into groups. One output row per unique group value.
- Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- DISTINCT: Removes duplicate rows by comparing all selected columns.
- ORDER BY: Sorts the final result set.
Optimization: Add an index on the GROUP BY column(s). For filtered aggregation, a composite index on (WHERE columns, GROUP BY columns) helps.
---
Q74. Retrieve employees with salary in top 5% company-wide
Problem: Retrieve employees with salary in top 5% company-wide.
How to think before writing:
- Which tables do I need? How are they related? What column connects them?
- Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
- Do I need to rank rows, handling ties? RANK skips after ties, DENSE_RANK does not.
- How should the result be sorted? ASC (ascending) or DESC (descending)?
Pseudo query (plain English first):
STEP 1: Create a temporary result (CTE) that ...
STEP 2: Use that result to ...
SQL Solution:
WITH salary_ranks AS (
SELECT salary, PERCENT_RANK() OVER
(ORDER BY salary DESC) AS pr
FROM employees
)
SELECT e.*
FROM employees e
JOIN salary_ranks sr ON e.salary = sr.salary
WHERE sr.pr <= 0.05;
Step-by-step how the database executes this:
- WITH (CTE): Materializes
salary_ranksas temporary result set(s). - FROM: Reads table
employees. - INNER JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Key Takeaway
This section covered 74 interview questions on Window Functions: ROW_NUMBER, RANK, DENSE_RANK, LEAD, LAG. 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.
Window functions compute a value for each row using a window of related rows, without collapsing rows like GROUP BY. Syntax: FUNCTION() OVER (PARTITION BY col ORDER BY col). ROW_NUMBER gives unique sequential numbers. RANK gives same rank to ties and skips next. DENSE_RANK does not skip. LEAD/LAG access the next/previous row value. SUM/AVG/COUNT with OVER compute running totals and moving averages.
Practice each query on the shared sample table. If you can solve all 74 without looking at the answer, you have mastered this concept.
---