TheShiraverseSELECT * TheShiraverseCurriculumPracticeKahaniFAQGet StartedPlaygroundNukteTheShiraverse ↗
Practice › Topic 07

Conditional Logic: practice questions

400 questions in four levels, all on RetailMart, the practice database of this course. Write every query yourself, get it wrong, read the error, fix it. That is how it sticks.

Open the SQL PlaygroundPut RetailMart on your laptop
Answers are coming soon. Post your query in the WhatsApp Community or Discord and we will check it together.

Core syntax, applied directly

CONCEPTUAL

  1. Q1Difference between Simple CASE and Searched CASE - give an example of each.
  2. Q2What happens if NONE of the WHEN clauses match and there's no ELSE?
  3. Q3Can CASE return different data types from different WHEN branches?
  4. Q4Why is ORDER of WHEN clauses important in Searched CASE?
  5. Q5Difference between COALESCE and NULLIF in one sentence each.
  6. Q6What does COALESCE(a, b, c, 'default') do?
  7. Q7When does NULLIF(x, 0) help - and what real problem does it solve?
  8. Q8Difference between CAST(x AS INT) and x::INT - are they equivalent?
  9. Q9Why does casting '12.5' to INT fail?
  10. Q10What does ::TEXT do to a number? Why might you need it before concatenation?

CASE WHEN ON V3 DATA

  1. Q11Map customer tier ('Gold','Silver','Bronze','Platinum') to a friendly label using Simple CASE.
  2. Q12Classify products into 'Cheap' (<100), 'Mid' (<1000), 'Premium' (>=1000) using Searched CASE on price.
  3. Q13Bucket orders by net_total: 'Small'(<500), 'Medium'(<5000), 'Large'(>=5000).
  4. Q14Classify employees by salary: 'Junior'(<30K), 'Mid'(<60K), 'Senior'(>=60K).
  5. Q15Bucket support tickets by priority into a numeric urgency score: Critical=4, High=3, Medium=2, Low=1.
  6. Q16Map order_status to a binary 'Active' (Pending, Processing, Shipped, Out for Delivery) vs 'Closed' (Delivered, Returned, Cancelled, Failed).
  7. Q17Label customer reviews: 'Positive' if rating >= 4, 'Neutral' if rating = 3, 'Negative' if rating < 3.
  8. Q18Classify pay_slips: 'High Tax' if income_tax > 10000, 'Mid' if > 5000, else 'Low'.
  9. Q19Label hr.attendance by check-in: if check_in IS NULL -> 'Absent', else 'Present'.
  10. Q20Label web_events.page_views by device: 'Mobile' or 'Tablet' = 'Touch', 'Desktop' = 'Desktop'.
  11. Q21Classify campaigns by budget: <50K 'Small', <200K 'Medium', else 'Large'.
  12. Q22Categorize call duration: <60s 'Short', <300s 'Medium', else 'Long'.
  13. Q23Label loyalty members: points_balance < 500 'Starter', < 2000 'Engaged', else 'VIP'.
  14. Q24Map api_requests.status_code: 2xx->'OK', 3xx->'Redirect', 4xx->'Client Error', 5xx->'Server Error'.
  15. Q25Bucket application_logs by level: 'ERROR' or 'FATAL' -> 'Alert'; 'WARN' -> 'Caution'; else 'Info'.
  16. Q26Label products as 'Made by Top Brand' if brand_id IN (1,2,3,4,5), else 'Other'.
  17. Q27Label customers as 'New' if registration_date >= CURRENT_DATE - 30, else 'Existing'.
  18. Q28Bucket orders by month-of-year using CASE on EXTRACT(MONTH FROM order_date): 1-3 'Q1', 4-6 'Q2', etc.
  19. Q29Day-of-week label for orders: 0/6 -> 'Weekend', else 'Weekday' (EXTRACT(DOW)).
  20. Q30Mark shipments 'In Transit' if delivered_date IS NULL else 'Delivered'.
  21. Q31Mark tickets 'Open' if resolved_date IS NULL else 'Resolved'.
  22. Q32Label employees as 'In-Store' if store_id IS NOT NULL else 'HQ'.
  23. Q33Tag work_orders by quantity_produced: <500 'Small Batch', <2000 'Mid', else 'Large'.
  24. Q34Tag expenses as 'Major' if amount > 100000, 'Standard' if > 10000, else 'Minor'.
  25. Q35Build a 'is_weekend' boolean using CASE: returns TRUE/FALSE on order_date.
  26. Q36Tag customers by tier ranking in numeric form: Platinum=4, Gold=3, Silver=2, Bronze=1.
  27. Q37Show product_id and a 'Premium' boolean: TRUE if price > 5000.
  28. Q38Categorize hr.attendance entries by check-in HOUR: <9 'Early', <12 'Morning', <17 'Afternoon', else 'Late'.
  29. Q39Label page_views by hour-of-day: <6 'Night', <12 'Morning', <18 'Afternoon', else 'Evening'.
  30. Q40CASE expression returning the LARGER of net_total and 1000 per order (use CASE WHEN ... THEN ... ELSE).

