# Actually Available

> The catalog is big. The shelf is smaller.

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

The warehouse team is reconciling physical inventory against the catalog. How many products are currently marked as in stock?

## Worked solution and explanation

### Why this problem exists in real interviews

Counting products matching a boolean condition tests WHERE + COUNT. This is a fundamental query pattern.

---

### Break down the requirements

#### Step 1: Filter for in-stock products

`WHERE in_stock = 1` (or `= TRUE`) isolates in-stock products.

#### Step 2: Count them

`COUNT(*)` gives the total number of in-stock products.

---

### The solution

**Conditional count with WHERE**

```sql
SELECT COUNT(*) AS in_stock_count
FROM products
WHERE in_stock = 1
```

> **Cost Analysis**
>
> An index on `in_stock` enables a fast scan.

> **Interviewers Watch For**
>
> This is a warm-up. The interviewer checks basic WHERE + COUNT fluency.

> **Common Pitfall**
>
> If `in_stock` is stored as text ('true'/'false'), integer comparison fails.

---

## Common follow-up questions

- How would you count per category? _(Tests GROUP BY category.)_
- How would you get both in-stock and out-of-stock counts? _(Tests SUM(CASE WHEN in_stock = 1 ...) for both.)_
- How does the optimizer handle COUNT(*) with WHERE? _(Tests index scan vs. table scan knowledge.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/in_stock_product_count)
- [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.