# Stock Status

> Human-readable availability labels.

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

The storefront team is redesigning product tiles and wants a clean availability label on each one. For every product, output the product name, category, price, and a label that reads 'In Stock' when the item is available and 'Out of Stock' otherwise. Only include products that have a price on file, ordered alphabetically by product name.

## Worked solution and explanation

### Why this problem exists in real interviews

This challenge asks you to apply filtering and projection to the `products` table, simulating a real product analytics workflow. Pay attention to the `price` column as they drive the aggregation and output.

---

### Break down the requirements

#### Step 1: Filter out null values

Exclude rows where `price` is NULL. This prevents nulls from polluting aggregations or creating phantom groups.

#### Step 2: Classify rows with a CASE expression

The `CASE` expression evaluates conditions top to bottom and returns the first match. Order matters: put the most restrictive condition first to avoid misclassification.

#### Step 3: Sort the final output

The `ORDER BY` clause ensures the result appears in the expected sequence. Interviewers check that the sort direction matches the prompt.

---

### The solution

**Filter out null values to find stock status**

```sql
SELECT product_name, category, price, CASE WHEN in_stock = 1 THEN 'In Stock' ELSE 'Out of Stock' END AS availability
FROM products
WHERE price IS NOT NULL
ORDER BY product_name
```

> **Cost Analysis**
>
> With ~6,000 rows, the query performs a single sequential scan. An index on the filter/join columns would reduce the scan to a seek.

> **Interviewers Watch For**
>
> Interviewers watch for how you handle NULL values and whether you account for them in filters and aggregations.

> **Common Pitfall**
>
> Forgetting to filter NULLs creates phantom groups or inflated counts. Always check `null_fraction` in the schema before assuming columns are clean.

---

## Common follow-up questions

- What result would you get if every value in `products.price` were NULL? Would your query return an empty set or something unexpected? _(Tests extreme NULL scenarios and whether the candidate guards against edge cases in `price`.)_
- The `category` column in `products` is heavily skewed toward a few popular values. How would data skew affect parallel execution of your query? _(Tests understanding of skew in `products.category` and its impact on distributed query performance.)_
- Your conditional CASE logic assumes the categories are exhaustive. What happens if a row in `products` 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/stock_status)
- [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.