SQL Practice — Expert & Real-World (Problems 111-150)

P112 — Customers Who Bought at Least One Product From Every Category  ·  Expert

Find customers who purchased products from every product category.

SQL

WITH customer_categories AS (
    SELECT
        o.customer_id,
        COUNT(DISTINCT p.category) AS category_count
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    JOIN products p ON oi.product_id = p.product_id
    WHERE o.status <> 'Cancelled'
    GROUP BY o.customer_id
)
SELECT customer_id
FROM customer_categories
WHERE category_count = (
    SELECT COUNT(DISTINCT category)
    FROM products
);

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

(7 rows total)

Explanation

This relaxes Problem 111's bar considerably: instead of buying every product in one category, a customer just needs to have bought something from every category (5 total: Electronics, Sports, Home & Kitchen, Clothing, Books). That's a much easier target for a customer with several varied orders to hit naturally, and 7 of the 30 customers manage it in this dataset.

P113 — Most Popular Product Pair  ·  Expert

Find the pair of products most frequently purchased together in the same order.

SQL

SELECT
    oi1.product_id AS product_a,
    oi2.product_id AS product_b,
    COUNT(*) AS pair_count
FROM order_items oi1
JOIN order_items oi2
    ON oi1.order_id = oi2.order_id
   AND oi1.product_id < oi2.product_id
GROUP BY oi1.product_id, oi2.product_id
ORDER BY pair_count DESC
LIMIT 1;

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

(1 row total)

Explanation

The self-join oi1.order_id = oi2.order_id AND oi1.product_id < oi2.product_id pairs up every two different products that appeared together in the same order, counts how often each specific pair recurs, and the < (rather than <>) is what prevents each pair from being counted twice in both directions (product 1 with product 4, and product 4 with product 1). Products 1 and 4 — the Wireless Mouse and the USB-C Hub — come out as the single most frequent pairing, appearing together in 40 separate orders, a strong affinity deliberately built into this dataset's order-generation logic.

P114 — Customers Who Bought a Product but Never Again  ·  Expert

Find customers whose last order was exactly their first order.

SQL

SELECT customer_id
FROM orders
GROUP BY customer_id
HAVING MIN(order_date) = MAX(order_date);

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

(6 rows total)

Explanation

MIN(order_date) = MAX(order_date) per customer is only true when a customer has exactly one order — if they had two or more, their earliest and latest order dates would necessarily differ. 6 customers in this dataset placed exactly one order and never returned, including customers 23-25, three accounts deliberately generated as single-order buyers.

P115 — Longest Gap Between Customer Orders  ·  Expert

Find the maximum number of days between consecutive orders for each customer.

SQL

WITH gaps AS (
    SELECT
        customer_id,
        order_date,
        order_date - LAG(order_date) OVER (
            PARTITION BY customer_id
            ORDER BY order_date
        ) AS gap_days
    FROM orders
)
SELECT
    customer_id,
    MAX(gap_days) AS longest_gap
FROM gaps
GROUP BY customer_id;

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

(showing 10 of 28 rows)

Explanation

LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) computes the days since the previous order for every order a customer placed, and MAX(gap_days) per customer then finds their single longest quiet stretch. Customer 17 has the largest gap in the dataset at 431 days between two orders — more than a year of inactivity before they came back.

P116 — Customers With Increasing Order Values  ·  Expert

Find customers whose order amount increased compared with their previous order every time after the first order.

SQL

WITH x AS (
    SELECT
        customer_id,
        order_id,
        total_amount,
        LAG(total_amount) OVER (
            PARTITION BY customer_id
            ORDER BY order_date, order_id
        ) AS previous_amount
    FROM orders
),
summary AS (
    SELECT
        customer_id,
        COUNT(*) FILTER (
            WHERE previous_amount IS NOT NULL
              AND total_amount > previous_amount
        ) AS increases,
        COUNT(*) FILTER (
            WHERE previous_amount IS NOT NULL
        ) AS comparisons
    FROM x
    GROUP BY customer_id
)
SELECT customer_id
FROM summary
WHERE comparisons > 0
  AND increases = comparisons;

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

