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

Label products as premium, standard, or budget based on price.
Count active and discontinued products in one grouped report.
Build a conversion funnel with conditional event counts.
Bucket session durations into short, medium, and long visits.
Explain why the order of CASE branches can change the result.

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 theprice >= 15 branch came before price >= 30, premium products would be labelled 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 becomes1, each non-matching row becomes 0, andsum() turns those flags into counts.