SQL Practice — Intermediate (Problems 31-70)

P32 — Employees With Department Names  ·  Intermediate

Show employee name, department name, and salary.

SQL

SELECT
    e.first_name,
    e.last_name,
    d.department_name,
    e.salary
FROM employees e
JOIN departments d
    ON e.department_id = d.department_id;

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

(showing 10 of 40 rows)

Explanation

This extends the Problem 30 join by also pulling in salary, giving a single row per employee that reads like a payroll report: name, department, and pay all together. All 40 employees appear because every one of them has a valid department_id that matches a row in departments.

P33 — Customers With No Orders  ·  Intermediate

Find customers who have never placed an order.

SQL

SELECT c.customer_id, c.customer_name
FROM customers c
LEFT JOIN orders o
    ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

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

(3 rows total)

Explanation

LEFT JOIN keeps every row from customers regardless of whether a match exists in orders — when a customer has no orders, the joined orders columns (including order_id) come back as NULL for that row, which is exactly the signal WHERE o.order_id IS NULL filters for. Three customers never placed a single order: Grace Clark, Sofia Rossi, and David Miller — this dataset's deliberately quiet accounts.

P34 — Orders With Customer Details  ·  Intermediate

Show order ID, customer name, order date, and status.

SQL

SELECT
    o.order_id,
    c.customer_name,
    o.order_date,
    o.status
FROM orders o
JOIN customers c
    ON o.customer_id = c.customer_id;

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

(showing 10 of 148 rows)

Explanation

This is the same shape as Problem 31 with order_date and status added to the projection — a straightforward inner join that returns all 148 orders paired with their customer's name, useful as a general-purpose 'orders list' report.

P35 — Products Never Ordered  ·  Intermediate

Find products that have never appeared in order_items.

SQL

SELECT p.product_id, p.product_name
FROM products p
LEFT JOIN order_items oi
    ON p.product_id = oi.product_id
WHERE oi.product_id IS NULL;

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

(1 row total)

Explanation

LEFT JOIN ... WHERE oi.product_id IS NULL finds products that never matched any row in order_items — the classic 'find things with no matching child rows' pattern. Only the Cycling Helmet (product 13) shows up: it's one of two low-stock products this dataset deliberately keeps out of every order (the other, the Smartwatch, gets pulled back in by a dedicated order built for Problem 111, so only the Cycling Helmet remains a true never-sold item here).

P36 — Total Spending Per Customer  ·  Intermediate

Calculate total order value for every customer.

SQL

