Get the latest change in value from a streak of values I'm looking for a SQL query which returns the most recent change (date) in a time series of sensor data.

Basically I need to know when the sensor with id==i had a value change for the first time from the last streak.

The value is unique per day, the primary key is (sensor_id, date).

See SQLite fiddle to play around with it.

Data example #1, expected return [(1,'2017-01-05',0.10), (2,'2017-01-05',0.70)]:

sendor_id  date        val
---------  ----------  ----
    1      2017-01-07  0.10
    1      2017-01-06  0.10
    1      2017-01-05  0.10 &lt;==
    1      2017-01-03  0.20
    1      2017-01-02  0.20
    1      2017-01-01  0.10
    2      2017-01-07  0.70
    2      2017-01-06  0.70
    2      2017-01-05  0.70 &lt;==
    2      2017-01-02  0.20
    2      2017-01-01  0.20
    2      2016-12-31  0.70


Data example #2, expected return [(1,'2017-01-08',0.20), (2,'2017-01-05',0.70)]:

sendor_id  date        val
---------  ----------  ----
    1      2017-01-08  0.20 &lt;==
    1      2017-01-07  0.10
    1      2017-01-06  0.10
    1      2017-01-05  0.10
    1      2017-01-03  0.20
    1      2017-01-02  0.20
    1      2017-01-01  0.20
    2      2017-01-08  0.70
    2      2017-01-07  0.70
    2      2017-01-06  0.70
    2      2017-01-05  0.70 &lt;==
    2      2017-01-02  0.20
    2      2017-01-01  0.20
    2      2016-12-31  0.70


I want to use SQLite for this and I prefer just one query which can get the data.
Not sure if this is the best solution, but this does the job. The only requirement is that each sensor has a least 2 values.

select *
from sensor_data as t8
inner join (
  select t6.sensor_id, min(t6.date) as date
  from sensor_data t6
  inner join (
     select t4.sensor_id, max(t4.date) as before_last_date
      from sensor_data t4
      left join (
          select t2.sensor_id, t2.val as last_val, t2.date as last_date
          from sensor_data t2
          inner join (
              select sensor_id, max(date) as last_change
              from sensor_data
              where 1
              group by sensor_id
          ) as t1 
          ON t2.sensor_id = t1.sensor_id AND t2.date = t1.last_change
      ) as t3
      ON t3.sensor_id = t4.sensor_id
      WHERE t4.val != last_val
      GROUP BY t4.sensor_id
  ) as t5
  ON t5.sensor_id = t6.sensor_id
  WHERE t5.before_last_date &lt; t6.date
  group by t6.sensor_id
) as t7
on t7.sensor_id = t8.sensor_id and t7.date = t8.date