Profit Tiers

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

easy

Seniority

L4

Problem

Count how many orders in the orders table fall into each profit tier. An order is 'high' once its profit clears 100, 'moderate' from 0 up to and including 100, and 'negative' when it lost money.

High, moderate, or in the red. Every order gets a label.

Tables

orders

Columns: order_id INTEGER, status TEXT, region TEXT, profit REAL

Expected output

Worked solution and explanation

This is a range partition dressed up as a CFO report. The skill is cutting profit into three tiers that never overlap and never leave a gap, so each of the 200 orders lands in exactly one bucket. Anyone can write three conditions. What separates candidates is where the edges land: exactly 0 is 'moderate', exactly 100 is still 'moderate', and only a loss is 'negative'. Get an edge wrong, or put the branches in the wrong order, and the counts stop adding up to the table, or the 'high' tier reads zero.

Building the tiers

Step 1: Open the CASE at the highest cutoff

WHEN profit > 100 THEN 'high' goes first. A CASE stops at the first branch that matches, so once the high orders are claimed, every later branch only ever sees a profit of 100 or less.

Step 2: Let branch order supply the upper bound

WHEN profit >= 0 THEN 'moderate' reads like 'anything non-negative', but it only runs on rows the first branch passed over, so it means 0 through 100 inclusive. The 8 orders sitting at exactly 0 land here, which is why >= rather than > matters on this data.

Step 3: Make the losses the ELSE

profit is never NULL in orders, so ELSE 'negative' catches exactly the loss-making rows. Writing it as ELSE instead of a third WHEN profit < 0 also guarantees that no row falls through every branch into a NULL tier.

Step 4: Count on the label, not the column

GROUP BY tier with COUNT(*) gives one row per tier in a single pass. COUNT(*) counts rows, which is what the question asks for; it does not depend on any column being filled.

SELECT
    CASE
        WHEN profit > 100 THEN 'high'
        WHEN profit >= 0 THEN 'moderate'
        ELSE 'negative'
    END AS tier,
    COUNT(*) AS order_count
FROM orders
GROUP BY tier

The trap

SELECT
    CASE
        WHEN profit >= 0 THEN 'moderate'
        WHEN profit > 100 THEN 'high'
        ELSE 'negative'
    END AS tier,
    COUNT(*) AS order_count
FROM orders
GROUP BY tier

Three independent conditions

Each tier is its own predicate, so each must restate both of its bounds (profit BETWEEN 0 AND 100). Drop one bound and the boundary rows count twice or vanish, and nothing in the query structure catches the mistake.

One ordered chain

Each branch states only its lower edge because the branch above already took everything higher. The tiers are disjoint and exhaustive by construction, and GROUP BY tier returns a row per tier.

Common follow-up questions

• Finance adds a 'thin' tier for profit above 0 up to 20. Where does the new branch go, and which existing tier shrinks? (Tests whether the candidate places the new branch correctly in the chain, keeping exactly 0 in 'moderate'.)
• What changes if `profit` can be `NULL` for orders that have not settled yet? (With no branch for it, the `ELSE` would label an unknown profit 'negative'.)
• The CFO now wants the tiers broken down by `region`, one column per tier. How does the query change? (Moves from a row per tier to a crosstab; `region` also has NULLs that need a place.)