How do you use conditional aggregation to pivot row data into columns without a PIVOT keyword?

How to think about it

The interviewer wants to see whether you can turn row-level data into a columnar report without relying on a vendor-specific PIVOT keyword. This pattern — wrapping CASE WHEN inside an aggregate — works in every SQL dialect and comes up constantly in analytics work.

How it works, step by step

Start with this: you want one row per customer, with separate columns for completed, cancelled, and pending order counts. The data has one row per order with a status column.

The trick is that CASE WHEN returns a value when the condition is true, and NULL otherwise (when there’s no ELSE). Since COUNT and SUM both skip NULLs, only the matching rows contribute to each bucket.

The COUNT pattern

-- Count orders by status for each customer — one row per customer
SELECT
    customer_id,
    COUNT(CASE WHEN status = 'completed'  THEN 1 END) AS completed,
    COUNT(CASE WHEN status = 'cancelled'  THEN 1 END) AS cancelled,
    COUNT(CASE WHEN status = 'pending'    THEN 1 END) AS pending
FROM orders
GROUP BY customer_id;

CASE WHEN returns NULL when the condition is false and no ELSE is given. COUNT ignores NULLs, so only matching rows are counted.

The SUM pattern (for revenue)

Use ELSE 0 with SUM so non-matching rows contribute 0, not NULL:

SELECT
    DATE_TRUNC('month', order_date) AS month,
    SUM(CASE WHEN channel = 'web'    THEN revenue ELSE 0 END) AS web_revenue,
    SUM(CASE WHEN channel = 'mobile' THEN revenue ELSE 0 END) AS mobile_revenue
FROM orders
GROUP BY 1
ORDER BY 1;

Try it yourself

Shorter form with FILTER (PostgreSQL, DuckDB)

COUNT(*) FILTER (WHERE status = 'completed') AS completed

This is cleaner when available. It is not supported in MySQL or BigQuery.