📦 Shared Sample Schema used in every example

Every example in this course reuses these four tables so you can actually run the queries yourself in a Snowflake trial account (or any SQL engine — the syntax is standard except where marked Snowflake-specific). Run this once at the top of your worksheet.

DDL

CREATE OR REPLACE TABLE employees (
    emp_id       INT,
    name         VARCHAR(50),
    department   VARCHAR(30),
    salary       NUMBER(10,2),
    hire_date    DATE,
    manager_id   INT,
    bonus        NUMBER(10,2)
);

CREATE OR REPLACE TABLE orders (
    order_id     INT,
    customer_id  INT,
    order_date   DATE,
    amount       NUMBER(10,2),
    status       VARCHAR(20),
    region       VARCHAR(20)
);

CREATE OR REPLACE TABLE products (
    product_id     INT,
    product_name   VARCHAR(50),
    category       VARCHAR(30),
    price          NUMBER(10,2),
    stock_qty      INT
);

CREATE OR REPLACE TABLE customers (
    customer_id    INT,
    customer_name  VARCHAR(50),
    signup_date    DATE,
    total_spent    NUMBER(10,2),
    country        VARCHAR(30)
);Copy

Sample Data

INSERT INTO employees VALUES
(1,'Asha','Engineering',95000,'2019-03-14',NULL,8000),
(2,'Ravi','Engineering',72000,'2021-07-01',1,3000),
(3,'Meera','Sales',58000,'2020-01-20',NULL,NULL),
(4,'John','Sales',61000,'2022-05-11',3,1500),
(5,'Divya','HR',49000,'2018-11-30',NULL,500),
(6,'Karan','Engineering',110000,'2016-02-01',1,12000);

INSERT INTO orders VALUES
(101,1,'2024-01-05',250.00,'DELIVERED','APAC'),
(102,2,'2024-01-08',80.50,'CANCELLED','EU'),
(103,1,'2024-02-14',1200.00,'DELIVERED','APAC'),
(104,3,'2024-02-20',NULL,'PENDING','US'),
(105,4,'2024-03-01',45.00,'RETURNED','US'),
(106,2,'2024-03-15',320.75,'DELIVERED','EU');

INSERT INTO products VALUES
(1,'Laptop','Electronics',75000,12),
(2,'Mouse','Electronics',600,0),
(3,'Desk Chair','Furniture',8500,5),
(4,'Notebook','Stationery',50,300);

INSERT INTO customers VALUES
(1,'Acme Corp','2020-06-01',45000,'India'),
(2,'Globex','2021-09-12',3200,'Germany'),
(3,'Initech','2019-01-01',0,'USA'),
(4,'Umbrella','2022-03-22',760,'USA');Copy

1. Introduction foundations

Think of CASE as SQL's version of "if this, then that, otherwise something else" — but it's an expression, not a statement. It plugs into any place a value is allowed: a column, a filter, an ORDER BY, even inside SUM().

What is a CASE expression?

