-- SQL TUTORIAL
-- ============

-- NOTE:  The output file for this script is generated as follows:
--
--    kisql --echoSql true --showTime false --file sql_tutorial.kisql > sql_tutorial.out
--
-- The taxi_trip_data.csv file is assumed to be in a peer directory to the one
-- containing this script.  Adjust the path needed, updating the UPLOAD FILE
-- statement in the INSERTING DATA section below.


-- CREATING TYPES & TABLES
-- -----------------------

-- Tutorial Schema
-- ***************

DROP SCHEMA IF EXISTS tutorial_sql CASCADE;

CREATE SCHEMA tutorial_sql
;


-- Vendor Table
-- ************

CREATE OR REPLACE REPLICATED TABLE tutorial_sql.vendor
(
  vendor_id VARCHAR(4) NOT NULL,
  vendor_name VARCHAR(32) NOT NULL,
  phone VARCHAR(10),
  email VARCHAR(32),
  hq_street VARCHAR(32) NOT NULL,
  hq_city VARCHAR(8) NOT NULL,
  hq_state VARCHAR(2) NOT NULL,
  hq_zip INT NOT NULL,
  num_emps INT NOT NULL,
  num_cabs INT NOT NULL,
  PRIMARY KEY (vendor_id)
)
;

-- Payment Table
-- *************

CREATE OR REPLACE TABLE tutorial_sql.payment
(
  payment_id LONG NOT NULL,
  payment_type VARCHAR(16),
  credit_type VARCHAR(16),
  payment_timestamp TYPE_TIMESTAMP,
  fare_amount DECIMAL(7,2),
  surcharge DECIMAL(7,2),
  mta_tax DECIMAL(5,2),
  tip_amount DECIMAL(7,2),
  tolls_amount DECIMAL(7,2),
  total_amount DECIMAL(7,2),
  PRIMARY KEY (payment_id)
)
;

-- Taxi Table
-- **********

CREATE OR REPLACE TABLE tutorial_sql.taxi_trip_data
(
  transaction_id LONG NOT NULL,
  payment_id LONG(SHARD_KEY) NOT NULL,
  vendor_id VARCHAR(4) NOT NULL,
  pickup_datetime TYPE_TIMESTAMP,
  dropoff_datetime TYPE_TIMESTAMP,
  passenger_count TINYINT,
  trip_distance REAL,
  pickup_longitude REAL,
  pickup_latitude REAL,
  dropoff_longitude REAL,
  dropoff_latitude REAL,
  PRIMARY KEY (transaction_id, payment_id)
)
;

-- INSERTING DATA
-- --------------

-- Use explicit column name syntax when the ordering of values for the set of
-- records doesn't match the natural ordering of all of the columns defined
-- within the Vendor table; here, num_emps and num_cabs are reversed
INSERT INTO tutorial_sql.vendor (vendor_id, vendor_name, phone, email, hq_street, hq_city, hq_state, hq_zip, num_cabs, num_emps)
VALUES
  ('VTS','Vine Taxi Service','9998880001','admin@vtstaxi.com','26 Summit St.','Flushing','NY',11354,450,400),
  ('YCAB','Yes Cab','7895444321',null,'97 Edgemont St.','Brooklyn','NY',11223,445,425),
  ('NYC','New York City Cabs',null,'support@nyc-taxis.com','9669 East Bayport St.','Bronx','NY',10453,505,500),
  ('DDS','Dependable Driver Service',null,null,'8554 North Homestead St.','Bronx','NY',10472,200,124),
  ('CMT','Crazy Manhattan Taxi','9778896500','admin@crazymanhattantaxi.com','950 4th Road Suite 78','Brooklyn','NY',11210,500,468),
  ('TNY','Taxi New York',null,null,'725 Squaw Creek St.','Bronx','NY',10458,315,305),
  ('NYMT','New York Metro Taxi',null,null,'4 East Jennings St.','Brooklyn','NY',11228,166,150),
  ('5BTC','Five Boroughs Taxi Co.','4566541278','mgmt@5btc.com','9128 Lantern Street','Brooklyn','NY',11229,193,175)
