Skip to content

Instantly share code, notes, and snippets.

@dcrystalj

dcrystalj/subselect-presto.sql

Last active March 26, 2022 13:01
Show Gist options
  • Save dcrystalj/bf0d0b12a5a0bef1b0ffcc8bafdb1cbf to your computer and use it in GitHub Desktop.
Save dcrystalj/bf0d0b12a5a0bef1b0ffcc8bafdb1cbf to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. dcrystalj revised this gist Mar 26, 2022. 1 changed file with 0 additions and 1 deletion.
    1 change: 0 additions & 1 deletion subselect-presto.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,4 +1,3 @@

    WITH city AS (
    SELECT user_id, name, moved_on,
    row_number() over (partition by user_id order by moved_on desc) city_freshness
  2. dcrystalj created this gist Mar 26, 2022.
    31 changes: 31 additions & 0 deletions subselect-presto.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,31 @@

    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'
    ;