Problem set 15: Art market fundamentals
Module 15. Total: 100 points. Format: no-code sandbox only. No API key or coding is required for any problem in this set.
Before you start
This problem set works with the fine art data. In that table the designer field holds the artist or maker name, and the vendor field holds the auction house. The documented fields you will use are designer, item_title, sale_date, usd_price_decimal, sale_estimates_high_usd_price, status, and vendor. Choose one well-known artist to use throughout, and confirm the exact spelling of that artist's name in the coverage browser before you write any filter, since exact-match filters are spelling-sensitive. Remember throughout that this dataset captures the secondary market only, resales at auction, not an artist's primary gallery or dealer sales, and that the newest periods are still being ingested, so no recent slice should be read as a trend.
Problem 1 (15 points): Build a comparables set for one artist
Write a SQL query against the fine art data that builds a comparables set for your chosen artist: sold lots only, ordered from the highest realized price to the lowest. Include the artist, item title, vendor, sale date, and realized price in your result. Then, in two to three sentences, explain what a comparables set is and why an appraiser or dealer builds one before valuing a new work.
-- your query here
Problem 2 (20 points): Price dispersion within one artist's market
Write a SQL query that returns, for the same artist, the count of sold lots, the minimum realized price, the median realized price, and the maximum realized price. Then, in three to four sentences, explain why the median is a safer measure of a typical price than the average when a handful of very high sales are present, and describe in your own words how wide or narrow your artist's range turned out to be.
-- your query here
Problem 3 (20 points): Estimate versus realized price as a test of the house's judgment
Write a SQL query that lists your chosen artist's sold lots with a valid, non-zero pre-sale high estimate, alongside the realized price, ordered by sale date from most recent. Then, in four to five sentences, explain what the pre-sale estimate represents, what it means when realized price consistently lands above that estimate, what it means when it consistently lands below, and what your own artist's lots suggest about how the auction houses' judgment held up against real buyers.
-- your query here
Problem 4 (20 points): A medium and format proxy, and its limits
Write a SQL query that uses a text search on item_title, such as LOWER(item_title) LIKE '%oil on canvas%', to approximate a medium or format for your chosen artist's sold lots, and returns the count of matched lots along with the minimum, median, and maximum realized price for that matched subset. Then, in four to five sentences, explain clearly why this is an imperfect proxy rather than a documented field (the fine art table has no medium, size, provenance, or condition column), name at least one kind of work this search might miss or wrongly include, and compare the matched subset's price range to your artist's full range from Problem 2.
-- your query here
Problem 5 (25 points): A short valuation-reasoning brief
Write a 200 to 300 word brief, in the voice of an adviser preparing a client for an upcoming consignment of a work by your chosen artist. The brief should draw on your results from Problems 1 through 4 to reason through where a specific hypothetical work might fall in the artist's range, naming which price-forming factors, artist, medium and format, size, period, rarity, provenance, condition, and timing, would push a value estimate up or down, even where the data itself cannot confirm those factors directly. The brief must include an explicit limitations section addressing the secondary-market-only nature of the data, the imperfect nature of any item_title text search used, and the recency caveat, and it must not read any recent-period total as evidence of a rising or falling market.
Submission. Turn in this file with your SQL, your short written answers, and your brief filled in.