SQL Challenges/Product Price at a Given Date

Challenge DetailMediumReviewed 2026-04-20

Product Price at a Given Date

Find the prices of all products on 2019-08-16. Assume the price of all products before any change is 10.

Date LogicSubqueriesWindow FunctionsJoin

This challenge now meets the current minimum standard for sitemap inclusion and independent indexing.

2026-01-13Medium

Product Price at a Given Date

Find the prices of all products on 2019-08-16. Assume the price of all products before any change is 10.

Available Tables:

SQL Editor

What This Challenge Is Testing

This is a classic point-in-time lookup problem. Pricing, subscriptions, permissions, and slowly changing dimensions all require the same skill: find the latest valid record up to a cutoff date, and handle entities that still fall back to a default state.

Topic Tags

DatesSubqueriesAnalytics

Schema At A Glance

products

product_idnew_pricechange_date

Solution Approaches

Latest change on or before the snapshot date

WITH latest_changes AS (
  SELECT
    product_id,
    MAX(change_date) AS last_change_date
  FROM products
  WHERE change_date <= '2019-08-16'
  GROUP BY product_id
)
SELECT
  p.product_id,
  COALESCE(pr.new_price, 10) AS price
FROM (SELECT DISTINCT product_id FROM products) p
LEFT JOIN latest_changes lc
  ON p.product_id = lc.product_id
LEFT JOIN products pr
  ON pr.product_id = lc.product_id
 AND pr.change_date = lc.last_change_date

This is the most robust point-in-time pattern. First isolate the last valid change before the cutoff date, then join back to the source rows and fall back to the default price when no earlier change exists.

Window rank by valid changes

WITH ranked_changes AS (
  SELECT
    product_id,
    new_price,
    change_date,
    ROW_NUMBER() OVER (
      PARTITION BY product_id
      ORDER BY change_date DESC
    ) AS rn
  FROM products
  WHERE change_date <= '2019-08-16'
)
SELECT
  p.product_id,
  COALESCE(r.new_price, 10) AS price
FROM (SELECT DISTINCT product_id FROM products) p
LEFT JOIN ranked_changes r
  ON p.product_id = r.product_id
 AND r.rn = 1

The window-function version generalizes well when you later need more history columns or more complex snapshot logic. It is also easier to extend into full slowly changing dimension queries.

Common Pitfalls

A frequent bug is ignoring products with no change before the snapshot date, even though they should return the default price of 10. Another is taking the absolute latest change per product instead of the latest change on or before the requested date.

Frequently Asked Questions

Why do some products need a default price instead of a matched row?

Because the prompt says any product with no prior change should be treated as having price 10 before its first update. That means missing history is still meaningful and must be represented in the final result.

What broader SQL pattern does this map to?

It maps to point-in-time state reconstruction: asking what the world looked like as of a given date rather than just reading the current latest record.

Concept Guides

Use these guides when you want the fuller explanation behind the SQL pattern this challenge is testing.

Mastering SQL Date and Time Functions

Stop struggling with dates in SQL. Learn how to handle current time, date arithmetic, formatting, and time differences using standard SQL and SQLite syntax.

Mastering Slowly Changing Dimensions (SCD Type 2) in SQL

Data changes, but history matters. Learn how to track changes over time using the SCD Type 2 pattern to keep a perfect audit trail of your data.

Understanding SQL Window Functions: A Visual Guide

Master ROW_NUMBER, RANK, and running totals with interactive visualizations. See exactly how window functions process your data row by row.

Topic Hubs

If this problem exposed a deeper weakness, use a topic hub to move through a curated cluster instead of random article hopping.

Advanced SQL

Advanced query patterns

Use this path when you are moving beyond beginner SELECT queries into CTEs, window functions, and conditional logic.

Analytics

Analytics SQL and business metrics

A practical cluster for cohorts, funnels, customer value, and the kinds of reporting questions teams actually ask every week.

Schema

Schema design and data modeling

Useful when the hard part is not the query itself but the table design, relationships, and semi-structured data underneath it.

Practice Path

If you want the rotating practice flow, open this same question from the daily stream. If you want interview-style packaging, continue into the interview pool after this rep.

Back to Challenges Open In Daily Browse Interviews

Related Challenges

Rising TemperatureMedium

