clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1305674 fiddles created (20240 in the last week).

CREATE TABLE locations ( location_id serial PRIMARY KEY, latitude VARCHAR (10), longitude VARCHAR (10), generated_at TIMESTAMP ); INSERT INTO locations (latitude, longitude, generated_at) VALUES ('51.123456', '-1.123456', '2020-04-27 17:00:00.000'), ('51.123456', '-1.123456', '2020-04-27 17:05:00.000'), ('51.654321', '-1.654321', '2020-04-27 17:10:00.000'), ('51.654321', '-1.654321', '2020-04-27 17:15:00.000'), ('51.654321', '-1.654321', '2020-04-27 17:20:00.000'), ('51.444555', '-1.444555', '2020-04-27 17:25:00.000'), ('51.123456', '-1.123456', '2020-04-27 17:30:00.000'), ('51.123456', '-1.123456', '2020-04-27 17:35:00.000'), ('51.123456', '-1.123456', '2020-04-27 17:40:00.000'), ('51.444555', '-1.444555', '2020-04-27 17:45:00.000'), ('51.444555', '-1.444555', '2020-04-27 17:50:00.000'), ('51.654321', '-1.654321', '2020-04-27 17:55:00.000');
12 rows affected
 hidden batch(es)


SELECT latitude, longitude, arrived_at FROM ( SELECT l.latitude, l.longitude, l.generated_at AS arrived_at, LAG (latitude) OVER w AS lagLatitude, LAG (longitude) OVER w AS lagLongitude from locations l WINDOW w as (ORDER BY generated_at) ) x WHERE latitude is distinct from lagLatitude or longitude is distinct from lagLongitude;
latitude longitude arrived_at
51.123456 -1.123456 2020-04-27 17:00:00
51.654321 -1.654321 2020-04-27 17:10:00
51.444555 -1.444555 2020-04-27 17:25:00
51.123456 -1.123456 2020-04-27 17:30:00
51.444555 -1.444555 2020-04-27 17:45:00
51.654321 -1.654321 2020-04-27 17:55:00
 hidden batch(es)


WITH base_cte AS ( SELECT latitude, longitude, generated_at AS arrived_at, LAG (latitude) OVER w AS lagLatitude, LAG (longitude) OVER w AS lagLongitude FROM locations WINDOW w AS (ORDER BY generated_at) ) SELECT latitude, longitude, arrived_at FROM base_cte WHERE latitude IS DISTINCT FROM lagLatitude or longitude IS DISTINCT FROM lagLongitude;
latitude longitude arrived_at
51.123456 -1.123456 2020-04-27 17:00:00
51.654321 -1.654321 2020-04-27 17:10:00
51.444555 -1.444555 2020-04-27 17:25:00
51.123456 -1.123456 2020-04-27 17:30:00
51.444555 -1.444555 2020-04-27 17:45:00
51.654321 -1.654321 2020-04-27 17:55:00
 hidden batch(es)


WITH grouped_cte AS ( WITH base_cte AS ( SELECT latitude, longitude, generated_at AS arrived_at, LAG (latitude) OVER w AS lagLatitude, LAG (longitude) OVER w AS lagLongitude FROM locations WINDOW w AS (ORDER BY generated_at) ) SELECT latitude, longitude, arrived_at FROM base_cte WHERE latitude IS DISTINCT FROM lagLatitude or longitude IS DISTINCT FROM lagLongitude ) SELECT latitude, longitude, arrived_at, LEAD(arrived_at, 1) OVER ( ORDER BY arrived_at ) left_at FROM grouped_cte;
latitude longitude arrived_at left_at
51.123456 -1.123456 2020-04-27 17:00:00 2020-04-27 17:10:00
51.654321 -1.654321 2020-04-27 17:10:00 2020-04-27 17:25:00
51.444555 -1.444555 2020-04-27 17:25:00 2020-04-27 17:30:00
51.123456 -1.123456 2020-04-27 17:30:00 2020-04-27 17:45:00
51.444555 -1.444555 2020-04-27 17:45:00 2020-04-27 17:55:00
51.654321 -1.654321 2020-04-27 17:55:00
 hidden batch(es)