- Tailored to your requirements
- Deadlines from 3 hours
- Easy Refund Policy
You’re given 2 datasets: uber driver_trips and uber_driver_info. There are 2 ways to access this data.
- Go to any question on stratascratch.com and type `select * from uber_driver_trips` and `select * from uber_driver_info` in the code editor.
- Upload the files into a database. Instructions can be found on ICA#5 and the dataset files can be found in the Files section on Canvas.
Answer the questions below to complete the data project. Provide the code in the blank space provided and copy and paste the output below the code, either as an image or table.
Week 1: Introduction to Databases and MySQL Basics
Basic SQL Syntax
- Write a basic SQL query to retrieve all data from one of the datasets
SELECT * FROM kayr_uber_driver_info |
- Write a basic SQL query to retrieve only the name and rating columns from the dataset.
SELECT name, rating FROM kayr_uber_driver_trips ; |
- Retrieve all drivers who completed more than 10 trips.
SELECT * FROM kayr_uber_driver_trips WHERE trips_completed > 10; |
- Retrieve all drivers who have an acceptance rate of less than 90%.
SELECT * FROM kayr_uber_driver_trips WHERE accept_rate < 90 ; |
Advanced SELECT Statements
- Retrieve the name and rating for all drivers with a rating greater than 4.5
SELECT name, rating FROM kayr_uber_driver_trips WHERE rating > 4.5; |
- Retrieve the name, Trips Completed, and Supply Hours for all drivers.
SELECT name, trips_completed, supply_hours FROM kayr_uber_driver_trips; |
Using LIKE, IN, and BETWEEN
- Retrieve all drivers with an acceptance rate between 80% and 90%
SELECT name, trips_completed, supply_hours FROM kayr_uber_driver_trips WHERE accept_rate BETWEEN 80 AND 90; |
- Retrieve all drivers whose name contains the letter 'A' (use the LIKE operator).
SELECT * FROM kayr_uber_driver_trips WHERE name LIKE '%A%'; |
Sorting and Limiting Results
- Retrieve the top 5 drivers with the highest number of completed trips.
SELECT name, trips_completed FROM kayr_uber_driver_trips ORDER BY trips_completed DESC LIMIT 5; |
- Retrieve the top 3 drivers with the highest rating.
SELECT name, rating FROM kayr_uber_driver_trips ORDER BY rating DESC LIMIT 3; |
Week 2: Data Types and Functions
String Functions
- Find the length of the name for each driver.
SELECT name, LENGTH(name) AS name_length FROM kayr_uber_driver_trips; |
- Concatenate the name with the string ' - Uber' and display it as a new column named uber_driver_info.
SELECT CONCAT(name, ' - Uber') AS uber_driver_info FROM kayr_uber_driver_trips; |
Week 3: Aggregate Functions, Grouping Data, Logical Statements
Aggregate Functions
- Calculate the total number of trips completed by all drivers.
SELECT SUM(trips_completed) AS total_trips FROM kayr_uber_driver_trips; |
- Calculate the average rating of all drivers.
SELECT AVG(rating) AS average_rating FROM kayr_uber_driver_trips; |
GROUP BY Clause
- Calculate the total number of trips completed by drivers with the same rating.
SELECT rating, AVG(accept_rate) AS avg_accept_rate FROM kayr_uber_driver_trips GROUP BY rating; |
- Calculate the average acceptance rate for drivers with the same rating.
SELECT rating, SUM(trips_completed) AS total_trips FROM kayr_uber_driver_trips GROUP BY rating HAVING SUM(trips_completed) > 5; |
HAVING Clause
- Find the ratings that have more than 5 total trips completed.
SELECT rating, SUM(trips_completed) AS total_trips FROM kayr_uber_driver_trips GROUP BY rating HAVING SUM(trips_completed) > 5; |
- Find the ratings with an average acceptance rate greater than 80%.
SELECT rating, AVG(accept_rate) AS avg_accept_rate FROM kayr_uber_driver_trips GROUP BY rating HAVING AVG(accept_rate) > 80; |
Case Statements
- Categorize drivers as 'High', 'Medium', or 'Low' based on their rating.
- A rating of 4.7 or greater should be labeled as “high”
- A rating of 4.0 or greater should be labeled as “medium”
- Else, the rating should be labeled as “low”
SELECT name, rating, CASE WHEN rating >= 4.7 THEN 'High' WHEN rating >= 4.0 THEN 'Medium' ELSE 'Low' END AS rating_category FROM kayr_uber_driver_trips; |
- Categorize drivers based on the number of trips completed into 'Active', 'Moderately Active', and 'Inactive'.
- If the number of trips completed is 10 or greater then label “active”
- If the number of trips completed is 5 or greater then label as “moderately active”
- Else, label as “inactive”
SELECT name, trips_completed, CASE WHEN trips_completed >= 10 THEN 'Active' WHEN trips_completed >= 5 THEN 'Moderately Active' ELSE 'Inactive' END AS activity_level FROM kayr_uber_driver_trips; |
Week 4: Joins and Subqueries
JOIN Operations
- Find the drivers with the highest rating and compare their Accept Rate with the average Accept Rate of all other drivers.
SELECT d.name, d.rating, d.accept_rate, (SELECT AVG(accept_rate) FROM kayr_uber_driver_trips WHERE rating < (SELECT MAX(rating) FROM kayr_uber_driver_trips)) AS avg_accept_rate_other_drivers FROM kayr_uber_driver_trips d WHERE rating = (SELECT MAX(rating) FROM kayr_uber_driver_trips); |
- Retrieve a list of all drivers and their Accept Rate from uber_driver_trips, and for each driver, retrieve their name and age.
SELECT d.driver_name, d.driver_age, t.accept_rate FROM kayr_uber_driver_trips t JOIN kayr_uber_driver_info d ON t.name = d.driver_name; |
Subqueries
- Retrieve drivers who have a rating higher than the average rating of all drivers.
SELECT name, rating FROM kayr_uber_driver_trips WHERE rating > (SELECT AVG(rating) FROM kayr_uber_driver_trips); |
- Retrieve drivers who have completed more trips than the average number of trips completed.
SELECT name, trips_completed FROM kayr_uber_driver_trips WHERE trips_completed > (SELECT AVG(trips_completed) FROM kayr_uber_driver_trips); |
- Retrieve the names and ages of drivers from the uber_driver_info table who have a rating higher than the average rating of all drivers.
SELECT u.driver_name, u.driver_age FROM kayr_uber_driver_info u JOIN kayr_uber_driver_trips d ON u.driver_name = d.name WHERE d.rating > (SELECT AVG(rating) FROM kayr_uber_driver_trips); |
CTEs
- Create a CTE to find the total number of hours worked by drivers with a rating above 4.7.
WITH high_rating_drivers AS ( SELECT name, supply_hours FROM uber_driver_trips WHERE rating > 4.7 ) SELECT SUM(supply_hours) AS total_hours_worked FROM high_rating_drivers; |
- Create a CTE to find the average acceptance rate of drivers who completed more than 10 trips.
WITH active_drivers AS ( SELECT name, accept_rate FROM uber_driver_trips WHERE trips_completed > 10 ) SELECT AVG(accept_rate) AS avg_accept_rate FROM active_drivers; |
- Create a CTE to find the average age of drivers (from uber_driver_info) who have completed more than 15 trips.
WITH drivers_with_more_trips AS ( SELECT d.name, u.driver_age FROM uber_driver_trips d JOIN uber_driver_info u ON d.name = u.driver_name WHERE d.trips_completed > 15 ) SELECT AVG(driver_age) AS avg_age FROM drivers_with_more_trips; |
Week 5:
Using the Uber tables you created from ICA #5, answer the questions below
Free Response Assignment
Scenario: It is going to be a huge Saturday and there will need to be many more cars on the road than last week. In order to get drivers to go online, we're assessing the following two bonus options in terms of cost:
- Option 1: $50 for each driver that is online at least 8 hours, accepts 90% of requests, completes 10 trips, and has a rating of 4.7 or better during the time frame;
- Option 2: $4/trip for all drivers who complete 12 trips, and have a 4.7 or better rating.
Using the dataset provided and given the scenario, provide answers to the questions below:
- How much would the total bonus payout be with Option 1?
- How much would the total bonus payout be with Option 2?
- How many drivers would qualify for a bonus under Option 1 but not under Option 2?
- What percentages of drivers online completed less than 10 trips, had an acceptance rate of less than 90%, and had a rating of 4.7 or higher?
- 1. How much would the total bonus payout be with Option 1?
Criteria for Option 1:
- Online at least 8 hours
- Accepts 90% of requests
- Completes 10 trips
- Has a rating of 4.7 or better
USE test1; SELECT FORMAT( COUNT(name) *50 , 2) AS pct_less_than_10_trips FROM kayr_uber_driver_trips WHERE trips_completed >= 10 AND supply_hours >= 8 AND accept_rate >= 0.9 AND rating >= 4.7; |
This query calculates the total number of drivers who meet the criteria for Option 1 and multiplies that count by $50 to get the total bonus payout.
- 2. How much would the total bonus payout be with Option 2?
Leave assignment stress behind!
Delegate your nursing or tough paper to our experts. We'll personalize your sample and ensure it's ready on short notice.
Order nowCriteria for Option 2:
- Completes 12 trips
- Has a rating of 4.7 or better
SELECT SUM(trips_completed) * 4 AS total_bonus_payout_option_2 FROM kayr_uber_driver_trips WHERE trips_completed >= 12 AND rating >= 4.7; |
This query calculates the total number of trips completed by drivers who meet the criteria for Option 2 and multiplies that by $4 to get the total bonus payout.
- 3. How many drivers would qualify for a bonus under Option 1 but not under Option 2?
SELECT COUNT(name) AS drivers_qualify_option_1_not_2 FROM kayr_uber_driver_trips WHERE supply_hours >= 8 AND accept_rate >= 0.9 AND trips_completed >= 10 AND trips_completed < 12 -- FILTER OUT OPTION 2 AND rating >= 4.7; |
EXPLANATION FOR THE ABOVE QUERY:
The drivers in Option 2 have been filtered out by the part of the query (AND) trips completed < 12), since Option 2 requires that the drivers complete more than 12 trips
This query counts the number of drivers who meet the criteria for Option 1 but do not meet the criteria for Option 2.
- 4. What percentages of drivers online completed less than 10 trips, had an acceptance rate of less than 90%, and had a rating of 4.7 or higher?
SELECT (COUNT(name) * 100.0 / (SELECT COUNT(name) FROM kayr_uber_driver_trips WHERE supply_hours > 0)) AS percentage FROM kayr_uber_driver_trips WHERE trips_completed < 10 AND accept_rate < 0.9 AND rating >= 4.7; |
This query calculates the percentage of drivers who completed less than 10 trips, had an acceptance rate of less than 90%, and had a rating of 4.7 or higher among all drivers who were online.
Offload drafts to field expert
Our writers can refine your work for better clarity, flow, and higher originality in 3+ hours.
Match with writer