CASE evaluates a list of conditions in order and returns the value tied to the first condition that is true. If nothing matches, it returns the ELSE value (or NULL if there's no ELSE). It always returns exactly one value per row.

SELECT name, salary,
    CASE
        WHEN salary >= 90000 THEN 'High'
        WHEN salary >= 60000 THEN 'Medium'
        ELSE 'Low'
    END AS salary_band
FROM employees;Copy

Difference between CASE expression and IF logic

Procedural languages use IF as a statement that controls program flow (branching code paths). SQL's CASE is an expression that produces a value inline, inside a query — it never "runs code," it just resolves to a scalar. Snowflake also has an IFF() function (Section 25) which behaves like a compact two-way IF, but it's still an expression under the hood, just shorthand for a simple two-branch CASE.

When to use CASE

• Turning raw values into readable labels (e.g., status codes → "Active"/"Inactive")
• Bucketing continuous values into bands (salary bands, age groups)
• Conditional aggregation — counting/summing only rows that meet a condition
• Custom sort order that isn't alphabetical/numeric
• Data cleaning — standardizing inconsistent values

How CASE is evaluated — first matching condition wins

Snowflake checks WHEN clauses top to bottom and stops at the first TRUE. Order matters — put the most specific condition first.

-- WRONG: salary >= 60000 catches everyone above 60k first,
-- so nobody ever reaches the "High" branch
CASE
    WHEN salary >= 60000 THEN 'Medium'
    WHEN salary >= 90000 THEN 'High'   -- unreachable!
    ELSE 'Low'
ENDCopy

ELSE behavior

ELSE is optional. If you skip it and no WHEN matches, the result is NULL — not an error. Always add an explicit ELSE in production code so "unexpected" values are visible instead of silently becoming NULL.

NULL behavior

A WHEN condition that evaluates to NULL (not TRUE, not FALSE) is treated as not matching — CASE just moves to the next WHEN. This is why WHEN column = NULL never works (see Section 6) — you need IS NULL.

2. Simple CASE syntax

"Simple CASE" compares one expression against a list of exact values — like a switch statement.

CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ELSE result
ENDCopy

Matching values & multiple WHEN clauses

SELECT department,
    CASE department
        WHEN 'Engineering' THEN 'Tech'
        WHEN 'Sales' THEN 'Revenue'
        WHEN 'HR' THEN 'People'
        ELSE 'Other'
    END AS dept_group
FROM employees;Copy

Data type consistency

Every THEN/ELSE result must resolve to a common data type. Snowflake will try to implicitly coerce (e.g., INT and NUMBER mix fine), but mixing VARCHAR and NUMBER without casting throws an error. Cast explicitly with ::VARCHAR when mixing types.

-- Error-prone: mixing text and number results
CASE WHEN bonus IS NULL THEN 'None' ELSE bonus END  -- fails
-- Fixed: cast bonus to text
CASE WHEN bonus IS NULL THEN 'None' ELSE bonus::VARCHAR ENDCopy

Returning NULL & using aliases

You can explicitly return NULL from a branch, and you should always alias the CASE expression with AS — otherwise Snowflake auto-names it something unreadable like CASE_WHEN....

CASE department
    WHEN 'HR' THEN NULL       -- deliberately hide HR from this report
    ELSE department
END AS visible_dept

3. Searched CASE syntax

"Searched CASE" evaluates a full boolean condition per WHEN — this is what you'll use 90% of the time in real ETL and reporting logic.

CASE
    WHEN condition THEN result
    WHEN condition THEN result
    ELSE result
ENDCopy

Boolean expressions & comparison operators

SELECT order_id, amount,
    CASE
        WHEN amount > 500 THEN 'Large'
        WHEN amount BETWEEN 100 AND 500 THEN 'Medium'
        WHEN amount IS NULL THEN 'Unknown'
        ELSE 'Small'
    END AS order_size
FROM orders;Copy

Multiple conditions, range checks & complex expressions

Each WHEN can combine several columns, functions, subqueries — anything that resolves to TRUE/FALSE.

SELECT e.name, e.salary, e.department,
    CASE
        WHEN e.department = 'Engineering' AND e.salary > 100000 THEN 'Senior Eng'
        WHEN e.department = 'Sales' AND e.hire_date < '2021-01-01' THEN 'Tenured Sales'
        ELSE 'Standard'
    END AS tier
FROM employees e;Copy

4. CASE with Comparison Operators operators

Every operator SQL supports for filtering also works inside a WHEN clause.

SELECT name, salary, department,
  CASE
    WHEN salary = 95000 THEN 'Exactly 95k'
    WHEN salary <> 95000 AND salary != 72000 THEN 'Neither'       -- <> and != are identical in Snowflake
    WHEN salary < 60000 THEN 'Under 60k'
    WHEN salary > 100000 THEN 'Over 100k'
    WHEN salary <= 61000 THEN 'At most 61k'
    WHEN salary >= 90000 THEN 'At least 90k'
    WHEN salary BETWEEN 60000 AND 80000 THEN '60k-80k range'
    WHEN department IN ('HR','Sales') THEN 'Non-tech'
    WHEN department NOT IN ('HR') THEN 'Not HR'
    WHEN name LIKE 'A%' THEN 'Starts with A'
    WHEN name ILIKE 'ravi' THEN 'Case-insensitive match'   -- Snowflake-specific
    WHEN name RLIKE '^[A-K].*' THEN 'Regex A-K start'
    ELSE 'Other'
  END AS flag
FROM employees;Copy

• ILIKE — case-insensitive LIKE, Snowflake convenience function (avoids wrapping both sides in UPPER())
• RLIKE — full regular-expression matching, useful for pattern-based classification (email domains, ID formats)

5. CASE with Logical Operators AND / OR / NOT

Combine multiple conditions per branch with AND/OR, and always use parentheses to make precedence explicit — SQL evaluates AND before OR, which surprises people.

SELECT department, salary, hire_date,
  CASE
    WHEN (department = 'Engineering' OR department = 'Sales')
         AND salary > 60000 THEN 'Priority Review'
    WHEN NOT (department = 'HR') THEN 'Non-HR'
    ELSE 'Standard'
  END AS review_flag
FROM employees;Copy

Nested conditions

You can nest AND/OR/NOT arbitrarily deep — but past 2-3 levels, readability suffers. Consider a CTE that pre-computes boolean flags as columns, then a simpler CASE reads those flags.

CASE
    WHEN (a AND (b OR c)) AND NOT (d AND e) THEN 'Complex Match'
    ELSE 'No Match'
END

6. CASE and NULL Handling critical topic

This is the single most-tested CASE topic in interviews. NULL is not a value — it's "unknown" — and that changes how every comparison behaves.

IS NULL / IS NOT NULL / NULL comparison / ELSE NULL

SELECT order_id, amount,
    CASE
        WHEN amount IS NULL THEN 'Missing'
        WHEN amount IS NOT NULL THEN 'Present'
        ELSE NULL   -- unreachable here, but shown for completeness
    END AS amount_status
FROM orders;Copy

NULL-handling functions (COALESCE, NVL, NVL2, IFNULL, NULLIF + Snowflake-specific)

SELECT name,
    COALESCE(bonus, 0) AS bonus_safe,
    NVL2(bonus, 'Has Bonus', 'No Bonus') AS bonus_flag,
    ZEROIFNULL(bonus) AS bonus_or_zero
FROM employees;Copy

7. CASE in SELECT derived columns

The most common use — turning raw data into a business-readable column right in the output.

Conditional labels, status columns, data classification, category mapping

SELECT order_id, status,
    CASE status
        WHEN 'DELIVERED' THEN '✅ Complete'
        WHEN 'CANCELLED' THEN '❌ Cancelled'
        WHEN 'PENDING' THEN '⏳ In Progress'
        WHEN 'RETURNED' THEN '↩️ Returned'
        ELSE 'Unknown'
    END AS friendly_status
FROM orders;Copy

You can put as many CASE-derived columns in one SELECT as you need — each is independent.

8. CASE in WHERE conditional filtering

A CASE inside WHERE must ultimately evaluate to TRUE/FALSE for the row to be kept — so it's typically written to return a boolean-like value that's then compared, or wrapped so the whole thing resolves to TRUE.

SELECT *
FROM orders
WHERE CASE
        WHEN region = 'US' THEN amount > 100
        WHEN region = 'EU' THEN amount > 50
        ELSE TRUE
    END;Copy

CASE returning TRUE/FALSE like this is powerful for "apply a different threshold per category" filters that would otherwise need multiple OR'd blocks.

9. CASE in ORDER BY custom sorting

Sort by business priority instead of alphabetical/numeric order by mapping values to sort ranks with CASE.

SELECT order_id, status
FROM orders
ORDER BY
    CASE status
        WHEN 'PENDING' THEN 1
        WHEN 'DELIVERED' THEN 2
        WHEN 'RETURNED' THEN 3
        WHEN 'CANCELLED' THEN 4
        ELSE 5
    END,
    order_date DESC;   -- multi-level ordering: tiebreak by dateCopy

This puts urgent PENDING orders first, regardless of alphabetical order, then breaks ties by most recent date — a very common "business ordering" interview pattern.

10. CASE in GROUP BY bucket creation

Group rows by a computed bucket instead of a raw column — the classic "bucket then aggregate" pattern.

SELECT
    CASE
        WHEN salary >= 90000 THEN 'High'
        WHEN salary >= 60000 THEN 'Medium'
        ELSE 'Low'
    END AS salary_band,
    COUNT(*) AS headcount,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY 1;   -- Snowflake lets you GROUP BY the column positionCopy

Tip: repeating the full CASE expression in GROUP BY is verbose — Snowflake supports grouping by ordinal position (GROUP BY 1) or by the column alias directly, both of which are cleaner.

11. CASE in HAVING filter grouped data

HAVING filters after aggregation — so CASE here usually filters on a conditionally-aggregated value.

SELECT department,
    COUNT(*) AS total,
    SUM(CASE WHEN salary >= 90000 THEN 1 ELSE 0 END) AS high_earners
FROM employees
GROUP BY department
HAVING SUM(CASE WHEN salary >= 90000 THEN 1 ELSE 0 END) > COUNT(*) / 2;Copy

12. CASE with Aggregate Functions conditional aggregation — ⭐ most interviewed

This is the pattern to master. Putting a CASE inside an aggregate function lets you compute multiple conditional metrics in a single pass over the data, instead of running separate filtered queries — this is exactly how pivot-style reports are built in plain SQL.

SUM(CASE...END) — conditional sum

SELECT
    SUM(CASE WHEN status = 'DELIVERED' THEN amount ELSE 0 END) AS delivered_revenue,
    SUM(CASE WHEN status = 'RETURNED' THEN amount ELSE 0 END) AS returned_revenue
FROM orders;Copy

COUNT(CASE...END) — conditional count

-- Pattern A: COUNT counts non-NULL rows, so ELSE NULL (or no ELSE) works
SELECT region,
    COUNT(CASE WHEN status = 'DELIVERED' THEN 1 END) AS delivered_count,
    COUNT(CASE WHEN status = 'CANCELLED' THEN 1 END) AS cancelled_count
FROM orders
GROUP BY region;Copy

AVG / MIN / MAX with CASE

SELECT
    AVG(CASE WHEN department = 'Engineering' THEN salary END) AS avg_eng_salary,
    MAX(CASE WHEN department = 'Sales' THEN salary END) AS max_sales_salary,
    MIN(CASE WHEN bonus IS NOT NULL THEN bonus END) AS min_bonus_given
FROM employees;Copy

Full pivot-style report (the interview favorite)

SELECT
    SUM(CASE WHEN department = 'Engineering' THEN 1 ELSE 0 END) AS engineering,
    SUM(CASE WHEN department = 'Sales'       THEN 1 ELSE 0 END) AS sales,
    SUM(CASE WHEN department = 'HR'          THEN 1 ELSE 0 END) AS hr
FROM employees;Copy

SELECT
    COUNT(*) AS total_orders,
    COUNT(CASE WHEN status='DELIVERED' THEN 1 END) AS delivered,
    ROUND(100.0 * COUNT(CASE WHEN status='CANCELLED' THEN 1 END) / COUNT(*), 2) AS cancel_rate_pct
FROM orders;

13. CASE with Window Functions ROW_NUMBER, RANK, LAG/LEAD...

CASE can wrap a window function's result, or a window function can be computed inside a CASE branch — both are common.

CASE around ROW_NUMBER / RANK / DENSE_RANK / NTILE

SELECT name, department, salary,
    CASE
        WHEN ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) = 1
        THEN 'Top Earner'
        ELSE 'Regular'
    END AS flag
