SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE current_data ( "id" serial, "created_at" date, "value" int ); CREATE TABLE temporary_data ( "id" serial, "created_at" date, "value" int ); INSERT INTO current_data VALUES (1,'2021-01-01',100), (1,'2021-01-21',115), (1,'2021-02-11',120), (2,'2021-01-25',115), (2,'2021-02-01',125); INSERT INTO temporary_data VALUES (1,'2021-02-01',100), (1,'2021-02-10',100), (1,'2021-02-21',115), (1,'2021-02-25',115), (1,'2021-03-01',115), (1,'2021-03-11',100), (2,'2021-02-25',115), (2,'2021-03-01',115), (2,'2021-03-11',100); WITH t AS (SELECT *, row_number() over (PARTITION BY id ORDER BY created_at) as row FROM temporary_data), d AS (SELECT DISTINCT ON (id) * FROM current_data ORDER BY id, created_at DESC) SELECT t.* FROM t LEFT JOIN t t2 ON t.id = t2.id AND t.row = t2.row+1 LEFT JOIN d ON t.row = 1 AND t.id = d.id WHERE t.value <> coalesce(t2.value, d.value);