;

-- Use shorthand syntax, where each record of values matches the natural
-- ordering of all of the columns defined within the Payment table
INSERT INTO tutorial_sql.payment
VALUES
  (136,'Cash',null,'2015-04-11 01:42:01',4,0.5,0.5,1,0,6.3),
  (148,'Cash',null,'2015-04-27 08:49:41',9.5,0,0.5,1,0,11.3),
  (114,'Cash',null,'2015-04-05 18:47:53',5.5,0,0.5,1.89,0,8.19),
  (180,'Cash',null,'2015-04-13 22:57:03',6.5,0.5,0.5,1,0,8.8),
  (109,'Cash',null,'2015-04-13 18:08:33',22.5,0.5,0.5,4.75,0,28.55),
  (132,'Cash',null,'2015-04-19 19:46:19',6.5,0.5,0.5,1.55,0,9.35),
  (134,'Cash',null,'2015-04-19 19:44:28',33.5,0.5,0.5,0,0,34.8),
  (176,'Cash',null,'2015-04-07 10:52:42',9,0.5,0.5,2.06,0,12.36),
  (100,'Cash',null,null,9,0,0.5,2.9,0,12.7),
  (193,'Cash',null,null,3.5,1,0.5,1.59,0,6.89),
  (140,'Credit','Visa',null,28,0,0.5,0,0,28.8),
  (161,'Credit','Visa',null,7,0,0.5,0,0,7.8),
  (199,'Credit','Visa',null,6,1,0.5,1,0,8.5),
  (159,'Credit','Visa','2015-04-10 14:01:27',7,0,0.5,0,0,7.8),
  (156,'Credit','MasterCard','2015-04-10 13:32:33',12.5,0.5,0.5,0,0,13.8),
  (198,'Credit','MasterCard','2015-04-19 19:43:56',9,0,0.5,0,0,9.8),
  (107,'Credit','MasterCard','2015-04-11 01:56:17',5,0.5,0.5,0,0,6.3),
  (166,'Credit','American Express','2015-04-12 03:18:43',17.5,0,0.5,0,0,18.3),
  (187,'Credit','American Express','2015-04-10 12:49:41',14,0,0.5,0,0,14.8),
  (125,'Credit','Discover','2015-04-24 10:01:13',8.5,0.5,0.5,0,0,9.8),
  (119,null,null,'2015-04-30 22:04:31',9.5,0,0.5,0,0,10.3),
  (150,null,null,'2015-04-30 22:20:47',7.5,0,0.5,0,0,8.3),
  (170,'No Charge',null,'2015-04-30 22:05:02',28.6,0,0.5,0,0,28.6),
  (123,'No Charge',null,'2015-04-27 12:10:49',20,0.5,0.5,0,0,21.3),
  (181,null,null,'2015-04-27 11:51:01',6.5,0.5,0.5,0,0,7.8),
  (189,'No Charge',null,null,6.5,0,0.5,0,0,7)
;


-- Upload a CSV file to KiFS
UPLOAD FILE '../data/taxi_trip_data.csv'
INTO 'data'
;

-- Insert records from CSV file in KiFS into the Taxi table
LOAD INTO tutorial_sql.taxi_trip_data
FROM FILE PATHS 'kifs://data/taxi_trip_data.csv'
;
  
  
-- RETRIEVING DATA
-- ---------------

-- Retrieve no more than 10 records from the Payment table
SELECT TOP 10 *
FROM tutorial_sql.payment
ORDER BY payment_id
;

-- Retrieve all records from the Vendor table
SELECT *
FROM tutorial_sql.vendor
ORDER BY vendor_id
;


-- UPDATING RECORDS
-- ----------------

