Back to SQL Practice: 331 Real Interview Queries
Q6Question 6 of 9

Window Functions: ROW_NUMBER, RANK, DENSE_RANK, LEAD, LAG

Answer: Window functions (ROW_NUMBER, RANK, DENSE_RANK, LEAD, LAG, SUM OVER) compute per-row values across a partition without collapsing rows like GROUP BY.

Key Concepts
  • Window functions compute a value per row without collapsing rows. Unlike GROUP BY, you keep the original rows plus the computed value.
  • Syntax: FUNCTION() OVER (PARTITION BY col ORDER BY col). PARTITION BY defines groups. ORDER BY defines row order within each group.
  • ROW_NUMBER() gives a unique sequential number per partition. No duplicates. Used for pagination and deduplication.
  • RANK() gives the same rank to ties and skips the next rank. DENSE_RANK() gives the same rank to ties but does NOT skip.
  • LEAD(col, N) accesses the value N rows ahead. LAG(col, N) accesses N rows behind. Default offset is 1. Useful for comparing consecutive rows.
  • SUM/AVG/COUNT with OVER() compute running totals and moving averages. SUM(salary) OVER(ORDER BY id) gives a cumulative sum.
  • Window functions execute after WHERE, GROUP BY, and HAVING. You cannot filter by a window function result in WHERE. Use a CTE or subquery wrapper.

Definition

Window functions (ROW_NUMBER, RANK, DENSE_RANK, LEAD, LAG, SUM OVER) compute per-row values across a partition without collapsing rows like GROUP BY.
  • 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_idemployee_iddepartment_idamountsale_dateregionproduct
S011101500002026-01-05NorthHome Loan
S022101300002026-01-10NorthCar Loan
S033102800002026-01-15SouthHome Loan
S041101450002026-02-01NorthHome Loan
S054102250002026-02-10SouthPersonal
S065103600002026-02-15EastHome Loan
S073102700002026-03-01SouthCar Loan
S086103350002026-03-10EastPersonal

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:

  1. Do I need a calculation per row without collapsing groups? That means a window function with OVER().
  2. What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
  3. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

TEXT
GET columns FROM table
SORT the result

SQL Solution:

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

  1. FROM: Reads table employees.
  2. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.
  4. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
  4. Do I need a calculation per row without collapsing groups? That means a window function with OVER().
  5. 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):

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

SQL Solution:

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

  1. WITH (CTE): Materializes login_dates as temporary result set(s).
  2. FROM: Reads table user_logins.
  3. GROUP BY: Collapses rows into groups. One output row per unique group value.
  4. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  5. SELECT: Projects the final columns and evaluates expressions/aliases.
  6. 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:

  1. Do I need a calculation per row without collapsing groups? That means a window function with OVER().
  2. What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
  3. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

TEXT
GET columns FROM table
SORT the result

SQL Solution:

SQL
SELECT name, salary, 
       CUME_DIST() OVER (ORDER BY salary) AS 
salary_cdf 
FROM employees;

Step-by-step how the database executes this:

  1. FROM: Reads table employees.
  2. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.
  4. 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:

  1. Do I need a calculation per row without collapsing groups? That means a window function with OVER().
  2. What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
  3. Do I need to rank rows, handling ties? RANK skips after ties, DENSE_RANK does not.
  4. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

TEXT
GET columns FROM table
SORT the result

SQL Solution:

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

  1. FROM: Reads table employees.
  2. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.
  4. 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:

  1. Do I need a calculation per row without collapsing groups? That means a window function with OVER().
  2. What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
  3. Do I need to compare each row with the next or previous row? Use LEAD() or LAG().
  4. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

TEXT
GET columns FROM table
SORT the result

SQL Solution:

SQL
SELECT name, salary, 
       salary - LAG(salary) OVER (ORDER BY id) AS 
salary_diff 
FROM employees;

Step-by-step how the database executes this:

  1. FROM: Reads table employees.
  2. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.
  4. 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:

  1. Can I solve the inner part first as a subquery, then use the result in the outer query?
  2. Do I need a calculation per row without collapsing groups? That means a window function with OVER().
  3. What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
  4. Do I need to assign a unique number to each row within a group? Use ROW_NUMBER().
  5. What condition filters the rows I need? Write the WHERE clause for that condition.

Pseudo query (plain English first):

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

SQL Solution:

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

  1. FROM: Reads table sales.
  2. WHERE: Filters individual rows. Rows failing the condition are discarded.
  3. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  4. SELECT: Projects the final columns and evaluates expressions/aliases.
  5. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Can I solve the inner part first as a subquery, then use the result in the outer query?
  4. Do I need a calculation per row without collapsing groups? That means a window function with OVER().
  5. 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):

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