COALESCE + NULLIF

  1. Q41Show customer_id with phone, replacing NULL phones with 'NOT PROVIDED'.
  2. Q42Show ticket resolved_date or '0001-01-01' if NULL using COALESCE.
  3. Q43Show shipment delivered_date with 'PENDING' label if NULL (note: COALESCE needs compatible types).
  4. Q44Show customer email or '[email protected]' fallback.
  5. Q45Show employee store_id or 0 if NULL.
  6. Q46For sales.shipments, show delivered_date or shipped_date (whichever exists first).
  7. Q47Show customer's tier_updated_at OR registration_date (fallback chain).
  8. Q48Show ticket resolved_date OR created_date + INTERVAL '7 days' as estimated resolution.
  9. Q49For api_requests, show user_agent OR 'unknown'.
  10. Q50For page_views, show customer_id::TEXT OR 'anonymous' label.
  11. Q51NULLIF on products.price - if a (hypothetical) zero price exists, return NULL so division won't fail.
  12. Q52NULLIF on call_duration_seconds - if zero, return NULL.
  13. Q53Safe percentage: SELECT 100.0 * x / NULLIF(y, 0) - demo with order quantity / unit_price.
  14. Q54Safe margin: products.products with (price - cost_price) / NULLIF(price, 0).
  15. Q55Safe ratio of resolved to total for tickets: just write the per-row expression, NULL-safe.
  16. Q56COALESCE chain for a customer's display contact: COALESCE(phone, email, 'NO_CONTACT').
  17. Q57Loyalty members: COALESCE(points_balance, 0) - show every customer in the 'members' table with 0 default.
  18. Q58Reviews: COALESCE(rating, 0) - show every review with 0 if no rating.
  19. Q59Returns: COALESCE(refund_amount, 0) - fallback for unrefunded returns.
  20. Q60Marketing campaigns: COALESCE(end_date, '2099-12-31') - open-ended campaigns get a sentinel future date.
  21. Q61Show every shipment with a 'duration' that's delivered_date - shipped_date OR 'N/A'. (Two CASTs needed.)
  22. Q62Show every ticket subject COALESCE(subject, '(empty subject)').
  23. Q63For sales.orders, show payment_mode_id OR -1 if NULL.
  24. Q64For finance.expenses, COALESCE(description, 'No description provided').
  25. Q65For products.products, COALESCE(supplier_id, 0) - show every product with a fallback supplier.
  26. Q66NULLIF on text: NULLIF(TRIM(subject), '') in tickets - converts whitespace-only subjects to NULL.
  27. Q67Show customer first_name normalized: COALESCE(NULLIF(TRIM(first_name), ''), 'Unknown').
  28. Q68Safe division: page_views per customer ID, dividing 1 by NULLIF(customer_id, 0).
  29. Q69Build a fallback chain for product price: COALESCE(price, cost_price, 0).
  30. Q70For sales.shipments, courier_name with fallback: COALESCE(courier_name, 'Unknown Courier').

CAST / TYPE CONVERSIONS

  1. Q71Cast order_id (INT) to TEXT and concatenate: 'Order ' || order_id::TEXT.
  2. Q72Cast price (NUMERIC) to INT to round down - products.products.
  3. Q73Cast net_total to a 2-decimal NUMERIC (ROUND alternative) - sales.orders.
  4. Q74Cast registration_date (DATE) to TEXT in 'YYYY-MM-DD' format via TO_CHAR (the proper way).
  5. Q75Cast text '2025-01-15' to DATE explicitly using ::DATE.
  6. Q76Cast '12.50' to NUMERIC.
  7. Q77Cast '12.50' to NUMERIC(10,2).
  8. Q78Cast a field to TEXT using ::TEXT (web_events.events.event_type).
  9. Q79Cast api_requests.status_code (INT) to TEXT before concatenation.
  10. Q80Cast call_duration_seconds to interval: (call_duration_seconds || ' seconds')::INTERVAL.
  11. Q81Cast quantity_produced (INT) to NUMERIC to get a decimal in division: quantity_produced::NUMERIC / 100.
  12. Q82Cast amount (NUMERIC) to INT for finance.expenses for a simplified summary.
  13. Q83Cast TIMESTAMP to DATE - sales.shipments.shipped_date is already DATE; pick a TIMESTAMP column: web_events.page_views.view_timestamp::DATE.
  14. Q84Cast attendance check_in (TIMESTAMP) to TIME using ::TIME.
  15. Q85Cast attendance check_in to DATE.
  16. Q86Cast a string '1' to BOOLEAN... actually BOOLEAN expects 't' or 'true' - try (value = 'true')::BOOLEAN. Or simpler: 'true'::BOOLEAN.
  17. Q87Cast a NUMERIC like 0.85 to TEXT with TO_CHAR(0.85, '0.00%') for percent display.
  18. Q88Cast products.price to BIGINT (no precision needed for whole-number price).
  19. Q89Cast '100' to INT explicitly using CAST syntax: CAST('100' AS INT).
  20. Q90Show TO_CHAR with format 'FM99,99,999' for Indian-style price grouping on products.price.
  21. Q91Cast text '99.99' to NUMERIC and add 0.01 - result should be 100.00.
  22. Q92Show TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS') - current timestamp as ISO-ish string.
  23. Q93Cast review_date to TEXT using ::TEXT (default formatting).
  24. Q94Cast customer_id (INT) to TEXT and pad with leading zeros using LPAD: LPAD(customer_id::TEXT, 8, '0').
  25. Q95Cast salary (NUMERIC) to INT - drop the decimal.
  26. Q96For sales.orders, cast net_total to INT and prefix with 'Rs': 'Rs' || net_total::INT.
  27. Q97Cast text to JSONB: '{"role": "admin"}'::JSONB.
  28. Q98Cast UUID to TEXT for display in a report.
  29. Q99Cast '2025' (text) to INT - should be 2025.
  30. Q100Build a display: order_id::TEXT || ' (' || TO_CHAR(net_total, 'FMRs99,99,999') || ')' for invoice-like label.

