Problem set 4: Investments and equity research
Module 4. Total: 100 points. Format: no-code sandbox, with an optional API extension in Problem 3 using a shared class API key from your instructor.
Before you start
This problem set centers on the stock_ticker field, which maps a brand appearing on an auction or resale record to the ticker of its public parent company, drawing on a company mapping that covers more than 2,000 public and private companies with S&P 500 sector mapping. Choose one publicly traded luxury conglomerate with brands in at least two of the platform's categories (for example Richemont, ticker CFR.SW, which owns brands spanning both watches and jewelry) to use throughout this problem set.
Problem 1 (15 points): Understanding the brand-to-ticker bridge
In four to five sentences, explain what the stock_ticker field represents, how it differs from the designer field, and why being able to connect a specific auction record to a public company's ticker is useful for an equity research analyst who covers luxury goods companies.
Problem 2 (20 points): Pricing power and sell-through for a single ticker
Write a SQL query against one category table that filters to stock_ticker equal to your chosen company's ticker and computes both pricing power (median of usd_price_decimal divided by sale_estimates_high_usd_price) and sell-through rate (share of offered lots that sold) for that ticker within that category.
-- your query here
Problem 3 (20 points): The same query via the API (optional code extension)
Using the shared class API key your instructor has provided, write the JSON request body and endpoint you would send to POST /v1/tables/{name}/query to retrieve the raw fields needed to reproduce Problem 2 (stock_ticker, status, usd_price_decimal, sale_estimates_high_usd_price). Note that the query endpoint returns matching rows, not a pre-computed aggregate, so describe in one to two sentences how you would need to paginate on offset and aggregate the results yourself, either in the tutorials notebook or with the Python client, to arrive at the same pricing power and sell-through figures.
POST /v1/tables/<table_name>/query
Header: X-API-Key: <class key>
Body:
{
...
}
Problem 4 (20 points): Applied analyst note
Write a short analyst note, 200 to 300 words, in the style of an equity research comment, using the following documented example: one weekend in June, Phillips' New York watch sale became the highest-grossing watch auction in US history at 75.8 million dollars with every lot sold, while the listed watch brand Movado fell about four percent in the same week. Your note should explain what this divergence between a strong secondary market result and a falling share price might suggest to an analyst, and should explicitly caution against over-reading a single auction result as a standalone signal, noting what additional evidence (for example, a sell-through rate or pricing power figure computed over a larger sample) would strengthen the case either way.
Problem 5 (25 points): Cross-category ticker roll-up
For your chosen ticker, write two SQL queries, one against each of two different category tables where that ticker's brands appear (for example watches and jewelry and gems for Richemont, CFR.SW). Report pricing power and sell-through for the ticker in each category separately. Then, in three to four sentences, explain why the ability to roll up demand signals for a single public company across multiple asset categories (rather than being limited to just one) is valuable for equity research on a diversified conglomerate, and note one reason the two category-level figures for the same ticker might legitimately differ from each other.
-- query for category 1
-- query for category 2
Submission. Turn in this file with your SQL, JSON body, and written answers filled in.