Module 2: Data science and SQL

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 over 100 auction houses with history to the late 1990s) 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).