Writing Reusable SQL Column Logic: Applying Expressions, Calculations, and CASE Statements Inside SELECT Without Repeating Yourself

Introduction

Here's a scenario that plays out every day in data teams: you've written a query that calculates a customer's lifetime value — some combination of total orders, average order size, days since first purchase, and a tiering rule that classifies them as Bronze, Silver, or Gold. It works. Your stakeholders love it. Then you get asked to add that same customer tier logic to three other reports, and you copy-paste the CASE statement into each one. Six months later, the business changes its tier thresholds, and you're hunting through a dozen queries trying to update them all.

This is the problem at the center of this lesson: SQL's SELECT clause is extraordinarily expressive — you can embed calculations, string manipulation, conditional logic, and type transformations directly alongside your column names — but that expressiveness comes with a trap. Because expressions live inline, they're trivially easy to repeat, and repetition is the enemy of maintainable, trustworthy code. By the end of this lesson, you'll know not just how to write powerful SELECT expressions, but when and how to structure them so your logic is defined once and reused cleanly.

What you'll learn:

• How SQL evaluates expressions inside SELECT, and the rules that govern what's possible
• Writing arithmetic and string calculations that don't break on real-world messy data
• Building readable, maintainable CASE statements for classification, transformation, and conditional aggregation
• How aliases, subqueries, CTEs, and derived columns give you tools to define logic once and reference it many times
• The most common anti-patterns that create brittle, repetitive column logic — and how to refactor them

---

Prerequisites

This lesson assumes you're comfortable writing basic SELECT queries with WHERE clauses, understand how JOINs work, and have seen GROUP BY in action. If you want a refresher on foundational query structure, SQL Basics: Master SELECT, FROM, WHERE Clauses and Build Your First Queries covers exactly that. Some examples in this lesson also use CTEs, which are explained in depth at Common Table Expressions (CTEs) for Cleaner SQL.

---

How SQL Actually Evaluates SELECT Expressions

Before we talk about reusability, we need to understand a constraint that catches even experienced SQL writers off guard: the SELECT clause is evaluated almost last in SQL's logical processing order.

The order SQL processes a query internally looks like this:

1. FROM (including JOINs)
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY
7. LIMIT / FETCH

The practical implication: a column alias you define in SELECT cannot be referenced in WHERE, GROUP BY, or HAVING in most databases. This surprises people constantly.

-- This will FAIL in PostgreSQL, SQL Server, and MySQL:
SELECT
    order_total * 0.10 AS tax_amount
FROM orders
WHERE tax_amount > 50;  -- ERROR: column "tax_amount" does not exist

-- You must repeat the expression:
SELECT
    order_total * 0.10 AS tax_amount
FROM orders
WHERE order_total * 0.10 > 50;

MySQL and SQLite have partial exceptions — MySQL allows alias references in ORDER BY and GROUP BY (but not WHERE or HAVING), and SQLite is similarly permissive in some clauses. PostgreSQL and SQL Server are strict: aliases don't exist until after the SELECT list is evaluated.

Understanding this evaluation order also explains why you can alias a column in SELECT and then reference that alias in ORDER BY — ORDER BY runs after SELECT, so the alias already exists.

---

Building Robust Arithmetic Expressions

The Basics Done Right

Arithmetic in SQL SELECT columns is straightforward on the surface: +, -, *, / work as expected on numeric types. But "as expected" has some important caveats.

Consider a sales table:

SELECT
    order_id,
    quantity,
    unit_price,
    discount_pct,
    quantity * unit_price AS gross_amount,
    quantity * unit_price * (1 - discount_pct / 100.0) AS net_amount,
    quantity * unit_price - (quantity * unit_price * (1 - discount_pct / 100.0)) AS discount_value
FROM order_line_items
WHERE order_date >= '2024-01-01';

Notice a few deliberate choices here:

• discount_pct / 100.0 uses 100.0 not 100. In databases that perform integer division (like PostgreSQL when both operands are integers), discount_pct / 100 for a value of 15 would give you 0, not 0.15. The .0 forces floating-point arithmetic.
• The discount_value calculation repeats the entire net_amount expression rather than referencing the alias. Frustrating, but necessary.

Already we can see the problem: quantity * unit_price appears three times. If the business rule changes — say, you need to add a currency conversion factor — you'll have to update every occurrence.

The Subquery Solution for Expression Reuse

The clean solution is to define your base expressions once in an inner query, then reference them by name in the outer query:

SELECT
    order_id,
    gross_amount,
    net_amount,
    gross_amount - net_amount AS discount_value,
    net_amount * 0.08 AS estimated_tax
FROM (
    SELECT
        order_id,
        quantity * unit_price AS gross_amount,
        quantity * unit_price * (1 - discount_pct / 100.0) AS net_amount
    FROM order_line_items
    WHERE order_date >= '2024-01-01'
) AS line_calcs;

Now gross_amount and net_amount are defined exactly once. The outer query can do arithmetic on those names without re-specifying the formula. If gross_amount needs to change, you change it in one place.

This pattern — computing base expressions in an inner query or CTE, then composing from those — is one of the most important habits you can develop as a SQL writer.

Guarding Against NULL in Calculations

Real data has NULLs, and NULL is contagious in arithmetic: any operation involving NULL produces NULL. This catches teams off guard when totals come out smaller than expected because some rows silently dropped out.

-- If discount_pct is NULL for some rows, net_amount will be NULL:
quantity * unit_price * (1 - discount_pct / 100.0) AS net_amount

-- Safer: treat NULL discount as zero
quantity * unit_price * (1 - COALESCE(discount_pct, 0) / 100.0) AS net_amount

Similarly, division by zero will crash your query in most databases. When a denominator can be zero, protect it:

-- Dangerous:
total_revenue / total_orders AS avg_order_value

-- Safe:
CASE WHEN total_orders = 0 THEN NULL
     ELSE total_revenue / total_orders
END AS avg_order_value

-- Or more concisely using NULLIF:
total_revenue / NULLIF(total_orders, 0) AS avg_order_value

NULLIF(a, b) returns NULL when a = b and returns a otherwise, which turns a zero denominator into NULL — and dividing by NULL produces NULL rather than a database error. For a thorough treatment of NULL handling strategies, NULL Handling in SQL: IS NULL, COALESCE, and NULLIF is required reading.

---

Writing Expressions That Transform Strings and Dates

String Expressions in SELECT

String transformations in SELECT follow the same reuse problems as arithmetic. Consider a customer data table where first and last names are stored separately, and you frequently need the full name plus a formatted display identifier:

SELECT
    customer_id,
    TRIM(first_name) || ' ' || TRIM(last_name) AS full_name,
    LOWER(TRIM(first_name)) || '.' || LOWER(TRIM(last_name)) AS email_prefix,
    'CUST-' || LPAD(customer_id::TEXT, 8, '0') AS display_id
FROM customers;

When these expressions are used across multiple queries, wrapping them in a CTE is particularly powerful:

WITH customer_display AS (
    SELECT
        customer_id,
        TRIM(first_name) || ' ' || TRIM(last_name) AS full_name,
        LOWER(TRIM(first_name)) || '.' || LOWER(TRIM(last_name)) AS email_prefix,
        'CUST-' || LPAD(customer_id::TEXT, 8, '0') AS display_id,
        email,
        account_status
    FROM customers
)
SELECT
    cd.display_id,
    cd.full_name,
    cd.email_prefix || '@example.com' AS generated_email,
    o.order_count
FROM customer_display cd
LEFT JOIN (
    SELECT customer_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY customer_id
) o ON o.customer_id = cd.customer_id;

The name formatting logic lives in one place. The outer query composes from it freely.

Date Expressions and Temporal Calculations

Date math is where expression complexity really accelerates. A typical analytics query might need the age of a record, the day of week, a week number, and whether a date falls in the current period — all at once.