SQL Solution:

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

  1. FROM: Reads table sales.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  4. SELECT: Projects the final columns and evaluates expressions/aliases.
  5. ORDER BY: Sorts the final result set.

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

---

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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Can I solve the inner part first as a subquery, then use the result in the outer query?
  4. Do I need a calculation per row without collapsing groups? That means a window function with OVER().
  5. 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):

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

SQL Solution:

SQL
SELECT product_id, 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:

  1. FROM: Reads table sales.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  4. SELECT: Projects the final columns and evaluates expressions/aliases.
  5. ORDER BY: Sorts the final result set.

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

---

Q9. Retrieve the last 5 orders for each customer

Problem: Retrieve the last 5 orders for each customer.

How to think before writing:

  1. Can I solve the inner part first as a subquery, then use the result in the outer query?
  2. Do I need to assign a unique number to each row within a group? Use ROW_NUMBER().
  3. What condition filters the rows I need? Write the WHERE clause for that condition.
  4. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

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

SQL Solution:

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

  1. FROM: Reads table orders.
  2. WHERE: Filters individual rows. Rows failing the condition are discarded.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.
  4. 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:

  1. Do I need a calculation per row without collapsing groups? That means a window function with OVER().
  2. What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
  3. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

TEXT
GET columns FROM table
SORT the result

SQL Solution:

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

  1. FROM: Reads table sales.
  2. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.
  4. 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:

  1. Do I need a calculation per row without collapsing groups? That means a window function with OVER().
  2. What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
  3. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

TEXT
GET columns FROM table
SORT the result

SQL Solution:

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

  1. FROM: Reads table employees.
  2. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.
  4. 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:

  1. Do I need a calculation per row without collapsing groups? That means a window function with OVER().
  2. What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
  3. Do I need to compare each row with the next or previous row? Use LEAD() or LAG().
  4. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

TEXT
GET columns FROM table
SORT the result

SQL Solution:

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

  1. FROM: Reads table sales.
  2. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.
  4. 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:

  1. Can I solve the inner part first as a subquery, then use the result in the outer query?
  2. Do I need a calculation per row without collapsing groups? That means a window function with OVER().
  3. What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
  4. Do I need to assign a unique number to each row within a group? Use ROW_NUMBER().
  5. What condition filters the rows I need? Write the WHERE clause for that condition.

Pseudo query (plain English first):

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

SQL Solution:

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

  1. FROM: Reads table orders.
  2. WHERE: Filters individual rows. Rows failing the condition are discarded.
  3. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  4. SELECT: Projects the final columns and evaluates expressions/aliases.
  5. 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:

  1. Can I solve the inner part first as a subquery, then use the result in the outer query?
  2. Do I need a calculation per row without collapsing groups? That means a window function with OVER().
  3. What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
  4. What condition filters the rows I need? Write the WHERE clause for that condition.
  5. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

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

SQL Solution:

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

  1. FROM: Reads table employees.
  2. WHERE: Filters individual rows. Rows failing the condition are discarded.
  3. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  4. SELECT: Projects the final columns and evaluates expressions/aliases.
  5. 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:

  1. Do I need a calculation per row without collapsing groups? That means a window function with OVER().
  2. What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
  3. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

TEXT
GET columns FROM table
SORT the result

SQL Solution:

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

  1. FROM: Reads table sales.
  2. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.
  4. 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:

  1. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
  2. Do I need a calculation per row without collapsing groups? That means a window function with OVER().
  3. What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
  4. Do I need to compare each row with the next or previous row? Use LEAD() or LAG().
  5. What condition filters the rows I need? Write the WHERE clause for that condition.

Pseudo query (plain English first):

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

SQL Solution:

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

  1. WITH (CTE): Materializes dates as temporary result set(s).
  2. FROM: Reads table sales.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  5. SELECT: Projects the final columns and evaluates expressions/aliases.
  6. 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:

  1. Do I need a calculation per row without collapsing groups? That means a window function with OVER().
  2. What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
  3. Do I need to rank rows, handling ties? RANK skips after ties, DENSE_RANK does not.
  4. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

TEXT
GET columns FROM table
SORT the result

SQL Solution:

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

  1. FROM: Reads table employees.
  2. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.
  4. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
  4. Do I need a calculation per row without collapsing groups? That means a window function with OVER().
  5. 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):

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

