Task 1: Drivers are essential for InstantRide, and the Driver Relationship team

Task 1:
Drivers are essential for InstantRide, and the Driver Relationship team is responsible for their integration and success. The team requires all the driver detail in the system for creating a new dashboard. You need to SELECT all available data for the drivers and return back to the team.
Query all the driver data from the DRIVERS table
Task 2:
The Driver Relationship team also requests the joining dates of the drivers to create a timeline. In the table, you only need to return the joining date of the drivers. You need to only return the DRIVER_START_DATE column inside a SELECT statement for the DRIVERS table.
Task
Query the start date for each driver.
Task 3:
The Driver Relationship team requires the following details about the drivers:
All drivers with their rating in descending order
All drivers currently having a rating higher than 4
You need to return the DRIVER_ID and DRIVER_RATING couples in two separate tables.
Task 4:
The InstantRide User Satisfaction team is a core team for InstantRide, and they focus on increasing the customer satisfaction. They want to learn the travel time for each ride in the system. You need to return the USER_ID, and the TRAVEL_TIME column which is calculated using the TIMEDIFF function on the TRAVEL_END_TIME and the TRAVEL_START_TIME.
Task
Query the travel time for each ride.
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.
Calculate the total amount of discounts given.
#2
Task 1:
The InstantRide received some traffic violation tickets from the government. The Legal team of InstantRide requires the travel information of the respective drivers along with corresponding Driving License IDs to proceed further. In addition, the team wants to include the drivers without travel information in the system yet for the completion of driver list. Therefore, you need to return DRIVER_FIRST_NAME, DRIVER_LAST_NAME, DRIVER_DRIVING_LICENSE_ID, TRAVEL_START_TIME, TRAVEL_END_TIME information from the DRIVERS and TRAVELS data connected by LEFT JOIN.
Task
Query all drivers with and without travel data.
Task 2:
The InstantRide Management team considers setting up a Lost & Found inventory. In order to start the setup, the team requires the detail of users with their travel start and end times. The team wants to track potential list of users who may have forgotten their items on the cars. Therefore, you need to return USER_FIRST_NAME, USER_LAST_NAME, TRAVEL_START_TIME, TRAVEL_END_TIME information from the USERS and TRAVELS tables connected inside a JOIN statement by the USING function and USER_ID field.
Task
Query user travel times.
Task 3:
The InstantRide Finance team wants to collect the price and discount information with the driver names for each travel in the system. You need to return the TRAVEL_ID, DRIVER_FIRST_NAME, DRIVER_LAST_NAME, TRAVEL_PRICE, and TRAVEL_DISCOUNT information from the TRAVELS and DRIVERS tables combined over DRIVER_ID field with the ON keyword.
Task
Calculate each user’s price and discount information
Task 4:
The InstantRide Driver Relationship team wants to create groups for drivers according to their ratings such as 3+ or 4+. For instance, a driver with the rating 3.8 will be 3+; whereas a driver with the rating 4.2 will be 4+. You need to return a two column output with DRIVER_ID and DRIVER_RATING which has first FLOOR applied and then CONCAT with + sign for all drivers with a rating greater than 0.
Task
Create groups of drivers according to their ratings.
Task 5:
The InstantRide User Satisfaction team are looking forward to creating discounts for the users. However, the team suspects that there could be duplicate users in the system with different emails. Check for the users with their names and surnames for potential duplicates. Therefore, you need to JOIN the USERS table with USERS table and compare for equality of USER_FIRST_NAME and USER_LAST_NAME and difference in USER_ID fields.
Task
Check for duplicate driver accounts.
#3
Task 1:
The InstantRide Driver Relationship team wants to analyze the travel information of the low rated drivers. You will need to provide them with all the travel information of the drivers with the average rating lower than 4. The team wants to get in touch with the travelers and analyze their feedback. You need to run SELECT query and return all travel data from TRAVELS table filtered by the drivers who has lower rating than 4 in the DRIVERS table.
Task
Analyze travel data for the low rated drivers.
Task 2:
The InstantRide Driver Relationship team wants to check if there are any drivers with zero rides. You need to extract the DRIVER_ID, DRIVER_FIRST_NAME, DRIVER_LAST_NAME of the drivers with zero rides. You can use a subquery with DRIVER_ID compared to ALL rows in TRAVELS.
Task
Check if any drivers have given zero rides.
Task 3:
The InstantRide Finance team wants to know the average discount amounts for each car in the InstantRide. Calculate the average discount amount as monetary value for the travels where a discount is applied. You need to create a subquery over the TRAVELS table to retrieve CAR_ID and DISCOUNT_AMOUNT, calculated with 2 decimals using the ROUND function.
To calculate the DISCOUNT_AMOUNT, multiply the TRAVEL_PRICE by the TRAVEL_DISCOUNT where the TRAVEL_DISCOUNT value is not NULL. Round the result to 2 decimals.
Then you can use this subquery to get the CAR_ID and AVG of DISCOUNT_AMOUNT values, once again using the ROUND function on the average results. Group the results by the CAR_ID. Use CAR_ID and DISCOUNT_AMOUNT as column aliases and return it back to the Finance team.
Task
Calculate the average discount for each car.
Task 4:
The InstantRide Finance team also wants to analyze travels where more than the average discount rate is applied. They want to look for any correlation between higher discount amounts against other travel characteristics. You need to create a SELECT statement which is filtered with a subquery to calculate the AVG of the TRAVEL_DISCOUNT column.
Task
Find all travels where an above average discount was applied.
Task 5:
The InstantRide Management team considers creating a new team for Car Maintenance. The new team needs to find/list the cars that are used more than average with the usage count. Collect the information of all rides and consolidate over the Car IDs. You need to create a three level SQL statement. Firstly, you need to COUNT the number of rows in TRAVELS and GROUP_BY the CAR_ID field. Then you need to calculate the AVG of the data to find the average usage of the cars. Finally, you need to return CAR_ID and the TRAVELS count (as the Usages column) filtered to only values greater than the calculated average.
Task
Query all cars used more than the average.
Task 6:
The InstantRide Marketing team wants to organize an InstantRide party. The team requires first name and last name of all the users and drivers in order to create a gate-pass for their entry. You need to join USERS and DRIVERS tables using UNION and return FIRST_NAME and LAST_NAME columns.
Task
Query all drivers and users.