Level 5 · Boss: homepage merchandising lists

Each homepage strip is a top-N list, which means three decisions every time: which rows qualify, how to sort them, and how many to keep. Getting the clause order right matters:

SELECT ...
FROM products
WHERE ...        -- 1. which rows qualify
ORDER BY ...     -- 2. sort, with a tie-breaker
LIMIT ... OFFSET ...;  -- 3. how many, starting where

The database filters first, then sorts, then cuts. So “the 5 most expensive Lighting products” is not “the 5 most expensive products, then keep the Lighting ones”. If you sorted all products and limited before filtering, you could end up with zero Lighting rows. SQL’s clause order does the right thing as long as the condition is in WHERE.

Step 1: priciest lighting

Filter to active Lighting products, sort by price descending, keep 5. The tie-breaker product_id ASC matters if two lamps share a price at the 5th position.

Step 2: newest launches

launched_on is a DATE, and several products can launch on the same day, so ties are likely. product_id DESC breaks them, a reasonable choice since higher IDs were created later. In a real ticket, say which tie-breaker you used so Ashley is not surprised.

Step 3: the second strip

The strip shows 6 items, and Ashley wants the second one, so skip 6 and take 6. Apply the price filter before paging. A product exactly at $40 is not “under 40”, so < 40 is right.

Inactive products

All three strips filter on is_active. Discontinued products should never be promoted, and forgetting the flag is one of the most common reasons a homepage links to an out-of-stock page. When you build any customer-facing list, ask whether inactive or hidden items need excluding.

Check your own work

Before sending results like these, spend ten seconds on sanity checks:

• Does step 1 show only Lighting, and are prices falling?
• Does step 2 show only active products, with dates falling?
• Does the first row of step 3 cost at least as much as the 6th row of the first strip would?

Interviewers love candidates who check their output without being asked. On the job, it is what keeps your name off the “who sent this?” thread.