SQL Logical Query Execution Order

SQL queries are logically processed in a specific sequence distinct from their written order:

1. FROM & JOIN (Identify tables & merge) → 2. ON (Join conditions) → 3. WHERE (Filter raw rows) → 4. GROUP BY (Aggregate rows) → 5. HAVING (Filter groups) → 6. SELECT (Choose columns) → 7. DISTINCT (Remove duplicates) → 8. ORDER BY (Sort result) → 9. LIMIT / OFFSET (Paginate)

WHERE vs HAVING

This distinction appears frequently in interviews.

• WHERE filters individual rows before grouping.
• HAVING filters groups after aggregation.

Learn to convert problem statements into SQL

When you read a SQL problem, don't start typing immediately. First, translate the question into a plan.

Use this six-step framework for almost every problem.

1. Identify the output. What columns must the result contain? One row per employee, department, customer, or something else?
2. Identify the source tables. Which table contains the information? Do you need to join multiple tables?
3. Identify the row-level conditions. Does the question specify a date, salary threshold, status, or category? Think WHERE.
4. Check whether aggregation is needed. Are you calculating counts, totals, averages, or one result per group? Think GROUP BY.
5. Identify ranking or comparison requirements. Does it ask for the highest, second-highest, top three, latest, or previous row? Consider sorting, subqueries, or window functions.
6. Check edge cases. What happens with ties, NULL values, duplicate rows, empty matches, or multiple records per person?

The problem-solving patterns you must master

These patterns matter more than memorizing hundreds of individual queries.

What is a JOIN in SQL?

A JOIN combines rows from two or more tables based on a specified condition.

Consider two tables.

Employees

Departments

Here, Employees.dept_id refers to Departments.dept_id, where a matching department exists.

Notice that:

• Aman and Ravi belong to IT.
• Priya belongs to HR.
• Neha has no department assigned.
• Karan references department 40, which doesn't exist in Departments.
• Finance has no employees.

These unmatched rows will help us understand the different joins.

Types of SQL joins

1. INNER JOIN

Returns only rows that have matching records in both tables.

SELECT e.name, d.dept_name
FROM Employees e
INNER JOIN Departments d
    ON e.dept_id = d.dept_id;

Use when only matched records are required.

2. LEFT JOIN

Returns every row from the left table and matching rows from the right table. Unmatched right-side columns become NULL.

SELECT e.name, d.dept_name
FROM Employees e
LEFT JOIN Departments d
    ON e.dept_id = d.dept_id;

Use when all employees must appear, even without a matching department.

3. RIGHT JOIN

Returns every row from the right table and matching rows from the left table.

SELECT e.name, d.dept_name
FROM Employees e
RIGHT JOIN Departments d
    ON e.dept_id = d.dept_id;

Use when every department must appear, including departments without employees.

4. FULL OUTER JOIN

Returns matched rows plus unmatched rows from both tables.

SELECT e.name, d.dept_name
FROM Employees e
FULL OUTER JOIN Departments d
    ON e.dept_id = d.dept_id;

Supported by PostgreSQL and SQL Server, but not directly by MySQL.

5. CROSS JOIN

Returns every possible combination of rows from the two tables.

SELECT e.name, d.dept_name
FROM Employees e
CROSS JOIN Departments d;

If the first table has 5 rows and the second has 3, the result has 15 rows.

Understand the results of each join

Let's examine which employees appear when joining the two tables.

INNER JOIN output

SELECT e.name, d.dept_name
FROM Employees e
INNER JOIN Departments d
    ON e.dept_id = d.dept_id;

Neha and Karan are excluded because they have no matching department. Finance is excluded because it has no matching employee.

LEFT JOIN output

SELECT e.name, d.dept_name
FROM Employees e
LEFT JOIN Departments d
    ON e.dept_id = d.dept_id;

All five employees appear.

RIGHT JOIN output

SELECT e.name, d.dept_name
FROM Employees e
RIGHT JOIN Departments d
    ON e.dept_id = d.dept_id;

Every department appears, including Finance.

FULL OUTER JOIN output

The result includes all matches and all unmatched rows from both tables.

The output row order is not guaranteed without ORDER BY.

A. Find employees who don't belong to any existing department

Use LEFT JOIN followed by IS NULL.

SELECT e.*
FROM Employees e
LEFT JOIN Departments d
    ON e.dept_id = d.dept_id
WHERE d.dept_id IS NULL;

Output: Neha and Karan.

This works because unmatched employees receive NULL values for the right table's columns.

B. Find departments with no employees

Reverse the table order.

SELECT d.*
FROM Departments d
LEFT JOIN Employees e
    ON d.dept_id = e.dept_id
WHERE e.emp_id IS NULL;

Output: Finance.

The general pattern:

SELECT A.*
FROM A
LEFT JOIN B
    ON A.key = B.key
WHERE B.key IS NULL;

This is commonly called an anti-join pattern.

Choose a column from B that is guaranteed to be non-NULL for real rows, typically its primary ke

Self JOIN

A self join joins a table with itself. It is useful when rows in the same table have relationships with one another.

Consider an Employee table:

Here, manager_id references another employee's emp_id.

Problem: Find each employee and their manager

SELECT
    e.name AS employee,
    m.name AS manager
FROM Employee e
LEFT JOIN Employee m
    ON e.manager_id = m.emp_id;

We use two aliases:

• e represents the employee.
• m represents the manager.

The LEFT JOIN keeps Aman even though he has no manager.

Common self-join interview questions

• Find employees earning more than their managers.
• Find employees reporting to the same manager.
• Find pairs of users or products meeting a relationship condition.
• Compare records from the same table.

Multiple JOINs

Real interview problems often require three or more tables.

Consider these tables:

• Customers(customer_id, name)
• Orders(order_id, customer_id)
• OrderItems(order_id, product_id, quantity)
• Products(product_id, product_name, price)

Problem: Find the total spending of each customer

SELECT
    c.customer_id,
    c.name,
    SUM(oi.quantity * p.price) AS total_spending
FROM Customers c
JOIN Orders o
    ON c.customer_id = o.customer_id
JOIN OrderItems oi
    ON o.order_id = oi.order_id
JOIN Products p
    ON oi.product_id = p.product_id
GROUP BY
    c.customer_id,
    c.name;

How to approach multiple joins

1. Identify the entity requested in the output: customers.
2. Identify where the necessary information exists.
3. Follow the relationships from customers to orders, order items, and products.
4. Calculate the spending for each order item.
5. Group by customer and sum the item totals.

This query returns customers who have qualifying order records. To include customers who have never ordered, use appropriate LEFT JOINs and handle NULL totals with COALESCE().

JOIN condition: ON vs. WHERE

This is a common interview trap, particularly with outer joins.

Suppose we want all employees, but we only want department information when the department is IT.

Correct for this requirement:

SELECT e.name, d.dept_name
FROM Employees e
LEFT JOIN Departments d
    ON e.dept_id = d.dept_id
   AND d.dept_name = 'IT';

All employees remain in the result. Employees who aren't matched to IT have NULL department information.

