title: Counting islands over multiple partitions
body: <p>I am trying to count the number of trips (where a car is ON and moved a bit) of multiple cars over some fixed period. There is a signal, which when jumps from a value to another two possible values, it signifies that the car is ON, and when drops from those two possible values, signifies that the car is OFF, combined with the speed, this will tell me if it is a trip or not.</p>
  <p>This is a gaps-and-islands problem, I need to identify the islands and do the rest. I was able to do that with one car. <em><strong>BUT</strong></em> when I include multiple cars, the query starts to behave weird, and give me different numbers of trips each time I add a car to it. I don't really know why is that happening, and the underlying cause of it.</p>
  <p>Here is the query:</p>
  <pre><code>WITH cte_data AS
  (   SELECT car_id, time_stamp, CAST(json_extract_scalar(json_load,'$.Trip_siganl_value') AS INTEGER) AS current_value,
           LAG(CAST(json_extract_scalar(&quot;json_load&quot;,'$.Trip_siganl_value') AS INTEGER)) OVER (PARTITION BY car_id ORDER BY time_stamp) as Previousvalue,
           LEAD(CAST(json_extract_scalar(&quot;json_load&quot;,'$.Trip_siganl_value') AS INTEGER)) OVER (PARTITION BY car_id ORDER BY time_stamp) as Nextvalue, 
           CAST(json_extract_scalar(&quot;json_load&quot;,'$.speed') AS DOUBLE)  AS speed
      FROM data_base
      WHERE &quot;car_id&quot;  in ('car_id1', 'car_id2', 'car_id3')
      ORDER BY time_stamp 
  ), 
  
  cte_start AS
  (   SELECT car_id, &quot;time_stamp&quot; as starting_time_location, current_value ,
              ROW_NUMBER() OVER (PARTITION BY car_id ORDER BY time_stamp) AS island_number
      FROM cte_data
      WHERE (Previousvalue &lt; 5 OR Previousvalue IS NULL)
      AND current_value in (5,6)
  
  ),
  
  cte_end AS
  (   SELECT car_id, &quot;time_stamp&quot; as ending_time_location, current_value ,
              ROW_NUMBER() OVER ( PARTITION BY car_id ORDER BY time_stamp) AS island_number
      FROM cte_data
      WHERE (Nextvalue &lt; 5 OR Nextvalue IS NULL)
      AND current_value  in (5,6)
  
  
  ), 
  cte_final AS 
  ( 
  SELECT cte_start.car_id AS car_id, cte_start.starting_time_location AS start_date_final, cte_end.ending_time_location as end_date_final, 
  ( 
      SELECT COUNT(*)
      FROM cte_data 
      WHERE cte_data.time_stamp &gt;= cte_start.starting_time_location 
      AND cte_data.time_stamp &lt;= cte_end.ending_time_location
  
  
  ) AS island_row_count, 
  ( 
      SELECT SUM(speed)
      FROM cte_data 
      WHERE cte_data.time_stamp &gt;= cte_start.starting_time_location 
      AND cte_data.time_stamp &lt;= cte_end.ending_time_location
  
  
  ) AS island_speed_sum 
  FROM cte_start 
  INNER JOIN cte_end 
  ON cte_start.island_number = cte_end.island_number
  
  )
  
  SELECT car_id, COUNT(*)
  FROM cte_final 
  WHERE island_row_count &gt; 1
  AND island_speed_sum &gt; 0
  GROUP BY car_id
  </code></pre>
  <p>can someone tell me why it does work well with a single car, but fails with multiple?</p>
  <p>I know, I can put the query inside a Python for-loop, and feed the list of cars individually to the sql query, but that seems like a dirty solution, and I want to understand why SQL is behaving this way with multiple cars.</p>
  <p>I am using AWS Athena, So I guess it is Presto or Trino</p>
  <p>Here is a dummy input sample data for two cars:</p>
  <pre><code>car_id  time_stamp  Trip_signal_value   speed
  ----------------------------------------------
  car_id1     1           3                   0
  car_id1     2           4                   0
  car_id1     3           5                   0
  car_id1     4           5                   3
  car_id1     5           5                   5
  car_id1     6           5                   8
  car_id1     7           6                   10
  car_id1     8           6                   14
  car_id1     9           5                   10
  car_id1     10          5                   5
  car_id1     11          6                   3
  car_id1     12          6                   0
  car_id1     13          3                   0
  car_id1     14          3                   0
  car_id1     15          3                   0
  car_id1     16          3                   0
  car_id1     17          0                   0
  car_id1     18          0                   0
  car_id1     19          0                   0
  car_id1     20          2                   0
  car_id1     21          4                   0
  car_id1     22          5                   0
  car_id1     23          5                   3
  car_id1     24          5                   6
  car_id1     25          5                   5
  car_id1     26          5                   9
  car_id1     27          5                   10
  car_id1     28          5                   5
  car_id1     29          5                   4
  car_id1     30          5                   0
  car_id1     31          2                   0
  car_id1     32          1                   0
  car_id1     33          0                   0
  car_id2     7           2                   0
  car_id2     8           2                   0
  car_id2     9           5                   0
  car_id2     10          5                   2
  car_id2     11          6                   4
  car_id2     12          6                   9
  car_id2     13          5                   12
  car_id2     14          5                   13
  car_id2     15          5                   10
  car_id2     16          5                   7
  car_id2     17          6                   4
  car_id2     18          5                   0
  car_id2     19          3                   0
  car_id2     20          2                   0
  car_id2     21          2                   0
  car_id2     22          2                   0
  </code></pre>
  <p>Note that <code>Trip_singal_value</code> should be either <code>5</code> or <code>6</code> so I know that the car is ON, any less values means the car is OFF, I guess that is reflected in the SQL query.</p>
  <p>Here id what i want to typically get:</p>
  <pre><code>car_id      Number_Of_trips 
  ---------------------------
  car_id1             2
  car_id2             1
  </code></pre>
  <p>Thanks In advance</p>