FROM employees;Copy

CASE with LAG / LEAD — detecting change

SELECT customer_id, order_date, amount,
    CASE
        WHEN amount > LAG(amount) OVER (PARTITION BY customer_id ORDER BY order_date) THEN 'Increased'
        WHEN amount < LAG(amount) OVER (PARTITION BY customer_id ORDER BY order_date) THEN 'Decreased'
        ELSE 'First Order / Same'
    END AS trend
FROM orders;Copy

NTILE bucketing & FIRST_VALUE/LAST_VALUE inside CASE

SELECT name, salary,
    CASE NTILE(4) OVER (ORDER BY salary)
        WHEN 1 THEN 'Bottom Quartile'
        WHEN 4 THEN 'Top Quartile'
        ELSE 'Middle'
    END AS quartile_label
FROM employees;Copy

14. Nested CASE decision trees

A CASE branch's result can itself be another full CASE expression — this builds a decision tree, but readability drops fast past 2 levels.

SELECT name, department, salary,
    CASE
        WHEN department = 'Engineering' THEN
            CASE
                WHEN salary > 100000 THEN 'Senior Engineer'
                ELSE 'Junior Engineer'
            END
        WHEN department = 'Sales' THEN
            CASE
                WHEN salary > 60000 THEN 'Senior Sales'
                ELSE 'Junior Sales'
            END
        ELSE 'Other'
    END AS title
