Tricky Queries and Puzzles

This page is a set of classic hard-mode SQL problems, of exactly the kind that show up in Oracle-focused interviews and real production incidents. Each one is posed as a problem first, then solved with a complete, correct query and an explanation of the underlying technique. Most reuse the employees/departments schema from earlier in this track; a couple introduce a small standalone table where that makes the problem clearer.

a) Find the Nth highest salary#

Problem: find the 3rd highest distinct salary in the employees table — not the 3rd row when sorted, the 3rd distinct salary value, so two employees tied for 1st don't push the real answer down to 4th place.

The robust solution uses DENSE_RANK() (see the analytic-functions page), which assigns the same rank to tied values and never skips a rank afterward:

SELECT salary
FROM (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
    FROM employees
)
WHERE salary_rank = 3;

A shorter alternative using OFFSET/FETCH (see the queries-and-joins page) works too, provided duplicate salaries are removed first with DISTINCT — without it, two employees tied for the highest salary would count as two separate "rows" for the offset, silently shifting every rank below them by one:

SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY;

The DENSE_RANK() version is the one to reach for whenever the "Nth highest" needs to be correct in the presence of ties — it's explicit about what "Nth" means, rather than relying on DISTINCT to have already collapsed the duplicates the right way.

b) Find duplicate rows#

Problem: a customer_import staging table was loaded from an external CSV feed with no unique constraint on email, and the same customer was accidentally loaded more than once under some rows. Find every email address that appears more than once.

CREATE TABLE customer_import (
    row_id NUMBER,
    email  VARCHAR2(100),
    name   VARCHAR2(100)
);

SELECT email, COUNT(*) AS occurrences
FROM customer_import
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY occurrences DESC;

The technique is GROUP BY the column that should have been unique, then HAVING COUNT(*) > 1 to keep only the groups that actually repeated — HAVING filters on the aggregated result, which is exactly why it has to be HAVING and not WHERE (WHERE runs before grouping happens, and COUNT(*) doesn't exist yet at that point).

c) Running total (cumulative sum)#

Problem: show the cumulative headcount of department 20, ordered by hire date — for each hire, how many employees department 20 had in total as of that date.

SELECT employee_id, last_name, hire_date,
       COUNT(*) OVER (ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_headcount
FROM employees
WHERE department_id = 20
ORDER BY hire_date;

This is the same analytic-function technique covered in depth on the functions-and-analytic-queries page — an ORDER BY inside OVER() turns an ordinary aggregate into a running one, accumulating from the start of the result set up through the current row. Before analytic functions existed, the same result required a correlated subquery counting all prior rows for every single row — correct, but far slower on a large table since it re-scans and re-counts for every output row instead of computing the total in one ordered pass:

-- The old way — still works, but re-executes a COUNT for every row
SELECT e1.employee_id, e1.last_name, e1.hire_date,
       (SELECT COUNT(*) FROM employees e2
        WHERE e2.department_id = e1.department_id AND e2.hire_date <= e1.hire_date) AS cumulative_headcount
FROM employees e1
WHERE e1.department_id = 20
ORDER BY e1.hire_date;

d) Gaps and islands (consecutive date ranges)#

Problem: an attendance table logs one row per employee per day they showed up to work. Find each unbroken streak ("island") of consecutive work days per employee — the start date, end date, and length of each streak.

CREATE TABLE attendance (
    employee_id NUMBER,
    work_date   DATE
);

The classic technique: number each employee's rows in date order with ROW_NUMBER(), then subtract that row number (in days) from the actual date. Within one unbroken streak of consecutive days, that subtraction produces the same resulting value for every row in the streak — because both the date and the row number increase by exactly 1 each day — which turns "find consecutive dates" into an ordinary GROUP BY on that computed value:

WITH numbered AS (
    SELECT employee_id, work_date,
           work_date - ROW_NUMBER() OVER (PARTITION BY employee_id ORDER BY work_date) AS island_group
    FROM attendance
)
SELECT employee_id,
       MIN(work_date) AS streak_start,
       MAX(work_date) AS streak_end,
       COUNT(*)       AS streak_length
FROM numbered
GROUP BY employee_id, island_group
ORDER BY employee_id, streak_start;

work_date - ROW_NUMBER() ... is where the technique earns the name "gaps and islands" — every row inside one continuous island of dates lands on the exact same island_group value, so grouping by it directly produces one summary row per streak, with MIN/MAX/COUNT giving the streak's boundaries and length for free.

e) Pivot rows into columns#

Problem: show a one-row-per-job-title report with a separate column for each department's headcount in that job title — turning department values that live in rows into columns in the output.

The manual technique uses conditional (CASE) aggregation — a SUM/COUNT wrapped around a CASE that only counts rows matching one specific department:

SELECT job_title,
       COUNT(CASE WHEN department_id = 10 THEN 1 END) AS dept_10_count,
       COUNT(CASE WHEN department_id = 20 THEN 1 END) AS dept_20_count,
       COUNT(CASE WHEN department_id = 30 THEN 1 END) AS dept_30_count