SELECT
    c.customer_id,
    c.customer_name,
    COALESCE(SUM(o.total_amount), 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
ORDER BY total_spent DESC;

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

(showing 10 of 30 rows)

Explanation

LEFT JOIN plus COALESCE(SUM(...), 0) is the standard way to compute a total that should still show 0 — not a missing row — for entities with no activity; without COALESCE, a customer with zero orders would get NULL instead of 0 after the SUM. Vikas Rana tops the list at ₹364,784 in total order value (including cancelled orders, since this query doesn't filter status), followed by Rajesh Pandey and Sneha Nair.

P37 — Customers Spending More Than 10000  ·  Intermediate

Find customers whose total order value exceeds 10,000.

SQL

SELECT
    c.customer_id,
    c.customer_name,
    SUM(o.total_amount) AS total_spent
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.status <> 'Cancelled'
GROUP BY c.customer_id, c.customer_name
HAVING SUM(o.total_amount) > 10000;

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

(showing 10 of 23 rows)

Explanation

Unlike Problem 36, this query uses an inner JOIN (dropping customers with no orders entirely) and explicitly excludes cancelled orders with WHERE o.status <> 'Cancelled' before the HAVING SUM(...) > 10000 filter runs — so the ₹10,000 threshold is judged against real, non-cancelled spending only. 23 of the 30 customers clear that bar.

P38 — Average Order Value by Customer  ·  Intermediate

Find the average order amount for each customer.

SQL

SELECT
    customer_id,
    AVG(total_amount) AS average_order_value
FROM orders
GROUP BY customer_id;

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

(showing 10 of 28 rows)

Explanation

Grouping directly on orders.customer_id (no join needed, since customer_id already lives on that table) and averaging total_amount per group gives each customer's typical order size — note this doesn't exclude cancelled orders, so it's a slightly different, simpler metric than the lifetime-value calculations later in the chapter. 28 of the 30 customers have at least one order and therefore an average.

P39 — Revenue by Product  ·  Intermediate

Calculate revenue generated by each product using order_items.

SQL

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
ORDER BY revenue DESC;

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

(showing 10 of 27 rows)

Explanation

Revenue has to be computed from order_items (quantity * unit_price), not from orders.total_amount, because the question asks for revenue per product — a single order's total_amount is a sum across possibly several different products. The two joins connect products to their line items and then to the parent order so the Cancelled filter can be applied; the 27-inch 4K Monitor leads by a wide margin at ₹849,966, roughly four times the next highest product, reflecting its high unit price (₹24,999) even at modest sales volume.

P40 — Best-Selling Products by Quantity  ·  Intermediate

Find the five products with the highest total quantity sold.

SQL

SELECT
    p.product_name,
    SUM(oi.quantity) AS total_quantity
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
ORDER BY total_quantity DESC
LIMIT 5;

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

(5 rows total)

Explanation

This is nearly identical to Problem 39 but sums quantity instead of quantity * unit_price and keeps only the top 5 — so it ranks products by units moved, not revenue earned. The Wireless Mouse tops this list (165 units) despite being one of the cheapest products in the catalog, which is exactly the kind of 'high revenue' vs 'high volume' distinction that matters when deciding what to reorder versus what to keep pushing.

P41 — Employees With Their Managers  ·  Intermediate

Display each employee and their manager's name.

SQL

SELECT
    e.first_name AS employee,
    m.first_name AS manager
FROM employees e
LEFT JOIN employees m
    ON e.manager_id = m.employee_id;

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

(showing 10 of 40 rows)

Explanation

This is a self-join: employees is joined to itself (aliased e and m) by matching each employee's manager_id to another employee's employee_id. LEFT JOIN is important here — using a plain JOIN would silently drop Arvind Krishnan, the CEO, since his manager_id is NULL and has no match; with LEFT JOIN, he still appears in the result with manager showing as NULL.

P42 — Employees Earning Above Department Average  ·  Intermediate

Find employees whose salary is greater than their department's average salary.

SQL

SELECT e.employee_id, e.first_name, e.salary, e.department_id
FROM employees e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e.department_id
);

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

(showing 10 of 15 rows)

Explanation

The correlated subquery SELECT AVG(e2.salary) FROM employees e2 WHERE e2.department_id = e.department_id re-runs once per outer row, each time computing that specific employee's own department average — it can't be computed once up front because the answer depends on which employee's department is being checked. 15 employees clear their department's bar, which by definition can never be more than half of any department (an average splits a group roughly down the middle, modulo how skewed the distribution is).

P43 — Employees With Highest Salary  ·  Intermediate

Find all employees who earn the maximum salary.

SQL

SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary = (
    SELECT MAX(salary)
    FROM employees
);

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

(1 row total)

Explanation

The subquery SELECT MAX(salary) FROM employees runs once and returns a single number (₹285,000), and the outer query then finds every employee whose salary equals it — using = rather than ORDER BY ... LIMIT 1 matters because it correctly returns all employees tied for the maximum, not just one, even though in this dataset there's exactly one: Arvind Krishnan.

P44 — Second Highest Salary  ·  Intermediate

Find the second highest distinct employee salary.

SQL

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

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

(1 row total)

Explanation

This finds the highest salary that is less than the overall maximum — effectively the second-highest distinct value. Since Arvind's ₹285,000 is unique, filtering it out and taking the max of what remains correctly returns ₹215,000 (Meera Nair's salary) without needing LIMIT 1, 1-style offset syntax, which isn't standard SQL anyway.

