9.4. Filtering

We’ve seen how to look only at certain columns of the table but it is often useful to only look at certain rows in a table. For example, we could want to look only at the bike trips which are at least a certain number of minutes. Let’s say you’re only interested in bike trips of 60 minutes or more:

%%sql

SELECT
  member_type, start_date, duration
FROM
  trip_data
WHERE
  duration >= 3600
LIMIT
  10
 * sqlite:///bikeshare.db
Done.
member_type start_date duration
Casual 2011-01-01 01:48:57.000000 40181
Casual 2011-01-01 09:47:33.000000 5009
Casual 2011-01-01 09:53:23.000000 4642
Casual 2011-01-01 09:53:38.000000 4645
Casual 2011-01-01 09:54:06.000000 4628
Casual 2011-01-01 10:16:55.000000 10474
Casual 2011-01-01 10:20:15.000000 10279
Casual 2011-01-01 10:20:42.000000 10250
Casual 2011-01-01 10:34:49.000000 5744
Casual 2011-01-01 10:37:04.000000 5611

It’s also possible to filter by multiple criteria. For example to look at only bike trips which are 60 minutes or more and only the subscriber type of Member:

%%sql

SELECT
  member_type, start_date, duration
FROM
  trip_data
WHERE
  duration >= 3600
AND
  member_type = "Member"
LIMIT
  10
 * sqlite:///bikeshare.db
Done.
member_type start_date duration
Member 2011-01-02 11:14:50.000000 4642
Member 2011-01-02 14:45:35.000000 7173
Member 2011-01-03 13:37:39.000000 3989
Member 2011-01-03 15:43:35.000000 10571
Member 2011-01-03 19:50:54.000000 7412
Member 2011-01-04 11:41:54.000000 7288
Member 2011-01-04 13:40:25.000000 29436
Member 2011-01-04 14:59:35.000000 7053
Member 2011-01-04 17:29:29.000000 11325
Member 2011-01-04 18:21:42.000000 4341

9.4.1. Exercise 1

Figure out how to get all the trips on the bike with id of W01274 and only include rides which are shorter than 15 minutes.

index duration start_date end_date start_station end_station bike_number member_type
1217513 387 2011-12-28 12:23:41.000000 2011-12-28 12:30:09.000000 31209 31108 W01274 Member
1218022 389 2011-12-28 16:12:29.000000 2011-12-28 16:18:58.000000 31619 31623 W01274 Member
1218218 303 2011-12-28 17:14:02.000000 2011-12-28 17:19:05.000000 31623 31618 W01274 Member
1218755 287 2011-12-28 20:19:32.000000 2011-12-28 20:24:20.000000 31105 31202 W01274 Member
1223563 430 2011-12-30 17:29:05.000000 2011-12-30 17:36:15.000000 31401 31107 W01274 Member
1224505 223 2011-12-31 08:51:24.000000 2011-12-31 08:55:08.000000 31107 31602 W01274 Member
1224651 422 2011-12-31 10:25:15.000000 2011-12-31 10:32:18.000000 31602 31104 W01274 Member

Lesson Feedback

    During this lesson I was primarily in my...
  • Comfort Zone
  • Learning Zone
  • Panic Zone
    Completing this lesson took...
  • Very little time
  • A reasonable amount of time
  • More time than is reasonable
    Based on my own interests and needs, the things taught in this lesson...
  • Don't seem worth learning
  • May be worth learning
  • Are definitely worth learning
    For me to master the things taught in this lesson feels...
  • Definitely within reach
  • Within reach if I try my hardest
  • Out of reach no matter how hard I try
Next Section - 9.5. Sorting