SELECT
    order_id,
    order_date,
    CURRENT_DATE - order_date::DATE AS days_since_order,
    EXTRACT(DOW FROM order_date) AS day_of_week,       -- 0=Sunday in PostgreSQL
    DATE_TRUNC('week', order_date) AS week_start,
    DATE_TRUNC('month', order_date) AS month_start,
    CASE 
        WHEN order_date >= DATE_TRUNC('month', CURRENT_DATE) THEN 'Current Month'
        WHEN order_date >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 month' 
         AND order_date < DATE_TRUNC('month', CURRENT_DATE) THEN 'Prior Month'
        ELSE 'Older'
    END AS period_label
FROM orders;

---

CASE Statements: The Real Power Tool (and the Biggest Source of Repetition)

The CASE expression is SQL's Swiss army knife. It appears in SELECT lists to categorize and transform data, in WHERE clauses to filter conditionally, inside aggregate functions for conditional aggregation, and in GROUP BY for custom grouping. It's also where the most egregious copy-paste repetition tends to live.

Searched CASE vs. Simple CASE

SQL has two syntactic forms of CASE:

Simple CASE — compares one expression to multiple values:

CASE order_status
    WHEN 'P' THEN 'Pending'
    WHEN 'C' THEN 'Complete'
    WHEN 'X' THEN 'Cancelled'
    WHEN 'R' THEN 'Refunded'
    ELSE 'Unknown'
END AS status_label

Searched CASE — evaluates an independent boolean condition per branch:

CASE
    WHEN order_total >= 1000 THEN 'Large'
    WHEN order_total >= 250  THEN 'Medium'
    WHEN order_total >= 50   THEN 'Small'
    ELSE 'Micro'
END AS order_size

Use Simple CASE for equality lookups against a single column — it's more readable. Use Searched CASE when conditions involve ranges, multiple columns, or complex logic.

The Classic Problem: Repeating Classification Logic

Imagine a query that reports on customer segments. You have a CASE statement that classifies customers into segments based on their lifetime order count, and you need that segment in the SELECT list, in a WHERE filter (to exclude one segment), in a GROUP BY (to aggregate by segment), and in a HAVING clause (to filter groups):

-- The naïve approach — exhausting to maintain:
SELECT
    CASE
        WHEN lifetime_orders >= 20 THEN 'Champion'
        WHEN lifetime_orders >= 10 THEN 'Loyal'
        WHEN lifetime_orders >= 3  THEN 'Developing'
        ELSE 'New'
    END AS segment,
    COUNT(*) AS customer_count,
    AVG(lifetime_value) AS avg_ltv
FROM customers
WHERE 
    CASE
        WHEN lifetime_orders >= 20 THEN 'Champion'
        WHEN lifetime_orders >= 10 THEN 'Loyal'
        WHEN lifetime_orders >= 3  THEN 'Developing'
        ELSE 'New'
    END != 'New'
GROUP BY
    CASE
        WHEN lifetime_orders >= 20 THEN 'Champion'
        WHEN lifetime_orders >= 10 THEN 'Loyal'
        WHEN lifetime_orders >= 3  THEN 'Developing'
        ELSE 'New'
    END
HAVING
    CASE
        WHEN lifetime_orders >= 20 THEN 'Champion'
        WHEN lifetime_orders >= 10 THEN 'Loyal'
        WHEN lifetime_orders >= 3  THEN 'Developing'
        ELSE 'New'
    END IN ('Champion', 'Loyal')
    OR AVG(lifetime_value) > 500;

This is unmaintainable. Four copies of the same CASE statement, each one a potential divergence point.

The Right Way: Define Once in a CTE or Subquery

