SQL SELECT, WHERE and ORDER BY on real taxi trips

#Why it matters

SQL SELECT, WHERE and ORDER BY are the first three clauses every SQL screening test checks, and they make up most of the queries an analyst writes in a normal week. SQL appears in 88% of the data analyst postings we analysed and 80% of data engineer postings. The clauses are easy; the job skill is knowing what each filter quietly removes and saying so.

#The concept

This lesson uses the taxi.duckdb database you built in What is SQL? Databases, tables and your first query: a trips table with every yellow cab trip from January 2021 (1,369,765 rows) and a zones lookup.

#SELECT: columns, expressions and aliases

select tpep_pickup_datetime,                         -- a column as it is
       fare_amount + tip_amount as fare_and_tip,     -- an expression with an alias
       round(100 * tip_amount / fare_amount, 1) as tip_pct
from trips;

as names the output column. The alias is optional but always worth writing: without it, the column is called something like (fare_amount + tip_amount), and BI tools and Python code downstream will choke on that.

select * returns every column. Use it to explore a small sample. Avoid it in anything saved, for two reasons: on columnar warehouses (BigQuery, Snowflake) you pay for every column read, and a saved query that silently gains a column when someone alters the table can break a report.

#WHERE: comparisons and logic

and binds tighter than or, exactly like multiplication binds tighter than addition. So

where payment_type = 1 or payment_type = 2 and trip_distance > 50

means payment_type = 1 or (payment_type = 2 and trip_distance > 50): every card trip, plus long cash trips. If you meant "long trips paid by card or cash", you need brackets. The worked example shows the difference is 934,510 rows against 106. Bracket every mix of and and or, even when you know the rule, because the next reader may not.

#ORDER BY

• Sort by several columns: order by payment_type, fare_amount desc sorts by payment type, and within each type by fare, highest first. asc (the default) and desc apply per column.

• You can sort by an alias (order by fare_and_tip desc) because order by runs after select.

• You can sort by an expression that is not selected: order by tip_amount / nullif(fare_amount, 0) desc.

#Where NULLs go when you sort

Databases disagree:

PostgreSQL treats NULL as larger than every value, which is why desc puts it on top. Write nulls first or nulls last whenever NULLs are possible and the order matters (PostgreSQL, DuckDB, Oracle, Snowflake and SQLite 3.30+ support it). MySQL and SQL Server do not; there you sort on a flag first: order by passenger_count is null, passenger_count in MySQL.

#LIMIT and OFFSET

limit 10 offset 20 skips 20 rows and returns the next 10: page 3 of a list. The trap is that the database still has to produce and throw away the skipped rows, so page 100,000 is slow. The fix, keyset pagination, remembers the last value you showed and asks for rows after it: where tpep_pickup_datetime > :last_seen order by tpep_pickup_datetime limit 10. In real code add a unique tie-breaker column (an id) to both the filter and the sort, otherwise rows with equal timestamps can be skipped or repeated.

#DISTINCT

select distinct a, b removes duplicate rows: combinations of a and b. It does not make a unique. count(distinct a) counts distinct non-NULL values of one column.

#Codes need a dictionary

payment_type = 1 means "credit card" only because the TLC data dictionary says so:

Never guess a code from the distribution. Find the dictionary, link it in your query comments, and check for codes it does not list (here: NULL).

#Worked example: big tips and the rows behind them

#The biggest card tips on long trips

select tpep_pickup_datetime, trip_distance, fare_amount, tip_amount,
       round(100 * tip_amount / fare_amount, 1) as tip_pct
from trips
where trip_distance > 20 and payment_type = 1   -- 1 = credit card
order by tip_amount desc
limit 5;

┌──────────────────────┬───────────────┬─────────────┬────────────┬─────────┐
│ tpep_pickup_datetime │ trip_distance │ fare_amount │ tip_amount │ tip_pct │
│      timestamp       │    double     │   double    │   double   │ double  │
├──────────────────────┼───────────────┼─────────────┼────────────┼─────────┤
│ 2021-01-20 11:22:05  │         427.7 │      1128.5 │    1140.44 │   101.1 │
│ 2021-01-03 11:36:52  │         267.7 │       708.0 │      369.4 │    52.2 │
│ 2021-01-12 12:57:36  │         326.1 │       879.0 │     192.61 │    21.9 │
│ 2021-01-19 11:38:47  │         260.5 │       722.0 │     149.03 │    20.6 │
│ 2021-01-18 16:58:52  │         153.1 │       418.5 │      87.31 │    20.9 │
└──────────────────────┴───────────────┴─────────────┴────────────┴─────────┘