Combined ideas, multi-step thinking

CONDITIONAL LOGIC DEEPER - CONCEPTUAL

  1. Q1Compare CASE in WHERE vs CASE in SELECT - when does each shine?
  2. Q2What's the difference between Simple CASE and Searched CASE - give example of each.
  3. Q3Can a CASE return different DATA TYPES from different WHEN branches?
  4. Q4Explain CASE inside SUM - what does SUM(CASE WHEN ... THEN net_total END) compute?
  5. Q5Compare COALESCE(a, b, c) vs CASE WHEN a IS NOT NULL THEN a WHEN b IS NOT NULL THEN b ELSE c END.
  6. Q6Why is NULLIF(x, 0) commonly used in a division denominator?
  7. Q7Explain CAST('12.5' AS INT) vs '12.5'::INT - same result?
  8. Q8What is the difference between :: (PostgreSQL cast) and CAST (SQL-standard)?
  9. Q9Why does casting '2025-13-01'::DATE fail - what's the rule for valid dates?
  10. Q10What's the type-conversion priority of CASE: if branches return TEXT and INT mixed, what does the optimizer do?
  11. Q11Explain how CASE evaluates short-circuit: does it eval all branches or stop at first match?
  12. Q12Why is CASE WHEN x = NULL THEN 'a' always FALSE - and what should you use instead?
  13. Q13Compare COALESCE(a, b) and a IS DISTINCT FROM b - when do they relate?
  14. Q14Why is NULLIF useful for sentinel values like empty string?
  15. Q15Can CAST happen implicitly - give two cases when PostgreSQL silently casts.
  16. Q16Difference between IS NULL and = NULL - why does the second never work?
  17. Q17Compare CAST(x AS TEXT) vs x::TEXT vs TO_CHAR(x, ...) - when do you choose each?
  18. Q18What happens if all WHEN branches in a CASE return NULL but there's no ELSE?
  19. Q19Why is GREATEST(a, b, c) related to MAX in spirit but different in scope?
  20. Q20Can you nest CASE inside CASE - give a use case.
  21. Q21What happens when COALESCE arguments include different types (INT, TEXT)?
  22. Q22When does CAST silently truncate vs error? Example: NUMERIC -> INT vs TEXT -> INT.
  23. Q23Why does NULLIF(NULL, 0) return NULL, not 0?
  24. Q24Compare CASE expression vs COALESCE for readability - which is preferred for fallback chains?
  25. Q25Why is CAST(price AS NUMERIC(10,2)) safer than CAST(price AS NUMERIC) in production?