WITH segmented_customers AS (
    SELECT
        customer_id,
        lifetime_orders,
        lifetime_value,
        CASE
            WHEN lifetime_orders >= 20 THEN 'Champion'
            WHEN lifetime_orders >= 10 THEN 'Loyal'
            WHEN lifetime_orders >= 3  THEN 'Developing'
            ELSE 'New'
        END AS segment
    FROM customers
)
SELECT
    segment,
    COUNT(*) AS customer_count,
    AVG(lifetime_value) AS avg_ltv
FROM segmented_customers
WHERE segment != 'New'
GROUP BY segment
HAVING segment IN ('Champion', 'Loyal')
   OR AVG(lifetime_value) > 500;

The CASE logic exists in exactly one place. The WHERE, GROUP BY, and HAVING all reference the alias segment — which works because those clauses run after the CTE materializes segment as a real column. This is a textbook example of why CTEs exist as a structural tool, not just a stylistic preference.

CASE Inside Aggregates: Conditional Aggregation

One of the most powerful patterns is using CASE inside aggregate functions to pivot or conditionally sum/count without multiple passes over the table. This is sometimes called "conditional aggregation" and it's a pattern you'll use constantly once you know it.

Suppose you want a single-row summary per customer showing their order counts broken down by channel:

SELECT
    customer_id,
    COUNT(*) AS total_orders,
    COUNT(CASE WHEN channel = 'web'    THEN 1 END) AS web_orders,
    COUNT(CASE WHEN channel = 'mobile' THEN 1 END) AS mobile_orders,
    COUNT(CASE WHEN channel = 'store'  THEN 1 END) AS store_orders,
    SUM(CASE WHEN channel = 'web'    THEN order_total ELSE 0 END) AS web_revenue,
    SUM(CASE WHEN channel = 'mobile' THEN order_total ELSE 0 END) AS mobile_revenue,
    SUM(CASE WHEN channel = 'store'  THEN order_total ELSE 0 END) AS store_revenue
FROM orders
GROUP BY customer_id;

How does this work? COUNT ignores NULLs. When the CASE condition doesn't match, the CASE returns NULL (no ELSE clause = NULL). So COUNT(CASE WHEN channel = 'web' THEN 1 END) counts only the rows where channel is 'web' — all others produce NULL and get skipped.

For SUM, you typically want ELSE 0 so non-matching rows contribute zero to the total rather than NULL. (Though if no rows match, the SUM of zeros is 0, while the SUM of NULLs is NULL — sometimes you want the NULL to signal "no data for this combination.")

CASE for Business Metric Calculations

The conditional aggregation pattern extends naturally to business metrics. Here's a query computing several key revenue metrics in a single pass:

WITH order_data AS (
    SELECT
        o.order_id,
        o.customer_id,
        o.order_date,
        o.order_total,
        o.order_status,
        DATE_TRUNC('month', o.order_date) AS order_month,
        CASE
            WHEN o.order_total >= 500 THEN 'high_value'
            WHEN o.order_total >= 100 THEN 'mid_value'
            ELSE 'low_value'
        END AS value_tier,
        CASE
            WHEN o.order_status IN ('completed', 'shipped') THEN TRUE
            ELSE FALSE
        END AS is_fulfilled
    FROM orders o
    WHERE o.order_date >= '2024-01-01'
)
SELECT
    order_month,
    COUNT(*) AS total_orders,
    COUNT(CASE WHEN is_fulfilled THEN 1 END) AS fulfilled_orders,
    SUM(CASE WHEN is_fulfilled THEN order_total ELSE 0 END) AS fulfilled_revenue,
    COUNT(CASE WHEN value_tier = 'high_value' THEN 1 END) AS high_value_orders,
    SUM(CASE WHEN value_tier = 'high_value' AND is_fulfilled THEN order_total ELSE 0 END) AS high_value_fulfilled_revenue,
    ROUND(
        100.0 * COUNT(CASE WHEN is_fulfilled THEN 1 END) / NULLIF(COUNT(*), 0),
        1
    ) AS fulfillment_rate_pct
FROM order_data
GROUP BY order_month
ORDER BY order_month;

