NC 07: Brand to ticker, linking auction results to public companies

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

The task

Use the stock_ticker field to pull every lot sold under a listed parent company's brands, connecting a secondary-market auction result to a public equity.

Starter steps

  1. Open the SQL editor.
  2. Paste and run this query, which filters all_watches_data to every lot associated with Richemont's stock ticker, regardless of which specific house within the group made the piece:
SELECT designer, model, item_title, sale_date, usd_price_decimal, status, stock_ticker
FROM all_watches_data
WHERE stock_ticker = 'CFR.SW'
  AND status = 'sold'
ORDER BY sale_date DESC
LIMIT 50;
  1. Look at the designer column across the result grid, notice that several different brand names can share the same stock_ticker, since one listed parent group can own multiple brands.
  2. Run the same query against all_jewels_gems_data, keeping the stock_ticker = 'CFR.SW' filter, to see the same parent group's footprint in the jewelry category.
  3. Use the Coverage panel's company browser from NC_01 to look up a different ticker for a different listed group, and substitute it into the WHERE clause.

Expected result

A result grid of sold lots spanning more than one brand name in the designer column, all sharing the same stock_ticker, illustrating how secondary-market results roll up to a single public company across its portfolio of brands. Running the query against a second table (jewelry) with the same ticker returns a different set of lots, showing the same company's presence in a second category.

Stretch challenge

Compute pricing power (as in NC_04) grouped by designer for only the rows where stock_ticker = 'CFR.SW', to see whether the group's individual brands have different pricing power on the secondary market despite sharing a parent ticker.