(3 rows total)

Explanation

The FILTER (WHERE ...) clause lets a single COUNT(*) aggregate only rows matching an extra condition, without needing separate CASE WHEN expressions — increases counts comparisons where the order grew, comparisons counts all valid (non-first) comparisons, and a customer only qualifies if every single comparison was an increase (increases = comparisons, with comparisons > 0 guarding against customers with only one order, who'd otherwise trivially pass with 0 = 0). Only 3 customers in this dataset had every one of their orders strictly larger than the one before it.

P117 — Employees With No Projects in the Last Year  ·  Expert

Assuming the reference date is 2026-01-01, find employees who worked on no projects during 2025.

SQL

SELECT e.employee_id, e.first_name
FROM employees e
LEFT JOIN employee_projects ep
    ON e.employee_id = ep.employee_id
LEFT JOIN projects p
    ON ep.project_id = p.project_id
    AND p.start_date >= '2025-01-01'
    AND p.start_date < '2026-01-01'
WHERE p.project_id IS NULL;

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

(showing 10 of 39 rows)

Explanation

This query has a real, worth-knowing limitation. Its LEFT JOIN ... WHERE p.project_id IS NULL pattern correctly finds employees with zero project rows at all, but for employees with multiple project assignments, it filters row by row rather than per employee — so an employee who worked on one project in 2025 and one project before 2025 still has a row where the date-filtered join fails to match (their pre-2025 project), and that row alone is enough to let them slip into the 'no projects in 2025' result. In this dataset, employees 4, 6, 9, 32, and 38 all genuinely worked on a 2025 project but leak into the 39-row result anyway for exactly this reason — only employees 11, 12, 17, and 30 (who happened to have only a single, 2025-dated project) are excluded correctly. A reliable version of this check would use NOT EXISTS (SELECT 1 FROM employee_projects ep JOIN projects p ON ... WHERE ep.employee_id = e.employee_id AND p.start_date >= '2025-01-01' AND p.start_date < '2026-01-01') instead, which evaluates the condition per employee rather than per matched row.

P118 — Project Utilization  ·  Expert

Calculate each project's percentage of the total hours worked across all projects.

SQL

WITH project_hours AS (
    SELECT
        p.project_id,
        p.project_name,
        COALESCE(SUM(ep.hours_worked), 0) AS hours
    FROM projects p
    LEFT JOIN employee_projects ep
        ON p.project_id = ep.project_id
    GROUP BY p.project_id, p.project_name
)
SELECT
    *,
    ROUND(
        100.0 * hours / NULLIF(SUM(hours) OVER (), 0),
        2
    ) AS utilization_percentage
FROM project_hours;

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

(8 rows total)

Explanation

SUM(hours) OVER () (no partition) totals hours across every project to use as a shared denominator, the same 'grand total window function' trick as Problem 108's revenue-share calculation — NULLIF guards against division by zero if the grand total were ever 0. Data Warehouse Migration, the project with the most hours logged, would claim the largest utilization share, while the Internal HR Portal (already completed, with a smaller team) sits at the low end around 6.79%.

P119 — Over-Budget Projects by Hourly Cost  ·  Expert

Assume each project hour costs 100. Find projects where calculated labor cost exceeds the budget.

SQL

SELECT
    p.project_id,
    p.project_name,
    p.budget,
    SUM(ep.hours_worked) * 100 AS labor_cost
FROM projects p
JOIN employee_projects ep
    ON p.project_id = ep.project_id
GROUP BY p.project_id, p.project_name, p.budget
HAVING SUM(ep.hours_worked) * 100 > p.budget;

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

(1 row total)

Explanation

Multiplying total logged hours by a flat ₹100/hour rate and comparing that estimate against each project's actual budget is a simple way to flag projects burning more labor than they were funded for — the Vendor Payment Automation project is the one case in this dataset where that happens (115 hours × ₹100 = ₹11,500 against an ₹8,000 budget), because it was deliberately given a low budget relative to the ongoing work logged against it.

P120 — Manager With Most Direct Reports  ·  Expert

Find the manager who has the most direct reports.

SQL

SELECT
    m.employee_id,
    m.first_name,
    COUNT(e.employee_id) AS direct_reports
FROM employees m
JOIN employees e
    ON e.manager_id = m.employee_id
GROUP BY m.employee_id, m.first_name
ORDER BY direct_reports DESC
LIMIT 1;

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

(1 row total)

Explanation

The self-join e.manager_id = m.employee_id pairs every employee with their manager, and grouping by manager while counting matched employees finds who has the largest team reporting directly to them. Arvind Krishnan, the CEO, has the most direct reports at 6 — exactly the six department heads who report straight to him, before the hierarchy fans out further at the manager and IC levels.

P121 — Managers With Average Team Salary  ·  Expert

Calculate the average salary of each manager's direct reports.

SQL

SELECT
    m.employee_id AS manager_id,
    m.first_name AS manager_name,
    AVG(e.salary) AS average_team_salary
FROM employees m
JOIN employees e
    ON e.manager_id = m.employee_id
GROUP BY m.employee_id, m.first_name;

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

(10 rows total)

Explanation

This is the same self-join as Problem 120, but averaging salary instead of counting rows — it reveals each manager's team pay level, not just team size. Meera Nair's direct reports average ₹150,000 (she manages senior engineering managers), while Deepak Saxena's team averages a more modest ~₹58,374, reflecting the more junior finance ICs reporting to him.

P122 — Employees Earning More Than Their Manager  ·  Expert

Find employees whose salary is greater than their manager's salary.

SQL

SELECT
    e.employee_id,
    e.first_name,
    e.salary,
    m.first_name AS manager_name,
    m.salary AS manager_salary
FROM employees e
JOIN employees m
    ON e.manager_id = m.employee_id
WHERE e.salary > m.salary;

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

(1 row total)

Explanation

Comparing e.salary > m.salary across the same self-join surfaces an organizational anomaly: an individual contributor out-earning their own manager. Exactly one such case exists in this dataset — employee Rohan (id 14, ₹167,000) earns more than his manager, also confusingly named Rohan (id 8, ₹152,000) — a scenario deliberately built into the data, since this kind of compression is a real (if awkward) thing that happens in growing companies when a specialist's market rate outpaces their manager's.

P123 — Department With Highest Revenue  ·  Expert

Calculate sales revenue attributed to employees by department and find the highest-revenue department.

SQL

SELECT
    d.department_id,
    d.department_name,
    SUM(o.total_amount) AS revenue
FROM departments d
JOIN employees e ON d.department_id = e.department_id
JOIN orders o ON e.employee_id = o.employee_id
WHERE o.status <> 'Cancelled'
GROUP BY d.department_id, d.department_name
ORDER BY revenue DESC
LIMIT 1;

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

(1 row total)

Explanation

Chaining three tables — departments → employees → orders — attributes each non-cancelled order's revenue to the department of the employee who processed the sale (not the customer's department, since customers don't have one). Because orders.employee_id in this schema always references a Sales-team employee (the "sales employee" role in this schema), every single order traces back to the Sales department — so Sales is trivially both the only department with any attributed revenue at all, and therefore also the department with the highest.

P124 — Employee Sales Ranking  ·  Expert

Rank employees by total sales revenue.

SQL

WITH sales AS (
    SELECT
        e.employee_id,
        e.first_name,
        COALESCE(SUM(
            CASE WHEN o.status <> 'Cancelled'
                 THEN o.total_amount ELSE 0 END
        ), 0) AS revenue
    FROM employees e
    LEFT JOIN orders o ON e.employee_id = o.employee_id
    GROUP BY e.employee_id, e.first_name
)
SELECT
    *,
    RANK() OVER (ORDER BY revenue DESC) AS sales_rank
FROM sales;

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

(showing 10 of 40 rows)

Explanation

This mirrors Problem 95's customer-spending rank, but for employees and sales revenue instead: the sales CTE sums each employee's non-cancelled order revenue (defaulting to 0 via COALESCE for anyone with none), and RANK() orders the result. Neha (employee 27) leads the whole company at ₹635,539 in attributed sales revenue — notably, only employees in the Sales department can have any nonzero revenue at all here, for the same reason as Problem 123.

P125 — Employees With Sales Above Department Average  ·  Expert

Find employees whose sales revenue is above the average employee revenue in their department.

SQL

WITH sales AS (
    SELECT
        e.employee_id,
        e.first_name,
        e.department_id,
        COALESCE(SUM(
            CASE WHEN o.status <> 'Cancelled'
                 THEN o.total_amount ELSE 0 END
        ), 0) AS revenue
    FROM employees e
    LEFT JOIN orders o ON e.employee_id = o.employee_id
    GROUP BY e.employee_id, e.first_name, e.department_id
)
SELECT *
FROM (
    SELECT
        s.*,
        AVG(revenue) OVER (
            PARTITION BY department_id
        ) AS department_avg
    FROM sales s
) x
WHERE revenue > department_avg;

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

(4 rows total)

Explanation

Because orders.employee_id only ever points to a Sales-department employee, the department_avg window average computed here is only ever nonzero for department_id 2 (Sales) — every other department's employees uniformly show 0 revenue against a 0 average, and 0 > 0 is false, so they correctly never appear. All 4 employees who clear this bar are Sales-team members outperforming their own team's average.

P126 — Recursive Employee Hierarchy  ·  Expert

Build an employee hierarchy starting from top-level employees.

SQL

WITH RECURSIVE hierarchy AS (
    SELECT
        employee_id,
        first_name,
        manager_id,
        1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT
        e.employee_id,
        e.first_name,
        e.manager_id,
        h.level + 1
    FROM employees e
    JOIN hierarchy h
        ON e.manager_id = h.employee_id
)
SELECT *
FROM hierarchy
ORDER BY level, employee_id;

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

(showing 10 of 40 rows)

Explanation

WITH RECURSIVE works in two parts: the 'anchor' half (WHERE manager_id IS NULL) seeds the recursion with the top of the hierarchy — Arvind Krishnan, at level 1 — and the 'recursive' half then repeatedly joins employees back onto the growing hierarchy result, one level at a time, until no more matches are found. All 40 employees eventually appear, each correctly tagged with their depth in the org chart, from Arvind at level 1 down to individual contributors at level 4.

P127 — Employee Hierarchy Path  ·  Expert

Build a text path showing each employee's hierarchy chain.

SQL

WITH RECURSIVE hierarchy AS (
    SELECT
        employee_id,
        first_name,
        manager_id,
        first_name::TEXT AS path
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT
        e.employee_id,
        e.first_name,
        e.manager_id,
        h.path || ' > ' || e.first_name
    FROM employees e
    JOIN hierarchy h
        ON e.manager_id = h.employee_id
)
SELECT *
FROM hierarchy;

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

(showing 10 of 40 rows)

Explanation

This is structurally the same recursive CTE as Problem 126, but instead of tracking a numeric level, it builds up a readable text trail with h.path || ' > ' || e.first_name at each recursive step — so by the time the recursion reaches a leaf-level individual contributor, their path column reads as a full chain like 'Arvind > Meera > Rohan > ...', showing every manager between them and the CEO in one string.

P128 — Customers With Orders in Three Consecutive Months  ·  Expert

Find customers who placed at least one order in three consecutive calendar months.

SQL

WITH months AS (
    SELECT DISTINCT
        customer_id,
        DATE_TRUNC('month', order_date) AS month
    FROM orders
    WHERE status <> 'Cancelled'
),
x AS (
    SELECT
        customer_id,
        month,
        LAG(month, 1) OVER (
            PARTITION BY customer_id ORDER BY month
        ) AS prev1,
        LAG(month, 2) OVER (
            PARTITION BY customer_id ORDER BY month
        ) AS prev2
    FROM months
)
SELECT DISTINCT customer_id
FROM x
WHERE month = prev1 + INTERVAL '1 month'
  AND prev1 = prev2 + INTERVAL '1 month';

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

(7 rows total)

Explanation

This chains two LAG calls (LAG(month, 1) and LAG(month, 2)) to look back one and two months respectively within each customer's distinct list of active months, then checks that the current month, the month before it, and the month before that are all exactly one calendar month apart — i.e., a genuine unbroken 3-month streak, not just any three months scattered across the year. 7 customers achieve this in the dataset, several of them deliberately given a guaranteed 4-month consecutive run of orders when the data was generated.

P129 — Customer Cohort by Signup Month  ·  Expert

Group customers into signup-month cohorts and count them.

SQL

SELECT
    DATE_TRUNC('month', signup_date) AS cohort_month,
    COUNT(*) AS customer_count
FROM customers
GROUP BY DATE_TRUNC('month', signup_date)
ORDER BY cohort_month;

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

(showing 10 of 19 rows)

Explanation

DATE_TRUNC('month', signup_date) groups every customer into the calendar month they signed up in — a 'cohort' in growth-analytics terms — and counting rows per group shows how signups were distributed over time. Signups trickle in from as early as January 2023 (this dataset's earliest customer) through the most recent months, with no single month dominating.

P130 — Cohort Revenue  ·  Expert

Calculate total revenue generated by each customer signup cohort.

SQL

SELECT
    DATE_TRUNC('month', c.signup_date) AS cohort_month,
    SUM(o.total_amount) AS revenue
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.status <> 'Cancelled'
GROUP BY DATE_TRUNC('month', c.signup_date)
ORDER BY cohort_month;

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

(showing 10 of 18 rows)

Explanation

Joining each customer's signup cohort to their (non-cancelled) order history and summing revenue per cohort answers a slightly different question than raw signup counts: not 'how many people signed up in a given month', but 'how much lifetime revenue has that whole cohort generated since'. Early cohorts like January and February 2023 already show substantial revenue (₹301,121 and ₹355,386 respectively), since those customers have had the most time to place orders.

P131 — Customers With No Orders in Their Signup Month  ·  Expert

Find customers who did not place an order during the month they signed up.

SQL

SELECT c.customer_id, c.customer_name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
      AND DATE_TRUNC('month', o.order_date)
          = DATE_TRUNC('month', c.signup_date)
);

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

(showing 10 of 25 rows)

Explanation

NOT EXISTS checks, for each customer, whether any row in orders matches both their customer_id and a DATE_TRUNC('month', ...) equal to their own signup month — if no such row exists, the customer is flagged. 25 of the 30 customers never ordered during their actual signup month (most likely ordering for the first time some weeks or months after registering instead), which is a very common real-world pattern: signing up and buying immediately is the exception, not the rule.

P132 — Customers With Repeat Purchase Within 30 Days  ·  Expert

Find customers who placed another order within 30 days of a previous order.

SQL

WITH x AS (
    SELECT
        customer_id,
        order_date,
        LEAD(order_date) OVER (
            PARTITION BY customer_id
            ORDER BY order_date
        ) AS next_order_date
    FROM orders
)
SELECT DISTINCT customer_id
FROM x
WHERE next_order_date <= order_date + INTERVAL '30 days';

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

(showing 10 of 12 rows)

Explanation

LEAD(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) looks ahead to each customer's next order, and the filter checks whether that next order landed within 30 days. 12 customers show this kind of quick repeat-purchase behavior, including customer 9, deliberately given two orders 19 days apart when the data was generated specifically to exercise this check.

P133 — Customers With Increasing Order Frequency  ·  Expert

Compare each customer's order count in the first and second half of 2025.

SQL

SELECT
    customer_id,
    SUM(CASE
        WHEN order_date >= '2025-01-01'
         AND order_date < '2025-07-01'
        THEN 1 ELSE 0 END) AS first_half,
    SUM(CASE
        WHEN order_date >= '2025-07-01'
         AND order_date < '2026-01-01'
        THEN 1 ELSE 0 END) AS second_half
FROM orders
GROUP BY customer_id
HAVING SUM(CASE
        WHEN order_date >= '2025-07-01'
         AND order_date < '2026-01-01'
        THEN 1 ELSE 0 END)
     >
       SUM(CASE
        WHEN order_date >= '2025-01-01'
         AND order_date < '2025-07-01'
        THEN 1 ELSE 0 END);

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

(10 rows total)

Explanation

Two SUM(CASE WHEN ... THEN 1 ELSE 0 END) expressions in the same query count each customer's order volume in the first half of 2025 (Jan-Jun) and second half (Jul-Dec) side by side, and the HAVING clause keeps only customers whose second-half count exceeds their first-half count. 10 customers ordered more often in the back half of the year than the front half — customer 8 nearly tripled their pace, from 2 orders in H1 to 5 in H2.

P134 — Find the Median Employee Salary  ·  Expert

Calculate the median salary of employees.

SQL

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

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

(1 row total)

Explanation

PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) computes the continuous median — for an even number of rows (40 employees here) it interpolates between the two middle values rather than picking one of them arbitrarily. At ₹85,078.57, the median sits well below the ₹102,073 average from Problem 22, confirming what that earlier problem already hinted at: the average is being pulled upward by a handful of very high leadership salaries, while the median reflects where the 'typical' employee actually sits.

