Recording price changes from e-commerce web sites


#1
  derived_table: {
    sql:
      SELECT *
        , LAG(crawl_at_date) over(partition by product_id order by crawl_at_date) as prev_price_change_at
        , LEAD(price) over(partition by product_id order by crawl_at_date) as next_price
        , LEAD(crawl_at_date) over(partition by product_id order by crawl_at_date) as next_price_change_at
      FROM (
        select *
          , LAG(price) over(partition by product_id order by crawl_at_date) as prev_price
        from prices
      ) p
      where price <> prev_price OR prev_price is NULL
       ;;
    sql_trigger_value: select count(1) from prices ;; #record changes to the prices table
    sortkeys: ["product_id", "crawl_at_date"]
    distribution: "product_id"
  }