-- Update the e-mail of, and add two employees and one cab to, the DDS vendor
UPDATE tutorial_sql.vendor
SET
  email = 'management@ddstaxico.com',
  num_emps = num_emps + 2,
  num_cabs = num_cabs + 1
WHERE vendor_id = 'DDS'
;

-- Verify the modification was applied successfully.
SELECT *
FROM tutorial_sql.vendor
ORDER BY vendor_id
;

-- DELETING RECORDS
-- ----------------

-- Delete payment 189
DELETE
FROM tutorial_sql.payment
WHERE payment_id = 189
;

-- Verify the deletion was applied successfully.
SELECT *
FROM tutorial_sql.payment
ORDER BY payment_id
;


-- ALTER TABLE
-- -----------

-- Indexes
-- *******

-- Add column index on:
--   - payment table, fare_amount (for filter example)
ALTER TABLE tutorial_sql.payment
ADD INDEX (fare_amount)
;

-- Add column index on:
--   - taxi table, vendor_id (for left join example)
ALTER TABLE tutorial_sql.taxi_trip_data
ADD INDEX (vendor_id)
;


-- Dictionary Encoding
-- *******************

-- Add the dictionary encoding column property to the taxi table vendor ID column
ALTER TABLE tutorial_sql.taxi_trip_data
ALTER COLUMN vendor_id VARCHAR(4, DICT) NOT NULL
;


-- FILTERING & AGGREGATES
-- ----------------------

-- Select all payments with a fare amount greater than 8
SELECT payment_id, fare_amount
FROM tutorial_sql.payment
WHERE fare_amount > 8 
ORDER BY payment_id
;

-- Select trips with passenger counts between 6 and 10
SELECT pickup_datetime, dropoff_datetime, trip_distance, passenger_count
FROM tutorial_sql.taxi_trip_data
WHERE passenger_count BETWEEN 6 AND 10
ORDER BY pickup_datetime
;

-- Select the top 30 records where the pickup is between April 20th and
-- April 26th, then order them from longest trip to shortest trip.  A trip
-- description will also be returned, designating any trip of over six miles as
-- a "long trip", between three and six miles as a "medium trip", and three or
-- shorter as a "short trip".
SELECT TOP 30
  vendor_id,
  pickup_datetime,
  dropoff_datetime,
  passenger_count,
  CASE
    WHEN trip_distance > 6 THEN 'long trip'
    WHEN trip_distance > 3 THEN 'medium trip'
    ELSE 'short trip'
  END AS trip_description
FROM tutorial_sql.taxi_trip_data
WHERE pickup_datetime BETWEEN '2015-04-20 00:00:00.000' AND '2015-04-27 00:00:00.000'
ORDER BY trip_distance DESC
;

-- Select the longest, shortest, and average trip distance & passenger count for
-- each vendor whose average passenger count is higher than 1.4
SELECT
  vendor_id,
  MAX(trip_distance) max_trip,
  MIN(trip_distance) min_trip,
  ROUND(AVG(trip_distance),2) avg_trip,
  INT(AVG(passenger_count)) avg_passenger_count
FROM tutorial_sql.taxi_trip_data
GROUP BY vendor_id
HAVING AVG(passenger_count) > 1.4
ORDER BY vendor_id
;


-- SUBQUERIES
-- ----------

-- Show how tips compare between cash and credit card payments: retrieve unique
-- paid-by-cash fare & tip combinations and calculate the relationship between
-- the cash tip percentage and the average credit tip percentage, across all
-- cash fares that were at least as much as the lowest credit card fare.  The
-- tip factor will be the size of the cash tip in terms of the average credit
-- tip; e.g., "10" indicates the cash tip percentage is 10 times higher than the	
-- average credit tip
SELECT
  fare_amount,
  tip_amount,
  DECIMAL
  (
    (tip_amount / fare_amount) * 100 /
    (
      SELECT AVG(tip_amount / fare_amount) * 100 as avg_credit_tip_pct
      FROM tutorial_sql.payment
      WHERE payment_type = 'Credit'
    )
  ) as tip_factor_cash_vs_credit_pct