SQL Solution:

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

  1. WITH (CTE): Materializes flagged_sales, groups as temporary result set(s).
  2. FROM: Reads table sales.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. GROUP BY: Collapses rows into groups. One output row per unique group value.
  5. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  6. SELECT: Projects the final columns and evaluates expressions/aliases.
  7. 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:

  1. Do I need a calculation per row without collapsing groups? That means a window function with OVER().
  2. What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
  3. How should the result be sorted? ASC (ascending) or DESC (descending)?
  4. Are there duplicates I need to remove? Use DISTINCT.

Pseudo query (plain English first):

TEXT
GET columns FROM table
SORT the result

SQL Solution:

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

  1. FROM: Reads table employees.
  2. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.
  4. DISTINCT: Removes duplicate rows by comparing all selected columns.
  5. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
  4. Do I need a calculation per row without collapsing groups? That means a window function with OVER().
  5. 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):

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

SQL Solution:

SQL
WITH monthly_sales AS ( 
  SELECT DATE_TRUNC('month', sale_date) AS 
month, SUM(amount) AS total_sales 
  FROM sales 
  GROUP BY month 
) 
SELECT 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:

  1. WITH (CTE): Materializes monthly_sales as temporary result set(s).
  2. FROM: Reads table sales.
  3. GROUP BY: Collapses rows into groups. One output row per unique group value.
  4. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  5. SELECT: Projects the final columns and evaluates expressions/aliases.
  6. 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:

  1. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
  2. Do I need a calculation per row without collapsing groups? That means a window function with OVER().
  3. What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
  4. Do I need to rank rows, handling ties? RANK skips after ties, DENSE_RANK does not.
  5. What condition filters the rows I need? Write the WHERE clause for that condition.

Pseudo query (plain English first):

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

SQL Solution:

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

  1. WITH (CTE): Materializes tenure as temporary result set(s).
  2. FROM: Reads table employees.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  5. SELECT: Projects the final columns and evaluates expressions/aliases.
  6. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Do I need a calculation per row without collapsing groups? That means a window function with OVER().
  4. What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
  5. Do I need to rank rows, handling ties? RANK skips after ties, DENSE_RANK does not.

Pseudo query (plain English first):

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

SQL Solution:

SQL
SELECT customer_id, SUM(amount) AS 
total_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:

  1. FROM: Reads table sales.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  4. SELECT: Projects the final columns and evaluates expressions/aliases.
  5. ORDER BY: Sorts the final result set.

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

---

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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
  4. Do I need a calculation per row without collapsing groups? That means a window function with OVER().
  5. 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):

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

SQL Solution:

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

  1. WITH (CTE): Materializes monthly_product_sales, ranked_sales as temporary result set(s).
  2. FROM: Reads table sales.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. GROUP BY: Collapses rows into groups. One output row per unique group value.
  5. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  6. SELECT: Projects the final columns and evaluates expressions/aliases.
  7. 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:

  1. Can I solve the inner part first as a subquery, then use the result in the outer query?
  2. Do I need to assign a unique number to each row within a group? Use ROW_NUMBER().
  3. What condition filters the rows I need? Write the WHERE clause for that condition.
  4. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

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

SQL Solution:

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

  1. FROM: Reads table employees.
  2. WHERE: Filters individual rows. Rows failing the condition are discarded.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.
  4. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
  4. Do I need a calculation per row without collapsing groups? That means a window function with OVER().
  5. 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):

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

SQL Solution:

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

  1. WITH (CTE): Materializes attendance_groups as temporary result set(s).
  2. FROM: Reads table attendance.
  3. GROUP BY: Collapses rows into groups. One output row per unique group value.
  4. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  5. SELECT: Projects the final columns and evaluates expressions/aliases.
  6. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
  4. Do I need a calculation per row without collapsing groups? That means a window function with OVER().
  5. 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):

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

SQL Solution:

SQL
WITH monthly_sales AS ( 
  SELECT product_id, DATE_TRUNC('month', 
sale_date) AS month, SUM(amount) AS total_sales 
  FROM sales 
  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:

  1. WITH (CTE): Materializes monthly_sales as temporary result set(s).
  2. FROM: Reads table sales.
  3. GROUP BY: Collapses rows into groups. One output row per unique group value.
  4. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  5. SELECT: Projects the final columns and evaluates expressions/aliases.
  6. 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:

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

Pseudo query (plain English first):

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

SQL Solution:

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

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

  1. Do I need a calculation per row without collapsing groups? That means a window function with OVER().
  2. What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
  3. Do I need to assign a unique number to each row within a group? Use ROW_NUMBER().
  4. Do I need to rank rows, handling ties? RANK skips after ties, DENSE_RANK does not.
  5. What condition filters the rows I need? Write the WHERE clause for that condition.

Pseudo query (plain English first):

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

SQL Solution:

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

  1. FROM: Reads table employees.
  2. WHERE: Filters individual rows. Rows failing the condition are discarded.
  3. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  4. SELECT: Projects the final columns and evaluates expressions/aliases.
  5. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
  4. Do I need a calculation per row without collapsing groups? That means a window function with OVER().
  5. 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):

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

