PY 05: Sell-through rate

Track: Code (Python client + raw REST) Prerequisite: PY_04.

The task

Fetch both sold and offered lots for a brand, and compute the sell-through (clearance) rate in Python as sold lots divided by all offered lots.

Starter steps

1. Fetch every lot for one designer, with no status filter, so the result includes both sold and unsold lots:

rows = client.query(
    "all_watches_data",
    fields=["designer", "status"],
    filters=[{"field": "designer", "op": "eq", "value": "Patek Philippe"}],
    limit=1000,
)

2. Watch for pagination: if len(rows) == 1000, fetch additional pages with offset (as in PY_03) before computing a rate, otherwise the denominator will be understated.
3. Count sold versus total, and compute the ratio:

offered_count = len(rows)
sold_count = sum(1 for r in rows if r["status"] == "sold")
sell_through_rate = sold_count / offered_count if offered_count else None
print(sold_count, offered_count, round(sell_through_rate, 3))

4. Wrap it in a function so you can compare brands or vendors easily:

def sell_through(designer, table="all_watches_data"):
    rows = client.query(
        table,
        fields=["status"],
        filters=[{"field": "designer", "op": "eq", "value": designer}],
        limit=1000,
    )
    offered = len(rows)
    sold = sum(1 for r in rows if r["status"] == "sold")
    return sold / offered if offered else None

for name in ["Patek Philippe", "Omega", "Rolex"]:
    print(name, sell_through(name))

Expected result

sell_through_rate is a float between 0 and 1. A high-demand brand tends to show a rate closer to 1.0, meaning nearly everything offered found a buyer, while a broader or more mixed set of lots typically clears at a lower rate. Looping sell_through(...) over a few designer names prints one rate per brand for direct comparison.

Stretch challenge

Adapt sell_through(...) to group by vendor instead of designer by adding a vendor filter argument, then loop it over a short list of vendor names pulled from the Coverage panel in the sandbox, to find which auction house in your sample clears the highest share of what it lists.