Different behavior:

SELECT e.name, d.dept_name
FROM Employees e
LEFT JOIN Departments d
    ON e.dept_id = d.dept_id
WHERE d.dept_name = 'IT';

The WHERE clause removes rows where the department name is NULL, so employees without a matching IT department are removed. The query effectively behaves like an inner join for this condition.

JOIN vs. subquery vs. EXISTS

There are multiple ways to solve the same problem.

Problem: Find customers who have placed at least one order

Using JOIN:

SELECT DISTINCT c.customer_id, c.name
FROM Customers c
JOIN Orders o
    ON c.customer_id = o.customer_id;

DISTINCT avoids repeated customers when they have multiple orders.

Using EXISTS:

SELECT c.customer_id, c.name
FROM Customers c
WHERE EXISTS (
    SELECT 1
    FROM Orders o
    WHERE o.customer_id = c.customer_id
);

EXISTS checks whether at least one matching order exists without multiplying the customer row in the output.

For questions that ask whether a matching record exists, EXISTS is often a clean choice. For questions that require columns from both tables, a join is usually more natural.

Important JOIN edge cases

Duplicate matches can multiply rows

Suppose one customer has three orders. Joining the customer with orders returns three rows for that customer.

This is expected behavior, not a bug.

Before writing an aggregate query, ask: What does one row represent after the join?

If you sum values after joining tables with one-to-many or many-to-many relationships, you may accidentally double-count data.

NULL values don't match with =

ON A.key = B.key

Two NULL values do not satisfy this condition in standard SQL. Use the database's NULL-safe comparison features only when the problem requires NULLs to match.

Prefer explicit JOIN syntax

Use:

SELECT *
FROM A
JOIN B ON A.id = B.id;

Avoid comma-separated table lists with conditions in WHERE for ordinary join problems. Explicit joins make relationships and outer-join behavior easier to understand.

What is a View in SQL?

A View is a virtual table created using the result of a SQL query. It does not normally store the actual result data; instead, it stores the query definition and retrieves data from the underlying tables when accessed.

A view can contain selected columns, filtered rows, joins, aggregate functions, and other SQL operations.

Syntax

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example: Suppose we have an Employees table.

Create a view containing only IT employees:

CREATE VIEW IT_Employees AS
SELECT emp_id, name, salary
FROM Employees
WHERE department = 'IT';

Query the view just like a table:

SELECT *
FROM IT_Employees;

The view exposes only the selected columns and rows.

Why Do We Use Views?

Views are useful for:

1. Security: Restrict access to sensitive columns such as salaries.
2. Simplicity: Hide complex joins and calculations behind a simple query.
3. Reusability: Write a complex query once and reuse it.
4. Logical data independence: Provide a stable interface even when the underlying schema changes, provided the view remains valid.
5. Consistent reporting: Allow multiple users to use the same predefined query logic.

Example: Hide sensitive salary information

CREATE VIEW Employee_Public AS
SELECT emp_id, name, department
FROM Employees;

Users who have access only to this view can query employee details without being given direct access to the salary column.

A. Create an index

Syntax

CREATE INDEX index_name
ON table_name (column_name);

Example:

CREATE INDEX idx_employee_name
ON Employees (name);

Now queries filtering by name may benefit from the index:

SELECT *
FROM Employees
WHERE name = 'Aman';

B. Create a unique index

A unique index prevents duplicate indexed values.

CREATE UNIQUE INDEX idx_employee_email
ON Employees (email);

This is useful when every employee must have a unique email address.

Note that how NULL values are handled by unique indexes depends on the database system. A UNIQUE index is not identical to a PRIMARY KEY.

C. Create a composite index

A composite index contains multiple columns.

CREATE INDEX idx_dept_salary
ON Employees (department, salary);

This index is particularly useful for queries such as:

SELECT *
FROM Employees
WHERE department = 'IT'
  AND salary > 70000;

The column order matters. An index on (department, salary) is generally most useful when a query filters or sorts by department, or filters by department and then salary.

It may not help as effectively for a query that filters only by salary.

D. Drop an index

The syntax varies by database.

MySQL:

DROP INDEX idx_employee_name
ON Employees;

PostgreSQL:

DROP INDEX idx_employee_name;

SQL Server:

DROP INDEX idx_employee_name
ON Employees;

E. Show indexes

MySQL:

SHOW INDEXES FROM Employees;

PostgreSQL — inspect index definitions:

SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'employees';

SQL Server:

EXEC sp_helpindex 'Employees';

F. Check whether a query uses an index

MySQL:

EXPLAIN
SELECT *
FROM Employees
WHERE emp_id = 50000;

PostgreSQL:

EXPLAIN
SELECT *
FROM Employees
WHERE emp_id = 50000;

PostgreSQL can also execute and measure the query:

EXPLAIN ANALYZE
SELECT *
FROM Employees
WHERE emp_id = 50000;

EXPLAIN helps you understand the execution plan. EXPLAIN ANALYZE actually runs the query in PostgreSQL, so use it thoughtfully for expensive querie

A. Primary Key Index

A primary key uniquely identifies each row.

CREATE TABLE Employees (
    emp_id INT PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10, 2)
);

Most relational databases automatically create an index to enforce the primary key constraint.

B. Unique Index

CREATE UNIQUE INDEX idx_email
ON Employees (email);

• Prevents duplicate indexed values.
• Can improve searches using email.
• Does not necessarily prohibit multiple NULL values; this depends on the DBMS.

C. Non-Unique Index

CREATE INDEX idx_department
ON Employees (department);

Multiple employees can belong to the same department. The index helps locate rows for that department.

D. Clustered Index

A clustered index determines how table rows are organized according to the index key in database systems that support this organization.

For example, SQL Server supports clustered indexes. InnoDB in MySQL organizes table data using the primary key as its clustered index by default when a primary key exists.

-- SQL Server example
CREATE CLUSTERED INDEX idx_emp_id
ON Employees (emp_id);

Important points:

• A table can have at most one clustered index because there can be only one physical organization of its rows.
• The clustered index key can affect the performance of range queries.
• Clustered-index behavior is database-specific.

E. Non-Clustered Index

A non-clustered index stores index keys and references to the corresponding table rows.

-- SQL Server example
CREATE NONCLUSTERED INDEX idx_salary
ON Employees (salary);

A table can have multiple non-clustered indexes, subject to DBMS limits.

Interview comparison

Do not assume that every DBMS implements clustered and non-clustered indexes identically.

F. Composite Index

A composite index is built on two or more columns.

CREATE INDEX idx_dept_salary
ON Employees (department, salary);

This is one of the most important index concepts for SQL interviews.

Suppose the index is:

CREATE INDEX idx_abc
ON Employees (A, B, C);

The index is generally useful for these leading-column combinations:

This is called the leftmost-prefix principle. Exact optimizer behavior depends on the database and index type.

G. Composite Index: Column Order Matters

Consider these two indexes:

CREATE INDEX idx_dept_salary
ON Employees (department, salary);
CREATE INDEX idx_salary_dept
ON Employees (salary, department);