FROM employees;Copy

Best practices for nesting

• Prefer flattening with AND where possible — WHEN department='Engineering' AND salary>100000 is often easier to read than nesting
• If you genuinely need a multi-stage decision tree, nest no more than 2 levels deep
• For 3+ levels, break into a CTE with intermediate flag columns instead

15. CASE with Date Functions DATEADD, DATEDIFF, DATE_TRUNC...

Extremely common in reporting: bucket rows into fiscal periods, weekday/weekend, or "recency" tiers.

Current month / weekend-weekday / quarter / month names / year buckets / date ranges

SELECT order_id, order_date,
    CASE
        WHEN DATE_TRUNC('MONTH', order_date) = DATE_TRUNC('MONTH', CURRENT_DATE()) THEN 'This Month'
        WHEN order_date >= DATEADD(DAY, -30, CURRENT_DATE()) THEN 'Last 30 Days'
        ELSE 'Older'
    END AS recency_bucket,
    CASE
        WHEN DAYOFWEEK(order_date) IN (0,6) THEN 'Weekend'
        ELSE 'Weekday'
    END AS day_type,
    CASE QUARTER(order_date)
        WHEN 1 THEN 'Q1' WHEN 2 THEN 'Q2'
        WHEN 3 THEN 'Q3' ELSE 'Q4'
    END AS fiscal_quarter