SQL Solution:

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

  1. WITH (CTE): Materializes category_sales as temporary result set(s).
  2. FROM: Reads table sales.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. GROUP BY: Collapses rows into groups. One output row per unique group value.
  5. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  6. SELECT: Projects the final columns and evaluates expressions/aliases.
  7. 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:

  1. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
  2. Do I need to assign a unique number to each row within a group? Use ROW_NUMBER().
  3. What condition filters the rows I need? Write the WHERE clause for that condition.
  4. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

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

SQL Solution:

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

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

  1. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
  2. Do I need a calculation per row without collapsing groups? That means a window function with OVER().
  3. What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
  4. Do I need to assign a unique number to each row within a group? Use ROW_NUMBER().
  5. Do I need to rank rows, handling ties? RANK skips after ties, DENSE_RANK does not.

Pseudo query (plain English first):

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

SQL Solution:

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

  1. WITH (CTE): Materializes ranked_employees as temporary result set(s).
  2. FROM: Reads table employees.
  3. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  4. SELECT: Projects the final columns and evaluates expressions/aliases.
  5. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
  4. Do I need a calculation per row without collapsing groups? That means a window function with OVER().
  5. 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):

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

SQL Solution:

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

  1. WITH (CTE): Materializes daily_sales as temporary result set(s).
  2. FROM: Reads table sales.
  3. GROUP BY: Collapses rows into groups. One output row per unique group value.
  4. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  5. SELECT: Projects the final columns and evaluates expressions/aliases.
  6. 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:

  1. Can I solve the inner part first as a subquery, then use the result in the outer query?
  2. Do I need to assign a unique number to each row within a group? Use ROW_NUMBER().
  3. What condition filters the rows I need? Write the WHERE clause for that condition.
  4. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

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

SQL Solution:

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

  1. FROM: Reads table orders.
  2. WHERE: Filters individual rows. Rows failing the condition are discarded.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.
  4. 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:

  1. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
  2. Do I need a calculation per row without collapsing groups? That means a window function with OVER().
  3. What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
  4. Do I need to compare each row with the next or previous row? Use LEAD() or LAG().
  5. What condition filters the rows I need? Write the WHERE clause for that condition.

Pseudo query (plain English first):

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

SQL Solution:

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

  1. WITH (CTE): Materializes price_changes as temporary result set(s).
  2. FROM: Reads table product_prices.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  5. SELECT: Projects the final columns and evaluates expressions/aliases.
  6. DISTINCT: Removes duplicate rows by comparing all selected columns.
  7. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
  4. Do I need a calculation per row without collapsing groups? That means a window function with OVER().
  5. 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):

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

SQL Solution:

SQL
WITH avg_salaries AS ( 
  SELECT department_id, AVG(salary) AS avg_salary 
  FROM employees 
  GROUP BY department_id 
), 
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:

  1. WITH (CTE): Materializes avg_salaries, ranked_salaries as temporary result set(s).
  2. FROM: Reads table employees.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. GROUP BY: Collapses rows into groups. One output row per unique group value.
  5. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  6. SELECT: Projects the final columns and evaluates expressions/aliases.
  7. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
  4. Do I need a calculation per row without collapsing groups? That means a window function with OVER().
  5. 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):

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

SQL Solution:

SQL
WITH monthly_sales AS ( 
  SELECT product_id, DATE_TRUNC('month', 
sale_date) AS month, SUM(amount) AS total_sales 
  FROM sales 
  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:

  1. WITH (CTE): Materializes monthly_sales as temporary result set(s).
  2. FROM: Reads table sales.
  3. GROUP BY: Collapses rows into groups. One output row per unique group value.
  4. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  5. SELECT: Projects the final columns and evaluates expressions/aliases.
  6. 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:

  1. Do I need a calculation per row without collapsing groups? That means a window function with OVER().
  2. What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
  3. What condition filters the rows I need? Write the WHERE clause for that condition.

Pseudo query (plain English first):

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

SQL Solution:

SQL
SELECT *, 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:

  1. FROM: Reads table table_name.
  2. WHERE: Filters individual rows. Rows failing the condition are discarded.
  3. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  4. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
  4. Do I need a calculation per row without collapsing groups? That means a window function with OVER().
  5. 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):

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