P45 — Employees With Same Salary  ·  Intermediate

Find salary values shared by more than one employee.

SQL

SELECT salary, COUNT(*) AS employee_count
FROM employees
GROUP BY salary
HAVING COUNT(*) > 1;

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

(1 row total)

Explanation

GROUP BY salary groups all employees who share the exact same numeric salary, and HAVING COUNT(*) > 1 keeps only groups with more than one member — normally salaries this granular (down to the cent) almost never collide by chance, and indeed this dataset returns exactly one such pair: two employees who happen to share a salary of ₹83,315.91. In a real payroll table, a hit here would usually be worth double-checking as a possible data-entry coincidence rather than assuming it's meaningful.

P46 — Duplicate Customer Emails  ·  Intermediate

Find email addresses used by more than one customer.

SQL

SELECT email, COUNT(*) AS email_count
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

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

(1 row total)

Explanation

The same GROUP BY ... HAVING COUNT(*) > 1 pattern as Problem 45, applied to email instead of salary — this is a genuinely useful data-quality check, since two customer accounts sharing one email address usually means either a duplicate signup or a shared family/work inbox. One pair turns up here.

P47 — Orders in 2025  ·  Intermediate

Find all orders placed during 2025.

SQL

SELECT *
FROM orders
WHERE order_date >= '2025-01-01'
  AND order_date < '2026-01-01';

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

(showing 10 of 75 rows)

Explanation

order_date >= '2025-01-01' AND order_date < '2026-01-01' is a half-open range — it deliberately avoids <= '2025-12-31', because if order_date ever carried a time component (a timestamp instead of a plain date), an order at 2025-12-31 15:00:00 would be excluded by a <= comparison against midnight but correctly included by < '2026-01-01'. 75 of the 148 orders in this dataset fall in 2025, versus the remainder in 2024.

P48 — Monthly Revenue  ·  Intermediate

Calculate revenue for each month in 2025.

SQL

SELECT
    DATE_TRUNC('month', order_date) AS month,
    SUM(total_amount) AS revenue
FROM orders
WHERE order_date >= '2025-01-01'
  AND order_date < '2026-01-01'
  AND status <> 'Cancelled'
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

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

(showing 10 of 12 rows)

Explanation

DATE_TRUNC('month', order_date) rounds every date down to the first day of its month, so all orders placed anywhere in, say, January 2025 collapse into one 2025-01-01 group; summing total_amount per group (after excluding cancelled orders and restricting to 2025) gives a real month-by-month revenue trend. Revenue swings noticeably month to month in this dataset — January comes in at ₹121,116 and February climbs to ₹144,580 before March drops sharply to ₹30,777 — exactly the kind of volatility that later problems (P89-P91) analyze with LAG and moving averages.

P49 — Orders Per Month  ·  Intermediate

Count how many orders were placed in each month.

SQL

SELECT
    DATE_TRUNC('month', order_date) AS month,
    COUNT(*) AS order_count
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

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

(showing 10 of 24 rows)

Explanation

This mirrors Problem 48's DATE_TRUNC('month', ...) grouping but counts orders instead of summing revenue, and — unlike P48 — doesn't filter by year or status, so it covers the full 24-month history (2024-2025) including cancelled orders. Order volume trends gently upward over the two years, consistent with the growing customer base this dataset was generated with.

P50 — Customers Who Placed Multiple Orders  ·  Intermediate

Find customers with at least three orders.

SQL

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

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

(showing 10 of 18 rows)

Explanation

Grouping by customer_id and filtering with HAVING COUNT(*) >= 3 identifies repeat buyers — 18 of the 30 customers have placed three or more orders. Customer 6 leads by a wide margin with 12 orders, reflecting this dataset's deliberate mix of a handful of very frequent buyers alongside many occasional ones.

P51 — Latest Order for Each Customer  ·  Intermediate