Notice how value_tier and is_fulfilled are computed once in the CTE and then used freely in the outer query's aggregations. The CASE logic for what constitutes "fulfilled" and "high value" lives in one place. If the business redefines "fulfilled" to exclude returns, you update one line in the CTE.

---

Structuring Multi-Level Derived Logic

Some analytical queries need more than two layers: you compute base fields, then derive intermediate metrics from those, then compute final outputs from those intermediate metrics. CTEs handle this elegantly through chaining.

Here's a realistic example: calculating cohort-level metrics for a subscription business, where you need to compute several intermediate values before you can produce the final report.

WITH 
-- Layer 1: Raw subscription events with key fields
subscription_events AS (
    SELECT
        s.customer_id,
        s.subscription_id,
        s.plan_type,
        s.start_date,
        s.end_date,
        s.monthly_amount,
        DATE_TRUNC('month', s.start_date) AS cohort_month,
        CASE
            WHEN s.plan_type = 'annual' THEN s.monthly_amount * 12
            WHEN s.plan_type = 'monthly' THEN s.monthly_amount
            ELSE s.monthly_amount
        END AS annualized_value,
        CASE
            WHEN s.end_date IS NULL THEN TRUE
            WHEN s.end_date > CURRENT_DATE THEN TRUE
            ELSE FALSE
        END AS is_active
    FROM subscriptions s
),

-- Layer 2: Customer-level summary built from layer 1
customer_summary AS (
    SELECT
        customer_id,
        cohort_month,
        COUNT(subscription_id) AS total_subscriptions,
        SUM(annualized_value) AS total_annualized_value,
        MAX(CASE WHEN is_active THEN 1 ELSE 0 END) AS has_active_subscription,
        MIN(start_date) AS first_subscription_date,
        MAX(start_date) AS latest_subscription_date
    FROM subscription_events
    GROUP BY customer_id, cohort_month
),

-- Layer 3: Cohort aggregates built from layer 2
cohort_metrics AS (
    SELECT
        cohort_month,
        COUNT(customer_id) AS cohort_size,
        SUM(total_annualized_value) AS cohort_arr,
        SUM(has_active_subscription) AS currently_active_customers,
        AVG(total_annualized_value) AS avg_arr_per_customer,
        ROUND(
            100.0 * SUM(has_active_subscription) / NULLIF(COUNT(customer_id), 0),
            1
        ) AS retention_rate_pct
    FROM customer_summary
    GROUP BY cohort_month
)

-- Final output: compose from layer 3
SELECT
    cohort_month,
    cohort_size,
    cohort_arr,
    currently_active_customers,
    ROUND(avg_arr_per_customer, 2) AS avg_arr_per_customer,
    retention_rate_pct,
    cohort_arr / NULLIF(cohort_size, 0) AS arr_per_original_customer
FROM cohort_metrics
ORDER BY cohort_month;

Each CTE layer builds cleanly on the one before it. The is_active flag, the annualized_value calculation, the cohort retention logic — each is defined exactly once. Adding a new metric at the output layer is a matter of referencing already-computed names.

---

Aliases as Documentation and the Limits of Alias Reuse

Good aliases are undervalued. The difference between col1 and annualized_subscription_value is the difference between a query that requires deep context to understand and one that teaches you what it's doing as you read it.

A few principles for alias naming:

• Be specific, not generic. revenue is ambiguous. net_revenue_after_refunds is not.
• Match the business vocabulary. If your stakeholders call it "ARR," use arr, not annual_recurring_revenue. Consistency with the language of the people reading your output matters.
• Use snake_case consistently. SQL identifiers aren't case-sensitive in most databases, but consistent casing in your aliases makes queries much more readable.
• Don't abbreviate unless the abbreviation is universal. ord_ttl_net_rev saves you twelve characters and costs you ten seconds of confusion every time someone reads it.

For a deeper treatment of aliasing strategy, Using SQL Aliases Effectively: Naming Columns and Tables for Readable, Maintainable Queries covers the full topic.