SQL Solution:

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

  1. WITH (CTE): Materializes yearly_sales, ranked_sales as temporary result set(s).
  2. FROM: Reads table sale_date.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. GROUP BY: Collapses rows into groups. One output row per unique group value.
  5. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  6. SELECT: Projects the final columns and evaluates expressions/aliases.
  7. 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:

  1. Do I need a calculation per row without collapsing groups? That means a window function with OVER().
  2. What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
  3. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

TEXT
GET columns FROM table
SORT the result

SQL Solution:

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

  1. FROM: Reads table sales.
  2. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.
  4. 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:

  1. Can I solve the inner part first as a subquery, then use the result in the outer query?
  2. Do I need to assign a unique number to each row within a group? Use ROW_NUMBER().
  3. What condition filters the rows I need? Write the WHERE clause for that condition.
  4. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

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

SQL Solution:

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

  1. FROM: Reads table orders.
  2. WHERE: Filters individual rows. Rows failing the condition are discarded.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.
  4. 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:

  1. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
  2. Do I need a calculation per row without collapsing groups? That means a window function with OVER().
  3. What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
  4. Do I need to assign a unique number to each row within a group? Use ROW_NUMBER().
  5. What condition filters the rows I need? Write the WHERE clause for that condition.

Pseudo query (plain English first):

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

SQL Solution:

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

  1. WITH (CTE): Materializes numbered as temporary result set(s).
  2. FROM: Reads table employees.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  5. SELECT: Projects the final columns and evaluates expressions/aliases.
  6. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
  4. Do I need a calculation per row without collapsing groups? That means a window function with OVER().
  5. 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):

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

SQL Solution:

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

  1. WITH (CTE): Materializes ordered_orders, gaps as temporary result set(s).
  2. FROM: Reads table orders.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. GROUP BY: Collapses rows into groups. One output row per unique group value.
  5. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  6. SELECT: Projects the final columns and evaluates expressions/aliases.
  7. ORDER BY: Sorts the final result set.
  8. LIMIT/TOP: Returns only the first N rows from the sorted result.

Optimization: Add an index on the 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):

TEXT
GET columns FROM table
SORT the result

SQL Solution:

SQL
SELECT PERCENTILE_CONT(0.5) WITHIN 
GROUP (ORDER BY salary) AS median_salary 
FROM employees;

Step-by-step how the database executes this:

  1. FROM: Reads table employees.
  2. SELECT: Projects the final columns and evaluates expressions/aliases.
  3. 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:

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

Pseudo query (plain English first):

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

SQL Solution:

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

  1. WITH (CTE): Materializes attendance as temporary result set(s).
  2. FROM: Reads table work_log.
  3. GROUP BY: Collapses rows into groups. One output row per unique group value.
  4. HAVING: Filters groups. Only groups passing the condition remain.
  5. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  6. SELECT: Projects the final columns and evaluates expressions/aliases.
  7. 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:

  1. Do I need a calculation per row without collapsing groups? That means a window function with OVER().
  2. What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
  3. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

TEXT
GET columns FROM table
SORT the result

SQL Solution:

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

  1. FROM: Reads table orders.
  2. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.
  4. 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:

  1. Do I need a calculation per row without collapsing groups? That means a window function with OVER().
  2. What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
  3. Do I need to compare each row with the next or previous row? Use LEAD() or LAG().
  4. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

TEXT
GET columns FROM table
SORT the result

SQL Solution:

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

  1. FROM: Reads table orders.
  2. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.
  4. 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:

  1. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
  2. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

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

SQL Solution:

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

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

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Which tables do I need? How are they related? What column connects them?
  4. Can I solve the inner part first as a subquery, then use the result in the outer query?
  5. Do I need a calculation per row without collapsing groups? That means a window function with OVER().

Pseudo query (plain English first):

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

SQL Solution:

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

  1. FROM: Reads table employees.
  2. INNER JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. GROUP BY: Collapses rows into groups. One output row per unique group value.
  5. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  6. SELECT: Projects the final columns and evaluates expressions/aliases.
  7. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
  4. Do I need a calculation per row without collapsing groups? That means a window function with OVER().
  5. 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):

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

SQL Solution:

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

  1. WITH (CTE): Materializes ordered_orders, gaps as temporary result set(s).
  2. FROM: Reads table orders.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. GROUP BY: Collapses rows into groups. One output row per unique group value.
  5. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  6. SELECT: Projects the final columns and evaluates expressions/aliases.
  7. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Which tables do I need? How are they related? What column connects them?
  4. Can I solve the inner part first as a subquery, then use the result in the outer query?
  5. Do I need a calculation per row without collapsing groups? That means a window function with OVER().