P135 — Median Order Value by Country  ·  Expert

Find the median order value for each customer country.

SQL

SELECT
    c.country,
    PERCENTILE_CONT(0.5)
    WITHIN GROUP (ORDER BY o.total_amount) AS median_order_value
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.country;

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

(8 rows total)

Explanation

The same PERCENTILE_CONT function as Problem 134, now computed once per country group via GROUP BY. Median order values vary meaningfully by market — the UAE sits at just ₹3,397 (a single customer with modest orders) while India's median of ₹11,794 reflects both a much larger and more varied set of orders.

P136 — Pareto Revenue Customers  ·  Expert

Find the smallest set of customers contributing to approximately the first 80% of total revenue.

SQL

WITH spending AS (
    SELECT
        customer_id,
        SUM(total_amount) AS revenue
    FROM orders
    WHERE status <> 'Cancelled'
    GROUP BY customer_id
),
ranked AS (
    SELECT
        *,
        SUM(revenue) OVER (
            ORDER BY revenue DESC
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS cumulative_revenue,
        SUM(revenue) OVER () AS total_revenue
    FROM spending
)
SELECT *
FROM ranked
WHERE cumulative_revenue <= total_revenue * 0.80;

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

(10 rows total)

Explanation

This builds a running cumulative-revenue total ordered from the highest customer down to the lowest (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), compares it against the grand total computed separately with an unpartitioned SUM(revenue) OVER (), and keeps every customer up to the point where the running total first reaches 80% of all revenue — a direct SQL implementation of the Pareto ('80/20') principle. It takes only 10 of this dataset's highest-spending customers to account for that first 80% of total revenue, a concentration that's common in real customer bases.

P137 — Product Market Share  ·  Expert

Calculate each product's percentage share of total product revenue.

SQL

WITH product_sales AS (
    SELECT
        p.product_id,
        p.product_name,
        SUM(oi.quantity * oi.unit_price) AS revenue
    FROM products p
    JOIN order_items oi ON p.product_id = oi.product_id
    JOIN orders o ON oi.order_id = o.order_id
    WHERE o.status <> 'Cancelled'
    GROUP BY p.product_id, p.product_name
)
SELECT
    *,
    ROUND(
        100.0 * revenue / SUM(revenue) OVER (),
        2
    ) AS market_share
FROM product_sales
ORDER BY market_share DESC;

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

(showing 10 of 27 rows)

Explanation

Revenue per product is computed from order_items (excluding cancelled orders), and SUM(revenue) OVER () again supplies the shared grand-total denominator for a percentage-of-total 'market share' figure. The 27-inch 4K Monitor alone claims 31.47% of all product revenue in this dataset — a single high-price, high-volume item dominating the entire catalog's earnings.

P138 — Category Revenue Ranking  ·  Expert

Rank product categories by revenue.

SQL

WITH category_sales AS (
    SELECT
        p.category,
        SUM(oi.quantity * oi.unit_price) AS revenue
    FROM products p
    JOIN order_items oi ON p.product_id = oi.product_id
    JOIN orders o ON oi.order_id = o.order_id
    WHERE o.status <> 'Cancelled'
    GROUP BY p.category
)
SELECT
    *,
    DENSE_RANK() OVER (ORDER BY revenue DESC) AS category_rank
FROM category_sales;

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

(5 rows total)

Explanation

This groups product-level revenue up one more level, into category, and uses DENSE_RANK() rather than RANK() so that (in principle) tied categories wouldn't produce a gap in the ranking. Electronics leads by a wide margin at ₹1,758,160 — more than triple the second-place category (Home & Kitchen at ₹477,027), driven largely by that same dominant 4K Monitor from Problem 137.

P139 — Highest Revenue Product Per Category  ·  Expert

Find the top revenue-generating product in every category.

SQL

WITH product_sales 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
),
ranked AS (
    SELECT *,
        ROW_NUMBER() OVER (
            PARTITION BY category
            ORDER BY revenue DESC
        ) AS rn
    FROM product_sales
)
SELECT *
FROM ranked
WHERE rn = 1;

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