They are not equivalent.

For this query:

SELECT *
FROM Employees
WHERE department = 'IT'
  AND salary > 70000;

The index beginning with department is often a natural choice because it can narrow down the department before applying the salary range.

For this query:

SELECT *
FROM Employees
WHERE salary > 70000;

An index beginning with salary may be more suitable.

How to decide the order:

1. Identify the actual query patterns.
2. Consider equality filters before range filters as a useful starting heuristic.
3. Check whether the index supports ORDER BY or GROUP BY.
4. Verify the execution plan instead of blindly following a rule.

Column selectivity matters, but it is not the only factor determining index order.

Overview

A Common Table Expression (CTE) is a temporary, named result set that you define using the WITH clause and use within a single SQL statement.

Why do we need CTEs?

Consider the question:

Find employees whose salary is greater than the average salary of all employees.

Suppose we have an Employee table:

We could solve it using a subquery:

SELECT name, salary
FROM Employee
WHERE salary > (
    SELECT AVG(salary)
    FROM Employee
);

We can also write it using a CTE:

WITH AverageSalary AS (
    SELECT AVG(salary) AS avg_salary
    FROM Employee
)
SELECT e.name, e.salary
FROM Employee e
CROSS JOIN AverageSalary a
WHERE e.salary > a.avg_salary;

Both queries solve the same problem.

The CTE gives the average calculation a meaningful name, AverageSalary, which makes the overall query easier to read and extend.

CTE syntax

WITH cte_name AS (
    SELECT column1, column2
    FROM table_name
    WHERE condition
)
SELECT *
FROM cte_name;

Understand each part:

Important points:

• A CTE exists only for the SQL statement in which it is defined.
• You can reference it in the main query and, where permitted, in other CTE definitions within the same WITH clause.
• A CTE is not a permanent database table.
• A CTE does not automatically store its result in physical memory or a temporary table. The database optimizer decides how to execute it.

Simple CTE example

Problem: Find employees earning more than ₹70,000.

WITH HighSalaryEmployees AS (
    SELECT id, name, department, salary
    FROM Employee
    WHERE salary > 70000
)
SELECT *
FROM HighSalaryEmployees;

The query works in two logical steps:

1. HighSalaryEmployees defines the rows that satisfy the salary condition.
2. The main query retrieves those rows.

You can think of the CTE as a named intermediate result that helps organize your query.

Multiple CTEs in one query

You can define multiple CTEs in the same WITH clause by separating their definitions with commas.

Problem: Find departments whose average salary is greater than the company's overall average salary.

WITH DepartmentAverage AS (
    SELECT
        department,
        AVG(salary) AS avg_salary
    FROM Employee
    GROUP BY department
),
CompanyAverage AS (
    SELECT AVG(salary) AS company_avg
    FROM Employee
)
SELECT
    d.department,
    d.avg_salary
FROM DepartmentAverage d
CROSS JOIN CompanyAverage c
WHERE d.avg_salary > c.company_avg;

How to approach it

• First CTE: calculate one average per department.
• Second CTE: calculate one average for the entire company.
• Main query: compare each department's average with the company average.

This is a useful pattern for questions involving multiple levels of aggregation.

CTE with window functions

This is one of the most important combinations for SQL interviews.

Problem: Find the second-highest distinct salary in each department

WITH RankedEmployees AS (
    SELECT
        id,
        name,
        department,
        salary,
        DENSE_RANK() OVER (
            PARTITION BY department
            ORDER BY salary DESC
        ) AS salary_rank
    FROM Employee
)
SELECT
    name,
    department,
    salary
FROM RankedEmployees
WHERE salary_rank = 2;

What are window functions?

A window function performs a calculation across a set of related rows while keeping each individual row in the output.

For example, suppose we have an Employee table:

Suppose the question asks us to display every employee along with their department's average salary.

SELECT
    name,
    department,
    salary,
    AVG(salary) OVER (
        PARTITION BY department
    ) AS avg_salary
FROM Employee;

Notice that every employee remains in the output. The average is calculated separately for each department and displayed alongside each employee.

Window functions vs. GROUP BY

This is a fundamental interview distinction.

Using GROUP BY:

SELECT department, AVG(salary) AS avg_salary
FROM Employee
GROUP BY department;

Result: one row per department.

Using a window function:

SELECT
    name,
    department,
    salary,
    AVG(salary) OVER (
        PARTITION BY department
    ) AS avg_salary
FROM Employee;

Result: one row per employee, with the department average added.

General syntax

SELECT
    column1,
    window_function(column2) OVER (
        PARTITION BY column3
        ORDER BY column4
    ) AS result
FROM table_name;

The OVER() clause defines the window used by the function.

Each part is optional in some situations. For example, you can use OVER() without either PARTITION BY or ORDER BY.

Understand the three window configurations

A. OVER() — Entire result set

COUNT(*) OVER ()

Counts all rows in the window and displays that count on every row.

B. PARTITION BY — Separate groups

AVG(salary) OVER (
    PARTITION BY department
)

Calculates a separate average for each department without removing employee rows.

C. ORDER BY — Ordered calculations

ROW_NUMBER() OVER (
    ORDER BY salary DESC
)

Numbers rows in descending salary order.

Types of window functions

For practical problem-solving, learn these categories.

Aggregate window functions

These functions calculate a summary across a window while preserving the original rows.

A. SUM()

Calculate the total salary of each department.

SELECT
    name,
    department,
    salary,
    SUM(salary) OVER (
        PARTITION BY department
    ) AS department_total
FROM Employee;

Finance total:

Sales total:

Every employee in Finance receives 120000 in department_total, and every employee in Sales receives 50000.

B. AVG()

SELECT
    name,
    department,
    salary,
    AVG(salary) OVER (
        PARTITION BY department
    ) AS department_avg
FROM Employee;

Use this when a question asks for each employee's salary compared with the average salary of their department.

C. COUNT()

SELECT
    name,
    department,
    COUNT(*) OVER (
        PARTITION BY department
    ) AS department_employee_count
FROM Employee;

This returns 3 for every Finance employee and 2 for every Sales employee.

D. MIN() and MAX()

SELECT
    name,
    department,
    salary,
    MAX(salary) OVER (
        PARTITION BY department
    ) AS highest_salary,
    MIN(salary) OVER (
        PARTITION BY department
    ) AS lowest_salary
FROM Employee;

This helps answer questions such as:

• Find each employee's salary and their department's highest salary.
• Compare an employee's salary with the department minimum.
• Identify employees who earn the maximum salary in their department.

To return only the highest-paid employees, use a subquery or CTE to calculate the window result and then filter it in the outer query.

Ranking functions

Consider salaries ordered from highest to lowest:

A. ROW_NUMBER()

Assigns a unique sequential number to every row.

SELECT
    name,
    salary,
    ROW_NUMBER() OVER (
        ORDER BY salary DESC, name ASC
    ) AS row_num
FROM Employee;

Even if two employees have the same salary, they get different row numbers. The extra name ordering makes the result deterministic for these distinct names.

