SQL Practice — Advanced (Problems 71-110)

P72 — Rank Employees Within Department  ·  Advanced

Rank employees by salary within each department.

SQL

SELECT
    employee_id,
    first_name,
    department_id,
    salary,
    RANK() OVER (
        PARTITION BY department_id
        ORDER BY salary DESC
    ) AS department_rank
FROM employees;

Output (computed against this section's live 9-table dataset)

(showing 10 of 40 rows)

Explanation

Adding PARTITION BY department_id restarts the ranking from 1 at the start of every department, instead of ranking across the whole company — so each department gets its own #1 earner. Interestingly, HR's top earner (Suresh Iyer, ₹160,000) doesn't come close to Engineering's top earner (Arvind, ₹285,000), which is exactly why 'rank #1 in your department' and 'rank #1 company-wide' (Problem 71) can point to very different people.

P73 — Top 3 Employees Per Department  ·  Advanced

Return the top three salary earners from every department.

SQL

WITH ranked AS (
    SELECT
        employee_id,
        first_name,
        department_id,
        salary,
        ROW_NUMBER() OVER (
            PARTITION BY department_id
            ORDER BY salary DESC
        ) AS rn
    FROM employees
)
SELECT *
FROM ranked
WHERE rn <= 3;

Output (computed against this section's live 9-table dataset)

(showing 10 of 18 rows)

Explanation

Window functions like RANK() can't be filtered directly in the same SELECT they're computed in (a WHERE rn <= 3 right after the window function is a syntax error), so the ranking has to be computed inside a CTE first and then filtered in an outer query. ROW_NUMBER() is used here instead of RANK() specifically because the goal is 'give me exactly 3 rows per department' — RANK() could return more than 3 rows for a department with a 3-way tie at the top, since tied rows share a rank number.

P74 — Dense Salary Ranking  ·  Advanced

Assign dense salary ranks across all employees.

SQL

SELECT
    employee_id,
    first_name,
    salary,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

Output (computed against this section's live 9-table dataset)

(showing 10 of 40 rows)

Explanation

DENSE_RANK() behaves like RANK() but never skips a number after a tie — if two people tied for rank 1, the very next distinct salary gets rank 2, not 3. In this dataset, since all 40 salaries happen to be distinct, DENSE_RANK and RANK (Problem 71) produce identical results here; the difference only becomes visible once real ties exist, as it does within some departments in Problem 72.

P75 — Running Revenue Total  ·  Advanced

Calculate cumulative order revenue over time.

SQL

SELECT
    order_date,
    order_id,
    total_amount,
    SUM(total_amount) OVER (
        ORDER BY order_date, order_id
    ) AS running_revenue
FROM orders
WHERE status <> 'Cancelled';

Output (computed against this section's live 9-table dataset)

(showing 10 of 124 rows)

Explanation

SUM(total_amount) OVER (ORDER BY order_date, order_id) (with no PARTITION BY) computes a cumulative total across all non-cancelled orders in chronological order — each row's running_revenue is the sum of every order up to and including that one. The order_id tiebreaker in the ORDER BY matters because several orders can share the same order_date; without a secondary sort key, ties would have an undefined order and the running total could differ between runs.

P76 — Running Revenue Per Customer  ·  Advanced

Calculate each customer's cumulative spending over their order history.

SQL

SELECT
    customer_id,
    order_id,
    order_date,
    total_amount,
    SUM(total_amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_date, order_id
    ) AS running_customer_spend
FROM orders
WHERE status <> 'Cancelled';

Output (computed against this section's live 9-table dataset)

(showing 10 of 124 rows)

Explanation

Adding PARTITION BY customer_id to Problem 75's pattern resets the running total to zero at the start of every customer's order history, so each customer accumulates their own spending independently instead of sharing one company-wide total. Customer 3's first order (₹17,789) establishes their running total, and each subsequent order adds on top of it — this is the building block behind lifetime-value and lifetime-order-count tracking.

P77 — Previous Order Amount  ·  Advanced

Show each customer's previous order amount.

SQL

SELECT
    customer_id,
    order_id,
    order_date,
    total_amount,
    LAG(total_amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_date, order_id
    ) AS previous_order_amount
FROM orders;

Output (computed against this section's live 9-table dataset)

(showing 10 of 149 rows)

Explanation

LAG(total_amount) OVER (PARTITION BY customer_id ORDER BY order_date, order_id) looks one row back within each customer's own chronologically ordered orders — the very first order for any customer has nothing before it, so LAG correctly returns NULL there (visible for customer 3's first order), and every subsequent order shows the amount that came immediately before it.

P78 — Next Order Date  ·  Advanced

Show the next order date for every customer order.

SQL

SELECT
    customer_id,
    order_id,
    order_date,
    LEAD(order_date) OVER (
        PARTITION BY customer_id
        ORDER BY order_date, order_id
    ) AS next_order_date
FROM orders;

Output (computed against this section's live 9-table dataset)

(showing 10 of 149 rows)

Explanation

LEAD is the mirror image of LAG: it looks one row forward instead of back. For customer 4's order on 2024-02-20, LEAD(order_date) returns 2024-03-11 — the date of their very next order — which is exactly the kind of 'time until next purchase' signal that feeds into repeat-purchase and churn analysis later on (P132, P146).

P79 — Difference From Previous Order  ·  Advanced

Calculate the difference between the current order amount and the previous order amount for each customer.

SQL

SELECT
    customer_id,
    order_id,
    total_amount,
    total_amount
      - LAG(total_amount) OVER (
            PARTITION BY customer_id
            ORDER BY order_date, order_id
        ) AS amount_difference
FROM orders;

Output (computed against this section's live 9-table dataset)

(showing 10 of 149 rows)

Explanation

Subtracting LAG(total_amount) from the current row's total_amount turns Problem 77's 'previous amount' into a period-over-period change — a positive amount_difference means the customer spent more than last time, negative means less. Just like LAG itself, the very first order for each customer has no previous value to subtract, so its difference is NULL rather than some fallback like 0 — a NULL here genuinely means 'not applicable', not 'zero change'.

P80 — Department Salary Percentage  ·  Advanced

Find what percentage of total department salary each employee earns.

SQL

SELECT
    employee_id,
    first_name,
    department_id,
    salary,
    ROUND(
        100.0 * salary /
        SUM(salary) OVER (PARTITION BY department_id), 2
    ) AS salary_percentage
FROM employees;

Output (computed against this section's live 9-table dataset)

(showing 10 of 40 rows)

Explanation

SUM(salary) OVER (PARTITION BY department_id) computes each department's total payroll without collapsing the individual employee rows — unlike a GROUP BY, every employee still appears individually, just now also carrying their department's total as a repeated value on every row in that partition. Dividing each employee's own salary by that shared total (and multiplying by 100) shows how much of the department's payroll budget one person represents: Karan Malhotra alone accounts for 30.51% of Sales' total payroll, reflecting how few employees are in that department relative to how senior he is.

P81 — Highest Paid Employee Per Department  ·  Advanced

Return the highest-paid employee from each department.

SQL

WITH ranked AS (
    SELECT
        e.*,
        ROW_NUMBER() OVER (
            PARTITION BY department_id
            ORDER BY salary DESC, employee_id
        ) AS rn
    FROM employees e
)
SELECT *
FROM ranked
WHERE rn = 1;

Output (computed against this section's live 9-table dataset)

(6 rows total)

Explanation

This is Problem 73's pattern applied without a category-size cap — ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC, employee_id) gives exactly one 'rank 1' row per department, and filtering WHERE rn = 1 returns exactly 6 rows, one highest-paid employee per department. The employee_id tiebreaker after salary DESC guarantees a single deterministic winner even if two people in the same department were ever tied on salary.

P82 — Second Highest Salary Per Department  ·  Advanced

Find the second highest distinct salary in each department.

SQL

WITH ranked AS (
    SELECT
        department_id,
        salary,
        DENSE_RANK() OVER (
            PARTITION BY department_id
            ORDER BY salary DESC
        ) AS rnk
    FROM employees
)
SELECT department_id, salary
FROM ranked
WHERE rnk = 2;

Output (computed against this section's live 9-table dataset)

(6 rows total)

Explanation

Using DENSE_RANK instead of ROW_NUMBER here matters: if two people in the same department were tied for the highest salary, ROW_NUMBER would arbitrarily assign one of them rank 2 (making them the 'second highest' even though they're actually tied for first), whereas DENSE_RANK correctly treats the tie as both being rank 1 and moves the true second-highest distinct salary to rank 2. All 6 departments have a well-defined second-highest salary in this dataset.

P83 — Employees Above Department Average  ·  Advanced

Use a window function to find employees earning above their department average.

SQL

SELECT *
FROM (
    SELECT
        employee_id,
        first_name,
        department_id,
        salary,
        AVG(salary) OVER (
            PARTITION BY department_id
        ) AS department_avg
    FROM employees
) x
WHERE salary > department_avg;

Output (computed against this section's live 9-table dataset)

(showing 10 of 15 rows)

Explanation

This uses a window function (AVG(salary) OVER (PARTITION BY department_id)) inside a subquery so the department average can be attached to every employee row, then filters in an outer query on salary > department_avg — functionally the same result as the correlated subquery in Problem 42, but computed differently: the window function calculates every department's average once and broadcasts it to all rows in that partition, rather than re-running a subquery once per employee. 15 employees clear their department's average, matching Problem 42 exactly.

P84 — Customer Order Number  ·  Advanced

Assign a sequential order number to every customer's orders.

SQL

SELECT
    customer_id,
    order_id,
    order_date,
    ROW_NUMBER() OVER (
        PARTITION BY customer_id
        ORDER BY order_date, order_id
    ) AS customer_order_number
FROM orders;

Output (computed against this section's live 9-table dataset)

(showing 10 of 149 rows)

Explanation

ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date, order_id) numbers each customer's own orders 1, 2, 3... in chronological order — customer 3's earliest order (2024-03-04) is their order #1, the next their #2, and so on. This numbering is exactly what Problem 85 and Problem 86 filter on to isolate a single order (first or latest) per customer.

P85 — First Order for Each Customer  ·  Advanced

Return the complete row for each customer's first order.

SQL

WITH ranked AS (
    SELECT
        o.*,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY order_date, order_id
        ) AS rn
    FROM orders o
)
SELECT *
FROM ranked
WHERE rn = 1;

Output (computed against this section's live 9-table dataset)

(showing 10 of 28 rows)

Explanation

Filtering Problem 84's numbering down to rn = 1 returns the complete row (every column, via o.*) for each customer's very first order — not just the date, but the full order record including status and amount. 28 rows come back, one per customer who has placed at least one order.

P86 — Latest Order for Each Customer  ·  Advanced

Return the complete latest order row for each customer.

SQL

WITH ranked AS (
    SELECT
        o.*,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY order_date DESC, order_id DESC
        ) AS rn
    FROM orders o
)
SELECT *
FROM ranked
WHERE rn = 1;

Output (computed against this section's live 9-table dataset)

(showing 10 of 28 rows)

Explanation

Reversing the ORDER BY to order_date DESC, order_id DESC before numbering flips which order gets rn = 1 — now it's each customer's most recent order instead of their earliest. Interestingly, this also picks up the orphan order (customer_id = 9999, which doesn't match any real row in customers) as its own 'customer's' latest order — a reminder that this query only ever looks at orders in isolation, so a customer_id that doesn't actually exist in the customers table can still show up here.

P87 — Top 10 Percent Employees  ·  Advanced

Find employees belonging to the top 10% by salary.

SQL

WITH ranked AS (
    SELECT
        e.*,
        NTILE(10) OVER (ORDER BY salary DESC) AS salary_bucket
    FROM employees e
)
SELECT *
FROM ranked
WHERE salary_bucket = 1;

Output (computed against this section's live 9-table dataset)

(4 rows total)

Explanation

NTILE(10) splits the entire ordered result into 10 roughly-equal-sized buckets, numbered 1 (highest salaries, since the ordering is DESC) through 10 (lowest) — with 40 employees split 10 ways, each bucket holds exactly 4. Filtering WHERE salary_bucket = 1 returns the top decile: the 4 highest-paid people in the company, led by Arvind, Meera, and Anil Bhatt.

P88 — Quartile of Product Prices  ·  Advanced

Divide products into four price groups.

SQL

SELECT
    product_id,
    product_name,
    price,
    NTILE(4) OVER (ORDER BY price) AS price_quartile
FROM products;

Output (computed against this section's live 9-table dataset)

(showing 10 of 28 rows)

Explanation

NTILE(4) divides the 28 products into 4 roughly-equal price quartiles (7 products each) after sorting by price ascending — quartile 1 holds the cheapest products (starting with Atomic Habits at ₹499), while quartile 4 would hold the most expensive. This is a common way to bucket a continuous value like price into a small number of discrete tiers for reporting or filtering.

P89 — Monthly Revenue With Previous Month  ·  Advanced

Show monthly revenue and the previous month's revenue.

SQL

WITH monthly AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        SUM(total_amount) AS revenue
    FROM orders
    WHERE status <> 'Cancelled'
    GROUP BY DATE_TRUNC('month', order_date)
)
SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS previous_month_revenue
FROM monthly
ORDER BY month;

Output (computed against this section's live 9-table dataset)

(showing 10 of 24 rows)

Explanation

The CTE first collapses orders into one revenue figure per month, then LAG(revenue) OVER (ORDER BY month) — with no PARTITION BY, since there's only one revenue series here — looks back to the immediately preceding month's total. January 2024 (this dataset's first month) correctly has no previous_month_revenue at all (NULL), since there's no month before it in the data; February's previous_month_revenue is populated with January's ₹69,565.

P90 — Month-over-Month Growth  ·  Advanced

Calculate monthly revenue growth percentage.

SQL

WITH monthly AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        SUM(total_amount) AS revenue
    FROM orders
    WHERE status <> 'Cancelled'
    GROUP BY DATE_TRUNC('month', order_date)
),
comparison AS (
    SELECT
        month,
        revenue,
        LAG(revenue) OVER (ORDER BY month) AS previous_revenue
    FROM monthly
)
SELECT
    month,
    revenue,
    ROUND(
        100.0 * (revenue - previous_revenue) /
NULLIF(previous_revenue, 0),
        2
    ) AS growth_percentage
FROM comparison
ORDER BY month;

Output (computed against this section's live 9-table dataset)

(showing 10 of 24 rows)

Explanation

Building on Problem 89's LAG, this computes (revenue - previous_revenue) / previous_revenue * 100 to express the change as a percentage rather than a raw difference — NULLIF(previous_revenue, 0) guards against a divide-by-zero error in case any month had exactly ₹0 in prior revenue (none did here, but it's cheap insurance). Revenue actually fell 62.79% from January to February 2024 in this dataset before rebounding sharply (+184.55%) in March — real month-to-month swings like this are exactly why growth percentages, not raw revenue, are often what gets tracked on a dashboard.

P91 — Three-Month Moving Average  ·  Advanced

Calculate a three-month moving average of revenue.

SQL

WITH monthly AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        SUM(total_amount) AS revenue
    FROM orders
    WHERE status <> 'Cancelled'
    GROUP BY DATE_TRUNC('month', order_date)
)
SELECT
    month,
    revenue,
    AVG(revenue) OVER (
        ORDER BY month
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_average
FROM monthly;

Output (computed against this section's live 9-table dataset)

(showing 10 of 24 rows)

Explanation

AVG(revenue) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) computes a genuine 3-month rolling window: for any given month, it averages that month's revenue together with the two months immediately before it. The very first month (January 2024) only has itself to average (no prior months exist yet), so its moving average equals its own revenue exactly; only from the third month onward does the window contain a full three data points, which smooths out month-to-month noise like the sharp February dip seen in Problem 90.

P92 — Customers With Consecutive Orders  ·  Advanced

Find customers who placed orders on consecutive calendar days.

SQL

WITH x AS (
    SELECT
        customer_id,
        order_date,
        LAG(order_date) OVER (
            PARTITION BY customer_id
            ORDER BY order_date
        ) AS previous_date
    FROM orders
)
SELECT DISTINCT customer_id
FROM x
WHERE order_date = previous_date + INTERVAL '1 day';

Output (computed against this section's live 9-table dataset)

(1 row total)

Explanation

LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) finds each customer's immediately preceding order date, and the outer query checks whether the current order landed exactly one calendar day later. Only customer 1 qualifies in this dataset — they placed one order on 2025-03-14 and another the very next day, 2025-03-15, a pattern deliberately built into the data to demonstrate this exact technique.

P93 — Duplicate Orders by Customer and Date  ·  Advanced

Find customers who placed more than one order on the same date.

SQL

SELECT
    customer_id,
    order_date,
    COUNT(*) AS order_count
FROM orders
GROUP BY customer_id, order_date
HAVING COUNT(*) > 1;

Output (computed against this section's live 9-table dataset)

(1 row total)

Explanation

Unlike Problem 92 (which looks for orders exactly one day apart), this groups by both customer_id and the exact same order_date to catch same-day duplicates — a signal that could indicate a genuine repeat purchase, a data-entry duplicate, or a retried checkout. Customer 2 placed two separate orders on 2025-06-10, deliberately included in this dataset to exercise exactly this check.

P94 — Products With Revenue Above Category Average  ·  Advanced

Find products whose revenue is above the average product revenue in their category.

SQL

WITH product_revenue AS (
    SELECT
        p.product_id,
        p.product_name,
        p.category,
        COALESCE(SUM(
            CASE WHEN o.status <> 'Cancelled'
                 THEN oi.quantity * oi.unit_price ELSE 0 END
        ), 0) AS revenue
    FROM products p
    LEFT JOIN order_items oi ON p.product_id = oi.product_id
    LEFT JOIN orders o ON oi.order_id = o.order_id
    GROUP BY p.product_id, p.product_name, p.category
)
SELECT *
FROM (
    SELECT
        pr.*,
        AVG(revenue) OVER (PARTITION BY category) AS category_avg
    FROM product_revenue pr
) x
WHERE revenue > category_avg;

Output (computed against this section's live 9-table dataset)

(8 rows total)

Explanation

Computing per-product revenue with conditional aggregation first (so cancelled orders never contribute), the outer window function then attaches each category's average product revenue to every row in that category via AVG(revenue) OVER (PARTITION BY category), and the final WHERE keeps only products beating their own category's average. The 27-inch 4K Monitor dominates Electronics so heavily (₹849,966 vs. a category average of ₹219,770) that it single-handedly makes the category average hard for any other Electronics product to clear.

P95 — Customer Spending Rank  ·  Advanced

Rank customers by total spending.

SQL

WITH spending AS (
    SELECT
        c.customer_id,
        c.customer_name,
        COALESCE(SUM(
            CASE WHEN o.status <> 'Cancelled'
                 THEN o.total_amount ELSE 0 END
        ), 0) AS total_spent
    FROM customers c
    LEFT JOIN orders o ON c.customer_id = o.customer_id
    GROUP BY c.customer_id, c.customer_name
)
SELECT
    *,
    RANK() OVER (ORDER BY total_spent DESC) AS spending_rank
FROM spending;

Output (computed against this section's live 9-table dataset)

(showing 10 of 30 rows)

Explanation

This wraps Problem 70's lifetime-spending calculation in a RANK() OVER (ORDER BY total_spent DESC) — since total_spent is computed first inside the spending CTE, the ranking operates on the already-aggregated per-customer totals rather than raw order rows. Vikas Rana ranks #1 at ₹316,810, matching the top of Problem 70's list exactly, since both queries compute lifetime value the same way.

P96 — Top 3 Customers Per Country  ·  Advanced

Find the three highest-spending customers in each country.

SQL

WITH spending AS (
    SELECT
        c.customer_id,
        c.customer_name,
        c.country,
        COALESCE(SUM(
            CASE WHEN o.status <> 'Cancelled'
                 THEN o.total_amount ELSE 0 END
        ), 0) AS total_spent
    FROM customers c
    LEFT JOIN orders o ON c.customer_id = o.customer_id
    GROUP BY c.customer_id, c.customer_name, c.country
),
ranked AS (
    SELECT *,
        ROW_NUMBER() OVER (
            PARTITION BY country
            ORDER BY total_spent DESC
        ) AS rn
    FROM spending
)
SELECT *
FROM ranked
WHERE rn <= 3;

Output (computed against this section's live 9-table dataset)

(showing 10 of 13 rows)

Explanation

Layering PARTITION BY country onto the ranking from Problem 95 restarts the rank at 1 within every country, and filtering rn <= 3 keeps each country's top 3 spenders — small countries with only 1-2 customers (like Canada or the UK) simply return all of their customers, correctly ranked, since there aren't three to compete for the podium. Notably, some customers with ₹0 in lifetime spending still appear here (rank 2 in Canada, rank 2 in the UK) — they have zero spend but are still their country's second-highest, which is a fair answer, just a low bar.

P97 — Employee Project Hours  ·  Advanced

Calculate total project hours for each employee.

SQL

SELECT
    e.employee_id,
    e.first_name,
    COALESCE(SUM(ep.hours_worked), 0) AS total_hours
FROM employees e
LEFT JOIN employee_projects ep
    ON e.employee_id = ep.employee_id
GROUP BY e.employee_id, e.first_name;

Output (computed against this section's live 9-table dataset)

(showing 10 of 40 rows)

Explanation

LEFT JOIN plus COALESCE(SUM(...), 0) — the same pattern from Problem 36 — ensures every employee appears with a real number, including the many who were never assigned to any project at all (they show 0.0 total hours rather than being silently dropped). Rohan (employee 14) leads with 400 hours logged across his project assignments.

P98 — Most Active Project  ·  Advanced

Find the project with the highest total employee hours.

SQL

SELECT
    p.project_id,
    p.project_name,
    SUM(ep.hours_worked) AS total_hours
FROM projects p
JOIN employee_projects ep
    ON p.project_id = ep.project_id
GROUP BY p.project_id, p.project_name
ORDER BY total_hours DESC
LIMIT 1;

Output (computed against this section's live 9-table dataset)

(1 row total)

Explanation

Grouping employee_projects by project_id (via a join to projects for the name) and summing hours_worked, then sorting descending and keeping just the top row, finds the single project that consumed the most person-hours. The Data Warehouse Migration project leads by a wide margin at 1,190 total hours — unsurprising given it also carries the dataset's largest team.

P99 — Employees Working on Multiple Projects  ·  Advanced

Find employees assigned to at least two projects.

SQL

SELECT
    employee_id,
    COUNT(DISTINCT project_id) AS project_count
FROM employee_projects
GROUP BY employee_id
HAVING COUNT(DISTINCT project_id) >= 2;

Output (computed against this section's live 9-table dataset)

(8 rows total)

Explanation

COUNT(DISTINCT project_id) per employee (note: DISTINCT matters in principle, though employee_projects has no duplicate employee/project pairs here) filtered by HAVING >= 2 finds employees stretched across multiple projects at once. 8 employees qualify, each juggling exactly two project assignments in this dataset — a realistic cross-section of a company's most in-demand engineers and managers.

P100 — Projects With No Employees  ·  Advanced

Find projects that have no employee assignments.

SQL

SELECT p.project_id, p.project_name
FROM projects p
LEFT JOIN employee_projects ep
    ON p.project_id = ep.project_id
WHERE ep.employee_id IS NULL;

Output (computed against this section's live 9-table dataset)

(1 row total)

Explanation

The same LEFT JOIN ... WHERE ... IS NULL 'find unmatched rows' pattern used for products (P35) and customers (P33), now applied to projects against employee_projects. Exactly one project — Legacy System Decommission — has no employee assignments at all, deliberately left that way in this dataset as a completed, already-wound-down initiative.

P101 — Employees Who Worked on the Largest Project  ·  Advanced

Find employees assigned to the project with the highest budget.

SQL

SELECT DISTINCT
    e.employee_id,
    e.first_name,
    p.project_name,
    p.budget
FROM employees e
JOIN employee_projects ep ON e.employee_id = ep.employee_id
JOIN projects p ON ep.project_id = p.project_id
WHERE p.budget = (
    SELECT MAX(budget)
    FROM projects
);

Output (computed against this section's live 9-table dataset)

(4 rows total)

Explanation

The subquery SELECT MAX(budget) FROM projects finds the single largest budget (₹3,200,000, belonging to the Fraud Detection Engine), and the outer query then finds every employee who worked on that specific project — SELECT DISTINCT matters because an employee could in principle have multiple employee_projects rows for the same project, which would otherwise duplicate them in the output. 4 employees worked on it, including manager Ananya Singh.

P102 — Employee Count Per Project  ·  Advanced

Show every project and the number of assigned employees.

SQL

SELECT
    p.project_id,
    p.project_name,
    COUNT(DISTINCT ep.employee_id) AS employee_count
FROM projects p
LEFT JOIN employee_projects ep
    ON p.project_id = ep.project_id
GROUP BY p.project_id, p.project_name;

Output (computed against this section's live 9-table dataset)

(8 rows total)

Explanation

LEFT JOIN plus COUNT(DISTINCT ep.employee_id) ensures every project appears in the result, even Legacy System Decommission (Problem 100's zero-employee project) — for that row, the COUNT correctly returns 0 rather than the row disappearing entirely, because COUNT (unlike SUM) counts non-null values, and COUNT(*) would incorrectly count 1 due to the single unmatched NULL row the LEFT JOIN produces.

P103 — Department With Highest Average Salary  ·  Advanced

Find the department with the highest average employee salary.

SQL

SELECT
    department_id,
    AVG(salary) AS average_salary
FROM employees
GROUP BY department_id
ORDER BY average_salary DESC
LIMIT 1;

Output (computed against this section's live 9-table dataset)

(1 row total)

Explanation

This reuses Problem 26's per-department average salary calculation, sorts it descending, and keeps only the top row — Engineering has both the highest average salary (~₹138,589) and, as seen in Problem 67, the widest range, since it houses both the CEO and several of the newest, lowest-paid hires.

P104 — Customer's First and Last Order  ·  Advanced

Show first order date, last order date, and order count per customer.

SQL

SELECT
    customer_id,
    MIN(order_date) AS first_order,
    MAX(order_date) AS last_order,
    COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;

Output (computed against this section's live 9-table dataset)

(showing 10 of 28 rows)

Explanation

Three aggregates computed together — MIN, MAX, and COUNT — summarize each customer's entire order history in one row: when they first ordered, when they most recently ordered, and how many orders they've placed in between. Customer 1 stands out with 14 orders spanning from February 2024 to September 2025, one of this dataset's most active buyers.

P105 — Customer Retention After First Order  ·  Advanced

Find customers who placed another order after their first order.

SQL

WITH order_history AS (
    SELECT
        customer_id,
        order_date,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY order_date, order_id
        ) AS rn
    FROM orders
)
SELECT customer_id
FROM order_history
GROUP BY customer_id
HAVING COUNT(*) > 1;