FROM orders;Copy

Fiscal year with DATEDIFF and LAST_DAY / EXTRACT

SELECT order_id,
    DATEDIFF(DAY, order_date, CURRENT_DATE()) AS days_old,
    CASE
        WHEN order_date = LAST_DAY(order_date) THEN 'Placed on month-end'
        ELSE 'Mid-month'
    END AS month_position,
    CASE
        WHEN EXTRACT(MONTH FROM order_date) IN (4,5,6) THEN 'FY-Q1'  -- example: fiscal year starting April
        ELSE 'FY-Other'
    END AS fiscal_period
FROM orders;Copy

16. CASE with String Functions UPPER, SUBSTRING, REGEXP...

String functions inside WHEN clauses power text-based classification — the bread and butter of data cleaning.

SELECT customer_name,
    CASE
        WHEN UPPER(customer_name) LIKE '%CORP%' THEN 'Corporation'
        WHEN LENGTH(customer_name) > 10 THEN 'Long Name'
        WHEN SUBSTRING(customer_name,1,1) = 'A' THEN 'A-Name'
        ELSE INITCAP(customer_name)
    END AS name_category
FROM customers;Copy

REPLACE, SPLIT_PART, CONCAT & Snowflake regex functions

SELECT customer_name,
    CASE
        WHEN REGEXP_LIKE(customer_name, '^[A-Z][a-z]+$') THEN 'Single clean word'
        WHEN SPLIT_PART(customer_name, ' ', 2) != '' THEN 'Multi-word name'
        ELSE 'Other'
    END AS name_shape
FROM customers;Copy

17. CASE with Numeric Functions ROUND, FLOOR, MOD...

SELECT product_name, price,
    CASE
        WHEN MOD(product_id, 2) = 0 THEN 'Even ID'
        ELSE 'Odd ID'
    END AS id_parity,
    CASE
        WHEN FLOOR(price / 1000) >= 50 THEN 'Premium'
        WHEN ROUND(price,-2) >= 500 THEN 'Mid-range'
        ELSE 'Budget'
    END AS price_tier,
    CASE WHEN ABS(price - 600) < 100 THEN 'Near 600' ELSE 'Far from 600' END AS proximity
FROM products;Copy

18. CASE with CTE classification pipelines

Use a CTE to compute a CASE-based classification once, then reference the labeled column downstream — avoids repeating the same CASE logic multiple times in one query.

WITH classified AS (
    SELECT *,
        CASE
            WHEN salary >= 90000 THEN 'High'
            WHEN salary >= 60000 THEN 'Medium'
            ELSE 'Low'
        END AS salary_band
    FROM employees
)
SELECT salary_band, COUNT(*) AS headcount, SUM(bonus) AS total_bonus
FROM classified
GROUP BY salary_band;Copy

Multiple CASE columns as intermediate calculations