Use it for: selecting exactly one row per group, deduplication, and selecting the latest record per customer.

B. RANK()

Rows with equal ordering values receive the same rank. Subsequent ranks skip numbers.

SELECT
    name,
    salary,
    RANK() OVER (
        ORDER BY salary DESC
    ) AS salary_rank
FROM Employee;

Because two employees share rank 1, rank 2 is skipped.

Use it for: competition-style rankings where tied rows share a position and later positions have gaps.

C. DENSE_RANK()

Equal values receive the same rank, but there are no gaps.

SELECT
    name,
    salary,
    DENSE_RANK() OVER (
        ORDER BY salary DESC
    ) AS salary_rank
FROM Employee;

Use it for: the second-highest distinct salary, third-highest distinct salary, or top three distinct salary levels.

Compare the results

*The row numbers shown assume a tie-breaker such as employee name. Without a complete ordering, the order among tied rows is not guaranteed.

D. PERCENT_RANK()

Calculates a row's relative rank within its window.

For a partition containing at least two rows:

• The first rank is generally 0.
• The last rank is 1.
• Intermediate ranks fall between 0 and 1.

SELECT
    name,
    salary,
    PERCENT_RANK() OVER (
        ORDER BY salary DESC
    ) AS relative_rank
FROM Employee;

For a partition with only one row, PERCENT_RANK() returns 0.

Use it for: relative-ranking analysis. For many standard interview questions about top N rows or distinct salary levels, RANK() and DENSE_RANK() are more useful.

The most important technique: PARTITION BY + ranking

This combination appears repeatedly in SQL interviews.

Suppose we want to rank employees within each department, rather than across the entire company.

SELECT
    name,
    department,
    salary,
    DENSE_RANK() OVER (
        PARTITION BY department
        ORDER BY salary DESC
    ) AS dept_rank
FROM Employee;

The ranking restarts for every department.

For example:

Without PARTITION BY department, the ranking would be calculated across all employees together.

Interview problem: Find the second-highest salary in each department

WITH RankedEmployees AS (
    SELECT
        name,
        department,
        salary,
        DENSE_RANK() OVER (
            PARTITION BY department
            ORDER BY salary DESC
        ) AS salary_rank
    FROM Employee
)
SELECT name, department, salary
FROM RankedEmployees
WHERE salary_rank = 2;

How to approach this problem:

1. The phrase in each department tells us to use PARTITION BY department.
2. The phrase second-highest distinct salary tells us to use DENSE_RANK().
3. ORDER BY salary DESC places the highest salary first.
4. The CTE calculates the ranking.
5. The outer query filters for rank 2.

Why use a CTE? In standard SQL, a window function's result cannot be filtered directly in the same query block's WHERE clause. The CTE lets us calculate the rank first and filter it afterward.

Window frames: running totals and moving averages

One additional concept is essential for mastering window functions: the window frame.

A window frame defines which rows within the ordered window contribute to a calculation for the current row.

Running total

Suppose we have a Sales table:

Query:

SELECT
    sale_date,
    amount,
    SUM(amount) OVER (
        ORDER BY sale_date
        ROWS BETWEEN UNBOUNDED PRECEDING
                 AND CURRENT ROW
    ) AS running_total
FROM Sales;

The frame begins at the first row and extends through the current row.

Moving average

SELECT
    sale_date,
    amount,
    AVG(amount) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 1 PRECEDING
                 AND CURRENT ROW
    ) AS moving_avg
FROM Sales;

This calculates the average of the current row and the immediately preceding row, when one exists.

Use a unique ordering key when the row sequence matters. Also, specify the frame explicitly for running totals and moving calculations, because default frames can behave differently in the presence of tied ordering values.

LAG() — Access a previous row

LAG() retrieves a value from a previous row in the ordered window without performing a self join.

Syntax

LAG(expression, offset, default_value)
OVER (
    PARTITION BY column
    ORDER BY column
)

• expression: The column whose value you want.
• offset: How many rows backward to look; defaults to 1.
• default_value: What to return if the requested preceding row doesn't exist; defaults to NULL.

Example: Compare today's sales with yesterday's

Suppose we have a Sales table:

SELECT
    sale_date,
    amount,
    LAG(amount) OVER (
        ORDER BY sale_date
    ) AS previous_amount
FROM Sales;

The first row has no preceding row, so the result is NULL.

Example: Calculate the change from the previous row

SELECT
    sale_date,
    amount,
    LAG(amount) OVER (
        ORDER BY sale_date
    ) AS previous_amount,
    amount - LAG(amount) OVER (
        ORDER BY sale_date
    ) AS sales_change
FROM Sales;

For January 3, the change is $120 - 150 = -30$.

Access two rows backward

LAG(amount, 2, 0) OVER (
    ORDER BY sale_date
)

This accesses the value from two preceding rows. It returns 0 when that row doesn't exist.

Interview applications:

• Compare current and previous day's sales.
• Find employees whose salary increased compared with their previous record.
• Detect changes in user activity.
• Identify consecutive dates or gaps in sequences.

LEAD() — Access a next row

LEAD() is the opposite of LAG(): it retrieves a value from a subsequent row.

LEAD(expression, offset, default_value)
OVER (
    PARTITION BY column
    ORDER BY column
)

Example:

SELECT
    sale_date,
    amount,
    LEAD(amount) OVER (
        ORDER BY sale_date
    ) AS next_amount
FROM Sales;

The last row has no subsequent row.

Interview applications:

• Compare a current event with the next event.
• Calculate the number of days until the next order.
• Find the next salary or status for an employee.
• Detect gaps between consecutive records.

LAG() vs. LEAD()

LAG()

Looks backward in the specified order.

Example: Today's sales versus yesterday's sales.

LEAD()

Looks forward in the specified order.

Example: Today's order versus the customer's next order.

FIRST_VALUE() — Get the first value in a window

Returns the value from the first row of the window frame.

SELECT
    name,
    department,
    salary,
    FIRST_VALUE(salary) OVER (
        PARTITION BY department
        ORDER BY salary DESC
    ) AS highest_salary
FROM Employee;

This displays the highest salary alongside every employee in each department, assuming the window starts with the highest-paid employee.

Use cases:

• Find the first transaction in a group.
• Display the earliest order date.
• Compare every employee's salary against the highest salary in their department.

LAST_VALUE() — Get the last value in a window

Returns the value from the last row of the window frame.

One important trap: the default window frame with ORDER BY often ends at the current row's peer group, rather than the end of the entire partition. As a result, LAST_VALUE() may return the current row's value instead of the final value in the partition.

Correct way to get the lowest salary in each department

SELECT
    name,
    department,
    salary,
    LAST_VALUE(salary) OVER (
        PARTITION BY department
        ORDER BY salary ASC
        ROWS BETWEEN UNBOUNDED PRECEDING
                 AND UNBOUNDED FOLLOWING
    ) AS lowest_salary
FROM Employee;

Here, the frame explicitly extends to the last row of the partition.

NTH_VALUE() — Get the Nth value

Returns the value from the Nth row of the window frame, if that row exists.