Pseudo query (plain English first):

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

SQL Solution:

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

  1. FROM: Reads table sales.
  2. INNER JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. GROUP BY: Collapses rows into groups. One output row per unique group value.
  5. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  6. SELECT: Projects the final columns and evaluates expressions/aliases.
  7. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
  4. Do I need a calculation per row without collapsing groups? That means a window function with OVER().
  5. 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):

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

SQL Solution:

SQL
WITH monthly_sales AS ( 
  SELECT product_id, DATE_TRUNC('month', 
sale_date) AS month, SUM(amount) AS total_sales 
  FROM sales 
  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:

  1. WITH (CTE): Materializes monthly_sales, sales_comparison as temporary result set(s).
  2. FROM: Reads table sales.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. GROUP BY: Collapses rows into groups. One output row per unique group value.
  5. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  6. SELECT: Projects the final columns and evaluates expressions/aliases.
  7. 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:

  1. Do I need a calculation per row without collapsing groups? That means a window function with OVER().
  2. What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
  3. Do I need to rank rows, handling ties? RANK skips after ties, DENSE_RANK does not.
  4. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

TEXT
GET columns FROM table
SORT the result

SQL Solution:

SQL
SELECT *, RANK() OVER (PARTITION BY 
department_id ORDER BY salary DESC) AS 
salary_rank 
FROM employees;

Step-by-step how the database executes this:

  1. FROM: Reads table employees.
  2. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.
  4. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
  4. Do I need a calculation per row without collapsing groups? That means a window function with OVER().
  5. 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):

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

SQL Solution:

SQL
WITH monthly_orders AS ( 
  SELECT customer_id, DATE_TRUNC('month', 
order_date) AS month, COUNT(*) AS 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:

  1. WITH (CTE): Materializes monthly_orders, orders_comparison as temporary result set(s).
  2. FROM: Reads table orders.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. GROUP BY: Collapses rows into groups. One output row per unique group value.
  5. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  6. SELECT: Projects the final columns and evaluates expressions/aliases.
  7. 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:

  1. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
  2. Do I need a calculation per row without collapsing groups? That means a window function with OVER().
  3. What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
  4. What condition filters the rows I need? Write the WHERE clause for that condition.
  5. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

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

SQL Solution:

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

  1. WITH (CTE): Materializes ordered_orders as temporary result set(s).
  2. FROM: Reads table orders.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  5. SELECT: Projects the final columns and evaluates expressions/aliases.
  6. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
  4. Do I need a calculation per row without collapsing groups? That means a window function with OVER().
  5. 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):

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

SQL Solution:

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

  1. WITH (CTE): Materializes workdays, consecutive_days as temporary result set(s).
  2. FROM: Reads table attendance.
  3. GROUP BY: Collapses rows into groups. One output row per unique group value.
  4. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  5. SELECT: Projects the final columns and evaluates expressions/aliases.
  6. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Which tables do I need? How are they related? What column connects them?
  4. Can I solve the inner part first as a subquery, then use the result in the outer query?
  5. Do I need a calculation per row without collapsing groups? That means a window function with OVER().

Pseudo query (plain English first):

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

SQL Solution:

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

  1. FROM: Reads table hire_date.
  2. INNER JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. GROUP BY: Collapses rows into groups. One output row per unique group value.
  5. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  6. SELECT: Projects the final columns and evaluates expressions/aliases.
  7. 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:

  1. Do I need a calculation per row without collapsing groups? That means a window function with OVER().
  2. What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
  3. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

TEXT
GET columns FROM table
SORT the result

SQL Solution:

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

  1. FROM: Reads table sales.
  2. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.
  4. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Which tables do I need? How are they related? What column connects them?
  4. Can I solve the inner part first as a subquery, then use the result in the outer query?
  5. Do I need a calculation per row without collapsing groups? That means a window function with OVER().

Pseudo query (plain English first):

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

SQL Solution:

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

  1. FROM: Reads table project_assignments.
  2. INNER JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. GROUP BY: Collapses rows into groups. One output row per unique group value.
  5. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  6. SELECT: Projects the final columns and evaluates expressions/aliases.
  7. DISTINCT: Removes duplicate rows by comparing all selected columns.
  8. ORDER BY: Sorts the final result set.

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

---

Q59. Find the 3 most recent orders per customer

Problem: Find the 3 most recent orders per customer.

