subselect-presto-create-db.sql

CREATE SCHEMA subquery;
USE subquery;

CREATE TABLE IF NOT EXISTS "user" (
   id INT,
   name VARCHAR (50)
);

CREATE TABLE IF NOT EXISTS "location" (
   id INT,
   user_id INT,
   name VARCHAR (50),
   moved_on TIMESTAMP
);

INSERT INTO "user" VALUES
  (1, 'tom'),
  (2, 'sally'),
  (3, 'ben');

INSERT INTO location VALUES
  (1, 1, 'New York', TIMESTAMP '2010-1-1 00:00:00'),
  (2, 1, 'Columbus', TIMESTAMP '2011-1-1 00:00:00'),
  (3, 1, 'Miami', TIMESTAMP '2012-1-1 00:00:00'),

  (4, 2, 'Miami', TIMESTAMP '2010-1-1 00:00:00'),
  (5, 2, 'Columbus', TIMESTAMP '2011-1-1 00:00:00'),

  (6, 3, 'Miami', TIMESTAMP '2010-1-1 00:00:00'),
  (7, 3, 'New York', TIMESTAMP '2011-1-1 00:00:00');