Midterm exam
Covers Modules 1 through 7: alternative data in finance, data science and SQL, consumer and luxury economics, investments and equity research, library data literacy, FinTech and data products, and the business of the art market. Time: 100 minutes. Total: 130 points. Format: no-code sandbox at sandbox.altfndata.com; no API key is needed for this exam.
Read all four sections before starting. Section 2 requires the sandbox SQL editor; keep the schema tab open to confirm exact table and column names as you work. Use only the documented fields: designer, model, item_title, sale_date, usd_price_decimal, sale_estimates_high_usd_price, status, vendor, stock_ticker.
Section 1: Conceptual short answer (48 points, 6 points each)
1.1. Name the three ALT/FNDATA access surfaces and state which one is the primary classroom tool for students.
1.2. Explain, in three to four sentences, why auction and resale pricing data on physical luxury goods qualifies as alternative data rather than traditional financial data.
1.3. Define pricing power and state, in one sentence, what a ratio above 1.0 means about buyer behavior.
1.4. Define sell-through rate and identify the two status values that must both be counted in its denominator.
1.5. In three to four sentences, explain the recency caveat that applies to any time-bucketed (quarterly) metric built from this dataset, and state what an analyst should do about it before drawing a conclusion.
1.6. Explain what the stock_ticker field lets an analyst do that the designer field alone does not, then describe the Phillips and Movado divergence in one to two sentences and what it illustrates about the relationship between the saleroom and public equity prices.
1.7. In plain language suitable for a general, non-technical reader, explain why an unsold lot should not be treated the same as a sold lot when someone is trying to understand what an item is really worth.
1.8. Name the three access surfaces again in the role of a product designer: explain why the production API issues keys manually while the sandbox is open to self-registration, and why discovery endpoints (GET /v1/tables and GET /v1/tables/{name}/schema) are good product design.
Section 2: Query writing (52 points)
Work in the sandbox SQL editor. For each problem, write a single SQL query. Confirm your table and column names in the schema tab before you begin.
2.1 (12 points): Write a query that returns item_title, designer, sale_date, and usd_price_decimal for the 15 most recent sold records for one designer of your choice.
-- your query here
2.2 (14 points): Write a query that computes pricing power (median or, if unavailable, average, of usd_price_decimal divided by sale_estimates_high_usd_price) for one designer, correctly restricted to sold records with a valid, non-zero high estimate.
-- your query here
2.3 (14 points): Write a query that computes sell-through rate for the same designer used in 2.2, correctly including both sold and unsold records in the denominator.
-- your query here
2.4 (12 points): Working in the fine art data, write a query that builds an auction house league table: for each vendor, return the number of sold lots and the total realized value, ordered so the highest-value houses appear first. (In the fine art data the vendor field holds the auction house.)
-- your query here
Section 3: Applied mini analysis (18 points)
Choose one subject: the designer from Section 2, a publicly traded luxury company via its stock_ticker, or an auction house via the league table you built in Problem 2.4. Write a short analysis, 200 to 300 words, that:
a. States the relevant figures clearly (pricing power and sell-through for the designer or ticker route; the house's league-table position by realized value and lot count for the house route). b. Interprets what the figures suggest about demand, or about the house's business (for the designer route, Problem set 3's four-category framework of pricing power crossed with sell-through is useful but not required). c. Explicitly notes one limitation of drawing a conclusion from a single subject and a single snapshot in time, and what additional evidence would strengthen the analysis.
Section 4: Plain-language communication (12 points)
Rewrite the core finding of your Section 3 analysis as a single short paragraph, no more than 100 words, suitable for a general audience with no background in finance or data, for example a library workshop attendee. Avoid jargon such as "pricing power," "sell-through rate," or "median," and describe the same finding in plain, concrete language.
Submission. Turn in this file with all four sections completed.