NC 04: Pricing power

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

The task

Calculate a brand's pricing power, the median ratio of realized price to pre-sale high estimate across its sold lots, and interpret what a ratio above or below 1.0 means.

Starter steps

  1. Open the SQL editor.
  2. Paste and run a query that computes the ratio per lot for sold lots only, and then takes the median across those lots using the sandbox's percentile function:
SELECT
  designer,
  approx_percentile(usd_price_decimal / sale_estimates_high_usd_price, 0.5) AS pricing_power_median
FROM all_watches_data
WHERE status = 'sold'
  AND sale_estimates_high_usd_price > 0
  AND designer = 'Van Cleef & Arpels'
GROUP BY designer;
  1. If the sandbox's SQL dialect does not expose approx_percentile, the intent is the same: compute the per-lot ratio usd_price_decimal / sale_estimates_high_usd_price, then take the median of that column. Check the sandbox documentation panel for whichever median or percentile function is available, and substitute it in place of approx_percentile.
  2. Re-run the query for two or three other designers by changing the designer = ... filter, and compare the resulting medians.
  3. Note that a pricing_power_median value above 1.0 means buyers are, on median, paying over the pre-sale high estimate; a value below 1.0 means the median sold lot cleared under estimate.

Expected result

A single-row result per designer showing a pricing_power_median value, typically a number in the range of roughly 0.5 to 1.5. A strong, in-demand brand such as Van Cleef & Arpels tends to show a ratio noticeably above 1.0 (illustratively, around 1.36x in past analysis), meaning buyers have historically paid over the auction house's own high estimate for at least half of its sold lots.

Stretch challenge

Extend the query to compute pricing power by designer and by vendor together (add vendor to the SELECT and GROUP BY), so you can see whether the same brand's pricing power differs meaningfully between auction houses.