Scenario and Database Model: InstantRide InstantRide is the new ride sharing application in the city and it has just started its operations. With the help of the InstantRide mobile application, the users request a ride with their location. Drivers and cars are assigned to the request; and then the driver picks up the user to ride their requested location. Information for the users, drivers and cars are stored in the database as well as the travel transactions. In the USERS table, information for the users are stored with their first name, last name and email: USER LAST NAME USER ID 3001 3002 3003 3004 USER FIRST NAME jack Ryan Nursin Sarah Bobby Collins Yilmaz Price USER_EMAIL hill@xmail.com r.collins@xmail.com natak@gmail.com s.price@xmail.com b.griffin@xmail.com r.clark@xmail.com J.thomas@xmail.com n.yilmaz@xmail.com 3005 Griffin 3006 Randy Clark 3007 Thomas Jose Nursin 3008 Yilmaz USERS Table In the DRIVERS table, all the drivers in the InstantRide are stored with their name, driving license number and check and rating information: DRIVER_ID DRIVER_FIRST_NAME DRIVER_LAST_NAME DRIVER_DRIVING_LICENSE_JD DRIVER_START_DATE DRIVER_DRIVING_LICENSE_CHECKED DRIVER RATING 2001 Willie Butler 1874501 2019-09-12 2002 Justin Howard 1953853 2019-09-09 43 2003 Anthony Walker 1735487 2019-09-15 35 2004 Ece Yilmaz 1734747 2019-08-15 0 DRIVERS Table In the CARS table, all the cars in the InstantRide system are kept with the license plate, model and year: CAR ID 1001 1002 1003 CAR PLATE BB-542-AB BB-883-EE CAR MODEL TOYOTA PRIUS TESLA MODEL 3 TOYOTA AURIS MERCEDES E200 CAR YEAR 2018 2019 2019 BB-451-ZN 1004 BB-189-MM 2019 CARS Table Finally, the transactions of the rides are stored in the TRAVELS table. For each travel, start and end time with location are stored. In addition, the involved driver, car and user are listed for each drive. Price and discount information are also available in the database: TRAVEL TRAVEL IN LOCATION TRAVELICE DRIVER ID CARD USER ID TRAVEL DISCOUNT TRAVEL START TIME 2016.10.1 2015.11 TRAVEL IND TIME 2012, 13:11 201213-01 : 11 TRAVEL START LOCATION so od chure gh Street OR tood 2015-10-03:13:12 gad 2000 500 2009-10-03 23:21 15 020 TRAVELS Table You are assigned as the database administrator to collect and manage transactional data of the InstantRide operations. Your main task is to create SQL scripts to help other teams to retrieve the requested data. In the following activities, you will create the scripts, run against the database and send the result to the corresponding teams. Task 5: User Satisfaction team wants to send monthly summaries for each user. They need the following details with the user ID: The last day of the month when the users traveled most recently One week after the last day of the month when the users traveled most recently You need to return a three-column output with USER_ID, LAST_TRAVEL_MONTH and NOTIFICATION. LAST_TRAVEL_MONTH should be calculated using the MAX of the LAST_DAY of the TRAVEL_END_TIME field. Similarly, NOTIFICATION should be calculated with DATE_ADD function to add one week. Task Query monthly summaries for each user. > Task 6: The Marketing team of InstantRide wants to know that how many discounts have been offered for each ride. You need to calculate this information for each travel where a discount is applied and return two columns: TRAVEL_ID and DISCOUNT_AMOUNT. In addition, you need to return the calculation as a money value using the ROUND function to 2 decimals. Task Calculate the total amount of discounts given. >
