# Actually Available > The catalog is big. The shelf is smaller. Canonical URL: 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 ### What's actually being tested This is a filtered count wearing an inventory-reconciliation costume. The whole question hinges on one word in the prompt: "marked as in stock." You are not counting the catalog, you are counting the subset where a flag is set. The trap is that in_stock is an INTEGER 0/1 flag, not a boolean and not the thing you sum. The candidates who miss it reach for COUNT of everything and hand back the catalog size. Skip the WHERE and you report 200 products in stock when only 158 actually are, and the warehouse team reconciles physical inventory against a number inflated by every out-of-stock item on the shelf. > **Trick to solving** > > Count the ROWS THAT PASS A FILTER, not the rows in the table. COUNT(*) over the whole table answers "how big is the catalog?" The question asked "how many are in stock?" Those are different numbers (200 vs 158 here). The WHERE clause is the entire problem; the COUNT is the easy part. ### Building the query #### Step 1: Filter to the flagged rows in_stock holds 0 or 1, so WHERE in_stock = 1 keeps exactly the in-stock products. Don't lean on WHERE in_stock (truthy-integer shorthand) or WHERE in_stock = TRUE: this column is an integer, and those forms make you depend on engine-specific truthiness instead of stating the value you mean. Compare against the literal 1. #### Step 2: Count what survived the filter COUNT(*) tallies the rows the WHERE clause let through. Because in_stock is never NULL here, COUNT(*) and COUNT(in_stock) agree, but COUNT(*) is the honest expression of intent: "how many rows match?" Alias it in_stock_count so the single-cell result reads as an answer, not a mystery scalar. **Filtered count** ```sql SELECT COUNT(*) AS in_stock_count FROM products WHERE in_stock = 1 ``` *The WHERE does the work; the COUNT just tallies what's left.* **Catalog size (wrong)** SELECT COUNT(*) AS in_stock_count FROM products; -- returns 200, every row regardless of stock. Answers a question nobody asked. **In-stock count (right)** SELECT COUNT(*) FROM products WHERE in_stock = 1; -- returns 158, only the flagged rows. Answers the actual question. > **Common pitfall** > > Writing SUM(in_stock) instead of a filtered COUNT. It happens to give the right number here because the flag is exactly 0/1 and never NULL, so it's a coincidence, not a technique. The moment the flag becomes 'Y'/'N', a status string, or picks up NULLs, SUM breaks and the filtered COUNT still reads correctly. State the filter, then count. > **Interviewers watch for** > > The tell isn't whether you can write COUNT and WHERE, everyone can. It's whether you registered that in_stock is a flag and pinned the count to = 1 without being nudged. Candidates who paraphrase the prompt back ("so I want only the rows where the flag is set") before typing signal they read requirements for scope, not just for keywords. > **Performance insight** > > At 12,000 rows this is a single sequential scan with a counter, sub-millisecond, no index needed. Don't propose indexing in_stock: it has cardinality 2 and is single-hot (most rows = 1), so a B-tree index buys nothing and the optimizer would ignore it anyway. Selective columns earn an index; a two-value flag does not. ## Common follow-up questions - Now give me in-stock AND out-of-stock counts in one result. _(Pushes from a WHERE filter to conditional aggregation: SUM(CASE WHEN in_stock = 1 THEN 1 ELSE 0 END) alongside the 0 case, computed in a single pass.)_ - Break the in-stock count down by category. _(Adds GROUP BY category while keeping the WHERE in_stock = 1 filter; tests filter-then-group ordering.)_ - What if in_stock could be NULL for products never audited? _(Tests three-valued logic: WHERE in_stock = 1 silently drops NULLs, and whether the candidate knows that's correct here versus needing COALESCE for a different question.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/actually_available) - [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). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.