Module 2, Session 11. Simple and Searched CASE WHEN branching, custom sort priorities, single-query conditional pivots, ANSI CAST, and PostgreSQL :: shorthand.
Open in Present mode · 400 practice questions · Live class schedule · Try the playground
Jo class mein bola, yahan nukte mein likha hai.
What I said in class, written here as key points.
Dynamic IF-THEN branching at row level, custom ordering priorities, single-query pivots, and explicit type conversions in PostgreSQL 18 on RetailMart.
Four pillars: branching expressions, custom sorting, single-pass pivots, and explicit type conversions
CASE WHEN ... THEN ... ELSE
ORDER BY
COUNT(CASE WHEN ... END)
SUM(CASE WHEN ... ELSE 0 END)
CAST(expr AS type)
expression::type
From deterministic date arithmetic to dynamic declarative branch evaluation
CASE WHEN is not a statement that redirects control flow; it is an inline scalar expression that returns exactly one evaluated result for each row processed.SELECT order_id, net_total, CASE WHEN net_total >= 10000 THEN 'Platinum' WHEN net_total >= 5000 THEN 'Gold' WHEN net_total >= 1000 THEN 'Silver' ELSE 'Standard' END AS tier FROM sales.orders ORDER BY net_total DESC, order_id LIMIT 10; -- Descending order prevents early match
CASE WHEN evaluates branches sequentially and stops at the very first TRUE condition. When checking ranges, always arrange thresholds from most restrictive to least restrictive (descending).Every crisis in the story translates directly into an essential conditional SQL rule.
CASE WHEN spend >= 100000 ... WHEN spend >= 50000.ELSE 'Standard Member' END.CAST(val AS TEXT) & uniform branch types.Click spend tiers to watch PostgreSQL evaluate conditions sequentially and short-circuit.
PostgreSQL's native IF-THEN branching evaluated row by row during SELECT execution.
CASE tier WHEN 'Platinum' THEN .... Cleanest for status codes and category tags.CASE WHEN salary >= 100000 THEN .... Essential for range comparisons (>, <) and compound logic.SELECT customer_id, INITCAP(first_name) || ' ' || INITCAP(last_name) AS name, tier, CASE tier WHEN 'Platinum' THEN 'VIP Platinum Member' WHEN 'Gold' THEN 'Premium Member' WHEN 'Silver' THEN 'Regular Member' WHEN 'Bronze' THEN 'Basic Member' ELSE 'Standard Member' END AS tier_label FROM customers.customers ORDER BY customer_id LIMIT 10;
Click to inspect capability differences between Simple and Searched CASE.
Custom priority ranking and dynamic operational filtering without procedural code.
ORDER BY (CASE ...) ASC, net_total DESC for deterministic output.SELECT order_id, cust_id, order_status, net_total FROM sales.orders ORDER BY CASE order_status WHEN 'Cancelled' THEN 1 WHEN 'Failed' THEN 2 WHEN 'Returned' THEN 3 WHEN 'Processing' THEN 4 ELSE 5 END ASC, net_total DESC LIMIT 10;
Click to inspect how conditional aggregation pivots categorical rows into KPI columns.
Computing multiple category KPIs in a single table scan using CASE inside aggregate functions.
COUNT(col) ignores NULLs, it counts only matching events cleanly.ELSE 0 for mathematical sums. Unmatched rows contribute ₹0 to the accumulator without NULL propagation.SELECT COUNT(*) AS total_orders, COUNT(CASE WHEN order_status = 'Delivered' THEN 1 END) AS delivered_orders, COUNT(CASE WHEN order_status = 'Cancelled' THEN 1 END) AS cancelled_orders, ROUND(SUM(CASE WHEN order_status = 'Delivered' THEN net_total ELSE 0 END), 2) AS delivered_revenue FROM sales.orders;
Avoid the 4 silent logic bugs that corrupt production reporting queries.
WHEN salary > 20000 THEN 'Mid' WHEN salary > 80000 THEN 'Exec'ELSE when classifying business categories.ELSE 'Other'.
ELSE 0 inside COUNT(CASE ...)! 0 is not NULL, so COUNT() counts every row: 1,50,000 instead of 7,587 cancelled orders. Use COUNT(CASE WHEN ... THEN 1 END) or SUM(CASE WHEN ... THEN 1 ELSE 0 END).
CASE col WHEN NULL THEN 'X' NEVER matches! In Simple CASE, this tests col = NULL, which evaluates to UNKNOWN. For NULL checks, always use Searched CASE: WHEN col IS NULL THEN 'X'.
Click to inspect explicit casting and numeric rounding behavior in PostgreSQL.
price::INT → 1499.95 becomes 1500 (rounds to nearest integer).
Mastering ANSI CAST and PostgreSQL shorthand to prevent silent data corruption.
CAST(expression AS target_type)expression::target_typeid::TEXT, ts::DATE).TO_CHAR(val, 'FM...').SELECT product_id, CAST(product_id AS TEXT) AS id_standard_cast, product_id::TEXT AS id_postgres_shorthand, price, price::INT AS rounded_integer_price, TO_CHAR(price, 'FM₹ 99,99,999.00') AS display_rupee_string FROM products.products ORDER BY product_id LIMIT 10;
Five core conditional logic questions interviewers ask, with production-grade answers.
CASE tier WHEN 'Gold' THEN 1 END. It cannot evaluate inequality (>, <) or check IS NULL because tier = NULL resolves to UNKNOWN. Searched CASE evaluates independent boolean predicates per branch: CASE WHEN marks >= 90 THEN 'A' WHEN marks IS NULL THEN 'Absent' END. Searched CASE is vastly more expressive.Click any flashcard to flip and test your understanding of PostgreSQL branching rules.
Universal principles for writing robust conditional expressions and data transformations.
ELSE 'Other' or ELSE 0) unless NULL is deliberate.COUNT() skips NULLs, it counts only matching occurrences.val::NUMERIC / divisor) to preserve fractional precision.Complete PostgreSQL conditional syntax and explicit conversion index with interactive ▶ Run links.
SELECT CASE 'Gold' WHEN 'Gold' THEN 'VIP' ELSE 'Regular' END;
SELECT CASE WHEN 75000 >= 50000 THEN 'Tier 1' ELSE 'Tier 2' END;
SELECT CASE WHEN 85 >= 90 THEN 'A' WHEN 85 >= 75 THEN 'B' ELSE 'C' END;
SELECT CASE WHEN 1 = 2 THEN 'Match' END;
Apply today's CASE WHEN and casting techniques in the playground, then check your counts against the ones given.
customers.customers, show customer_id, full name, and classify into 'High Priority VIP' (Platinum, Gold: 10,194 customers) or 'General Member' (39,806).sales.orders, order transactions prioritizing 'Cancelled' (1), 'Failed' (2), 'Processing' (3), and others (4).products.products, classify price into 'Budget' (< ₹1,000), 'Mid-Range' (₹1,000–₹10,000), and 'Premium' (> ₹10,000): 848, 3,239 and 1,949 products.products.products, calculate profit margin percentage casting numerator to numeric: (price - cost_price)::NUMERIC / price * 100.sales.shipments, calculate delivered_date - shipped_date and classify into Express (≤ 2 days: 18,058) vs Standard. Handle NULLs cleanly: 14,982 parcels have no delivered_date yet.stores.employees, classify monthly salary into Executive (≥ ₹1,00,000), Senior (≥ ₹60,000), and General: 578, 479 and 1,943 employees.'₹ ' || TO_CHAR(price, 'FM99,99,999.00'). Order by price descending.Track your progress across the 59-session SQL course.
Coming next in Module 2: Defensive SQL & Three-Valued Logic.