SELECT
    name,
    salary,
    NTH_VALUE(salary, 2) OVER (
        ORDER BY salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING
                 AND UNBOUNDED FOLLOWING
    ) AS second_salary
FROM Employee;

This retrieves the second salary in the ordered window frame. Because ties may exist, this does not necessarily mean the second-highest distinct salary.

Use cases:

• Retrieve a value at a specific position.
• Compare a record with the second or third ordered record.
• Analyze ordered measurements.

For a second-highest distinct salary problem, DENSE_RANK() is usually a better choice.

NTILE(N) — Divide rows into buckets

Divides the ordered rows into up to N approximately equal-sized buckets, numbered from 1 through N.

SELECT
    name,
    salary,
    NTILE(4) OVER (
        ORDER BY salary DESC
    ) AS salary_quartile
FROM Employee;

This distributes the employees into four groups based on salary order.

Use cases:

• Divide customers into four groups.
• Create salary bands based on ordered rows.
• Split users into approximately equal-sized cohorts.

Note that NTILE() divides rows, not distinct salary values, so tied salaries can end up in different buckets.

Overview

A Common Table Expression (CTE) is a temporary named result set defined using WITH. It helps divide a complex SQL query into readable steps.

WITH CTE_Name AS (
    SELECT ...
)
SELECT *
FROM CTE_Name;

Pattern 1. Basic Readability — Replace Nested Subqueries

WITH HighSales AS (
    SELECT order_id, customer_id, amount
    FROM Orders
    WHERE amount > 1000
)
SELECT c.name, h.amount
FROM Customers c
JOIN HighSales h
    ON c.customer_id = h.customer_id;

How it works:

1. HighSales selects orders above 1000.
2. The main query joins those orders with customers.
3. The CTE makes the logic easier to read and reuse within the statement.

Pattern 2. Multiple CTEs — Chain Logic Steps

WITH Sales2023 AS (
    SELECT product_id, amount
    FROM Sales
    WHERE sale_date >= DATE '2023-01-01'
      AND sale_date < DATE '2024-01-01'
),
Sales2024 AS (
    SELECT product_id, amount
    FROM Sales
    WHERE sale_date >= DATE '2024-01-01'
      AND sale_date < DATE '2025-01-01'
)
SELECT a.product_id,
       a.amount AS sales_2023,
       b.amount AS sales_2024
FROM Sales2023 a
JOIN Sales2024 b
    ON a.product_id = b.product_id;

Pattern 3. Recursion — Generate Numbers from 1 to 10

WITH RECURSIVE NumSeries AS (
    SELECT 1 AS n              -- Anchor
    UNION ALL
    SELECT n + 1               -- Recursive step
    FROM NumSeries
    WHERE n < 10               -- Stop condition
)
SELECT n
FROM NumSeries;

Output: 1, 2, 3, ..., 10

The recursive CTE has three parts:

• Anchor: Starts with 1.
• Recursive step: Generates the next number.
• Termination: Stops generating numbers after 10.

PostgreSQL supports WITH RECURSIVE. SQL Server uses WITH for recursive CTEs and generally does not use the RECURSIVE keyword.

Pattern 4. Manager Hierarchy — Who Reports to Whom?

Example structure:

WITH RECURSIVE OrgChart AS (
    -- Start with top-level managers
    SELECT id, name, manager_id, 1 AS lvl
    FROM Employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Find employees reporting to each person
    SELECT e.id, e.name, e.manager_id, o.lvl + 1
    FROM Employees e
    JOIN OrgChart o
        ON e.manager_id = o.id
)
SELECT *
FROM OrgChart
ORDER BY lvl, id;

lvl represents the employee's depth in the hierarchy.

Pattern 5. Nth Highest Salary — DENSE_RANK() + CTE

WITH RankedSalaries AS (
    SELECT salary,
           DENSE_RANK() OVER (
               ORDER BY salary DESC
           ) AS rnk
    FROM Employees
)
SELECT DISTINCT salary
FROM RankedSalaries
WHERE rnk = 2;

For the third-highest salary, change rnk = 2 to rnk = 3.

Why DENSE_RANK()?

• ROW_NUMBER() assigns a different number to every row.
• RANK() leaves gaps after ties.
• DENSE_RANK() ranks distinct values without gaps.

Pattern 6. Delete Duplicates — ROW_NUMBER()

PostgreSQL example:

WITH RankedUsers AS (
    SELECT id,
           ROW_NUMBER() OVER (
               PARTITION BY email
               ORDER BY id
           ) AS rn
    FROM Users
)
DELETE FROM Users u
USING RankedUsers r
WHERE u.id = r.id
  AND r.rn > 1;

How it works:

1. PARTITION BY email groups rows with the same email.
2. ORDER BY id selects the row with the smallest ID as rn = 1.
3. Rows with rn > 1 are duplicates.
4. The DELETE removes only those duplicate rows.

The original image's DELETE FROM DupeRows pattern is not universally valid. In PostgreSQL, use DELETE ... USING; SQL Server supports deleting through an updatable CTE.

Before deleting: Run the ranking query as a SELECT first to verify which rows will be removed. Decide explicitly how NULL emails and case differences should be treated.

Pattern 7. Running Total — Cumulative Sum

Suppose Sales contains sale_date and amount.

WITH DailySales AS (
    SELECT sale_date,
           SUM(amount) AS daily_amount
    FROM Sales
    GROUP BY sale_date
)
SELECT sale_date,
       daily_amount,
       SUM(daily_amount) OVER (
           ORDER BY sale_date
           ROWS BETWEEN UNBOUNDED PRECEDING
                    AND CURRENT ROW
       ) AS running_total
FROM DailySales
ORDER BY sale_date;

Example:

Pattern 8. Moving Average — Three-Row Rolling Average

WITH DailyStats AS (
    SELECT sale_date,
           COUNT(*) AS orders
    FROM Sales
    GROUP BY sale_date
)
SELECT sale_date,
       orders,
       AVG(orders) OVER (
           ORDER BY sale_date
           ROWS BETWEEN 2 PRECEDING
                    AND CURRENT ROW
       ) AS moving_avg
FROM DailyStats
ORDER BY sale_date;

For order counts 3, 6, 9, the moving averages are:

The first two averages use fewer than three rows because fewer rows are available.

Pattern 9. Gaps and Islands — Group Consecutive Dates

PostgreSQL example:

WITH NumberedDates AS (
    SELECT DISTINCT activity_date,
           activity_date
             - ROW_NUMBER() OVER (
                   ORDER BY activity_date
               )::int AS grp
    FROM ActiveDays
)
SELECT MIN(activity_date) AS start_date,
       MAX(activity_date) AS end_date,
       COUNT(*) AS consecutive_days
FROM NumberedDates
GROUP BY grp
ORDER BY start_date;

Example:

DISTINCT prevents duplicate activity dates from breaking the sequence.

Pattern 10. Date Generation — Fill Missing Dates

PostgreSQL recursive CTE example:

