NC 05: Sell-through rate

Track: No-code sandbox (sandbox.altfndata.com) Prerequisite: NC_04.

The task

Calculate a brand's sell-through (clearance) rate, the share of offered lots that actually sold, and compare it across a couple of brands or vendors.

Starter steps

  1. Open the SQL editor.
  2. Paste and run this query, which counts sold lots and total offered lots for a single designer, then divides:
SELECT
  designer,
  COUNT(*) FILTER (WHERE status = 'sold') AS sold_count,
  COUNT(*) AS offered_count,
  CAST(COUNT(*) FILTER (WHERE status = 'sold') AS DOUBLE) / COUNT(*) AS sell_through_rate
FROM all_watches_data
WHERE designer = 'Patek Philippe'
GROUP BY designer;
  1. If the sandbox's SQL dialect does not support FILTER, use the equivalent SUM(CASE WHEN status = 'sold' THEN 1 ELSE 0 END) form instead, the intent is identical: count sold lots over all offered lots.
  2. Re-run with a different designer to compare sell-through rates side by side.
  3. Try grouping by vendor instead of designer to see which auction houses or marketplaces clear a higher share of what they list.

Expected result

A single row per designer (or vendor) with a sold_count, an offered_count, and a sell_through_rate between 0 and 1. A high-demand brand or a strong single-owner sale should show a sell-through rate closer to 1.0 (nearly everything offered found a buyer), while a broader or more mixed set of lots typically clears at a lower rate.

Stretch challenge

Combine designer and vendor in the same GROUP BY to find the single vendor-designer pairing in the data with the highest sell-through rate among pairings with at least, say, 20 offered lots (add a HAVING COUNT(*) >= 20 clause).