TUTORIAL OUTPUT =============== CREATING SCHEMA, TYPES, & TABLES -------------------------------- Tutorial Schema *************** Tutorial schema successfully created Vendor Table ************ Vendor table successfully created Payment Table ************* Payment table successfully created Taxi Table ********** Taxi table successfully created INSERTING DATA -------------- Number of records inserted into the Payment table: 1 Number of records inserted into the Vendor table: 8 Number of records inserted into the Payment table: 25 Number of records inserted into the Taxi table: 1081 RETRIEVING DATA --------------- Payment ID Payment Type Credit Type Payment Timestamp Fare Amount Surcharge MTA Tax Tip Amount Tolls Amount Total Amount ========== ============ =========== ================= =========== ========= ======= ========== ============ ============ 100 Cash 9.00 0.00 0.50 2.90 0.00 12.70 107 Credit MasterCard 1428717377000 5.00 0.50 0.50 0.00 0.00 6.30 109 Cash 1428948513000 22.50 0.50 0.50 4.75 0.00 28.55 114 Cash 1428259673000 5.50 0.00 0.50 1.89 0.00 8.19 119 1430431471000 9.50 0.00 0.50 0.00 0.00 10.30 123 No Charge 1430136649000 20.00 0.50 0.50 0.00 0.00 21.30 125 Credit Discover 1429869673000 8.50 0.50 0.50 0.00 0.00 9.80 132 Cash 1429472779000 6.50 0.50 0.50 1.55 0.00 9.35 134 Cash 1429472668000 33.50 0.50 0.50 0.00 0.00 34.80 136 Cash 1428716521000 4.00 0.50 0.50 1.00 0.00 6.30 Vendor ID Vendor Name Phone Email HQ Street HQ City HQ State HQ Zip # Employees # Cabs ========= ========================== =========== ============================= ======================== ======== ======== ====== =========== ====== 5BTC Five Boroughs Taxi Co. 4566541278 mgmt@5btc.com 9128 Lantern Street Brooklyn NY 11229 193 175 CMT Crazy Manhattan Taxi 9778896500 admin@crazymanhattantaxi.com 950 4th Road Suite 78 Brooklyn NY 11210 500 468 DDS Dependable Driver Service 8554 North Homestead St. Bronx NY 10472 200 124 NYC New York City Cabs support@nyc-taxis.com 9669 East Bayport St. Bronx NY 10453 505 500 NYMT New York Metro Taxi 4 East Jennings St. Brooklyn NY 11228 166 150 TNY Taxi New York 725 Squaw Creek St. Bronx NY 10458 315 305 VTS Vine Taxi Service 9998880001 admin@vtstaxi.com 26 Summit St. Flushing NY 11354 450 400 YCAB Yes Cab 7895444321 97 Edgemont St. Brooklyn NY 11223 445 425 UPDATING RECORDS ---------------- Updated DDS vendor information: Vendor ID Vendor Name Phone Email HQ Street HQ City HQ State HQ Zip # Employees # Cabs ========= ========================= ===== ======================== ======================== ======= ======== ====== =========== ====== DDS Dependable Driver Service management@ddstaxico.com 8554 North Homestead St. Bronx NY 10472 202 125 DELETING RECORDS ---------------- Records in the payment table (before delete): 26 Deleting record where payment_id = 189 Records in the payment table (after delete): 25 ALTER TABLE ----------- Indexes ******* Indexes added successfully Dictionary Encoding ******************* Memory usage (in bytes) for 'vendor_id' column before adding dictionary encoding: 4324 Dictionary encoding added to 'vendor_id' column properties list: [data, char4, dict] Memory usage (in bytes) for 'vendor_id' column after adding dictionary encoding: 1176 FILTERING --------- Number of null payments: 3 Number of null payments with a fare amount greater than $8.00 (with query chaining): 1 Number of records where vendor is either NYC or YCAB: 498 Number of trips with a passenger count between 1 and 3: 972 AGGREGATING, GROUPING, & HISTOGRAMS ----------------------------------- Statistics of values in the trip_distance column: Count: 1081 Min: 0.00 Mean: 2.86 Max: 21.88 Unique vendor IDs in the taxi trip table: * CMT * DDS * LYFT * NYC * UBER * VTS * YCAB Trips per vendor: CMT: 242 DDS: 106 LYFT: 3 NYC: 262 UBER: 3 VTS: 229 YCAB: 236 Passenger count groups by size: Passengers Total Trips ========== =========== 1 775 2 145 3 52 4 22 >5 87 JOINS ----- Payment information for rides having more than three passengers: Payment ID Payment Type Total Amount Passenger Count Vendor ID Trip Distance ========== ============ ============ =============== ========= ============= 136 Cash 6.30 5 NYC 2.31 148 Cash 11.30 6 NYC 1.17 176 Cash 12.36 5 NYC 1.22 Transaction, trip, and vendor information where Payment ID is not null: Transaction ID Pickup (in secs since Epoch) Trip Distance Vendor ID Vendor Name ============== ============================ ============= ========= ================== 104118718 1428716058000 1.53 UBER 104506140 1428674077000 1.40 YCAB Yes Cab 106041671 1429869346000 0.79 NYC New York City Cabs 107008976 1428880198000 1.66 NYC New York City Cabs 109329823 1430431115000 1.20 YCAB Yes Cab 111998195 1428259167000 2.10 YCAB Yes Cab 112901023 1429799817000 2.37 NYC New York City Cabs 128530951 1428672482000 1.00 YCAB Yes Cab 129891539 1428948421000 0.51 NYC New York City Cabs 132706957 1429035716000 1.02 NYC New York City Cabs 132760909 1430124124000 1.17 NYC New York City Cabs 139294057 1428813585000 4.30 YCAB Yes Cab 149211614 1427981386000 1.50 YCAB Yes Cab 155194734 1430135005000 1.08 NYC New York City Cabs 160856304 1428716062000 2.31 NYC New York City Cabs 161650444 1428965054000 3.09 NYC New York City Cabs 163836875 1428520490000 0.18 LYFT 164839979 1429471893000 1.02 NYC New York City Cabs 169677687 1430431112000 6.50 YCAB Yes Cab 170101359 1428922783000 0.99 NYC New York City Cabs 174019433 1429471890000 2.56 NYC New York City Cabs 174771463 1429471891000 2.01 NYC New York City Cabs 177106037 1430431112000 1.20 YCAB Yes Cab 178732586 1430135005000 8.39 NYC New York City Cabs 181667689 1430135005000 3.03 UBER 186246812 1429413166000 6.10 LYFT 190120416 1429413163000 1.49 LYFT 191386113 1428669361000 1.30 YCAB Yes Cab 192545327 1428403054000 1.22 NYC New York City Cabs 195905599 1428716058000 12.03 NYC New York City Cabs 197243309 1430135005000 2.00 UBER 199751611 1428808559000 0.50 YCAB Yes Cab Known vendors with no transactions and unknown vendors with transactions: Vend. Table Vendors Taxi Table Vendors Total Records =================== ================== ============= LYFT 3 UBER 3 5BTC 1 NYMT 1 TNY 1 PROJECTIONS ----------- Projection of only credit payment types: Payment ID Payment Type Credit Type Timestamp Fare Surcharge MTA Tax Tip Tolls Total ========== ============ ================ ============= ===== ========= ======= ===== ===== ===== 107 Credit MasterCard 1428717377000 5.00 0.50 0.50 0.00 0.00 6.30 125 Credit Discover 1429869673000 8.50 0.50 0.50 0.00 0.00 9.80 140 Credit Visa 28.00 0.00 0.50 0.00 0.00 28.80 156 Credit MasterCard 1428672753000 12.50 0.50 0.50 0.00 0.00 13.80 159 Credit Visa 1428674487000 7.00 0.00 0.50 0.00 0.00 7.80 161 Credit Visa 7.00 0.00 0.50 0.00 0.00 7.80 166 Credit American Express 1428808723000 17.50 0.00 0.50 0.00 0.00 18.30 187 Credit American Express 1428670181000 14.00 0.00 0.50 0.00 0.00 14.80 198 Credit MasterCard 1429472636000 9.00 0.00 0.50 0.00 0.00 9.80 199 Credit Visa 6.00 1.00 0.50 1.00 0.00 8.50 Projection of long trips taken during lunch hours: Hour of Day Vendor ID Passenger Count Trip Distance =========== ========= =============== ============= 11 CMT 1 16.70 11 NYC 1 8.39 11 NYC 5 7.26 11 VTS 1 6.33 11 VTS 2 11.43 11 YCAB 1 6.00 12 CMT 1 9.50 12 CMT 1 9.80 12 NYC 1 5.07 12 NYC 2 5.70 12 NYC 3 12.01 12 VTS 1 9.65 13 DDS 1 12.50 13 NYC 1 5.38 13 NYC 1 21.88 13 VTS 3 12.56 13 YCAB 1 7.10 13 YCAB 1 20.70 14 CMT 1 8.20 14 CMT 1 9.90 14 NYC 1 9.72 14 NYC 3 5.96 14 NYC 5 16.92 14 NYC 6 9.02 14 YCAB 1 8.60 14 YCAB 1 18.00 14 YCAB 2 17.00 UNION, INTERSECT, & EXCEPT -------------------------- Passenger statistics for each half of April: Pickup Window Range Avg. Pass. Count Avg. Trip Min. Trip Max. Trip ======================= ================ ========= ========= ========= 2015-04-01 - 2015-04-15 1.6 3.05 0.00 21.88 2015-04-16 - 2015-04-23 1.8 3.13 0.38 19.40 Latitude/Longitude pairs that were both pick-up and drop-off points: Latitude Longitude =========== ============ 40.64923477 -74.25534058 40.65737534 -73.79367828 40.71453476 -73.94219971 40.71491241 -74.00749207 40.72101593 -74.00538635 40.72580338 -73.72501373 40.75041199 -73.99066925 40.75131226 -73.97505188 40.75819016 -73.93734741 40.76081848 -73.98005676 40.76392746 -73.90199280 40.76406479 -73.96186066 40.77339172 -73.98106384 41.36613846 -73.13739014 Vendors that operate between midnight and noon: Vendor ID ========= UBER