High-Rated In-Stock Percentage

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: Meta

Problem

The merchandising lead wants a single number for the board slide: what percentage of the entire catalog is both in stock and rated 4 or above?

Highly rated and in stock. A rare combo.

Tables

products

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

products: 10 of 200 rows
product_id	product_name	category	price	rating	in_stock
1001	Basic Device 1X	Books	17.52	2.7	1
1050	Deluxe Bundle 2X	Clothing	25.05	4.4	1
1099	Pro Unit 3X	Home & Kitchen	32.58	2.1	1
1148	Ultra Tool 4X	Collectibles	40.11	3.8	1
1197	Essential Set 5X	Toys	47.64	1.5	1
1246	Classic System 6X	Beauty	0	3.2	1
1295	Smart Gadget 7X	Automotive	62.7	4.9	0
1344	Eco Kit 8X	Garden	70.23	2.6	1
1393	Turbo Pack 9X	Music	77.76	4.3	0
1442	Premium Widget 10X	Electronics	85.29	2	1

Expected output

First 1 of 1 rows
pct_in_stock_high_rated
15.5

Worked solution and explanation

This is a denominator question dressed up as a board metric. Anyone can write the two conditions. What separates candidates is whether the base stays at all 200 products. Thirteen products have no rating, and for them rating >= 4.0 is not false: it is NULL. Divide by COUNT(rating), or by a rated-only subset, and those 13 rows quietly leave the base. The same 31 qualifying products then read as 16.58% instead of 15.5%, and the slide says 'entire catalog' about a number that is really 'rated catalog'.

Walking it

Step 1: Keep every product in the base

Write no WHERE at all. The question is a share of the whole catalog, so the aggregate has to see every row of products, unrated ones included. Any filter you add here becomes the denominator.

Step 2: Collapse both conditions into one flag

CASE WHEN in_stock = 1 AND rating >= 4.0 THEN 1.0 ELSE 0.0 END gives each product a 1 or a 0. For an unrated product the comparison is NULL, so WHEN is not satisfied and the row falls to ELSE 0.0. It stays in the count as a 'no'. That ELSE branch is the whole problem.

Step 3: Let the average do the division

AVG of a 0/1 flag is the share of 1s across every row that carries a flag, so counting and dividing happen in one aggregate. Multiply by 100 for a percentage and wrap it in ROUND(..., 2) for the slide.

Conditional aggregation for compound percentage

SELECT ROUND(
    AVG(CASE WHEN in_stock = 1 AND rating >= 4.0 THEN 1.0 ELSE 0.0 END) * 100, 2
) AS pct_in_stock_high_rated
FROM products

31 flagged products over all 200 rows of products: 15.5.

Rated-only base: COUNT(CASE WHEN ... THEN 1 END) / COUNT(rating) divides 31 by 187, because COUNT(rating) skips the 13 NULLs. The slide reads 16.58.
Whole-catalog base: AVG(CASE ... ELSE 0.0 END) divides 31 by 200, because every row carries a flag and an unrated product carries 0. The slide reads 15.5.

The version that overstates the slide

-- Rated-only base: reads 16.58, not 15.5
SELECT ROUND(
    100.0 * COUNT(CASE WHEN in_stock = 1 AND rating >= 4.0 THEN 1 END) / COUNT(rating), 2
) AS pct_in_stock_high_rated
FROM products

Divides by the 187 rated products; the 13 unrated ones vanish from the base.

Common follow-up questions

How would you report this per `category`, keeping categories with no qualifying product at 0? (Adds a grouping; the flag already yields 0 rather than a missing row.)
Product wants unrated items left out on purpose. What changes, and how do you label the number? (Tests switching the base deliberately and saying so on the slide.)
What if `rating` moves to a 1-10 scale? (Tests isolating the threshold from the rest of the logic.)