Now, about that alias reuse limitation — there's one more pattern worth knowing: the lateral reference. Some databases (notably BigQuery, Databricks, and DuckDB) support "lateral column aliases" that let you reference a SELECT alias later in the same SELECT list:

-- This works in BigQuery and DuckDB but NOT in PostgreSQL or SQL Server:
SELECT
    quantity * unit_price AS gross_amount,
    gross_amount * (1 - discount_pct / 100.0) AS net_amount,
    gross_amount - net_amount AS discount_value
FROM order_line_items;

This is genuinely useful when it's supported. But because it's non-standard, be cautious using it in shared codebases where the database might change, or in queries that will be ported across environments.

---

Performance Considerations: When Expression Complexity Becomes a Problem

Expression logic in SELECT is generally free — the database evaluates it once per row as it processes the result set. But there are some patterns that carry real performance implications.

CASE in GROUP BY: Index Blindness

When you GROUP BY a CASE expression, the database can't use an index on the underlying column directly. This is usually fine for small-to-medium datasets, but becomes a concern at scale.

-- This GROUP BY can't use an index on order_total:
GROUP BY
    CASE
        WHEN order_total >= 1000 THEN 'Large'
        WHEN order_total >= 250  THEN 'Medium'
        ELSE 'Small'
    END

The mitigation is to precompute the tier in a CTE or persisted column (a generated/computed column in your table schema) and index that. Many databases support generated columns precisely for this use case.

Scalar Subqueries in SELECT: The Performance Trap

A common anti-pattern is using a correlated scalar subquery in SELECT:

-- This executes the subquery once per row -- catastrophic at scale:
SELECT
    customer_id,
    customer_name,
    (SELECT SUM(order_total) FROM orders WHERE orders.customer_id = c.customer_id) AS lifetime_value
FROM customers c;

If customers has 100,000 rows, this fires the subquery 100,000 times. Always rewrite this as a JOIN to a pre-aggregated subquery or CTE:

WITH customer_totals AS (
    SELECT customer_id, SUM(order_total) AS lifetime_value
    FROM orders
    GROUP BY customer_id
)
SELECT
    c.customer_id,
    c.customer_name,
    ct.lifetime_value
FROM customers c
LEFT JOIN customer_totals ct ON ct.customer_id = c.customer_id;

Expression Pushdown: When the Optimizer Helps

Modern query optimizers (PostgreSQL, SQL Server, BigQuery, Snowflake, Spark SQL) can often push filter logic down through layers of subqueries and CTEs. But they don't always do it perfectly, especially with complex CASE expressions. If you have a large dataset and a query with multiple CTE layers, add EXPLAIN (or EXPLAIN ANALYZE in PostgreSQL) to check whether filters are being applied early or late.

EXPLAIN ANALYZE
SELECT ...
FROM your_complex_cte
WHERE some_filter;

