lesson 79 Warehouse & reporting projects · advanced

Product validity with LEAD & half-open intervals

Define product validity using the next change date.

the idea

A versioned table stores each version with the date it became valid. LEAD(valid_from) over the same product gives the next version’s start, which is this version’s exclusive end. An as-of query then picks the version with valid_from <= date < valid_to.

Dataset: Retail

The tables the challenges use, with their first 6 rows. Under each column name: the kind of value it holds, and which table it links to.

worked example

Run these steps in order to see the idea before you write your own query. The step marked “the pattern” is what the challenges ask for. Running the example does not count toward progress.

1. 1

   List one product’s cost versions. Each row is a new cost starting on valid_from for product 20726—as-of queries pick the row whose interval contains your date.the pattern

   example.sql Run

   sql›

   SELECT product_id, cost, valid_from
   FROM product_versions
   WHERE product_id = '20726'
   ORDER BY valid_from;

2. 2

   Find products with many versions. Products at the top changed cost more than once in the period, so valid_to boundaries matter for historical pricing.

   step-2.sql Run

   sql›

   SELECT product_id, COUNT(*) AS versions
   FROM product_versions
   GROUP BY product_id
   ORDER BY versions DESC
   LIMIT 10;

3. 3

   Compute the next version with LEAD. LEAD(valid_from) over each product gives the next version’s start, which becomes this version’s exclusive end date.

   step-3.sql Run

   sql›

   SELECT
     product_id,
     cost,
     valid_from,
     LEAD(valid_from) OVER (PARTITION BY product_id ORDER BY valid_from) AS next_valid_from
   FROM product_versions
   WHERE product_id = '20726';

common mistake

Using <= on both ends makes the boundary day match two versions.