Interview practice

SQL CASE Interview Questions

Practice bucketing rows, conditional counts, business labels, and report flags.

CASE questions test whether you can translate business rules into query logic. They often appear as labels, buckets, flags, or conditional totals in a reporting query.

Start by restating the rule in plain language, then put the most specific condition first. CASE returns the first matching branch, so order matters when conditions overlap.

Common CASE interview prompts

Answer each one yourself before you look: write your query or your reasoning in the box, or open the playground and run it. Then reveal the model answer and compare. Nothing you type is saved or sent anywhere.

Label products as premium, standard, or budget based on price.
Your answer Try it in the playground →
Reveal a model answer
```
select product_name,
       price,
       case
         when price >= 30 then 'premium'
         when price >= 15 then 'standard'
         else 'budget'
       end as price_band
from products
order by price desc, product_name
```
The highest threshold goes first, because the first true branch wins. Ask for the cut-offs rather than inventing them.
Count active and discontinued products in one grouped report.
Your answer Try it in the playground →
Reveal a model answer
```
select c.category_name,
       sum(case when not p.discontinued then 1 else 0 end) as active,
       sum(case when p.discontinued then 1 else 0 end) as discontinued
from products as p
join categories as c on c.category_id = p.category_id
group by c.category_name
order by c.category_name
```
Conditional aggregation: each CASE turns a row into 1 or 0, and sum() counts them. One pass, one row per category, both counts side by side. count(*) filter (where ...) is the tidier spelling where the engine supports it.

Count the sessions reaching each conversion stage with conditional counts.

Your answer Try it in the playground →

Reveal a model answer

select count(distinct case when event_name = 'signup' then session_id end) as signup,
       count(distinct case when event_name = 'add_to_cart' then session_id end) as add_to_cart,
       count(distinct case when event_name = 'checkout' then session_id end) as checkout,
       count(distinct case when event_name = 'purchase' then session_id end) as purchase
from events

Each CASE returns the session id only for its stage and NULL otherwise, and count(distinct ...) ignores the NULLs. The result is one wide row with a column per stage, rather than a row per stage.

Bucket session durations into short, medium, and long visits.
Your answer Try it in the playground →
Reveal a model answer
```
select case
         when duration_seconds < 120 then 'short'
         when duration_seconds < 400 then 'medium'
         else 'long'
       end as visit_length,
       count(*) as sessions
from sessions
group by visit_length
order by min(duration_seconds)
```
Group on the CASE, and sort by the smallest value in each bucket so the bands come back in order rather than alphabetically. State the thresholds you assumed.
Explain why the order of CASE branches can change the result.
Your answer

Reveal a model answer

CASE returns the first branch that is true and stops. With overlapping conditions, a broad one listed first catches rows meant for a narrower one: check price >= 15 before price >= 30 and nothing is ever premium. Order from most specific to most general.

Bucketing pattern

select product_name,
       price,
       case
         when price >= 30 then 'premium'
         when price >= 15 then 'standard'
         else 'budget'
       end as price_band
from products
order by price desc, product_name

What matters

The price-band query checks whether you understand branch order. If the price >= 15 branch came before price >= 30, premium products would be labeled standard because the first true branch wins.

Conditional aggregation is the next level. The grouped query below keeps one row per category while counting different conditions side by side.

Conditional count pattern

select c.category_name,
       count(*) as products,
       sum(case when p.discontinued then 1 else 0 end) as discontinued_products,
       sum(case when p.quantity_on_hand < p.reorder_level then 1 else 0 end) as needs_reorder
from products as p
join categories as c on c.category_id = p.category_id
group by c.category_name
order by c.category_name

How to talk it through

Say it plainly: "I will use searched CASE because each branch is a condition. The highest threshold goes first, so the premium label gets a chance to win before the standard label."

For conditional counts, explain the numeric trick: each matching row becomes 1, each non-matching row becomes 0, and sum() turns those flags into counts.