Find the most recent order date for every customer.

SQL

SELECT
    customer_id,
    MAX(order_date) AS latest_order_date
FROM orders
GROUP BY customer_id;

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

(showing 10 of 28 rows)

Explanation

MAX(order_date) per customer_id group gives each customer's single most recent order — a useful building block for recency analysis, reused directly in the next problem's inactivity check. 28 of the 30 customers have at least one order and therefore a latest-order date; the 2 with zero orders are silently excluded since this uses an inner-style aggregation with no LEFT JOIN back to customers.

P52 — Customers Inactive for 180 Days  ·  Intermediate

Find customers whose latest order was more than 180 days before 2026-01-01.

SQL

SELECT c.customer_id, c.customer_name, MAX(o.order_date) AS latest_order
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name
HAVING MAX(o.order_date) < DATE '2026-01-01' - INTERVAL '180 days';

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

(10 rows total)

Explanation

This wraps the MAX(order_date) idea from Problem 51 in a HAVING clause compared against a computed cutoff — DATE '2026-01-01' - INTERVAL '180 days' evaluates to 2025-07-05, so any customer whose most recent order predates that is flagged as inactive. 10 customers qualify, including Olivia Davis, whose last order was back in July 2024 — well over a year stale relative to the 2026-01-01 reference point.

P53 — Orders Above Customer Average  ·  Intermediate

Find orders whose amount is greater than that customer's average order value.

SQL

SELECT o.order_id, o.customer_id, o.total_amount
FROM orders o
WHERE o.total_amount > (
    SELECT AVG(o2.total_amount)
    FROM orders o2
    WHERE o2.customer_id = o.customer_id
);

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

(showing 10 of 43 rows)

Explanation

The correlated subquery here recomputes AVG(o2.total_amount) separately for each customer (matching o2.customer_id = o.customer_id in the inner query to the current outer row), so 'above average' means above that customer's own average order size, not the company-wide average. 43 orders qualify — by construction, roughly half of any customer's orders will sit above their own personal average, so this is a reasonable-looking result.

P54 — Highest Order Per Customer  ·  Intermediate

Find each customer's highest order amount.

SQL

SELECT customer_id, MAX(total_amount) AS highest_order
FROM orders
GROUP BY customer_id;

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

(showing 10 of 28 rows)

Explanation

A straightforward MAX(total_amount) grouped by customer_id — each customer's single largest order. This becomes useful context for Problem 116 later, which checks whether a customer's orders have been trending upward over time.

P55 — Orders Above Overall Average  ·  Intermediate

Find all orders whose amount is above the overall average order amount.

SQL

SELECT order_id, customer_id, total_amount
FROM orders
WHERE total_amount > (
    SELECT AVG(total_amount)
    FROM orders
);

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

(showing 10 of 39 rows)

Explanation

This is the company-wide version of Problem 53: the subquery SELECT AVG(total_amount) FROM orders runs once (not once per customer) and returns a single number, so every order is compared against the same global bar. 39 orders clear it — fewer than Problem 53's 43, because a company-wide average pulled up by a few very large orders is a harder bar to clear than each customer's own, more personalized average.

P56 — Employee Salary Bands  ·  Intermediate

Show employees and classify salaries into four bands.

SQL

SELECT
    first_name,
    salary,
    CASE
        WHEN salary < 40000 THEN 'Band 1'
        WHEN salary < 60000 THEN 'Band 2'
        WHEN salary < 90000 THEN 'Band 3'
        ELSE 'Band 4'
    END AS salary_band
FROM employees;

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

(showing 10 of 40 rows)

Explanation

This is the same CASE WHEN pattern as Problem 29 but with four bands instead of three and different thresholds. Because this org's leadership salaries are so high relative to the bands (all six figures well above the ₹90,000 top threshold), most senior employees land in 'Band 4', while the newer individual contributors spread across the lower bands — the CEO, the department heads, and several managers all land in Band 4 here.