FROM
(
  SELECT DISTINCT
    fare_amount,
    tip_amount
  FROM tutorial_sql.payment
  WHERE payment_type = 'Cash'
) cash_fare_tip
WHERE fare_amount >=
(
  SELECT MIN(fare_amount)
  FROM tutorial_sql.payment
  WHERE payment_type = 'Credit'
)
ORDER BY fare_amount
;


-- CTEs & WITH
-- -----------

-- Retrieve the set of cash payments that fall within the timestamp range of
-- recorded credit payments
WITH credit_pay_ts_min_max (min_pay_ts, max_pay_ts) AS
(
  SELECT
    MIN(payment_timestamp) AS min_pay_ts,
    MAX(payment_timestamp) AS max_pay_ts
  FROM tutorial_sql.payment
  WHERE payment_type = 'Credit'
)
SELECT
  payment_id,
  payment_timestamp,
  total_amount
FROM tutorial_sql.payment
WHERE
  payment_type = 'Cash' AND
  payment_timestamp BETWEEN
    (SELECT min_pay_ts FROM credit_pay_ts_min_max) AND
    (SELECT max_pay_ts FROM credit_pay_ts_min_max)
ORDER BY payment_timestamp
;


-- JOINS
-- -----

-- Join Example 1 (Inner Join)
-- Retrieve payment information for rides having more than three passengers
SELECT
  t.payment_id,
  payment_type,
  total_amount,
  passenger_count,
  vendor_id,
  trip_distance
FROM tutorial_sql.taxi_trip_data t
INNER JOIN tutorial_sql.payment p ON t.payment_id = p.payment_id
WHERE
  passenger_count > 3
ORDER BY payment_id
;

-- Join Example 2 (Left Join)
-- Retrieve cab ride transactions and the full name of the associated vendor (if
-- available--blank if vendor name is unknown) for transactions with associated
-- payment data, sorting by increasing values of transaction ID.
SELECT
  transaction_id,
  pickup_datetime,
  trip_distance,
  t.vendor_id,
  vendor_name
FROM tutorial_sql.taxi_trip_data t
LEFT JOIN tutorial_sql.vendor v ON t.vendor_id = v.vendor_id
WHERE payment_id != 0
ORDER BY transaction_id
;

-- Full outer joins may require both tables to be replicated. Set
-- merges like Union Distinct, Intersect, and Except need to use replicated
-- tables to ensure the correct results. Create a replicated copy of the Taxi
-- table and copy the records from the non-replicated table to the replicated
-- one
CREATE OR REPLACE REPLICATED TABLE tutorial_sql.taxi_trip_data_replicated (
  transaction_id LONG NOT NULL,
  payment_id LONG NOT NULL,
  vendor_id VARCHAR(4) NOT NULL,
  pickup_datetime TYPE_TIMESTAMP,
  dropoff_datetime TYPE_TIMESTAMP,
  passenger_count DECIMAL(2),
  trip_distance FLOAT,
  pickup_longitude FLOAT,
  pickup_latitude FLOAT,
  dropoff_longitude FLOAT,
  dropoff_latitude FLOAT
)
;

INSERT INTO tutorial_sql.taxi_trip_data_replicated
SELECT *
FROM tutorial_sql.taxi_trip_data
;

-- Join Example 3 (Full Outer Join)
-- Retrieve the vendor IDs of known vendors with no recorded cab ride
-- transactions, as well as the vendor ID and number of transactions for unknown
-- vendors with recorded cab ride transactions
SELECT
  v.vendor_id vend_table_vendors,
  t.vendor_id taxi_table_vendors,
  COUNT(*) as total_records