WITH RECURSIVE Calendar AS (
    SELECT DATE '2024-01-01' AS dt

    UNION ALL

    SELECT dt + 1
    FROM Calendar
    WHERE dt < DATE '2024-01-31'
),
DailySales AS (
    SELECT sale_date,
           SUM(amount) AS amount
    FROM Sales
    GROUP BY sale_date
)
SELECT c.dt,
       COALESCE(s.amount, 0) AS amount
FROM Calendar c
LEFT JOIN DailySales s
    ON c.dt = s.sale_date
ORDER BY c.dt;

Why LEFT JOIN? The calendar is the complete list of dates. Missing sales records become NULL, which COALESCE() converts to 0.

For PostgreSQL specifically, generate_series() is another convenient way to generate dates without recursion.

Pattern 11. Year-over-Year Growth — LAG() + CTE

WITH YearlySales AS (
    SELECT EXTRACT(YEAR FROM sale_date) AS yr,
           SUM(amount) AS total_sales
    FROM Sales
    GROUP BY EXTRACT(YEAR FROM sale_date)
),
Comparison AS (
    SELECT yr,
           total_sales,
           LAG(total_sales) OVER (
               ORDER BY yr
           ) AS prev_year_sales
    FROM YearlySales
)
SELECT yr,
       total_sales,
       prev_year_sales,
       ROUND(
           100.0 * (total_sales - prev_year_sales)
           / NULLIF(prev_year_sales, 0),
           2
       ) AS growth_pct
FROM Comparison
ORDER BY yr;

Formula:

Example: Previous year sales = 200, current year sales = 250.

• LAG() gets the previous row's value.
• NULLIF(previous, 0) prevents division by zero.
• The first available year has no previous value, so its growth is NULL.

Pattern 12. Monthly Active Users — Retention Analysis

Assume Logins(user_id, login_date).

WITH MonthlyUsers AS (
    SELECT DISTINCT
           user_id,
           DATE_TRUNC('month', login_date)::date AS month
    FROM Logins
)
SELECT m1.month,
       COUNT(*) AS active_users,
       COUNT(m2.user_id) AS retained_next_month
FROM MonthlyUsers m1
LEFT JOIN MonthlyUsers m2
    ON m1.user_id = m2.user_id
   AND m2.month = (m1.month + INTERVAL '1 month')::date
GROUP BY m1.month
ORDER BY m1.month;

How it works:

1. DISTINCT ensures each user appears at most once per month.
2. m1 represents users active in a particular month.
3. m2 finds the same users in the next month.
4. COUNT(*) counts current-month active users.
5. COUNT(m2.user_id) counts users retained into the next month.

Retention rate can be calculated as:

ROUND(
    100.0 * COUNT(m2.user_id) / NULLIF(COUNT(*), 0),
    2
) AS retention_pct

Add this expression to the SELECT list above.

Pattern 13. Find Missing IDs — Gaps in a Sequence

PostgreSQL example using recursion:

WITH RECURSIVE Expected AS (
    SELECT 1 AS id

    UNION ALL

    SELECT id + 1
    FROM Expected
    WHERE id < (SELECT MAX(id) FROM Users)
)
SELECT e.id
FROM Expected e
LEFT JOIN Users u
    ON e.id = u.id
WHERE u.id IS NULL
ORDER BY e.id;

Suppose Users contains IDs 1, 2, 4, 6.

This assumes IDs start at 1. If the range starts elsewhere, change the anchor. For large sequences in PostgreSQL, generate_series() is generally more convenient than recursion.

Pattern 14. String Splitting — Convert a String into Rows

Input: Apple,Banana,Orange

PostgreSQL solution:

SELECT TRIM(item) AS item
FROM UNNEST(
    STRING_TO_ARRAY('Apple,Banana,Orange', ',')
) AS t(item);

For values stored in a table, for example Strings(id, val):

SELECT s.id,
       TRIM(x.item) AS item
FROM Strings s
CROSS JOIN LATERAL
    UNNEST(STRING_TO_ARRAY(s.val, ',')) AS x(item);

Pattern 15. Basket Analysis — Products Bought Together

Assume Order_Items(order_id, product_id).

WITH Pairs AS (
    SELECT a.product_id AS p1,
           b.product_id AS p2,
           a.order_id
    FROM Order_Items a
    JOIN Order_Items b
        ON a.order_id = b.order_id
       AND a.product_id < b.product_id
)
SELECT p1,
       p2,
       COUNT(DISTINCT order_id) AS frequency
FROM Pairs
GROUP BY p1, p2
ORDER BY frequency DESC;

Why a.product_id < b.product_id?

For a pair of products A and B, it avoids generating both (A, B) and (B, A), and prevents pairing a product with itself.

COUNT(DISTINCT order_id) avoids overcounting if the source contains repeated lines for the same product in one order.

Pattern 16. Pivot Preparation — Rows to Columns

Assume Sales(year, quarter, revenue).

WITH BaseData AS (
    SELECT year,
           quarter,
           SUM(revenue) AS revenue
    FROM Sales
    GROUP BY year, quarter
)
SELECT year,
       MAX(CASE WHEN quarter = 1 THEN revenue END) AS Q1,
       MAX(CASE WHEN quarter = 2 THEN revenue END) AS Q2,
       MAX(CASE WHEN quarter = 3 THEN revenue END) AS Q3,
       MAX(CASE WHEN quarter = 4 THEN revenue END) AS Q4
FROM BaseData
GROUP BY year
ORDER BY year;

This is called conditional aggregation. MAX() is appropriate here because the CTE already contains at most one row per year and quarter.

Pattern 17. Consecutive Wins — Find Three Wins in a Row

Assume Matches(id, player_id, match_date, result).

WITH Streaks AS (
    SELECT id,
           player_id,
           match_date,
           result,
           LAG(result, 1) OVER (
               PARTITION BY player_id
               ORDER BY match_date, id
           ) AS prev1,
           LAG(result, 2) OVER (
               PARTITION BY player_id
               ORDER BY match_date, id
           ) AS prev2
    FROM Matches
)
SELECT *
FROM Streaks
WHERE result = 'Win'
  AND prev1 = 'Win'
  AND prev2 = 'Win';

How it works:

• prev1 gives the previous match result.
• prev2 gives the result two matches earlier.
• If all three results are Win, the current match completes a three-win streak.

PARTITION BY player_id prevents one player's result from being compared with another player's result.

If matches have gaps in dates, this still means three consecutive matches, not necessarily three consecutive calendar days.

Pattern 18. Update via CTE — Modify Data Safely

PostgreSQL example:

WITH ToUpdate AS (
    SELECT id
    FROM Employees
    WHERE performance_score > 90
)
UPDATE Employees e
SET salary = e.salary * 1.10
FROM ToUpdate t
WHERE e.id = t.id;

This increases salaries by 10% for employees with a performance score above 90.

In SQL Server, an updatable CTE can also be used directly:

WITH ToUpdate AS (
    SELECT *
    FROM Employees
    WHERE performance_score > 90
)
UPDATE ToUpdate
SET salary = salary * 1.10;

Syntax and updateability depend on the database. For important data changes, inspect the target rows first and use a transaction where appropriate.