NESTED + COMBINED CASE

  1. Q26Classify customers into 'Premium' (Gold/Platinum) -> further into 'Loyal' (registered >= 3 yrs) vs 'New Premium' (< 3 yrs). Use NESTED CASE.
  2. Q27Classify orders: high-value (> 10000) AND Delivered -> 'A'; high-value AND not delivered -> 'B'; low-value AND Delivered -> 'C'; else 'D'.
  3. Q28Bucket products: brand IS NULL -> 'Unknown brand'; ELSE bucket by price (cheap/mid/premium).
  4. Q29Combine CASE + LIKE: classify employees by role pattern: contains 'Manager' -> 'Mgr'; contains 'Engineer' -> 'Eng'; else 'Other'.
  5. Q30Combine CASE + EXTRACT(DOW): label each order_date as 'Weekend' (DOW IN 0,6) or weekday name.
  6. Q31Build a customer "lifecycle stage" label using 3+ conditions: tier + registration_date + (assume) order count > N.
  7. Q32Show every ticket with a 'severity_score' INT: priority=Critical->4, High->3, Medium->2, Low->1, else 0.
  8. Q33Show every order with an 'urgency_label': order_status='Delivered' -> 'Done'; 'Pending' -> 'Urgent if > 7 days old' else 'OK'; etc.
  9. Q34Conditional aggregation preview: SUM(CASE WHEN order_status='Delivered' THEN net_total END) AS delivered_revenue from sales.orders.
  10. Q35Conditional COUNT: COUNT(CASE WHEN rating = 5 THEN 1 END) AS five_stars from customers.reviews.
  11. Q36Show every employee with bracket: salary < 30K -> 'Junior'; < 60K -> 'Mid'; < 100K -> 'Senior'; else 'Executive'.
  12. Q37Build a 'safety score' from sales.shipments using CASE: delivered same day as shipped -> 5; 1-2 days -> 4; 3-7 days -> 3; > 7 days -> 2; not delivered -> 1.
  13. Q38Bucket product reviews using BOTH rating and length(comment): 5-star with long comment -> 'Detailed praise'; 5-star with no comment -> 'Quick like'; etc.
  14. Q39Conditional aggregation: SUM of net_total only for orders placed on weekends.
  15. Q40Use CASE inside ORDER BY: sort by priority custom order (Critical first, then High, Medium, Low).
  16. Q41Show every customer with their tier_rank: Bronze=1, Silver=2, Gold=3, Platinum=4 - useful as a sort key.
  17. Q42Build a hierarchical label: combine tier + registration year as a label like 'Gold-2024'.
  18. Q43Use CASE to show only specific months by name (filter via WHERE doesn't quite fit - use CASE in SELECT to label 'Q1', 'Q2', 'Q3', 'Q4').
  19. Q44Build a 'days_to_action' field: if status='Delivered' return delivered_date - order_date; else CURRENT_DATE - order_date.
  20. Q45Show every order with a 'paid_status' derived from sales.payments via a subquery + CASE (paid in full, partial, unpaid).
  21. Q46Compute the GREATEST of three: GREATEST(price, cost_price, 100) from products.products.
  22. Q47Compute the LEAST of three: LEAST(price, cost_price, 100).
  23. Q48Use CASE to detect "this row is the FIRST order of its customer" - show ticket_id with a Y/N marker (correlated subquery for MIN).
  24. Q49Build a CASE that converts hr.attendance check_in into a shift label: < 6am 'Night', < 12pm 'Morning', < 6pm 'Day', else 'Evening'.
  25. Q50Combine CASE + INTERVAL: tag every ticket as 'Fast' if resolved within 1 hour, 'Same Day' if same day, 'Slow' otherwise.

COALESCE / NULLIF CHAINS

  1. Q51For each customer, prefer phone, fall back to email, fall back to 'NO CONTACT' - single COALESCE chain.
  2. Q52For each shipment, show "delivered_date OR shipped_date + 5 days OR CURRENT_DATE" - three-level COALESCE.
  3. Q53For each loyalty member, show next_tier_threshold using COALESCE on points_balance vs benchmarks.
  4. Q54NULLIF(LENGTH(TRIM(subject)), 0) - convert empty/whitespace-only subjects to NULL on tickets.
  5. Q55NULLIF(price, 0) prevents divide-by-zero in margin calc.
  6. Q56Safe percentage: 100.0 * good_count / NULLIF(total_count, 0) - apply to a real RetailMart aggregate.
  7. Q57COALESCE multiple email columns into one preferred email (real-world: contact_email, billing_email, alt_email).
  8. Q58Replace ALL "" with NULL on the fly: NULLIF(TRIM(col), '') for every text column you select.
  9. Q59COALESCE(t.tier_name, 'No Tier') for customers who aren't loyalty members.
  10. Q60COALESCE(refund_amount, 0) for returns awaiting refund.
  11. Q61Show every customer's tier OR 'Standard' if NULL - single-line.
  12. Q62COALESCE inside a SUM: SUM(COALESCE(refund_amount, 0)) so NULLs count as 0.
  13. Q63NULLIF(end_date, '9999-12-31') - turn sentinel dates back into NULL.
  14. Q64COALESCE on JSONB key access: payload->>'city' OR 'unknown'.
  15. Q65Triple COALESCE: city from addresses -> city from store -> 'India' as final fallback.
  16. Q66Build "Hello [name]!" greeting using COALESCE first_name, last_name, email, 'Guest'.
  17. Q67NULLIF for whitespace-only emails: NULLIF(TRIM(email), '').
  18. Q68For payments, COALESCE(amount, 0) AS amount when amount might be NULL.
  19. Q69For employees, COALESCE(store_id::TEXT, 'HQ') so HQ staff get a label, not NULL.
  20. Q70COALESCE customer tier_updated_at OR registration_date AS effective_change_date.
  21. Q71Show shipments with: status if not NULL, else 'In Transit' if shipped_date IS NOT NULL, else 'Unknown'.
  22. Q72For tickets, COALESCE category, 'General' AS effective_category.
  23. Q73For finance.expenses, COALESCE(description, 'No description') for display.
  24. Q74For api_requests, COALESCE(user_agent, 'unknown') AS ua.
  25. Q75NULLIF + COALESCE combo: COALESCE(NULLIF(TRIM(first_name), ''), 'Unknown') AS clean_first.

CAST + PRODUCTION COMBOS

  1. Q76Build an invoice string with multiple CASTs: 'Order #' || order_id::TEXT || ' (Rs' || net_total::TEXT || ')'.
  2. Q77Cast products.price to ROUND-friendly NUMERIC(10,2).
  3. Q78Cast view_timestamp::DATE to bucket page_views by date.
  4. Q79Use ::INTERVAL to cast '300 seconds' for a call into an interval.
  5. Q80Combine ::DATE + ::TIME on hr.attendance check_in for separate display.
  6. Q81Use TO_CHAR(price, 'FMRs99,99,999.00') to display Indian-style currency.
  7. Q82Use TO_CHAR(order_date, 'FMDay, DD Mon YYYY') for a friendly date display.
  8. Q83Show every employee's age_in_years using EXTRACT(YEAR FROM AGE(joining_date))::INT.
  9. Q84Show every customer with their preferred display name: full name OR email OR 'Customer #' || customer_id.
  10. Q85Combine CASE + CAST: bucket products by price, return a labeled string.
  11. Q86Build a "stock alert" line per product: 'PRODUCT-' || product_id::TEXT || ': ' || product_name || ' is ' || CASE WHEN in_stock THEN 'IN STOCK' ELSE 'OUT' END.
  12. Q87Build an order summary: order_id, customer name (INITCAP), net_total (FORMATTED), status (UPPER), date (TO_CHAR).
  13. Q88Build a ticket aging banner: 'AGING ' || (CURRENT_DATE - created_date::DATE)::TEXT || ' DAYS - PRIORITY ' || UPPER(priority) for open tickets.
  14. Q89Build a customer status row: tier (COALESCE), tenure (years), and a SAFE-divided "points-per-year" using NULLIF.
  15. Q90Build a 'risk' flag for sales.shipments combining CASE + NULLIF: high risk if delayed > 14 days AND not yet delivered.
  16. Q91Cast loyalty.members.points_balance to BIGINT to support summed totals over many customers.
  17. Q92Combine CASE + CAST + COALESCE: show every payment with amount (COALESCE 0), mode (CASE in-store/online), and date (TO_CHAR).
  18. Q93Build a refund_request_age in days using CASE on refund_amount IS NULL plus date arithmetic.
  19. Q94For each customer, compute a "spend tier" using a single CASE on a (hypothetical) total_spend, with NULLIF for missing data.
  20. Q95Show every employee with a 'shift' classification using CASE on EXTRACT(HOUR FROM check_in), and treat NULL check_in as 'Absent'.
  21. Q96Show every product with 'margin_pct' = ROUND(100.0 * (price - cost_price) / NULLIF(price, 0), 2) - safe division.
  22. Q97Compute the 'pay component split' per pay_slip: basic %, hra %, allowances %, taxes %, net % using NULLIF(gross, 0).
  23. Q98For each application_log, build a severity_int = CASE level WHEN 'FATAL' THEN 5 WHEN 'ERROR' THEN 4 WHEN 'WARN' THEN 3 WHEN 'INFO' THEN 2 ELSE 1 END.
  24. Q99For each api_request, compute a status_category (2xx/3xx/4xx/5xx) using CASE on status_code.
  25. Q100Combine ALL TOOLS: For each order, show order_id, INITCAP customer name, net_total formatted, status uppercased, and 'days since' as INT - with NULL-safety throughout.

Interview grade, edge cases

CONDITIONAL LOGIC - CONCEPTUAL

  1. Q1Compare simple CASE WHEN val=X vs searched CASE WHEN cond.
  2. Q2Explain how CASE short-circuits - first match wins.
  3. Q3Why does CASE inside a WHERE clause defeat indexes?
  4. Q4Can CASE return different types per branch?
  5. Q5What does CASE return when no WHEN matches and no ELSE - NULL.
  6. Q6COALESCE(a, b, c, d) - what determines the chosen value?
  7. Q7COALESCE return type - promotion rules across branches.
  8. Q8When does COALESCE(NULL, NULL, NULL) = NULL - and how to convert to default value.
  9. Q9NULLIF(a, b) returns NULL if a=b else a - what's the prime use case?
  10. Q10Why does NULLIF protect against division by zero (NULLIF(denom, 0))?
  11. Q11Compare CAST(x AS numeric) vs x::numeric - same result.
  12. Q12What happens when CAST fails on garbage input?
  13. Q13Why is CAST inside WHERE often non-sargable?
  14. Q14Explain implicit vs explicit casting - and when implicit fails.
  15. Q15Why does '1' + 1 fail in strict modes but auto-cast in Postgres?
  16. Q16Compare TRUE/FALSE/NULL three-valued logic (Boolean ternary).
  17. Q17Explain WHERE col AND NOT col1 - how NULL propagates.
  18. Q18Compare IS TRUE / IS FALSE / IS UNKNOWN - and when each is needed.
  19. Q19Why is GREATEST(NULL, 5) NULL-safe in some dialects but not others - check Postgres behavior.
  20. Q20What is the ELSE-fallback hierarchy: COALESCE > CASE?
  21. Q21Explain how CASE drives "conditional aggregation" (SUM CASE WHEN ...).
  22. Q22Why is CASE in ORDER BY useful for custom sort priorities?
  23. Q23Compare CASE in SELECT (per-row) vs HAVING (after grouping).
  24. Q24What is BOOL_AND / BOOL_OR - aggregate boolean reducers.
  25. Q25Explain why CASE NULL behaviour is symmetrical: CASE NULL WHEN NULL ... never matches (use IS NULL).

MULTI-BRANCH CASE

  1. Q26Per order, classify net_total: <100 = 'tiny', <500 = 'small', <2000 = 'medium', else 'large'.
  2. Q27Per ticket priority + status, classify into action queue (Critical+Open = '1-act-now', etc.).
  3. Q28Per customer, classify tier_id + city to "VIP-metro", "Mid-metro", etc.
  4. Q29Per product, classify by price band AND brand reputation.
  5. Q30Per employee, classify role + tenure into seniority level.
  6. Q31Per call_duration, bucket into short/medium/long with custom thresholds.
  7. Q32Per page_view URL, classify visit type (landing, product, checkout, other).
  8. Q33Per shipment, classify delivery_age (on_time, late, very_late).
  9. Q34Per pay_slip, classify bonus eligibility (>10% over base).
  10. Q35Per ad spend, classify ROI band (low/med/high).
  11. Q36Per review, classify sentiment (rating + review_text length).
  12. Q37Per warehouse snapshot, classify stock status (low/healthy/excess).
  13. Q38Per loyalty member, classify status (Bronze new, Silver active, etc.).
  14. Q39Per campaign, classify lifecycle (planning/active/completed/closed).
  15. Q40Per inventory_snapshot, flag risk (qty < reorder_level).
  16. Q41Per customer, derive "lifecycle_stage" (new/active/dormant/churned) based on last_order_date.
  17. Q42Nested CASE: classify by region THEN city THEN tier.
  18. Q43Per refund, classify reason category (product issue/shipping/wrong item/other).
  19. Q44Per support call, classify shift (morning/afternoon/evening/night) by hour.
  20. Q45Per fiscal quarter, classify orders into seasonality buckets.
  21. Q46CASE inside JOIN ON: join orders to staff schedule based on hour-of-day.
  22. Q47CASE inside ORDER BY: sort by priority CASE WHEN ... THEN 1 ... END.
  23. Q48CASE inside GROUP BY: bucket revenues then group by bucket.
  24. Q49CASE inside SUM: conditional total of net_total for delivered orders.
  25. Q50CASE returning JSONB: build a per-row JSON object based on conditions.

NULL SAFETY & TYPE COERCION

  1. Q51Use COALESCE to default missing tier_id to 0.
  2. Q52Use COALESCE to display "Unknown" for missing city.
  3. Q53Use NULLIF to safely divide net_total by total_units (0 -> NULL).
  4. Q54Compute conversion rate with NULLIF: visits / NULLIF(orders, 0).
  5. Q55Use COALESCE chain: phone -> mobile -> office -> 'No phone'.
  6. Q56Compute "effective tier": COALESCE(loyalty.tier_id, customer.tier_id, 1).
  7. Q57Compute customer age, default 0 when DOB missing.
  8. Q58Cast TEXT date to DATE - handle parse errors with try-catch via regex pre-check.
  9. Q59Cast TEXT price to NUMERIC - first strip currency symbols.
  10. Q60Cast IP as INET - only if regex matches.
  11. Q61Use CASE-in-ON: LEFT JOIN orders on (CASE WHEN o.status='Active' THEN o.cust_id END) = c.id.
  12. Q62Use IS DISTINCT FROM for nullable equality (vs =).
  13. Q63Use IS NOT DISTINCT FROM to detect "same or both NULL".
  14. Q64Boolean reduction: BOOL_OR(is_active) per group.
  15. Q65Boolean reduction: BOOL_AND(is_valid) per group.
  16. Q66Compute "% delivered" using FILTER + COUNT.
  17. Q67Convert empty string to NULL: NULLIF(col, '').
  18. Q68Convert 0 to NULL before dividing.
  19. Q69CASE to safely cast '123abc' -> return NULL when not numeric.
  20. Q70GREATEST(salary, min_wage) - floor a value.
  21. Q71LEAST(due_date, deadline) - cap a value.
  22. Q72BOOL_AND on NULL - what does it return? (Hint: depends on rows.)
  23. Q73Test predicate IS UNKNOWN: WHERE (col1 = col2) IS NOT TRUE.
  24. Q74NULLIF stacked: NULLIF(NULLIF(col, ''), 'N/A').
  25. Q75Verify GREATEST(NULL, 5) = 5 in Postgres (skips NULLs).

BUSINESS SCORING/TIER LOGIC

  1. Q76Customer "VIP score" = points*0.5 + lifetime_orders*1 + (tier_id*100).
  2. Q77Tier upgrade rule: if points > 1000 AND lifetime_orders > 20 -> Gold.
  3. Q78Risk score for tickets: 100 - resolution_hours * 5 (CASE for negative).
  4. Q79Fraud-flag: large net_total AND new customer (< 30 days).
  5. Q80Churn risk: last_order > 90 days AND tier = 'Bronze'.
  6. Q81Reorder suggestion: stock < threshold AND velocity > 5/week.
  7. Q82Employee performance: tickets_resolved * 2 + reviews_received - escalations * 3.
  8. Q83Shipment efficiency score: 100 - delivery_days * 10.
  9. Q84Campaign success: ROI = (revenue - spend) / spend, conditional buckets.
  10. Q85Customer health index: combination of orders + reviews + tickets.
  11. Q86Product profitability: (price - cost) / cost; tier = high/med/low.
  12. Q87Store performance index: revenue * 0.5 + orders * 0.3 + avg_review * 100.
  13. Q88Inventory turnover ratio: COGS / avg_inventory; CASE for unhealthy ranges.
  14. Q89Support agent workload score: open_tickets + (urgent_tickets * 3).
  15. Q90Multi-channel engagement score: orders + reviews + tickets + calls (with weights).
  16. Q91Build a "next best action" CASE: if churn_risk='high' -> call; if tickets_unresolved > 2 -> support.
  17. Q92Build a "promotion eligibility" CASE for employees.
  18. Q93Build a "loyalty next-tier" CASE: how many points needed to advance.
  19. Q94Build a "refund auto-approval" CASE based on customer LTV.
  20. Q95Build a "delivery SLA" CASE: by tier and product price.
  21. Q96Build a "discount tier" CASE based on order_total.
  22. Q97Build a "geo-zone shipping cost" CASE based on city.
  23. Q98Build a "subscription tier" CASE: monthly orders + avg_basket.
  24. Q99Build a "credit score-equivalent" CASE: orders / refunds ratio.
  25. Q100Single-query 360deg customer dashboard with 10 derived CASE columns.

Production scenarios, optimisation

DECISION TABLES

  1. Q1Tier upgrade matrix: points x lifetime_orders -> tier.
  2. Q2Pricing matrix: brand x region x tier -> discount %.
  3. Q3Shipping rules: weight x distance x tier -> cost.
  4. Q4Returns approval: amount x tenure x tier -> auto-approve / manual.
  5. Q5Fraud rules: amount x ip_country x velocity -> block / review / allow.
  6. Q6Loyalty bonus: spend x multiplier x tier -> points.
  7. Q7Subscription tier: monthly orders x avg basket -> plan.
  8. Q8Ticket priority: customer tier x product price x urgency -> priority.
  9. Q9Ad spend rules: platform x geo x season -> bid.
  10. Q10Inventory restock: velocity x lead_time x season -> quantity.
  11. Q11Employee bonus: review x tenure x dept -> bonus %.
  12. Q12Promotion eligibility: revenue x tenure x violations -> flag.
  13. Q13Tax bracket: income x dependents x region -> rate.
  14. Q14Commission rule: sales x tier x product -> commission.
  15. Q15Lead score: source x pages x actions -> score.
  16. Q16Churn risk: last_order x tickets x tier -> risk_band.
  17. Q17VIP status: spend x engagement x tenure -> VIP_level.
  18. Q18Refund auto-approval: amount x LTV x reason -> auto/manual.
  19. Q19Credit limit: tier x history x geographic -> limit.
  20. Q20Bonus eligibility: salary x performance x tenure -> eligible.
  21. Q21Risk score: amount x time-of-day x device -> score.
  22. Q22Personalization: recent x interests x purchases -> recommend.
  23. Q23Marketing segment: tier x geo x purchase pattern -> segment.
  24. Q24SLA tier: contract x support level x volume -> SLA.
  25. Q25Build a "100-rule decision table" using nested CASE.

RFM, NPS, COHORTS

  1. Q26R (Recency): days since last order.
  2. Q27F (Frequency): orders in last 365d.
  3. Q28M (Monetary): SUM(net_total) lifetime.
  4. Q29R-score: NTILE(5) of recency.
  5. Q30F-score: NTILE(5) of frequency.
  6. Q31M-score: NTILE(5) of monetary.
  7. Q32RFM combined: '555' = best, '111' = worst.
  8. Q33RFM segment names (Champions, Loyal, At-Risk, Lost).
  9. Q34Compute NPS from ratings (Promoters - Detractors).
  10. Q35NPS per region.
  11. Q36NPS per product.
  12. Q37NPS per ticket category.
  13. Q38NPS trend over months.
  14. Q39Cohort by signup month, retention by month-N.
  15. Q40Cohort by first-order month, repeat rate.
  16. Q41Activation rate per campaign.
  17. Q42Customer LTV by cohort.
  18. Q43Churn rate per month.
  19. Q44Win-back targets: lapsed customers.
  20. Q45Engagement decile: visits x purchases x reviews.
  21. Q46Risk decile: refund rate x ticket rate x deliveries.
  22. Q47Health score: NPS x R x F x M weighted.
  23. Q48Trend slope: linear regression of orders over time.
  24. Q49Stickiness: DAU/MAU equivalent for orders.
  25. Q50Build a complete RFM + NPS + cohort dashboard.

STATE MACHINES & WORKFLOWS

  1. Q51Order state: Pending -> Paid -> Shipped -> Delivered.
  2. Q52Ticket state: Open -> Assigned -> InProgress -> Resolved -> Closed.
  3. Q53Shipment state: Created -> Picked -> InTransit -> Delivered / Failed.
  4. Q54Customer lifecycle: New -> Active -> Dormant -> Churned.
  5. Q55Subscription state: Trial -> Active -> Paused -> Cancelled.
  6. Q56Refund state: Requested -> Reviewed -> Approved/Denied.
  7. Q57Campaign state: Draft -> Active -> Ended -> Archived.
  8. Q58Employee onboarding state: Applied -> Interviewing -> Hired -> Onboarded.
  9. Q59Detect invalid transitions (Open -> Resolved without InProgress).
  10. Q60State age (time in each state).
  11. Q61SLA breach detection.
  12. Q62Auto-advance states via cron.
  13. Q63State velocity: avg time per state.
  14. Q64State funnel: % reaching each.
  15. Q65Build a state graph (mermaid output).
  16. Q66Reverse engineer state transitions from audit.
  17. Q67Detect "stuck" rows (in same state too long).
  18. Q68Build a workflow engine in pure SQL (small DSL).
  19. Q69Multi-actor approvals (manager + finance + legal).
  20. Q70Priority elevation if SLA risk.
  21. Q71Auto-escalation rules.
  22. Q72State rollback rules.
  23. Q73Conditional branching (CASE in next-state).
  24. Q74Parallel states (multiple in flight).
  25. Q75Build "order lifecycle dashboard" via state-time analysis.

360deg DASHBOARDS

  1. Q76Customer 360deg: 20 derived metrics in one row.
  2. Q77Product 360deg: sales, returns, inventory, reviews.
  3. Q78Store 360deg: orders, revenue, employees, inventory, complaints.
  4. Q79Employee 360deg: tickets, calls, sales, attendance, bonus.
  5. Q80Region 360deg: customers, stores, revenue, complaints.
  6. Q81Campaign 360deg: spend, attributions, conversions, ROI.
  7. Q82Supplier 360deg: shipments, products, costs.
  8. Q83Brand 360deg: products, sales, returns, reviews.
  9. Q84Category 360deg: brands, products, revenue, trends.
  10. Q85Tier 360deg: members, points, retention, revenue.
  11. Q86Warehouse 360deg: capacity, throughput, inventory age.
  12. Q87Courier 360deg: shipments, delivery time, complaints.
  13. Q88Agent 360deg: tickets, calls, ratings, hours.
  14. Q89Reviewer 360deg: count, ratings, products covered, sentiment.
  15. Q90Refund 360deg: rate, reasons, top customers.
  16. Q91Health dashboard: 30 KPIs.
  17. Q92Inventory health: SKUs in stock, low, out-of-stock.
  18. Q93Marketing dashboard: spend, revenue, CAC, ROAS.
  19. Q94Operations dashboard: throughput, lag, errors.
  20. Q95Finance dashboard: revenue, cost, profit, margin.
  21. Q96HR dashboard: headcount, payroll, attrition.
  22. Q97Customer service dashboard: SLA, CSAT, FCR.
  23. Q98Sales dashboard: orders, AOV, CR, repeat.
  24. Q99Build executive 1-page report (50 metrics).
  25. Q100Build "board meeting summary" - 10 numbers that matter.