CASE, Subqueries, and CTEs

When do you need more than a basic query?

Example using CASE

Conditional aggregation (counting by category in one row):

SELECT
COUNT(CASE WHEN total_amount >= 5000 THEN 1 END) AS large_orders,
COUNT(CASE WHEN total_amount < 1000 THEN 1 END) AS small_orders
FROM orders;

Subquery example (orders above the average):

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

Example using a CTE

-- PostgreSQL syntax; in BigQuery use DATE_TRUNC(order_date, MONTH)
WITH monthly_sales AS (
SELECT DATE_TRUNC('month', order_date) AS month,
SUM(total_amount) AS revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
)
SELECT month, revenue
FROM monthly_sales
ORDER BY month;    

Going further – a window function for month-on-month change:

WITH monthly_sales AS (
SELECT DATE_TRUNC('month', order_date) AS month,
SUM(total_amount) AS revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
)
SELECT month,
revenue,
revenue - LAG(revenue) OVER (ORDER BY month) AS change_vs_prev_month
FROM monthly_sales
ORDER BY month;

How it works

• CASE evaluates conditions in order and returns the first match. Put the most specific condition first.
• A CTE names an intermediate result so the final query reads top-to-bottom like steps.
• You can chain multiple CTEs: WITH a AS (...), b AS (...) SELECT ...
• Date functions differ by database. Always check your system's documentation.

Common mistakes

• Writing everything at once. Build and test complex queries in small pieces.
• Wrong CASE order: if >= 1000 comes before >= 5000, large orders are labelled "Medium".
• Missing ELSE: unmatched rows become NULL.
• Subqueries that return multiple rows where one value is expected cause errors.
• Not checking intermediate results: run each CTE on its own before adding the next join, filter, or calculation.

Try it: Use CASE to label orders as low, medium, or high value, then calculate the number of orders in each band.