Definition
- Self-join – Joins a table to itself to compare rows within the same table (employee to manager).
- Nth-highest – Without window functions: correlated subquery counting distinct higher values. With windows:
DENSE_RANKpartitioned by group. - Conditional aggregation (pivot) –
SUM(CASE WHEN ...)turns rows into columns for cross-tab reports. - Gaps-and-islands – Subtracting a dense row number from the value collapses consecutive sequences into a constant group.
- Running total –
SUM(amount) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING)computes cumulative sums. - Deduplication –
ROW_NUMBER() OVER (PARTITION BY key ORDER BY id)numbers duplicates; keep row 1, delete the rest.
These are the recurring SQL puzzles National Bank of India's reporting team solves daily. Who earns more than their boss? Who is the third-best-paid? Turn a tall list of monthly totals into a wide grid. Find missing cheque numbers. Find the longest login streak. Add up balances day by day. Find bookings that clash. Remove repeated rows keeping one.
Understanding the Concepts
How do you find employees earning more than their manager (self-join)?
Join the employees table to itself, matching each employee to their manager, and filter where the employee's salary exceeds the manager's.
-- Self-join: employees earning more than their manager
SELECT e.emp_name AS employee, e.salary AS emp_salary,
m.emp_name AS manager, m.salary AS mgr_salary
FROM employees e
JOIN employees m ON e.manager_id = m.emp_id
WHERE e.salary > m.salary;
Expected output:
employee | emp_salary | manager | mgr_salary
--------------+------------+--------------+-----------
Rajesh Kumar | 150000.00 | Pankaj Gupta | 120000.00
How do you find the Nth-highest salary without window functions?
Count how many distinct salaries are strictly greater. The Nth-highest has exactly N-1 greater values.
-- 3rd-highest salary (N = 3)
SELECT salary FROM employees e1
WHERE 2 = (
SELECT COUNT(DISTINCT salary)
FROM employees e2
WHERE e2.salary > e1.salary
);
Expected output:
salary
----------
120000.00
-- Two distinct salaries are higher than 120000
How do you find the 2nd-highest salary per department?
Use DENSE_RANK() partitioned by department, ordered by salary descending, then filter to rank 2.
-- 2nd-highest salary per department
SELECT dept_name, emp_name, salary
FROM (
SELECT e.emp_name, e.salary, d.dept_name,
DENSE_RANK() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS rnk
FROM employees e
JOIN department d ON e.dept_id = d.dept_id
) ranked
WHERE rnk = 2;
Expected output:
dept_name | emp_name | salary
-----------+--------------+-----------
Loans | Rajesh Kumar | 150000.00
Fraud | Priya Sharma | 130000.00
How do you pivot rows to columns with CASE?
Conditional aggregation turns each category into its own column.
-- Pivot: monthly transaction totals by status
SELECT branch,
SUM(CASE WHEN status = 'COMPLETED' THEN amount ELSE 0 END) AS completed,
SUM(CASE WHEN status = 'PENDING' THEN amount ELSE 0 END) AS pending,
SUM(CASE WHEN status = 'FAILED' THEN amount ELSE 0 END) AS failed
FROM transactions
WHERE txn_date >= '2024-01-01'
GROUP BY branch
ORDER BY branch;
Expected output:
branch | completed | pending | failed
----------------+--------------+------------+----------
Delhi-Central | 5000000.00 | 200000.00 | 50000.00
Mumbai-Central | 8000000.00 | 350000.00 | 75000.00
How do you detect gaps in ID ranges?
Use the LEAD window function to find where the next ID is not current+1.
-- Detect gaps in cheque numbers
SELECT cheque_no + 1 AS gap_start, next_no - 1 AS gap_end
FROM (
SELECT cheque_no, LEAD(cheque_no) OVER (ORDER BY cheque_no) AS next_no
FROM cheques
WHERE account_id = 'SAV2001'
) t
WHERE next_no - cheque_no > 1;
Expected output:
gap_start | gap_end
-----------+--------
104 | 106
-- Cheques 104, 105, 106 are missing
How do you find the longest streak of consecutive login days?
This is a gaps-and-islands problem. Subtract a dense row number from the date – consecutive dates produce a constant group value. Then group and count.
-- Longest consecutive login streak per user
WITH streaks AS (
SELECT user_id, login_date,
login_date - (ROW_NUMBER() OVER (
PARTITION BY user_id ORDER BY login_date
))::int AS grp
FROM user_logins
)
SELECT user_id, MIN(login_date) AS streak_start,
MAX(login_date) AS streak_end, COUNT(*) AS streak_days
FROM streaks
GROUP BY user_id, grp
ORDER BY streak_days DESC
LIMIT 1;
Expected output:
user_id | streak_start | streak_end | streak_days
---------+--------------+------------+------------
42 | 2024-01-05 | 2024-01-15 | 11
How do you compute a running total?
Use SUM(amount) OVER (ORDER BY txn_date ROWS UNBOUNDED PRECEDING) for a cumulative sum.
-- Running balance for account SAV2001
SELECT txn_date, amount,
SUM(amount) OVER (ORDER BY txn_date ROWS UNBOUNDED PRECEDING) AS running_balance
FROM transactions
WHERE account_id = 'SAV2001'
ORDER BY txn_date;
Expected output:
txn_date | amount | running_balance
------------+-----------+----------------
2024-01-02 | 100000.00 | 100000.00
2024-01-05 | -30000.00 | 70000.00
2024-01-10 | 50000.00 | 120000.00
2024-01-15 | -20000.00 | 100000.00
How do you find overlapping date ranges?
Two ranges overlap when each starts before the other ends.
-- Find overlapping meeting room bookings
SELECT a.booking_id AS booking_a, b.booking_id AS booking_b,
a.start_time, a.end_time, b.start_time, b.end_time
FROM bookings a
JOIN bookings b ON a.room_id = b.room_id
AND a.booking_id < b.booking_id
AND a.start_time < b.end_time
AND b.start_time < a.end_time;
How do you remove duplicates without DISTINCT?
Number rows within each duplicate key and keep only row number 1. Delete or filter the rest.
-- Delete duplicate customers keeping the one with the lowest id
DELETE FROM customers
WHERE customer_id IN (
SELECT customer_id FROM (
SELECT customer_id,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY customer_id) AS rn
FROM customers
) t
WHERE rn > 1
);
package com.bank.service;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class RunningBalanceService {
public List<String> getRunningBalance(Connection conn,
String accountId) throws SQLException {
String sql = """
SELECT txn_date, amount,
SUM(amount) OVER (ORDER BY txn_date
ROWS UNBOUNDED PRECEDING) AS running_balance
FROM transactions
WHERE account_id = ?
ORDER BY txn_date
""";
List<String> results = new ArrayList<>();
try (PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setString(1, accountId);
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
results.add(String.format("%s: Rs.%s (balance: Rs.%s)",
rs.getDate("txn_date"),
rs.getBigDecimal("amount"),
rs.getBigDecimal("running_balance")));
}
}
}
return results;
}
}
What should a senior engineer know?
The skill is recognizing which abstract pattern a problem maps to. Comparison-within-table is a self-join. Ranking is a window function or correlated count. Rows-to-columns is conditional aggregation. Consecutive runs are gaps-and-islands. Accumulation is a windowed SUM. Deduplication is a partitioned ROW_NUMBER. Once you see the pattern, the query writes itself.
Visual Overview
┌─────────────────────────────────────────────────────┐ │ Pattern Matching: Problem → SQL Shape │ ├─────────────────────────────────────────────────────┤ │ │ │ Problem SQL Pattern │ │ ───────────────── ────────────── │ │ Compare within table → Self-join │ │ Nth highest → DENSE_RANK / correlated │ │ Rows → columns → CASE + GROUP BY │ │ Missing IDs → LEAD + gap check │ │ Consecutive streaks → Gaps-and-islands │ │ Running total → SUM OVER (ORDER BY) │ │ Overlapping ranges → a.start < b.end AND ... │ │ Remove duplicates → ROW_NUMBER + delete > 1 │ │ │ │ Gaps-and-Islands Technique: │ │ date: Jan 5, 6, 7, 8, 10, 11 │ │ row_num: 1, 2, 3, 4, 5, 6 │ │ grp: 4, 4, 4, 4, 5, 5 │ │ ────────────── ────── │ │ streak 1 (4d) streak 2 (2d) │ │ │ └─────────────────────────────────────────────────────┘
Key Takeaway
This question is a catalog of classic SQL patterns, and the skill is recognizing which abstract shape each problem maps to. Comparing a row to a related row in the same table – employees earning more than their manager – is a self-join. Nth-highest salary without window functions is a correlated subquery counting how many distinct salaries are strictly greater. Nth per department uses DENSE_RANK partitioned by department. Turning rows into columns is conditional aggregation with SUM(CASE WHEN ...). Detecting gaps and finding consecutive streaks are gaps-and-islands problems solved by subtracting a row number from the value.
Running totals and cumulative sums use SUM(amount) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING), and a cumulative sum with reset just adds a PARTITION BY on the reset key. Overlapping date ranges test a.start < b.end AND b.start < a.end. Removing duplicates without DISTINCT uses ROW_NUMBER() OVER (PARTITION BY key ORDER BY id) and keeps row number 1.
In Rajesh's National Bank of India reporting work, he uses a self-join pay-equity flag for officers earning more than their manager, a windowed cumulative sum for account-statement running balances, a gaps-and-islands streak for security login reports, and conditional aggregation for branch-level pivot tables. The senior framing is that once you see the shape – comparison equals self-join, ranking equals window or correlated count, rows-to-columns equals CASE, streaks equals gaps-and-islands, accumulation equals SUM OVER, deduplication equals ROW_NUMBER – the specific query almost writes itself.
Remember one sentence: Match the problem to its canonical pattern – self-join, DENSE_RANK, CASE pivot, gaps-and-islands, SUM OVER, and ROW_NUMBER cover almost every classic SQL interview question.
---