How to think before writing:

  1. Can I solve the inner part first as a subquery, then use the result in the outer query?
  2. Do I need a calculation per row without collapsing groups? That means a window function with OVER().
  3. What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
  4. Do I need to assign a unique number to each row within a group? Use ROW_NUMBER().
  5. What condition filters the rows I need? Write the WHERE clause for that condition.

Pseudo query (plain English first):

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

SQL Solution:

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

  1. FROM: Reads table orders.
  2. WHERE: Filters individual rows. Rows failing the condition are discarded.
  3. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  4. SELECT: Projects the final columns and evaluates expressions/aliases.
  5. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

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

SQL Solution:

SQL
SELECT department_id, 
       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:

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

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:

  1. Can I solve the inner part first as a subquery, then use the result in the outer query?
  2. Do I need a calculation per row without collapsing groups? That means a window function with OVER().
  3. What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
  4. Do I need to assign a unique number to each row within a group? Use ROW_NUMBER().
  5. What condition filters the rows I need? Write the WHERE clause for that condition.

Pseudo query (plain English first):

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

SQL Solution:

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

  1. FROM: Reads table products.
  2. WHERE: Filters individual rows. Rows failing the condition are discarded.
  3. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  4. SELECT: Projects the final columns and evaluates expressions/aliases.
  5. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Which tables do I need? How are they related? What column connects them?
  4. Can I solve the inner part first as a subquery, then use the result in the outer query?
  5. Do I need a calculation per row without collapsing groups? That means a window function with OVER().

Pseudo query (plain English first):

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

SQL Solution:

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

  1. FROM: Reads table customers.
  2. INNER JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. GROUP BY: Collapses rows into groups. One output row per unique group value.
  5. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  6. SELECT: Projects the final columns and evaluates expressions/aliases.
  7. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
  4. Do I need a calculation per row without collapsing groups? That means a window function with OVER().
  5. 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):

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

SQL Solution:

SQL
WITH monthly_orders AS ( 
  SELECT customer_id, DATE_TRUNC('month', 
order_date) AS month, COUNT(*) AS 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:

  1. WITH (CTE): Materializes monthly_orders, orders_with_lag as temporary result set(s).
  2. FROM: Reads table orders.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. GROUP BY: Collapses rows into groups. One output row per unique group value.
  5. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  6. SELECT: Projects the final columns and evaluates expressions/aliases.
  7. DISTINCT: Removes duplicate rows by comparing all selected columns.
  8. ORDER BY: Sorts the final result set.

Optimization: Add an index on the 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
  4. Do I need a calculation per row without collapsing groups? That means a window function with OVER().
  5. 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):

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

SQL Solution:

SQL
WITH monthly_sales AS ( 
  SELECT product_id, DATE_TRUNC('month', 
sale_date) AS month, SUM(amount) AS total_sales 
  FROM sales 
  WHERE sale_date >= CURRENT_DATE - 
INTERVAL '3 months' 
  GROUP BY product_id, month 
), 
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:

  1. WITH (CTE): Materializes monthly_sales, sales_ranked as temporary result set(s).
  2. FROM: Reads table sales.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. GROUP BY: Collapses rows into groups. One output row per unique group value.
  5. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  6. SELECT: Projects the final columns and evaluates expressions/aliases.
  7. DISTINCT: Removes duplicate rows by comparing all selected columns.
  8. ORDER BY: Sorts the final result set.

Optimization: Add an index on the 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:

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

Pseudo query (plain English first):

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

SQL Solution:

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

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

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
  4. Do I need a calculation per row without collapsing groups? That means a window function with OVER().
  5. 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):

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

SQL Solution:

SQL
WITH monthly_orders AS ( 
  SELECT customer_id, DATE_TRUNC('month', 
order_date) AS month, COUNT(*) AS 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:

  1. WITH (CTE): Materializes monthly_orders, orders_with_lag as temporary result set(s).
  2. FROM: Reads table orders.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. GROUP BY: Collapses rows into groups. One output row per unique group value.
  5. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  6. SELECT: Projects the final columns and evaluates expressions/aliases.
  7. DISTINCT: Removes duplicate rows by comparing all selected columns.
  8. ORDER BY: Sorts the final result set.

Optimization: Add an index on the 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:

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

Pseudo query (plain English first):

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

SQL Solution:

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

  1. WITH (CTE): Materializes salary_diff as temporary result set(s).
  2. FROM: Reads table salaries.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. GROUP BY: Collapses rows into groups. One output row per unique group value.
  5. HAVING: Filters groups. Only groups passing the condition remain.
  6. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  7. SELECT: Projects the final columns and evaluates expressions/aliases.
  8. DISTINCT: Removes duplicate rows by comparing all selected columns.
  9. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
  4. Do I need a calculation per row without collapsing groups? That means a window function with OVER().
  5. 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):

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