Pattern 19. Tree Path Generation — Build Breadcrumbs

Assume Categories(id, name, parent_id).

PostgreSQL example:

WITH RECURSIVE Paths AS (
    SELECT id,
           name,
           parent_id,
           name::text AS path
    FROM Categories
    WHERE parent_id IS NULL

    UNION ALL

    SELECT c.id,
           c.name,
           c.parent_id,
           p.path || ' > ' || c.name
    FROM Categories c
    JOIN Paths p
        ON c.parent_id = p.id
)
SELECT id, name, path
FROM Paths
ORDER BY id;

Example path:

Home > Electronics > TV

The hierarchy should not contain cycles; otherwise, recursion may continue indefinitely unless you add cycle detection or a depth limit.

Pattern 20. Insert from CTE — ETL and Data Migration

Assume Raw_Sales(region, amount) and Summary_Table(region, total_sales).

WITH CalculatedData AS (
    SELECT region,
           SUM(amount) AS total_sales
    FROM Raw_Sales
    GROUP BY region
)
INSERT INTO Summary_Table (region, total_sales)
SELECT region, total_sales
FROM CalculatedData;

How it works:

1. CalculatedData aggregates sales by region.
2. INSERT INTO stores the calculated results in the summary table.

The destination table must exist and have compatible columns. Re-running this query may insert duplicate summary rows unless you truncate the table, enforce suitable uniqueness, or use an appropriate upsert strategy.

The position of WITH relative to INSERT varies by SQL dialect; the example uses PostgreSQL-compatible syntax.

1. Find the second-highest salary

SELECT MAX(salary) AS second_highest_salary
FROM employees
WHERE salary < (
    SELECT MAX(salary)
    FROM employees
);

Logic:

1. The subquery finds the highest salary.
2. WHERE salary < ... excludes the highest salary.
3. MAX(salary) returns the next-highest distinct salary.

2. Find department-wise average salary where departments have more than 5 employees

SELECT
    department_id,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;

Logic:

1. GROUP BY creates one group per department.
2. AVG(salary) calculates the average salary for each group.
3. HAVING COUNT(*) > 5 keeps only departments with more than five employees.

3. Find customers with more than 3 orders in the last 30 days

SELECT customer_id
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY customer_id
HAVING COUNT(*) > 3;

Logic:

1. Filter orders to the last 30 days.
2. Group orders by customer.
3. Return customers with more than three qualifying orders.

4. Find duplicate combinations of multiple columns

SELECT
    col1,
    col2,
    COUNT(*) AS duplicate_count
FROM my_table
GROUP BY col1, col2
HAVING COUNT(*) > 1;

Logic:

1. Group rows having the same col1 and col2.
2. Count the rows in each group.
3. Return groups appearing more than once.

5. Transpose rows into columns using CASE

SELECT
    user_id,
    MAX(CASE WHEN month = 'Jan' THEN spend END) AS Jan,
    MAX(CASE WHEN month = 'Feb' THEN spend END) AS Feb
FROM user_spend
GROUP BY user_id;

Logic:

1. CASE places each month's spending in its corresponding column.
2. MAX() combines values for each user into one row.
3. GROUP BY user_id produces one row per user.

6. Understand INNER JOIN vs. EXISTS

Using EXISTS to find customers who have placed at least one order:

SELECT c.name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.id
);

Equivalent using INNER JOIN:

SELECT DISTINCT c.name
FROM customers c
INNER JOIN orders o
    ON o.customer_id = c.id;

Difference:

7. Find products that have never been sold

SELECT p.*
FROM products p
LEFT JOIN order_items o
    ON p.id = o.product_id
WHERE o.product_id IS NULL;

Logic:

1. LEFT JOIN preserves every product.
2. Products with matching order items receive matching values.
3. Products without a match have NULL on the order-item side.

Alternative using NOT EXISTS:

SELECT p.*
FROM products p
WHERE NOT EXISTS (
    SELECT 1
    FROM order_items o
    WHERE o.product_id = p.id
);

8. Find the highest order value for each customer

SELECT o.*
FROM orders o
WHERE o.amount = (
    SELECT MAX(o2.amount)
    FROM orders o2
    WHERE o2.customer_id = o.customer_id
);

Logic:

1. The inner query finds the maximum order amount for the current customer.
2. The outer query selects orders matching that amount.

9. Compare today's active users with yesterday's active users

Assume activity_date is a DATE column and active users are identified by distinct user_id values.

WITH daily_users AS (
    SELECT
        activity_date,
        COUNT(DISTINCT user_id) AS user_count
    FROM activity_log
    WHERE activity_date IN (
        CURRENT_DATE,
        CURRENT_DATE - 1
    )
    GROUP BY activity_date
)
SELECT
    MAX(CASE
        WHEN activity_date = CURRENT_DATE
        THEN user_count
    END) AS today_users,
    MAX(CASE
        WHEN activity_date = CURRENT_DATE - 1
        THEN user_count
    END) AS yesterday_users
FROM daily_users;

Logic:

1. The CTE calculates distinct active users for each date.
2. CASE places today's and yesterday's counts into separate columns.
3. MAX() combines those values into one result row.

10. Use a recursive CTE to retrieve an employee hierarchy

Assume employees.id identifies an employee and parent_id identifies their manager.

WITH RECURSIVE org_chart AS (
    -- Base case: top-level employees
    SELECT
        id,
        parent_id,
        1 AS level
    FROM employees
    WHERE parent_id IS NULL

    UNION ALL

    -- Recursive case: find each employee's reports
    SELECT
        e.id,
        e.parent_id,
        oc.level + 1
    FROM employees e
    JOIN org_chart oc
        ON e.parent_id = oc.id
)
SELECT *
FROM org_chart
ORDER BY level, id;

Logic:

1. The anchor query finds top-level employees.
2. The recursive query finds employees reporting to the previous level.
3. level + 1 records the hierarchy depth.
4. Recursion continues until no additional matching employees are found.

11. Rank products by sales within each category

SELECT
    product_id,
    category,
    total_sales,
    RANK() OVER (
        PARTITION BY category
        ORDER BY total_sales DESC
    ) AS rnk
FROM product_sales;

Logic:

1. PARTITION BY category ranks products independently within each category.
2. ORDER BY total_sales DESC places the highest-selling products first.
3. RANK() assigns rankings, giving tied products the same rank and leaving gaps afterward.

Example:

12. Calculate a 7-row rolling average of website visits