Read the output before you report it. A 427.7-mile yellow cab ride with a tip larger than the fare is possible (an out-of-state trip with a generous rider) but rare enough to check. The rows below it, 20% tips on long trips, look like normal behaviour. In a report you would show this list and flag row one.

#Precedence changes the answer

select count(*) as unbracketed from trips
where payment_type = 1 or payment_type = 2 and trip_distance > 50;

select count(*) as bracketed from trips
where (payment_type = 1 or payment_type = 2) and trip_distance > 50;

┌─────────────┐
│ unbracketed │
│    int64    │
├─────────────┤
│      934510 │
└─────────────┘
┌───────────┐
│ bracketed │
│   int64   │
├───────────┤
│       106 │
└───────────┘

Same words, two different questions, and a 9,000-fold difference. No error message warns you.

#NULLs at the top or the bottom

select passenger_count, count(*) as trips
from trips
group by passenger_count
order by passenger_count desc
limit 3;

DuckDB:

┌─────────────────┬───────┐
│ passenger_count │ trips │
│      int64      │ int64 │
├─────────────────┼───────┤
│               8 │     2 │
│               7 │     5 │
│               6 │ 25362 │
└─────────────────┴───────┘

The same query in PostgreSQL:

 passenger_count | trips
-----------------+-------
                 | 98352
               8 |     2
               7 |     5
(3 rows)

PostgreSQL put the 98,352 NULL rows first. A "top 3 by passenger count" built in one database and moved to the other changes silently. order by passenger_count desc nulls last gives the DuckDB answer in both. In MySQL, the same issue runs the other way:

-- MySQL 8.4, a three-row table with 1, NULL and 6
select passenger_count from t order by passenger_count;
select passenger_count from t order by passenger_count is null, passenger_count;

passenger_count
NULL
1
6
passenger_count
1
6
NULL

#Paging: OFFSET gets slower as you go

With an index on tpep_pickup_datetime in PostgreSQL (\timing on in psql):

select tpep_pickup_datetime from trips order by tpep_pickup_datetime limit 10 offset 10;
select tpep_pickup_datetime from trips order by tpep_pickup_datetime limit 10 offset 1300000;
select tpep_pickup_datetime from trips
where tpep_pickup_datetime > '2021-01-30 01:19:16'      -- last value on the previous page
order by tpep_pickup_datetime limit 10;

Time: 1.106 ms
Time: 111.067 ms
Time: 1.262 ms

Page 2 took about 1 ms; page 130,001 took 111 ms because PostgreSQL walked 1.3 million index entries to skip them. The keyset version jumps straight to the right place in the index and is as fast as page 2. Your timings will differ; the ratio is the point.

#DISTINCT on one column vs several

select count(distinct payment_type) as payment_types,
       count(distinct PULocationID)  as pickup_zones
from trips;

select count(*) from (select distinct payment_type, VendorID from trips);

┌───────────────┬──────────────┐
│ payment_types │ pickup_zones │
│     int64     │    int64     │
├───────────────┼──────────────┤
│             4 │          258 │
└───────────────┴──────────────┘
┌──────────────┐
│ count_star() │
│    int64     │
├──────────────┤
│            9 │
└──────────────┘

Four payment types (1 to 4; count(distinct) ignores NULL), but nine distinct pairs of payment type and vendor. select distinct payment_type on its own returns five rows, because distinct keeps one NULL row. Codes 5 and 6 never appear in this month.

#Count what your filters hide

Before you answer any question, count the strange rows:

select count(*) filter (where fare_amount <= 0)           as zero_or_negative_fare,
       count(*) filter (where fare_amount < 0)            as negative_fare,
       count(*) filter (where trip_distance = 0)          as zero_distance,
       count(*) filter (where passenger_count is null)    as missing_passengers,
       count(*) filter (where tpep_dropoff_datetime < tpep_pickup_datetime) as dropoff_before_pickup