SQL Solution:

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

  1. WITH (CTE): Materializes daily_sales, ranked_sales as temporary result set(s).
  2. FROM: Reads table orders.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. GROUP BY: Collapses rows into groups. One output row per unique group value.
  5. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  6. SELECT: Projects the final columns and evaluates expressions/aliases.
  7. 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
  4. Do I need a calculation per row without collapsing groups? That means a window function with OVER().
  5. 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):

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

SQL Solution:

SQL
WITH monthly_sales AS ( 
  SELECT product_id, DATE_TRUNC('month', 
sale_date) AS month, SUM(amount) AS total_sales 
  FROM sales 
  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:

  1. WITH (CTE): Materializes monthly_sales, sales_diff as temporary result set(s).
  2. FROM: Reads table sales.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. GROUP BY: Collapses rows into groups. One output row per unique group value.
  5. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  6. SELECT: Projects the final columns and evaluates expressions/aliases.
  7. ORDER BY: Sorts the final result set.
  8. LIMIT/TOP: Returns only the first N rows from the sorted result.

Optimization: Add an index on the 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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Do I need a calculation per row without collapsing groups? That means a window function with OVER().
  4. What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
  5. Do I need to assign a unique number to each row within a group? Use ROW_NUMBER().

Pseudo query (plain English first):

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

SQL Solution:

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

  1. FROM: Reads table product_reviews.
  2. GROUP BY: Collapses rows into groups. One output row per unique group value.
  3. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  4. SELECT: Projects the final columns and evaluates expressions/aliases.
  5. ORDER BY: Sorts the final result set.

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

---

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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
  4. Do I need a calculation per row without collapsing groups? That means a window function with OVER().
  5. 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):

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

SQL Solution:

SQL
WITH monthly_sales AS ( 
  SELECT product_id, DATE_TRUNC('month', 
sale_date) AS month, SUM(amount) AS total_sales 
  FROM sales 
  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:

  1. WITH (CTE): Materializes monthly_sales, ranked_sales as temporary result set(s).
  2. FROM: Reads table sales.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. GROUP BY: Collapses rows into groups. One output row per unique group value.
  5. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  6. SELECT: Projects the final columns and evaluates expressions/aliases.
  7. 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:

  1. Which tables do I need? How are they related? What column connects them?
  2. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
  3. Do I need a calculation per row without collapsing groups? That means a window function with OVER().
  4. What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
  5. Do I need to assign a unique number to each row within a group? Use ROW_NUMBER().

Pseudo query (plain English first):

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

SQL Solution:

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

  1. WITH (CTE): Materializes customer_order_counts as temporary result set(s).
  2. FROM: Reads table customers.
  3. INNER JOIN: Combines rows from joined tables using the ON condition.
  4. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  5. SELECT: Projects the final columns and evaluates expressions/aliases.
  6. ORDER BY: Sorts the final result set.

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

---

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:

  1. What do I need to group by? Identify the column(s) that define each group.
  2. What aggregate do I need? COUNT for counting, SUM for totals, AVG for averages, MAX/MIN for extremes.
  3. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
  4. Do I need a calculation per row without collapsing groups? That means a window function with OVER().
  5. 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):

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

SQL Solution:

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

  1. WITH (CTE): Materializes monthly_counts, increase_check as temporary result set(s).
  2. FROM: Reads table orders.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. GROUP BY: Collapses rows into groups. One output row per unique group value.
  5. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  6. SELECT: Projects the final columns and evaluates expressions/aliases.
  7. DISTINCT: Removes duplicate rows by comparing all selected columns.
  8. ORDER BY: Sorts the final result set.

Optimization: Add an index on the 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:

  1. Which tables do I need? How are they related? What column connects them?
  2. Break the problem into steps. Each step becomes a CTE (WITH clause). Name each step clearly.
  3. Do I need to rank rows, handling ties? RANK skips after ties, DENSE_RANK does not.
  4. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

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

SQL Solution:

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

  1. WITH (CTE): Materializes salary_ranks as temporary result set(s).
  2. FROM: Reads table employees.
  3. INNER JOIN: Combines rows from joined tables using the ON condition.
  4. WHERE: Filters individual rows. Rows failing the condition are discarded.
  5. SELECT: Projects the final columns and evaluates expressions/aliases.
  6. ORDER BY: Sorts the final result set.

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

---

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.

---

Share
Use ← → arrow keys to navigate