WITH flags AS (
    SELECT *,
        CASE WHEN salary > 90000 THEN 1 ELSE 0 END AS is_high_earner,
        CASE WHEN hire_date < '2020-01-01' THEN 1 ELSE 0 END AS is_tenured
    FROM employees
)
SELECT * FROM flags WHERE is_high_earner = 1 AND is_tenured = 1;Copy

19. CASE with JOIN conditional columns across tables

SELECT c.customer_name, o.order_id, o.amount,
    CASE
        WHEN o.amount > c.total_spent * 0.5 THEN 'Major Order'
        WHEN c.country = 'USA' AND o.status = 'RETURNED' THEN 'US Return - Review'
        ELSE 'Normal'
    END AS order_flag
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;Copy

CASE after a JOIN can reference columns from either side of the join in the same condition — this is what makes cross-table business rules possible in one pass.

20. CASE with Subqueries EXISTS, IN, correlated

SELECT c.customer_name,
    CASE
        WHEN EXISTS (
            SELECT 1 FROM orders o
            WHERE o.customer_id = c.customer_id AND o.status = 'CANCELLED'
        ) THEN 'Has Cancellations'
        ELSE 'Clean Record'
    END AS risk_flag
FROM customers c;Copy

IN with a subquery, and correlated subqueries

SELECT product_name,
    CASE
        WHEN product_id IN (SELECT product_id FROM products WHERE stock_qty = 0) THEN 'Out of Stock'
        ELSE 'Available'
    END AS availability
FROM products;Copy

21. CASE inside Window Aggregation conditional running totals

Combine CASE + SUM + OVER to get a running total of only the rows matching a condition — a favorite for "cumulative delivered revenue" style reports.

