# Profit Tiers

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

Canonical URL: <https://datadriven.io/problems/profit_tiers>

Domain: SQL · Difficulty: medium · Seniority: L4

## Problem

The CFO wants orders bucketed by profitability for the operating review. Label each order in orders as 'high' when profit exceeds 100, 'moderate' when profit is between 0 and 100 inclusive, and 'negative' when profit is below 0, then count orders per tier. Return the tier label and its order count.

## Worked solution and explanation

### Why this problem exists in real interviews

Drawn from a order processing domain, this question centers on conditional aggregation via CASE over the `orders` table. The tricky part is handling the `profit` column correctly under the given constraints.

---

### Break down the requirements

#### Step 1: Label each order with a tier

`CASE WHEN profit > 100 THEN 'high' WHEN profit >= 0 THEN 'moderate' ELSE 'negative' END` assigns the tier.

#### Step 2: Group by tier and count

`GROUP BY tier` with `COUNT(*)` tallies orders per profitability bucket.

---

### The solution

**Case pivot for profit tiers**

```sql
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
```

> **Cost Analysis**
>
> With `orders` (30,000,000 rows), the full scan reads significant data. A composite index on the filter columns pushes the predicate into the index layer. Pre-aggregation in a materialized view is worth considering at this scale.

> **Interviewers Watch For**
>
> Interviewers evaluate whether you translate the English requirements into the correct SQL clauses on the first attempt. They watch for clean syntax, correct column references, and whether you verify edge cases before declaring the query complete.

> **Common Pitfall**
>
> The most common mistake is misreading the prompt's filtering or grouping requirements. Double-check which columns to group by, which to aggregate, and whether the output should be filtered with `WHERE` (before grouping) or `HAVING` (after grouping).

---

## Common follow-up questions

- What would happen to your result if `orders.profit` contained duplicate values that you did not expect? _(Tests whether the candidate considers data quality issues in `profit` and uses DISTINCT or deduplication where needed.)_
- With 30,000,000 distinct values in `orders.order_id`, how would a composite index on the GROUP BY columns change the execution plan? _(Probes understanding of how cardinality in `order_id` affects grouping and sort operations.)_
- Your conditional CASE logic assumes the categories are exhaustive. What happens if a row in `orders` falls into none of the branches? _(Tests awareness of the implicit ELSE NULL in CASE expressions.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/profit_tiers)
- [SQL Interview Questions](https://datadriven.io/sql-interview-questions)
- [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep)
- [Daily Challenge](https://datadriven.io/daily)

---

Source: DataDriven (https://datadriven.io). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.