subselect-presto.sql

WITH city AS (
  SELECT user_id, name, moved_on,
    row_number() over (partition by user_id order by moved_on desc) city_freshness
  FROM location
),

latest_location AS (SELECT * FROM city where city_freshness=1)

SELECT 
  "user".id,
  "user".name,
  latest_location.name as current_location,
  CASE WHEN 
    EXISTS (
      SELECT l.id 
      FROM location l
      WHERE 
        location.user_id = l.user_id AND 
        location.moved_on > l.moved_on
    )
    THEN 'True'
    ELSE 'False'
  END AS is_miami_latest_location
FROM location
JOIN "user"
  ON "user".id = location.user_id
LEFT join latest_location on location.user_id = latest_location.user_id
WHERE
  location.name = 'Miami'
;