Actually Available

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

L3

Asked in

Yelp

Problem

How many products are currently marked as in stock, the figure an inventory analyst checks against the warehouse's physical count?

The catalog is big. The shelf is smaller.

Tables

products

Columns: product_id INTEGER, product_name TEXT, category TEXT, price REAL, rating REAL, in_stock INTEGER

Expected output

Worked solution and explanation

What's actually being tested

This is a filtered count wearing an inventory-reconciliation costume. The whole question hinges on three words 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 that is never NULL, so COUNT(in_stock) counts every row exactly like COUNT(*) does. Candidates who miss this hand back the catalog size: 200 products in stock when only 158 actually are, so the reconciliation is off by every one of the 42 out-of-stock items.

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: the 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.

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

Catalog size (wrong)

SELECT COUNT(*) AS in_stock_count FROM products returns 200, every row regardless of stock. It answers a question nobody asked.

In-stock count (right)

SELECT COUNT(*) AS in_stock_count FROM products WHERE in_stock = 1 returns 158, only the flagged rows. It answers the actual question.

Common follow-up questions

• Now give me in-stock AND out-of-stock counts in one result. (Moves 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 `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 is correct here or whether a different question needs `COALESCE`.)