Module 2: Data science and SQL

Lesson video · Data science and SQL (4:19)

View study sheet (PDF) View SQL cheat sheet (PDF)

Teaching this module? A facilitator guide adds a preparation checklist, timing cues, discussion guidance, slides, and a printable PDF.

This session is a hands-on SQL workshop built on a real institutional dataset rather than a toy database. Students practice filtering, grouping, and aggregating transaction-level records to build a simple demand index, one of the core analytical patterns used by working data analysts. The dataset (watches, jewelry, handbags, fine art, and more, drawn from more than 850 vendors with history to 1949) is large and messy enough to be realistic, while the sandbox environment removes any setup friction, so class time goes entirely to query design and interpretation. Students who want a coding extension can also see the same query issued against the production API using a short Python snippet, but the graded exercise stays entirely in SQL.

Target course(s) and level

Database systems, data science methods, or applied SQL course. Suitable for undergraduate students who have completed an introductory SQL unit, and for graduate students in a data analytics or business intelligence program.

Learning objectives

By the end of this session, students will be able to:

  1. Write SELECT statements with WHERE, GROUP BY, and ORDER BY clauses against a real multi-million-row dataset.
  2. Construct an aggregate metric (a brand-level demand index) from raw transaction records using quarterly bucketing.
  3. Compute a ratio metric (pricing power, defined as median realized price divided by high estimate) and explain what values above or below 1.0 mean.
  4. Identify and handle a data quality issue, specifically unsold lots, without discarding rows that matter to the analysis.
  5. Translate a SQL query result into a short written interpretation suitable for a non-technical stakeholder.
  6. (Extension) Reproduce a sandbox query as a POST request against a REST API and compare the two workflows.

Prerequisites

Completion of an introductory SQL unit covering SELECT, WHERE, GROUP BY, ORDER BY, and basic aggregate functions (COUNT, AVG, SUM). No prior exposure to alternative data or to ALT/FNDATA is required. The optional code extension assumes basic familiarity with Python and running a script from the command line.

Materials and access needed

  • Sandbox access at sandbox.altfndata.com, self-registered with a work or school email, auto-approved.
  • Projector or screen share for the instructor demo.
  • For the optional extension only: an instructor class API key, requested in advance from info@altfndata.com, shared with students who want to run the Python snippet; the downloadable client at docs.altfndata.com (altfndata_client.py) and the runnable tutorials notebook (altfndata_tutorials.ipynb).
  • The data dictionary and schema tabs in the sandbox, used as a reference throughout the session.

Session outline (90 minutes)

  • 0 to 10 min: Recap of SQL fundamentals and introduction to the dataset's shape: what a transaction record contains and which fields are documented (designer, model, item_title, sale_date, usd_price_decimal, sale_estimates_high_usd_price, status, vendor, stock_ticker).
  • 10 to 25 min: Guided query 1, filtering and sorting a single brand's sold lots.
  • 25 to 45 min: Guided query 2, building the quarterly demand index with GROUP BY and date bucketing.
  • 45 to 60 min: Guided query 3, computing pricing power as a ratio metric and discussing the sell-through concept.
  • 60 to 75 min: Independent practice, students adapt the demand index query to a brand of their choice.
  • 75 to 85 min: Optional extension demo, instructor shows the same demand index query issued via the production API with the Python client.
  • 85 to 90 min: Wrap-up and homework assignment.

In-class demo (sandbox-first, no code)

  1. Open sandbox.altfndata.com, sign in, and select the jewelry data table from the SQL editor dropdown.
  2. Run the first guided query below and review the columns returned: designer, item_title, sale_date, usd_price_decimal.
  3. Introduce the concept of date bucketing by asking students how they would group these rows into quarters using only SQL functions available in the editor.
  4. Run the second guided query, which buckets sold lots by quarter and computes a count and average realized price for each quarter, and display the result as a line chart using the pre-built charts tab.
  5. Run the third guided query, which computes the ratio of median realized price to median high estimate for a single brand, and explain that a result above 1.0 means buyers paid over the pre-sale estimate.
  6. Ask students to change the designer filter in query 3 to a second brand and compare the two ratios.
  7. Export both result sets to CSV using the export button, for use in the homework assignment.
  8. (Optional extension, code) Show the same quarterly demand index query issued as a POST request through the Python client, projected on screen only, to demonstrate that the sandbox and the API return the same underlying data.