Compare each weather row with the previous day and return only the IDs where temperature increased day over day.

Last Person to Fit in the BusMedium

Use a running total to find the last passenger whose cumulative weight still stays under the bus limit.

Customers Who Bought All ProductsMedium

Return only the customers whose distinct purchased product set covers every product listed in the catalog.

SQL Challenges/Product Price at a Given Date

Challenge DetailMediumReviewed 2026-04-20

Product Price at a Given Date

Find the prices of all products on 2019-08-16. Assume the price of all products before any change is 10.

Date LogicSubqueriesWindow FunctionsJoin

This challenge now meets the current minimum standard for sitemap inclusion and independent indexing.

2026-01-13Medium

Product Price at a Given Date

Find the prices of all products on 2019-08-16. Assume the price of all products before any change is 10.

Available Tables:

SQL Editor

What This Challenge Is Testing

Topic Tags

DatesSubqueriesAnalytics

Schema At A Glance

products

product_idnew_pricechange_date

Solution Approaches

Latest change on or before the snapshot date

WITH latest_changes AS (
  SELECT
    product_id,
    MAX(change_date) AS last_change_date
  FROM products
  WHERE change_date <= '2019-08-16'
  GROUP BY product_id
)
SELECT
  p.product_id,
  COALESCE(pr.new_price, 10) AS price
FROM (SELECT DISTINCT product_id FROM products) p
LEFT JOIN latest_changes lc
  ON p.product_id = lc.product_id
LEFT JOIN products pr
  ON pr.product_id = lc.product_id
 AND pr.change_date = lc.last_change_date

Window rank by valid changes

WITH ranked_changes AS (
  SELECT
    product_id,
    new_price,
    change_date,
    ROW_NUMBER() OVER (
      PARTITION BY product_id
      ORDER BY change_date DESC
    ) AS rn
  FROM products
  WHERE change_date <= '2019-08-16'
)
SELECT
  p.product_id,
  COALESCE(r.new_price, 10) AS price
FROM (SELECT DISTINCT product_id FROM products) p
LEFT JOIN ranked_changes r
  ON p.product_id = r.product_id
 AND r.rn = 1

The window-function version generalizes well when you later need more history columns or more complex snapshot logic. It is also easier to extend into full slowly changing dimension queries.

Common Pitfalls

Frequently Asked Questions

Why do some products need a default price instead of a matched row?

What broader SQL pattern does this map to?

It maps to point-in-time state reconstruction: asking what the world looked like as of a given date rather than just reading the current latest record.

Concept Guides

Use these guides when you want the fuller explanation behind the SQL pattern this challenge is testing.

Mastering SQL Date and Time Functions

Stop struggling with dates in SQL. Learn how to handle current time, date arithmetic, formatting, and time differences using standard SQL and SQLite syntax.

Mastering Slowly Changing Dimensions (SCD Type 2) in SQL

Data changes, but history matters. Learn how to track changes over time using the SCD Type 2 pattern to keep a perfect audit trail of your data.

Understanding SQL Window Functions: A Visual Guide

Master ROW_NUMBER, RANK, and running totals with interactive visualizations. See exactly how window functions process your data row by row.

Topic Hubs

If this problem exposed a deeper weakness, use a topic hub to move through a curated cluster instead of random article hopping.

Advanced SQL

Advanced query patterns

Use this path when you are moving beyond beginner SELECT queries into CTEs, window functions, and conditional logic.

Analytics

Analytics SQL and business metrics

A practical cluster for cohorts, funnels, customer value, and the kinds of reporting questions teams actually ask every week.

Schema

Schema design and data modeling

Useful when the hard part is not the query itself but the table design, relationships, and semi-structured data underneath it.

Practice Path

If you want the rotating practice flow, open this same question from the daily stream. If you want interview-style packaging, continue into the interview pool after this rep.

Back to Challenges Open In Daily Browse Interviews

Related Challenges

Rising TemperatureMedium

Compare each weather row with the previous day and return only the IDs where temperature increased day over day.

Last Person to Fit in the BusMedium

Use a running total to find the last passenger whose cumulative weight still stays under the bus limit.

Customers Who Bought All ProductsMedium

Return only the customers whose distinct purchased product set covers every product listed in the catalog.