SELECT
    visit_date,
    visits,
    AVG(visits) OVER (
        ORDER BY visit_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS rolling_avg
FROM web_traffic;

Logic:

1. Order rows by date.
2. Include the current row and six preceding rows.
3. Calculate their average visits.

13. Find the first and last transaction for each user

SELECT *
FROM (
    SELECT
        t.*,
        ROW_NUMBER() OVER (
            PARTITION BY user_id
            ORDER BY tx_date ASC, transaction_id ASC
        ) AS rn_asc,
        ROW_NUMBER() OVER (
            PARTITION BY user_id
            ORDER BY tx_date DESC, transaction_id DESC
        ) AS rn_desc
    FROM transactions t
) ranked
WHERE rn_asc = 1
   OR rn_desc = 1;

Logic:

1. rn_asc = 1 identifies the first transaction for each user.
2. rn_desc = 1 identifies the last transaction.
3. The outer query returns rows matching either condition.

14. Detect gaps between sequential dates

SELECT
    event_date,
    LAG(event_date) OVER (
        ORDER BY event_date
    ) AS prev_date,
    event_date - LAG(event_date) OVER (
        ORDER BY event_date
    ) AS gap
FROM events
ORDER BY event_date;

Logic:

1. LAG() retrieves the previous row's date.
2. Subtract the previous date from the current date.
3. A gap greater than one day indicates missing calendar days, assuming one row per date and date values are consecutive at daily granularity.

15. Calculate cumulative spend per customer per month

SELECT
    customer_id,
    month,
    spend,
    SUM(spend) OVER (
        PARTITION BY customer_id
        ORDER BY month
        ROWS BETWEEN UNBOUNDED PRECEDING
                 AND CURRENT ROW
    ) AS cumulative_spend
FROM monthly_spend
ORDER BY customer_id, month;

Logic:

1. PARTITION BY customer_id starts a separate running total for each customer.
2. ORDER BY month processes each customer's records chronologically.
3. SUM() accumulates spend from the first row through the current row.

1. Find the Top 5 Most-Ordered Dishes from a Restaurant

SELECT
    DishName,
    SUM(Quantity) AS TotalOrders
FROM Orders
WHERE RestaurantID = 'R101'
GROUP BY DishName
ORDER BY TotalOrders DESC
LIMIT 5;

2. Retrieve Orders from the Last 30 Days for a Specific User

SELECT *
FROM Orders
WHERE UserID = 'U123'
  AND OrderDate >= CURDATE() - INTERVAL 30 DAY
  AND OrderDate < CURDATE() + INTERVAL 1 DAY;

If OrderDate is a DATE column, the upper bound is optional. It is useful when OrderDate is a DATETIME and you want to include all of today's orders.

3. Find Total Revenue Generated by All Restaurants in the Previous Calendar Month

SELECT SUM(Amount) AS TotalRevenue
FROM Orders
WHERE OrderDate >= DATE_FORMAT(
          CURDATE() - INTERVAL 1 MONTH, '%Y-%m-01'
      )
  AND OrderDate < DATE_FORMAT(
          CURDATE(), '%Y-%m-01'
      );

This calculates revenue for the previous calendar month, not a rolling 30-day period.

For the supplied sample data, if the intended period is December 2024, the total is:

4. Find the Top 10 Restaurants by Average Customer Rating

SELECT
    RestaurantID,
    ROUND(AVG(Rating), 2) AS AverageRating
FROM Ratings
GROUP BY RestaurantID
ORDER BY AverageRating DESC
LIMIT 10;

For a production ranking, consider requiring a minimum number of ratings so that restaurants with very few reviews are not unfairly favored.

5. Find Customers Who Ordered from More Than 3 Different Restaurants

SELECT UserID
FROM Orders
GROUP BY UserID
HAVING COUNT(DISTINCT RestaurantID) > 3;

Use HAVING, rather than WHERE, because the condition depends on an aggregate count.

6. Find Restaurants Whose Average Order Value Exceeds the Overall Average

WITH RestaurantAverage AS (
    SELECT
        RestaurantID,
        AVG(Amount) AS AvgOrderValue
    FROM Orders
    GROUP BY RestaurantID
),
OverallAverage AS (
    SELECT AVG(Amount) AS OverallAvg
    FROM Orders
)
SELECT
    r.RestaurantID,
    ROUND(r.AvgOrderValue, 2) AS AvgOrderValue
FROM RestaurantAverage r
CROSS JOIN OverallAverage o
WHERE r.AvgOrderValue > o.OverallAvg;

For the supplied data:

• Overall average: $(200+150+300+100+250)/5 = 200$
• R101 average: $175$
• R102 average: $275$
• R103 average: $100$

This is a good example of multiple CTEs.

7. Calculate Total Revenue for Each Cuisine

SELECT
    Cuisine,
    SUM(Amount) AS TotalRevenue
FROM Orders
GROUP BY Cuisine
ORDER BY TotalRevenue DESC;

8. Rank Restaurants by Total Revenue Within Each City

WITH RestaurantRevenue AS (
    SELECT
        RestaurantID,
        City,
        SUM(Amount) AS TotalRevenue
    FROM Orders
    GROUP BY RestaurantID, City
)
SELECT
    RestaurantID,
    City,
    TotalRevenue,
    RANK() OVER (
        PARTITION BY City
        ORDER BY TotalRevenue DESC
    ) AS RevenueRank
FROM RestaurantRevenue
ORDER BY City, RevenueRank;

The order of cities in the output depends on the final ORDER BY; the ranking itself is calculated separately within each city.

9. Categorize Restaurants by Monthly Revenue

Using the thresholds in your question:

• Above 300: High Revenue
• 150 through 300: Medium Revenue
• Below 150: Low Revenue

WITH MonthlyRevenue AS (
    SELECT
        RestaurantID,
        SUM(Amount) AS TotalMonthlyRevenue
    FROM Orders
    WHERE OrderDate >= DATE_FORMAT(
              CURDATE() - INTERVAL 1 MONTH, '%Y-%m-01'
          )
      AND OrderDate < DATE_FORMAT(
              CURDATE(), '%Y-%m-01'
          )
    GROUP BY RestaurantID
)
SELECT
    RestaurantID,
    TotalMonthlyRevenue,
    CASE
        WHEN TotalMonthlyRevenue > 300
            THEN 'High Revenue'
        WHEN TotalMonthlyRevenue >= 150
            THEN 'Medium Revenue'
        ELSE 'Low Revenue'
    END AS RevenueCategory
FROM MonthlyRevenue
ORDER BY TotalMonthlyRevenue DESC;

The simplified second condition >= 150 works because values above 300 have already been caught by the first condition.

10. Find the Top 3 Dishes for Each Restaurant in New York

WITH DishSales AS (
    SELECT
        RestaurantID,
        City,
        DishName,
        SUM(Quantity) AS TotalQuantity
    FROM Orders
    WHERE City = 'New York'
    GROUP BY RestaurantID, City, DishName
),
RankedDishes AS (
    SELECT
        RestaurantID,
        City,
        DishName,
        TotalQuantity,
        ROW_NUMBER() OVER (
            PARTITION BY RestaurantID, City
            ORDER BY TotalQuantity DESC, DishName
        ) AS rn
    FROM DishSales
)
SELECT
    RestaurantID,
    City,
    DishName,
    TotalQuantity
FROM RankedDishes
WHERE rn <= 3
ORDER BY RestaurantID, rn;

I used ROW_NUMBER() to return at most three dishes per restaurant. The secondary ordering by DishName makes ties deterministic.

If the requirement is to include every dish tied within the top three ranks, use RANK() or DENSE_RANK() instead.