FROM tutorial_sql.taxi_trip_data_replicated t
FULL OUTER JOIN tutorial_sql.vendor v ON v.vendor_id = t.vendor_id
WHERE
  v.vendor_id IS null OR
  t.vendor_id IS null
GROUP BY
  v.vendor_id,
  t.vendor_id
ORDER BY 1, 2
;


-- CTAs
-- ----

-- Create a memory-only table containing all payments by credit card
CREATE OR REPLACE TEMP TABLE tutorial_sql.credit_payment AS (
  SELECT *
  FROM tutorial_sql.payment
  WHERE payment_type = 'Credit'
)
;

-- Verify the table was created successfully
SELECT *
FROM tutorial_sql.credit_payment
ORDER BY payment_id
;

-- Create a persisted table with cab ride transactions greater than 5 miles
-- whose trip started during lunch hours
CREATE OR REPLACE TABLE tutorial_sql.lunch_time_rides AS (
  SELECT
    HOUR(pickup_datetime) hour_of_day,
    vendor_id,
    passenger_count,
    trip_distance
  FROM tutorial_sql.taxi_trip_data
  WHERE
    HOUR(pickup_datetime) BETWEEN '11' AND '14' AND
    trip_distance > 5
)
;

-- Verify the table was created successfully
SELECT *
FROM tutorial_sql.lunch_time_rides
ORDER BY 1, 2, 3, 4
;


-- UNION, INTERSECT, & EXCEPT
-- --------------------------

-- Set Union Example (Union All)
-- Calculate the average number of passengers, as well as the shortest, average,
-- and longest trips for all trips in each of the two time periods--from April
-- 1st through the 15th, 2015 and from April 16th through the 23rd, 2015--and
-- return those two sets of statistics in a single result set
SELECT
  '2015-04-01 - 2015-04-15' pickup_window_range,
  INT(AVG(passenger_count)) avg_pass_count,
  ROUND(AVG(trip_distance),2) avg_trip,
  MIN(trip_distance) min_trip,
  MAX(trip_distance) max_trip
FROM tutorial_sql.taxi_trip_data
WHERE pickup_datetime BETWEEN '2015-04-01' AND '2015-04-15 23:59:59.999'
UNION ALL
SELECT
  '2015-04-16 - 2015-04-23',
  INT(AVG(passenger_count)),
  ROUND(AVG(trip_distance),2),
  MIN(trip_distance),
  MAX(trip_distance)
FROM tutorial_sql.taxi_trip_data
WHERE pickup_datetime BETWEEN '2015-04-16' AND '2015-04-23 23:59:59.999'
;

-- Set Intersection Example
-- Retrieve locations (as lat/lon pairs) that were both pick-up and drop-off
-- points
SELECT
  pickup_latitude AS latitude,
  pickup_longitude AS longitude
FROM tutorial_sql.taxi_trip_data_replicated
WHERE
  pickup_latitude <> 0 AND
  pickup_longitude <> 0
INTERSECT
SELECT
  dropoff_latitude,
  dropoff_longitude
FROM tutorial_sql.taxi_trip_data_replicated
ORDER BY latitude, longitude
;

-- Set Subtraction Example (Except)
-- Show vendors that operate before noon, but not after noon:  retrieve the
-- unique list of IDs of vendors who provided cab rides between midnight and
-- noon, and remove from that list the IDs of any vendors who provided cab rides
-- between noon and midnight
SELECT vendor_id
FROM tutorial_sql.taxi_trip_data_replicated
WHERE HOUR(pickup_datetime) BETWEEN 0 AND 11
EXCEPT
SELECT vendor_id
FROM tutorial_sql.taxi_trip_data_replicated
WHERE HOUR(pickup_datetime) BETWEEN 12 AND 23
;


-- TRUNCATING DATA
-- ---------------

-- Remove all records from the given table
TRUNCATE TABLE tutorial_sql.credit_payment
;

-- Verify the table was truncated successfully
SELECT *
FROM tutorial_sql.credit_payment
;