Look for filter nodes high up in the plan (early filtering = good) versus filter nodes at the very top of the plan after a full scan (late filtering = the optimizer didn't push the predicate down).

---

The Pivoting Pattern: CASE Meets GROUP BY for Column Transformation

One specific use of CASE in SELECT that deserves its own discussion is pivoting — turning distinct values from a column into their own columns. This is conditional aggregation applied to reshape data.

Suppose you have a monthly sales table with one row per region per month, and you want one row per month with each region as a column:

WITH regional_sales AS (
    SELECT
        sale_month,
        region,
        total_revenue
    FROM monthly_region_sales
    WHERE sale_year = 2024
)
SELECT
    sale_month,
    SUM(CASE WHEN region = 'Northeast' THEN total_revenue ELSE 0 END) AS northeast_revenue,
    SUM(CASE WHEN region = 'Southeast' THEN total_revenue ELSE 0 END) AS southeast_revenue,
    SUM(CASE WHEN region = 'Midwest'   THEN total_revenue ELSE 0 END) AS midwest_revenue,
    SUM(CASE WHEN region = 'West'      THEN total_revenue ELSE 0 END) AS west_revenue,
    SUM(total_revenue) AS total_revenue
FROM regional_sales
GROUP BY sale_month
ORDER BY sale_month;

The limitation of this approach is that the column names are hardcoded — if a new region is added, you have to update the query. Dynamic pivoting (where the column names are derived from data) requires database-specific syntax (PIVOT in SQL Server and Snowflake, dynamic SQL in PostgreSQL) or application-layer logic. For a thorough treatment of this pattern, see Pivoting Query Results in SQL: Using CASE WHEN and GROUP BY to Turn Rows into Columns.

---

Hands-On Exercise

You'll work with two tables:

-- customers table
CREATE TABLE customers (
    customer_id     INT,
    first_name      VARCHAR(50),
    last_name       VARCHAR(50),
    signup_date     DATE,
    country_code    CHAR(2),
    account_status  VARCHAR(20)  -- 'active', 'churned', 'suspended'
);

-- orders table
CREATE TABLE orders (
    order_id       INT,
    customer_id    INT,
    order_date     DATE,
    order_total    NUMERIC(10,2),
    order_status   VARCHAR(20),  -- 'pending', 'completed', 'cancelled', 'refunded'
    channel        VARCHAR(20)   -- 'web', 'mobile', 'store'
);

Your task: Write a single query (using CTEs) that produces a report with one row per customer containing the following columns:

1. display_name — Full name formatted as "Last, First" with all whitespace trimmed
2. customer_code — "CUST-" followed by the customer_id zero-padded to 6 digits
3. years_as_customer — Rounded to one decimal place, how long they've been a customer
4. account_status_label — 'Active', 'Churned', 'Suspended', or 'Unknown' (map from the raw status codes)
5. total_orders — Count of all non-cancelled, non-refunded orders
6. total_revenue — Sum of completed orders only
7. avg_order_value — Average order total (guard against division by zero)
8. primary_channel — The channel used in the most orders (web, mobile, or store)
9. value_segment — 'Platinum' if total_revenue >= 2000, 'Gold' if >= 500, 'Silver' if >= 100, else 'Bronze'
10. status_revenue_label — A combined label: account_status_label + " / " + value_segment (e.g. "Active / Gold")

Requirements:

• Every expression must be defined exactly once
• No repetition of CASE logic or calculation formulas
• The query should handle NULLs gracefully (customers with no orders should show 0 counts, NULL revenue, and 'Bronze' segment)

This exercise forces you to think carefully about which CTE layer each computation belongs to, and where you need COALESCE or NULLIF to handle edge cases.

---

Common Mistakes and Troubleshooting

Mistake 1: Referencing an Alias in the Same SELECT List

-- FAILS in standard SQL (and most databases):
SELECT
    quantity * unit_price AS gross,
    gross * 0.10 AS tax  -- 'gross' doesn't exist yet
FROM orders;

-- Fix: repeat the expression or use a subquery/CTE
SELECT
    gross,
    gross * 0.10 AS tax
FROM (
    SELECT quantity * unit_price AS gross FROM orders
) base;

Mistake 2: Integer Division Dropping Decimal Places

-- If discount_pct is an INTEGER column, this loses precision:
1 - discount_pct / 100   -- e.g., 1 - 15/100 = 1 - 0 = 1 (wrong!)

-- Fix: cast to numeric first
1 - discount_pct::NUMERIC / 100
-- Or:
1 - discount_pct / 100.0

Mistake 3: CASE ELSE Producing Unexpected NULLs

-- Missing ELSE means non-matching rows get NULL, not zero:
SUM(CASE WHEN channel = 'web' THEN order_total END) AS web_revenue
-- If no web orders, this is NULL, not 0

-- Fix with ELSE 0 if you want zero for missing segments:
SUM(CASE WHEN channel = 'web' THEN order_total ELSE 0 END) AS web_revenue

But be deliberate — sometimes NULL is the right answer (you want to distinguish "no data" from "zero"). Don't reflexively add ELSE 0 everywhere.

Mistake 4: Nesting CASE Statements Unnecessarily

Sometimes writers nest CASE inside CASE when a flat searched CASE would work:

-- Overcomplicated nesting:
CASE
    WHEN region = 'US' THEN
        CASE
            WHEN tier = 'enterprise' THEN 'US Enterprise'
            ELSE 'US Standard'
        END
    ELSE 'International'
END

-- Flat and clearer:
CASE
    WHEN region = 'US' AND tier = 'enterprise' THEN 'US Enterprise'
    WHEN region = 'US' THEN 'US Standard'
    ELSE 'International'
END

The flat version is easier to read, easier to extend, and performs identically.

Mistake 5: Putting Expensive Logic in WHERE Instead of CTE

-- This re-evaluates the CASE for every row during filtering:
WHERE
    CASE
        WHEN long_complex_expression_1 AND long_complex_expression_2 THEN 'A'
        WHEN long_complex_expression_3 THEN 'B'
        ...
    END = 'A'

-- Better: precompute the category in a CTE, filter on the alias

Most optimizers will handle this correctly, but it's a readability problem regardless — and on some databases and large datasets, the pattern prevents index use.

Mistake 6: Forgetting That CASE Branches Short-Circuit

SQL CASE does short-circuit — once a TRUE branch is found, subsequent branches aren't evaluated. This matters when a later branch would cause an error if evaluated:

-- Safe because of short-circuit evaluation:
CASE
    WHEN denominator = 0 THEN NULL
    WHEN numerator / denominator > 10 THEN 'High'
    ELSE 'Normal'
END

The division only evaluates when denominator != 0. However, this behavior isn't universally guaranteed to extend to the checking of other errors (like type conversion failures), and SQL Server in particular has known cases where it evaluates expressions in non-obvious orders. When in doubt, use NULLIF or TRY_CAST/TRY_CONVERT rather than relying on CASE short-circuit for error protection.

---

Summary and Next Steps

Let's bring together what you've worked through:

• SQL's logical evaluation order means SELECT aliases can't be referenced earlier in the same query — but wrapping your expressions in a subquery or CTE turns computed columns into referenceable names
• Arithmetic expressions need explicit attention to integer division, NULL propagation, and division by zero — COALESCE, NULLIF, and careful casting are your tools
• CASE statements are most maintainable when defined once in a CTE layer, allowing WHERE, GROUP BY, and HAVING to reference the alias instead of re-specifying the logic
• Conditional aggregation — CASE inside COUNT/SUM — lets you compute multiple perspectives on data in a single pass, which is both efficient and elegant
• Chained CTEs give you a natural multi-layer architecture for building complex derived metrics without repetition
• Scalar correlated subqueries in SELECT are a performance trap; always replace them with pre-aggregated JOINs

The discipline of "define once, reference by name" isn't just about style. It's about trust. When the business rule for "what counts as a completed order" is in one place, a change to that rule propagates correctly everywhere. When it's scattered across a dozen queries, you're one missed copy-paste away from inconsistent reporting.

Where to go next:

The natural extension of this lesson is learning how window functions — RANK(), LAG(), SUM() OVER(...) — bring another layer of expressive power to column logic, letting you compute running totals, period-over-period comparisons, and rankings without GROUP BY collapsing your rows. Window Functions: RANK, ROW_NUMBER, and LAG picks up exactly where this lesson leaves off.

For multi-step analytical queries that chain together the subquery/CTE patterns introduced here with JOINs and aggregation, Writing Multi-Step Analytical Queries: Chaining Subqueries, JOINs, and GROUP BY to Answer Real Business Questions is the practical next challenge.

And if you want to sharpen your ability to translate business requirements into these structured query patterns, Translating Business Questions into SQL: Decomposing Requirements into SELECT, JOIN, GROUP BY, and Subquery Steps closes the loop between the analytical techniques and real-world problem decomposition.