FROM employees
GROUP BY job_title;

Oracle also has a native PIVOT clause (11g onward) that expresses the same idea more declaratively, without hand-writing one CASE per column:

SELECT *
FROM (
    SELECT job_title, department_id FROM employees
)
PIVOT (
    COUNT(department_id)
    FOR department_id IN (10 AS dept_10, 20 AS dept_20, 30 AS dept_30)
);

Both produce identical output. PIVOT reads more clearly once there are many columns to pivot into, but requires knowing the exact set of values (10, 20, 30) up front at query-writing time — same limitation the manual CASE version has, since neither approach can produce a column for a department that didn't exist when the query was written.

f) Hierarchical query: walking an org chart with CONNECT BY#

Problem: given a specific employee, list their entire chain of command — their manager, that manager's manager, and so on up to the top of the organization.

This is exactly what employees.manager_id (a self-referencing foreign key, introduced on the data-types page) is for, walked with Oracle's CONNECT BY PRIOR hierarchical query syntax:

SELECT employee_id, last_name, manager_id, LEVEL AS depth
FROM employees
START WITH employee_id = 205
CONNECT BY PRIOR manager_id = employee_id;

EMPLOYEE_ID  LAST_NAME  MANAGER_ID  DEPTH
205          Bianchi    202         1
202          Lindgren   201         2
201          Okafor     (null)      3

• START WITH — the anchor row(s) to begin from, exactly like the anchor of a recursive CTE.
• CONNECT BY PRIOR manager_id = employee_id — the rule linking each row to the next: the next row in the walk must have employee_id equal to this row's manager_id, which is precisely "go find my manager" repeated until there's no manager left (manager_id IS NULL).
• LEVEL — a pseudo-column giving the depth of each row in the walk, starting at 1 for the anchor row.

Reversing the direction of the same table walks downward instead — every employee reporting (directly or indirectly) to a given manager:

SELECT employee_id, last_name, manager_id, LEVEL AS depth,
       LPAD(' ', 2 * (LEVEL - 1)) || last_name AS org_chart_display
FROM employees
START WITH employee_id = 201
CONNECT BY PRIOR employee_id = manager_id;

Swapping which side PRIOR is on is the entire difference between "walk up to my managers" and "walk down to my subordinates" — a detail worth internalizing rather than memorizing by trial and error. LPAD combined with LEVEL is the standard trick for rendering an indented, tree-shaped org chart directly out of a flat query result. Two more hierarchical-query tools worth knowing: CONNECT_BY_ROOT prefixes any column with the value from the anchor row at the top of that branch, and SYS_CONNECT_BY_PATH(column, separator) builds a full breadcrumb path (e.g., '/Okafor/Lindgren/Bianchi') from the root down to the current row.

g) Delete duplicate rows, keeping only one copy#

Problem: using the same customer_import table from problem (b), permanently remove the duplicate rows for each repeated email address, keeping exactly one row per email.

This is the one genuinely legitimate everyday use for ROWID as a tool (see the data-types page's caution against relying on it as a stable identifier in general) — every row has a distinct, comparable ROWID, which makes "keep the row with the smallest ROWID, delete the rest" a reliable way to pick exactly one survivor per duplicate group:

DELETE FROM customer_import c
WHERE ROWID > (
    SELECT MIN(d.ROWID)
    FROM customer_import d
    WHERE d.email = c.email
);

For every group of rows sharing the same email, this keeps the one with the lowest ROWID and deletes every other row in that group — leaving exactly one row per distinct email behind. An equivalent, arguably more readable modern approach uses ROW_NUMBER() instead of ROWID comparisons, and generalizes more easily to "keep the most recently loaded row" or some other tiebreak rule rather than an arbitrary physical-address ordering:

DELETE FROM customer_import
WHERE row_id IN (
    SELECT row_id FROM (
        SELECT row_id,
               ROW_NUMBER() OVER (PARTITION BY email ORDER BY row_id) AS rn
        FROM customer_import
    )
    WHERE rn > 1
);

Both delete the same rows here; the ROW_NUMBER() version is worth preferring in new code specifically because the ORDER BY inside OVER() can express which copy to keep (oldest, newest, lowest ID) explicitly, rather than relying on physical row placement.

Common mistakes#

• Using plain RANK() instead of DENSE_RANK() for "Nth highest" when ties are possible — RANK() skips positions after a tie, so the "3rd highest" can silently become a value that most people watching the data wouldn't call 3rd place.
• Filtering with WHERE COUNT(*) > 1 instead of HAVING COUNT(*) > 1 when looking for duplicates — WHERE is evaluated before grouping, so the aggregate doesn't exist yet at that point and Oracle rejects the query outright.
• Getting the two sides of CONNECT BY PRIOR backwards and silently walking the wrong direction (subordinates instead of managers, or vice versa) — the query still runs and returns a hierarchy, just not the one intended, which makes this an easy mistake to miss without checking the output carefully.
• Running the duplicate-delete query without first running the equivalent SELECT to see exactly which rows would be removed — there's no undo once a DELETE commits, and confirming the target set first costs nothing.