Datasets and queries used

Dataset: jewelry data (documented fields: designer, model, item_title, sale_date, usd_price_decimal, sale_estimates_high_usd_price, status, vendor, stock_ticker).

Query 1, filter and sort:

SELECT designer, item_title, sale_date, usd_price_decimal
FROM all_jewels_gems_data
WHERE designer LIKE '%Van Cleef%'
  AND status = 'sold'
ORDER BY sale_date DESC
LIMIT 50;

Query 2, quarterly demand index:

SELECT
  DATE_TRUNC('quarter', sale_date) AS quarter,
  COUNT(*) AS lots_sold,
  AVG(usd_price_decimal) AS avg_realized_price
FROM all_jewels_gems_data
WHERE designer LIKE '%Van Cleef%'
  AND status = 'sold'
GROUP BY DATE_TRUNC('quarter', sale_date)
ORDER BY quarter;

Query 3, pricing power ratio:

SELECT
  designer,
  APPROX_PERCENTILE(usd_price_decimal, 0.5) AS median_realized,
  APPROX_PERCENTILE(sale_estimates_high_usd_price, 0.5) AS median_high_estimate,
  APPROX_PERCENTILE(usd_price_decimal, 0.5) / APPROX_PERCENTILE(sale_estimates_high_usd_price, 0.5) AS pricing_power
FROM all_jewels_gems_data
WHERE designer LIKE '%Van Cleef%'
  AND status = 'sold'
GROUP BY designer;

Optional API extension, equivalent request body to query 1 (POST /v1/tables/all_jewels_gems_data/query, header X-API-Key):

{
  "fields": ["designer", "item_title", "sale_date", "usd_price_decimal"],
  "filters": [
    {"field": "designer", "op": "like", "value": "Van Cleef"},
    {"field": "status", "op": "eq", "value": "sold"}
  ],
  "sort": [{"field": "sale_date", "direction": "desc"}],
  "limit": 50
}

Discussion questions

  1. Why do we filter on status equals sold before computing pricing power, and what would change if unsold lots were included without adjustment?
  2. What does a pricing power ratio above 1.0 tell you about a brand, and can you think of a business reason a brand might intentionally set conservative estimates?
  3. Why is quarterly bucketing a reasonable choice for a demand index in this dataset, as opposed to monthly or annual bucketing?
  4. What are the tradeoffs of using AVG versus a percentile-based measure like median when summarizing realized prices?
  5. If two brands have the same average realized price but very different lot counts, how would you decide which one has stronger evidence behind its number?
  6. What is the practical difference between running a query in the sandbox SQL editor and issuing the same query through the production API, and when would an analyst prefer one over the other?
  7. What data quality checks would you want to run on this dataset before trusting an aggregate metric built from it?

Homework assignment

Each student selects two brands from the jewelry or watches data and builds a quarterly demand index and a pricing power ratio for each, using only the sandbox SQL editor. The deliverable is a short technical writeup (three pages maximum) containing all four SQL queries used, the exported result tables or charts, and a written comparison of the two brands' demand index and pricing power. The writeup should explicitly note whether sold-lot counts are large enough in each quarter to trust the average, and should not extrapolate a growth trend from the last one or two quarters, since recent-period ingestion volume is not stable enough for period-over-period comparisons. Grading criteria: correctness of SQL (30 percent), correct construction of the demand index and pricing power ratio (25 percent), appropriate caveats about sample size and recency (20 percent), and clarity of the written comparison (25 percent).

Going deeper

