NC 06: Demand over time

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

The task

Build a simple quarterly demand index for one brand by bucketing sold lots by quarter and counting them, as a learning exercise in time bucketing rather than a market call.

Starter steps

  1. Open the SQL editor.
  2. Paste and run this query, which truncates sale_date to the quarter and counts sold lots per quarter for one designer:
SELECT
  designer,
  DATE_TRUNC('quarter', sale_date) AS sale_quarter,
  COUNT(*) AS lots_sold,
  approx_percentile(usd_price_decimal, 0.5) AS median_price_usd
FROM all_watches_data
WHERE designer = 'Omega'
  AND status = 'sold'
GROUP BY designer, DATE_TRUNC('quarter', sale_date)
ORDER BY sale_quarter;
  1. If the sandbox has a built-in charting panel, plot lots_sold (or median_price_usd) against sale_quarter as a line or bar chart to see the shape of the series.
  2. Read the series left to right, oldest quarter to most recent.

Expected result

A row per quarter with a lots_sold count and a median_price_usd, going back toward the late 1990s where coverage supports it and running up to the most recent quarters. This is a learning exercise in building a time series, not a market-appreciation claim: the most recent one to two quarters typically show fewer lots than the trend, because new auction results take time to be scraped, classified, and ingested into the dataset. Do not read a drop in the final quarter or two as a real decline in demand; treat those trailing points as provisional and under-ingested.

Stretch challenge

Rebuild the same query for a second designer in a different category, for example a jewelry house in all_jewels_gems_data, and compare the shape of the two quarterly series, excluding the trailing one to two under-ingested quarters from any comparison.