(5 rows total)

Explanation

Filtering ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) = 1 after computing every product's revenue (with LEFT JOINs and COALESCE so even a product with zero sales still gets a 0 and stays in contention) picks the single best-selling product within each of the 5 categories. Interestingly, Clothing's top product is the Denim Jacket (₹82,467), not the cheaper, higher-volume items in that category — reflecting its higher unit price relative to how many units of it actually sold.

P140 — Products With Declining Monthly Sales  ·  Expert

Find products whose sales decreased from one month to the next.

SQL

WITH monthly AS (
    SELECT
        oi.product_id,
        DATE_TRUNC('month', o.order_date) AS month,
        SUM(oi.quantity) AS units
    FROM order_items oi
    JOIN orders o ON oi.order_id = o.order_id
    WHERE o.status <> 'Cancelled'
    GROUP BY oi.product_id, DATE_TRUNC('month', o.order_date)
),
x AS (
    SELECT
        *,
        LAG(units) OVER (
            PARTITION BY product_id ORDER BY month
        ) AS previous_units
    FROM monthly
)
SELECT *
FROM x
WHERE previous_units IS NOT NULL
  AND units < previous_units;

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

(showing 10 of 83 rows)

Explanation

The monthly CTE first collapses order_items into total units sold per product per month, and LAG(units) OVER (PARTITION BY product_id ORDER BY month) then compares each month against the one before it for that same product — the outer query keeps every month where sales dropped versus the prior month. This is a broad, expected-to-be-noisy signal by design (83 rows match here), since almost every product will have some month where sales dipped relative to the month before; it's a starting point for investigation, not by itself proof of a real declining trend (Problem 141 tackles that stricter, more meaningful version).