SELECT customer_id, order_date, status, amount,
    SUM(
        CASE WHEN status = 'DELIVERED' THEN amount ELSE 0 END
    ) OVER (
        PARTITION BY customer_id ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_delivered_total
FROM orders;Copy

This is different from Section 13: there, CASE wraps a window function's output; here, CASE feeds a filtered value into the window aggregate's input, before the running calculation happens.

22. CASE for Data Cleaning standardization

Standardizing values & replacing bad values

SELECT customer_name,
    CASE UPPER(TRIM(country))
        WHEN 'USA' THEN 'United States'
        WHEN 'US'  THEN 'United States'
        WHEN 'UK'  THEN 'United Kingdom'
        ELSE INITCAP(TRIM(country))
    END AS clean_country
FROM customers;Copy

Flagging invalid records, default values & data quality flags

SELECT order_id, amount,
    CASE
        WHEN amount IS NULL THEN 'MISSING_AMOUNT'
        WHEN amount < 0 THEN 'NEGATIVE_AMOUNT'
        WHEN amount = 0 THEN 'ZERO_AMOUNT'
        ELSE 'VALID'
    END AS dq_flag,
    COALESCE(amount, 0) AS amount_cleaned
FROM orders;Copy

23. CASE for ETL SCD, status flags, business rules

Slowly Changing Dimensions (SCD) — CASE to detect changes

SELECT s.customer_id, s.country AS new_country, t.country AS current_country,
    CASE
        WHEN t.customer_id IS NULL THEN 'INSERT'              -- new record
        WHEN s.country <> t.country THEN 'EXPIRE_AND_INSERT'  -- attribute changed
        ELSE 'NO_CHANGE'
    END AS scd_action
FROM staging_customers s
LEFT JOIN customers t ON s.customer_id = t.customer_id;Copy

Active/Inactive status flags & error flags

SELECT customer_id,
    CASE WHEN total_spent > 0 THEN 'ACTIVE' ELSE 'INACTIVE' END AS account_status,
    CASE WHEN signup_date > CURRENT_DATE() THEN 'ERROR: future signup date' ELSE 'OK' END AS validation_flag
FROM customers;Copy

Business rules encoded as CASE (validation pipeline)

SELECT order_id, amount, status,
    CASE
        WHEN status = 'DELIVERED' AND amount IS NULL THEN 'REJECT: delivered order needs amount'
        WHEN status NOT IN ('DELIVERED','CANCELLED','PENDING','RETURNED') THEN 'REJECT: unknown status'
        ELSE 'PASS'
    END AS business_rule_check
FROM orders;Copy

24. CASE for Reporting KPI labels, segmentation, buckets

KPI labels & revenue bands

SELECT customer_name, total_spent,
    CASE
        WHEN total_spent >= 10000 THEN '💎 Platinum'
        WHEN total_spent >= 1000  THEN '🥇 Gold'
        WHEN total_spent > 0     THEN '🥈 Silver'
        ELSE 'Inactive'
    END AS customer_tier
FROM customers;Copy

Age groups & sales buckets

SELECT name,
    DATEDIFF(YEAR, hire_date, CURRENT_DATE()) AS tenure_years,
    CASE
        WHEN DATEDIFF(YEAR, hire_date, CURRENT_DATE()) >= 5 THEN 'Veteran (5+ yrs)'
        WHEN DATEDIFF(YEAR, hire_date, CURRENT_DATE()) >= 2 THEN 'Established (2-5 yrs)'
        ELSE 'New Hire (<2 yrs)'
    END AS tenure_band
FROM employees;Copy

WITH seg AS (
  SELECT *,
    CASE WHEN total_spent>=10000 THEN 'Gold'
         WHEN total_spent>=1000  THEN 'Silver'
         ELSE 'Bronze' END AS segment
  FROM customers)
SELECT segment, COUNT(*), AVG(total_spent)
FROM seg GROUP BY segment;

25. Snowflake Alternatives to CASE IFF, DECODE, NVL...

These shortcuts often make Snowflake queries shorter — know when each one fits and when it doesn't.

IFF() — the compact 2-way decision

IFF(condition, true_value, false_value)

-- These two are identical:
IFF(salary > 90000, 'High', 'Low')

CASE WHEN salary > 90000 THEN 'High' ELSE 'Low' ENDCopy

Limitation: IFF only handles exactly one condition (two branches). The moment you need a 3rd branch, you're back to CASE (or nested IFF, which gets messy fast — prefer CASE at 3+ branches).

DECODE() — Oracle-style value mapping

DECODE(expression,
       search1, result1,
       search2, result2,
       default)

DECODE(department, 'Engineering', 'Tech', 'Sales', 'Revenue', 'Other')
-- equivalent to:
CASE department WHEN 'Engineering' THEN 'Tech' WHEN 'Sales' THEN 'Revenue' ELSE 'Other' ENDCopy

Difference from Simple CASE: DECODE treats two NULLs as equal for matching purposes (DECODE(col, NULL, 'is null') actually works), whereas Simple CASE's WHEN NULL never matches (Section 1/6). This is DECODE's one real edge over CASE.

NULL functions & GREATEST / LEAST / EQUAL_NULL

GREATEST(a, b, c)   -- largest of the values, NULL if any input is NULL
LEAST(a, b, c)      -- smallest of the values
EQUAL_NULL(a, b)   -- Snowflake-specific: TRUE if a=b OR both NULL, unlike a=b which is NULL when either is NULLCopy

26. Performance Considerations optimization

CASE evaluation order

Since Snowflake stops at the first TRUE match, putting the most frequently true or cheapest-to-evaluate condition first can shave a little evaluation time per row — noticeable mainly on very large scans with expensive conditions (regex, subqueries).

Predicate pushdown

Snowflake's optimizer can push simple column filters down to prune micro-partitions before scanning. A CASE expression wrapped around a filtered column in WHERE (Section 8) generally blocks this pushdown, since the optimizer can't statically resolve which branch applies per partition's min/max stats.

Avoid repeated CASE logic

If the same CASE expression appears in SELECT, WHERE, GROUP BY and ORDER BY of one query, compute it once in a CTE/subquery and reference the alias everywhere else (Section 18) — this avoids re-evaluating identical logic per row multiple times and keeps the query maintainable.

Using CTEs for readability (and the optimizer generally handles this fine)

Modern Snowflake typically inlines simple CTEs during optimization, so splitting logic into a CTE for readability rarely costs performance — don't avoid CTEs for fear of "extra materialization" unless you've confirmed it with EXPLAIN.

Expression simplification, data type consistency & NULL propagation

• Simplify overlapping ranges into non-overlapping ones — fewer branches to check
• Cast explicitly rather than relying on implicit coercion, which can silently degrade performance or throw errors on edge cases
• Remember any NULL input anywhere in a comparison propagates as "unknown," not "false" — always trace through what happens when inputs are NULL before assuming a branch is unreachable

27. Best Practices checklist

• ✅ Prefer searched CASE for complex logic — it handles ranges, ANDs/ORs, and multi-column rules that Simple CASE can't.
• ✅ Keep all THEN/ELSE return values in a compatible data type, casting explicitly (::VARCHAR, ::NUMBER) rather than relying on implicit coercion.
• ✅ Always use meaningful aliases (AS salary_band) — never leave a CASE column unnamed.
• ✅ Avoid deeply nested CASE — 2 levels max; beyond that, break logic into CTEs with named boolean flags.
• ✅ Put the most common or cheapest conditions first when order doesn't affect correctness.
• ✅ Use IFF() only for genuinely simple two-way decisions; switch to CASE once a 3rd branch appears.
• ✅ Always include an explicit ELSE in production code so unexpected values surface instead of silently becoming NULL.
• ✅ Watch NULL semantics everywhere — = NULL never matches; use IS NULL.

28. Common Interview Patterns practice bank — attempt before revealing answers

These are the exact CASE patterns most frequently asked in Data Engineer / Analyst interviews. Try to write the query yourself before clicking to reveal the answer.

SELECT customer_name,
  CASE WHEN total_spent>=10000 THEN 'Gold'
       WHEN total_spent>=1000  THEN 'Silver'
       ELSE 'Bronze' END AS segment
FROM customers;

SELECT name,
  CASE WHEN salary>=90000 THEN 'High'
       WHEN salary>=60000 THEN 'Medium'
       ELSE 'Low' END AS band
FROM employees;

SELECT name,
  CASE
    WHEN DATEDIFF(YEAR,birth_date,CURRENT_DATE()) < 25 THEN 'Under 25'
    WHEN DATEDIFF(YEAR,birth_date,CURRENT_DATE()) < 40 THEN '25-39'
    ELSE '40+'
  END AS age_bucket
FROM employees;

SELECT student_id, score,
  CASE
    WHEN score>=90 THEN 'A' WHEN score>=80 THEN 'B'
    WHEN score>=70 THEN 'C' WHEN score>=60 THEN 'D'
    ELSE 'F'
  END AS grade
FROM scores;

SELECT student_id, score,
  IFF(score>=40, 'Pass', 'Fail') AS result
FROM scores;

SELECT order_id,
  CASE WHEN amount>1000 THEN 'Large'
       WHEN amount>100  THEN 'Medium'
       ELSE 'Small' END AS revenue_category
FROM orders;

SELECT
  COUNT(CASE WHEN status='DELIVERED' THEN 1 END) AS delivered,
  COUNT(CASE WHEN status='CANCELLED' THEN 1 END) AS cancelled,
  COUNT(CASE WHEN status='PENDING'   THEN 1 END) AS pending
FROM orders;

SELECT
  SUM(CASE WHEN MONTH(order_date)=1 THEN amount ELSE 0 END) AS jan_revenue,
  SUM(CASE WHEN MONTH(order_date)=2 THEN amount ELSE 0 END) AS feb_revenue
FROM orders;

SELECT order_id,
  CASE WHEN amount IS NULL THEN 'missing_amount'
       WHEN order_date IS NULL THEN 'missing_date'
       ELSE 'ok' END AS dq_flag
FROM orders;

SELECT * FROM customers
ORDER BY CASE WHEN total_spent>=10000 THEN 0 ELSE 1 END, total_spent DESC;

SELECT ROUND(100.0*COUNT(CASE WHEN status='CANCELLED' THEN 1 END)/COUNT(*),2) AS cancel_pct
FROM orders;

SELECT order_id, amount, customer_id,
  CASE
    WHEN amount > 5000 AND customer_id IN
        (SELECT customer_id FROM customers WHERE DATEDIFF(DAY,signup_date,CURRENT_DATE())<30)
    THEN 'High Risk'
    ELSE 'Normal'
  END AS risk_flag
FROM orders;

🎯 Scenario-Based Interview Questions

WITH avgs AS (SELECT AVG(salary) AS co_avg FROM employees)
SELECT e.department, COUNT(*) AS headcount, AVG(e.salary) AS avg_salary,
  ROUND(100.0*AVG(CASE WHEN e.salary > a.co_avg THEN 1.0 ELSE 0 END),1) AS pct_above_avg
FROM employees e CROSS JOIN avgs a
GROUP BY e.department;