P57 — Payment Success Rate  ·  Intermediate

Calculate the percentage of payments that were successful.

SQL

SELECT
    ROUND(
        100.0 * SUM(CASE WHEN payment_status = 'Success' THEN 1 ELSE 0 END)
        / COUNT(*), 2
    ) AS success_rate
FROM payments;

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

(1 row total)

Explanation

SUM(CASE WHEN payment_status = 'Success' THEN 1 ELSE 0 END) is 'conditional counting': the CASE expression turns each row into a 1 or 0 depending on whether it matches, and SUM then adds those up — this is a very common technique for computing a rate or percentage in one pass without a self-join or subquery. Multiplying by 100.0 (not 100) before dividing forces floating-point division instead of integer division, which matters in engines where integer/integer truncates the decimal part. The result: 83.69% of all payment attempts in this dataset succeeded on the first try.

P58 — Payment Method Usage  ·  Intermediate

Count payments made using each payment method.

SQL

SELECT payment_method, COUNT(*) AS payment_count
FROM payments
GROUP BY payment_method
ORDER BY payment_count DESC;

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

(4 rows total)

Explanation

Grouping payments by payment_method and counting rows per group shows how customers are actually paying — Cash leads narrowly (38 payments) over Netbanking (37) and Card (33), with the fourth method (UPI) close behind, a fairly even spread across all four methods in this dataset.

P59 — Successful Payment Revenue  ·  Intermediate

Calculate total amount from successful payments.

SQL

SELECT SUM(amount) AS successful_payment_amount
FROM payments
WHERE payment_status = 'Success';

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

(1 row total)

Explanation