from trips;

┌───────────────────────┬───────────────┬───────────────┬────────────────────┬───────────────────────┐
│ zero_or_negative_fare │ negative_fare │ zero_distance │ missing_passengers │ dropoff_before_pickup │
│         int64         │     int64     │     int64     │       int64        │         int64         │
├───────────────────────┼───────────────┼───────────────┼────────────────────┼───────────────────────┤
│                  7411 │          6769 │         19952 │              98352 │                  5642 │
└───────────────────────┴───────────────┴───────────────┴────────────────────┴───────────────────────┘

count(*) filter (where ...) counts only rows that match the condition; the CASE WHEN lesson covers it properly. Who has negative fares?

select payment_type, count(*) from trips where fare_amount < 0 group by all order by 2 desc;

┌──────────────┬──────────────┐
│ payment_type │ count_star() │
│    int64     │    int64     │
├──────────────┼──────────────┤
│            4 │         3455 │
│            3 │         2187 │
│            2 │         1011 │
│         NULL │          114 │
│            1 │            2 │
└──────────────┴──────────────┘

Most negative fares are disputes (4) and no-charge trips (3): refunds and reversals, not data entry errors. That changes how you treat them. Dropping them from a revenue total overstates revenue; keeping them is correct for "net revenue". Say which one you computed.

#Common mistakes

• Mixing and and or without brackets. As shown, the query runs and returns a wrong number. Bracket every mix.

• = null. where passenger_count = null is never true, so it returns zero rows. Use is null.

• Relying on default NULL order. Code moved from MySQL to PostgreSQL flips NULLs from the top of an ascending list to the bottom. Write nulls first or nulls last.

• distinct to "fix" duplicates. If a query returns duplicate rows you did not expect, the cause is usually a join (the join fan-out lesson). distinct hides the symptom and costs a sort.

• Deep offset paging in an API. Fine for page 3, painful for page 30,000. Use keyset pagination with a unique tie-breaker.

• Filtering without counting. Every where removes rows. Count what you removed, and state whether it changes the conclusion.

#Interview angle

select tpep_pickup_datetime, DOLocationID, trip_distance, fare_amount, total_amount
from trips
where PULocationID = 132                         -- JFK Airport in the zone lookup
  and tpep_pickup_datetime >= '2021-01-01'
  and tpep_pickup_datetime <  '2021-02-01'
order by total_amount desc
limit 10;

#Practice task

On taxi.duckdb:

1. The 10 longest trips by distance that started in January 2021 and had a positive fare. Which ones look like data errors?

2. Trips from LaGuardia (zone 138) to JFK (zone 132), sorted by fare highest first, with ties broken by earliest pickup.

3. The number of distinct pickup zones used by card trips vs cash trips.

4. For each of the five checks in suspicious_rows.sql, decide whether you would exclude, keep or fix those rows when computing "average fare per trip", and write one line of reasoning each.

Done when you have the three result sets and five one-line decisions.

-- 1
select tpep_pickup_datetime, tpep_dropoff_datetime, trip_distance, fare_amount
from trips
where tpep_pickup_datetime >= '2021-01-01' and tpep_pickup_datetime < '2021-02-01'
  and fare_amount > 0
order by trip_distance desc
limit 10;

-- 2
select tpep_pickup_datetime, trip_distance, fare_amount
from trips
where PULocationID = 138 and DOLocationID = 132
order by fare_amount desc, tpep_pickup_datetime;

-- 3
select count(distinct PULocationID) filter (where payment_type = 1) as card_zones,
       count(distinct PULocationID) filter (where payment_type = 2) as cash_zones
from trips;

#Further reading

• PostgreSQL: sorting rows: nulls first, nulls last and sort expressions.

• PostgreSQL: LIMIT and OFFSET: why large offsets are inefficient, from the source.

• DuckDB: ORDER BY clause: DuckDB's NULL ordering default and how to change it.

• Use The Index, Luke: paging through results: keyset pagination explained with plans.