Project 7

Suppose you are a Data Analyst at Lyft, a ride-sharing platform. For a project, you were given three tables:

• trips: trips information
• riders: user data
• cars: autonomous cars

1. Click on the following link to open the Online SQL Editor.

2. Click on File>Open DB and select the file lyft.db in ClassesICT

3. Let’s examine the three tables. What are the column names?

SQL

SELECT * FROM trips;

SELECT * FROM riders;

SELECT * FROM cars

4. What’s the primary key of trips? What’s the primary key of riders? What’s the primary key of cars?

Help

The primary key is id each time.

5. Try out a simple cross join between riders and cars. (for example the names of the riders and the car models) Is the result useful?

6. Suppose we want to create a Trip Log with the trips and its users. Find the columns to join between trips and riders and combine the two tables using a LEFT JOIN. Let trips be the left table.

7. Suppose we want to create a link between the trips and the cars used during those trips. Find the columns to join on and combine the trips and cars table using an INNER JOIN.

8. The new riders data are in! There are three new users this month. Stack the riders table on top of the new table named riders2.

---

Queries and Aggregates

9. What is the average cost for a trip?

10. Lyft is looking to do an email campaign for all the irregular users. Find all the riders who have used Lyft less than 500 times!

11. Calculate the number of cars that are active.

12. It’s safety recall time for cars that have been on the road for a while. Write a query that finds the two cars that have the highest trips_completed. What are the column name?