Key terms

  • Date bucketing: grouping timestamped rows into fixed periods, such as quarters, using a function like DATE_TRUNC so trends can be read at a coarser grain than individual transactions.
  • Demand index: an aggregate count and average price built by bucketing sold lots over time, used here as a simple proxy for brand-level activity.
  • Pricing power: the ratio of median realized price to median pre-sale high estimate; a value above 1.0 means buyers paid over the auction house's forecast.
  • APPROX_PERCENTILE: the SQL function used to compute a median, or any percentile, efficiently over a large result set, preferred here over AVG because it is less sensitive to a handful of extreme prices.
  • Aggregate function: a SQL function such as COUNT, AVG, or APPROX_PERCENTILE that collapses many rows into a single summary value per group.
  • Null guard: a WHERE condition, such as sale_estimates_high_usd_price > 0, added specifically to keep a division from failing or producing a misleading result on missing or zero values.
  • Pagination: the practice of retrieving a large result set in pages using limit and offset, relevant once a query run through the production API returns more rows than a single request should carry.

Common pitfalls

  1. Computing pricing power without a guard on the denominator. If sale_estimates_high_usd_price is null or zero for any row, the ratio can return null or an unreliable number; always add sale_estimates_high_usd_price > 0 to the WHERE clause before dividing.
  2. Using AVG(usd_price_decimal) when a median would tell a cleaner story. A single exceptional lot can pull an average far higher than what most buyers actually paid, while APPROX_PERCENTILE is far less affected by that one row.
  3. Grouping by quarter without also grouping by designer when a query covers more than one brand, which blends two brands' activity into a single misleading quarter-by-quarter line.
  4. Treating the most recent one or two quarters in a demand index as a rising or falling trend. Recent periods are still having records added to them, so a dip or spike at the very end of the series usually reflects ingestion timing, not the market.
  5. Misspelling or under-specifying a designer filter, forgetting the % wildcards or using an unusual abbreviation, and quietly working with a much smaller result set than intended without noticing the row count is low.
  6. Assuming the sandbox SQL editor and the production API filter operators are identical without checking. The API's filters array uses explicit op values like "eq" and "like", which map to SQL logic but are not written the same way.

Additional queries to explore

A guarded, row-level version of pricing power that computes the ratio per lot before taking the median, rather than dividing two separate medians, which avoids any row where the estimate is missing or zero:

SELECT
  designer,
  APPROX_PERCENTILE(usd_price_decimal / sale_estimates_high_usd_price, 0.5) AS pricing_power_guarded
FROM all_jewels_gems_data
WHERE designer LIKE '%Van Cleef%'
  AND status = 'sold'
  AND sale_estimates_high_usd_price > 0
GROUP BY designer;

A single query comparing two brands' demand index side by side, using an IN clause and grouping by both designer and quarter, instead of running the quarterly query twice:

SELECT
  designer,
  DATE_TRUNC('quarter', sale_date) AS quarter,
  COUNT(*) AS lots_sold,
  APPROX_PERCENTILE(usd_price_decimal, 0.5) AS median_realized_price
FROM all_jewels_gems_data
WHERE designer IN ('Van Cleef & Arpels', 'Cartier')
  AND status = 'sold'
GROUP BY designer, DATE_TRUNC('quarter', sale_date)
ORDER BY designer, quarter;

The production API equivalent of a paginated pull for a high-volume designer, showing how offset moves through a result set larger than a single page:

{
  "fields": ["designer", "item_title", "sale_date", "usd_price_decimal"],
  "filters": [
    {"field": "designer", "op": "like", "value": "Van Cleef"},
    {"field": "status", "op": "eq", "value": "sold"}
  ],
  "sort": [{"field": "sale_date", "direction": "desc"}],
  "limit": 50,
  "offset": 50
}

Extension activities

  • Rebuild the quarterly demand index and pricing power ratio for a brand in the handbags or watches data instead of jewelry, and compare whether the same guard and percentile choices matter as much in that category.
  • Widen the query's implicit time window by removing any date filter and looking at the full history back to 1949, then discuss with the class how much of the row count sits in the last few years versus earlier decades.
  • Using the paginated API query above as a starting point, have students with an instructor class API key pull a full result set across multiple pages and reconcile the total row count against a plain COUNT(*) query in the sandbox.

Connections to other modules

Module 1, alternative data in finance, introduces the same documented fields at a conceptual level before this module teaches the SQL to aggregate them. Module 3, consumer and luxury economics, takes the pricing power ratio built here and pairs it with sell-through to read brand demand. Module 12, data engineering and the API, goes further into the pagination and filter-operator mechanics only sketched in the optional extension above.