Stock Status

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

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.

Human-readable availability labels.

Tables

products

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

Expected output

Worked solution and explanation

This is a row-by-row relabel with a completeness gate, dressed up as a storefront redesign. Nothing gets grouped: every product that passes the gate comes out exactly once. Anyone can write the label. What separates candidates is reading 'has a price on file' as an existence test. Nine of the 200 rows in products have a NULL price and must go, while product 1246, a free sample priced at 0.0, has a price and must stay. Reach for price > 0 and you quietly ship 190 tiles instead of 191.

Walking to the answer

Step 1: Gate on whether price exists

WHERE price IS NOT NULL asks the only question the storefront cares about: is there a number to print? A value test like price > 0 answers a different question, and on this seed it costs you the free sample. The 0.0 row sits in the sample data, so check it before you decide.

Step 2: Turn the 1/0 flag into the tile text

in_stock is an INTEGER, not a status string, so the label keys off the number: CASE WHEN in_stock = 1 THEN 'In Stock' ELSE 'Out of Stock' END. Alias it availability, the header the expected output uses. Selecting the raw column gives the right rows with every label wrong.

Step 3: Sort by product_name as text

ORDER BY product_name compares character by character, which is why 'Basic Device 11X' comes before 'Basic Device 1X' in the preview. That is the expected order, not a bug to work around; pulling the trailing number out of the name would answer a question nobody asked.

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

Value test

WHERE price > 0 drops the 9 NULL prices and also the free sample at 0.0, returning 190 rows. WHERE price != NULL is worse: the comparison is unknown on every row, so nothing survives.

Existence test

WHERE price IS NOT NULL drops exactly the 9 missing prices and keeps the 0.0 row, returning all 191 tiles the wall needs.

Common follow-up questions

• Suppose `in_stock` can be NULL when a warehouse has not reported. What label does the current query print for those products, and what should it print? (Tests that the `ELSE` branch quietly turns unknown into 'Out of Stock'.)
• Merchandising wants every out-of-stock tile after the in-stock ones, each group still A to Z. How does the ordering change? (Tests sorting on a derived column ahead of `product_name`.)
• The tiles should show the price as text like '$17.52', and 'Free' for the zero-priced sample. How would you build that column? (Tests a second conditional label combined with number formatting.)