WHERE payment_status = 'Success' filters out failed and refunded payment rows before the SUM, so this total (~₹2.49 million) reflects money that was actually, successfully collected — it will always be somewhat less than the ₹2.7 million non-cancelled order revenue from Problem 24, since not every non-cancelled order has a fully successful payment (see Problem 60 and Problem 143 for exactly which ones don't).

P60 — Orders Without Successful Payments  ·  Intermediate

Find orders that do not have a successful payment.

SQL

SELECT o.order_id, o.total_amount
FROM orders o
LEFT JOIN payments p
    ON o.order_id = p.order_id
    AND p.payment_status = 'Success'
WHERE p.payment_id IS NULL;

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

(showing 10 of 38 rows)

Explanation

The join condition here does double duty: LEFT JOIN payments p ON o.order_id = p.order_id AND p.payment_status = 'Success' filters for successful payments inside the join itself, not in a WHERE clause — that distinction matters, because putting p.payment_status = 'Success' in WHERE instead would silently turn this back into an inner join and lose every order that has zero payment rows at all. WHERE p.payment_id IS NULL then catches orders where no successful payment ever matched. 38 orders show up here — a mix of this dataset's deliberately unpaid orders and its deliberately underpaid ones (their one payment row exists but wasn't enough, or wasn't marked Success).

P61 — Employee Name Formatting  ·  Intermediate

Return each employee's full name in one column.

SQL

SELECT
    CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;

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

(showing 10 of 40 rows)

Explanation

CONCAT(first_name, ' ', last_name) joins three pieces of text — first name, a literal space, and last name — into a single computed column; the underlying table is untouched, this only affects what's returned. The same effect could be written with first_name || ' ' || last_name in DuckDB.

P62 — Uppercase Customer Names  ·  Intermediate

Display all customer names in uppercase.

SQL

SELECT UPPER(customer_name) AS customer_name
FROM customers;

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

(showing 10 of 30 rows)

Explanation

UPPER() transforms the text of customer_name to all-caps for display, without changing the stored data — useful for case-insensitive comparisons or simply for a formatting requirement in a report or label.

P63 — Product Name Length  ·  Intermediate

Find products whose names contain more than 20 characters.

SQL

SELECT product_name, LENGTH(product_name) AS name_length
FROM products
WHERE LENGTH(product_name) > 20;

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

(3 rows total)

Explanation

LENGTH() counts characters, and the WHERE clause filters for names longer than 20 — only 3 of the 28 products have names that long: 'Noise Cancelling Headphones' (27 characters), 'The Pragmatic Programmer' (24), and 'Designing Data-Intensive Applications' (37, the longest product name in the catalog).

P64 — Customers Signed Up in 2024  ·  Intermediate

Find customers who registered during 2024.

SQL

SELECT customer_id, customer_name, signup_date
FROM customers
WHERE signup_date >= '2024-01-01'
  AND signup_date < '2025-01-01';

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

(showing 10 of 18 rows)

Explanation

The same half-open date-range pattern as Problem 47, now applied to signup_date on customers and shifted to the 2024 calendar year. 18 of the 30 customers signed up during 2024, the year this dataset weights most heavily for new signups.

P65 — Employees Hired by Year  ·  Intermediate

Count employees hired in each year.

SQL

SELECT
    EXTRACT(YEAR FROM hire_date) AS hire_year,
    COUNT(*) AS employee_count
FROM employees
GROUP BY EXTRACT(YEAR FROM hire_date)
ORDER BY hire_year;

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

(7 rows total)

Explanation

EXTRACT(YEAR FROM hire_date) pulls just the year component out of each date, so grouping by it buckets employees by hiring year regardless of month or day. Hiring is fairly steady from 2019 through 2025 in this dataset, with 4 hires in the founding year (2019) climbing to a peak in the following years as the company grew.

P66 — Average Order by Status  ·  Intermediate

Calculate the average order amount for each order status.

SQL

SELECT status, AVG(total_amount) AS average_amount
FROM orders
GROUP BY status;

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

(6 rows total)

Explanation

This groups by the raw status column with no validation or filtering — which means it faithfully surfaces this dataset's two deliberately dirty status values ('Returned' and lowercase 'processing') as their own separate groups alongside the four legitimate statuses (Pending, Shipped, Delivered, Cancelled). That's a realistic outcome: a naive GROUP BY status report in production will just as readily reveal bad data as it reveals real trends — which is exactly the kind of issue Problem 149 later hunts down explicitly.

P68 — Products Above Category Average  ·  Intermediate

Find products priced above their category's average price.

SQL

SELECT p.product_id, p.product_name, p.category, p.price
FROM products p
WHERE p.price > (
    SELECT AVG(p2.price)
    FROM products p2
    WHERE p2.category = p.category
);

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

(10 rows total)

Explanation

This is the product-catalog equivalent of Problem 42's correlated subquery: for each product, the inner query recomputes the average price within that product's own category and compares the outer row against it. 10 of the 28 products sit above their category's average price — the 27-inch 4K Monitor (₹24,999) is the most extreme example, sitting far above the Electronics category average thanks to being the single most expensive product in the whole catalog.

P69 — First Order Per Customer  ·  Intermediate

Find each customer's first order date.

SQL

SELECT customer_id, MIN(order_date) AS first_order_date
FROM orders
GROUP BY customer_id;

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

(showing 10 of 28 rows)

Explanation

MIN(order_date) per customer group gives each customer's very first order — the mirror image of Problem 51's MAX. This becomes the anchor point Problem 114 later compares against MAX(order_date) to detect customers who only ever ordered once.

P70 — Customer Lifetime Value  ·  Intermediate

Calculate lifetime order value for every customer, including customers with no orders.

SQL

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

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

(showing 10 of 30 rows)

Explanation

COALESCE(SUM(CASE WHEN o.status <> 'Cancelled' THEN o.total_amount ELSE 0 END), 0) combines two ideas at once: the CASE expression zeroes out cancelled orders' contribution before summing, and COALESCE catches customers with zero orders at all (where the SUM itself would otherwise be NULL after the LEFT JOIN). All 30 customers appear with a real number — Vikas Rana again leads at ₹316,810, slightly lower than his Problem 36 total because this version correctly excludes his cancelled orders.