Plan overnight bike rebalancing

What you will be able to do

Turn a month of real bike-share trips into a table for a night crew. It answers one question: which 20 stations should get bikes before tomorrow’s morning peak? You will find the export’s defects and count every row that you set aside. Then you test a simple ranking against “same as yesterday” on a week that you kept apart.

Case studyPortfolio piece

Dataset

Bike Share Toronto Ridership Data, June 2025 (City of Toronto)

Licence

Open Government Licence – Toronto

Time

About 2.5 hours

Runs in

Colab, Kaggle or VS Code

Skills used

• Profiling a real export
• Quarantine with reasons
• LAG per partition
• A complete grid for window functions
• Features from earlier days only
• Time split and one final test
• Validation rules

Open in ColabOpen in KaggleView on GitHub

Colab and Kaggle open your own copy. To use VS Code, download the notebook from GitHub. Stuck? See the solution notebook.

The notebook runs every step of this page, with the same code and the same output, plus four tasks with checks. Its setup cells install pandas and download the trips (28 MB), so it needs no local setup. It runs in about one minute.

Before you startWhat to know and to have readyAbout 150 minutes

What you need to know

This case study uses the whole course. These lessons matter most:

• Window functions with PARTITION BY, LAG and ROW_NUMBER, ties, and half-open date ranges, from Time and ranking.
• Profiling before you change anything, from Profile before changing, and quarantine with a reason, from Handle messy values.
• The prediction time and features from the past only, from Features and target timing.
• Time splits, from Choose a split, and rules as queries that must return no rows, from Validation rules.

What you need on your computer (only for the local route; the notebook needs none of it)

• Python 3.12 or later with pandas 3.0.6, as in Transform with Python, and matplotlib for the charts. The ticket-data project’s virtual environment works; this case study does not change its files.
• The notebook, downloaded from the link above, in VS Code or JupyterLab. Its setup cell makes a new folder bike-rebalancing and downloads the data into it. To start again, delete that folder.

Tested on macOS with Python 3.14.6 (SQLite 3.50.4) and pandas 3.0.6, in Jupyter from a fresh start. Windows and Linux are not tested.

The situation

Leila Haddad leads operations for a city bike-share system. During a working morning, people ride from homes to offices. Stations near homes empty, and stations near offices fill up. A commuter who finds an empty station walks, or takes the bus, and may not try the bikes again.

So every night at 22:00, Leila sends a crew with vans. They take bikes from full stations and leave them at stations that will empty in the morning. This is called rebalancing. The crew can restock about 20 stations a night, out of more than 900. The choice of those 20 is the decision.

Today Leila picks “the stations that emptied this morning”. She asks her analyst, Tomasz Nowak: “Can you give me a better list every evening? And can you prove that it is better?”

The trips in this case study are real: one month of Bike Share Toronto. Leila, Tomasz and the van crew are made up. The system’s real rebalancing plans are not public.

What you will build

You will build a station table: one row for each station and working day, with the target and a few features. The target is the number of bikes the station loses between 7:00 and 10:00. The features are values known at 22:00 the evening before. From the table you make a ranking, and the top 20 stations become the night plan.

Before that, the data needs work, and that work is the main skill of this case study. The export has a column that is wrong in every row. Some trips have no end, and some last 0 seconds. About 6% of the trips start after a move that no rider made. The steps come from the whole course. You profile the data as in Profile before changing, and quarantine rows as in Handle messy values. Window functions come from Time and ranking. Target timing and splits come from Features and target timing and Choose a split, and the checks from Validation rules.

The data

The data is the June 2025 file of Bike Share Toronto Ridership Data, published by the City of Toronto for the Toronto Parking Authority. It has every rental of the month: where and when it started, where and when it ended, and which bike it used.

Source and licence

Bike Share Toronto Ridership Data, City of Toronto Open Data, open.toronto.ca, file bikeshare_2025_06.csv from bikeshare-ridership-2025.zip, downloaded on 2026-10-07. Contains information licensed under the Open Government Licence – Toronto. Nextia Learning changed the file from the cp1252 encoding to UTF-8 and its line ends from CRLF to LF, and compressed it with gzip. Nothing else changed.

Tomasz reads the file into pandas and copies it into a SQLite database, bikes.db. He reads every column as text (dtype=str) and keeps empty values as empty text (keep_default_na=False). This way, nothing changes before he has looked at it, and an empty value stays visible as '':

import sqlite3

raw = pd.read_csv("data/bike_share_toronto_2025-06.csv.gz", dtype=str, keep_default_na=False)
print(raw.shape)
print(raw.iloc[0].to_string())

con = sqlite3.connect("bikes.db")
raw.to_sql("trips_raw", con, if_exists="replace", index=False)

The output:

(980624, 11)
Trip_Id                            36767099
Trip_Duration                          6946
Start_Station_Id                       7569
Start_Time              2025-06-01 00:01:56
Start_Station_Name    Toronto Inukshuk Park
End_Station_Id                         7753
End_Time                2025-06-01 01:57:42
End_Station_Name      Toronto Inukshuk Park
Bike_Id                                5096
User_Type                            Casual
Bike_Model                           ICONIC

What this tells us. The file has 980,624 rows and 11 columns. One row is one rental. Look closely at the first row: it starts at station 7569 and ends at station 7753, but both names are Toronto Inukshuk Park. Two different stations should not have the same name. Keep that in mind.

From here on, the queries run in SQLite, as in the rest of the course. Two small helpers print a result as a table with its row count, as q.py does, or return a single value:

def sql(query, db=None, limit=20):
    """Run a query (on bikes.db, or on `db`) and print the result as a table, with its row count."""
    cursor = (db or con).execute(query)
    rows = cursor.fetchall()
    table = pd.DataFrame(rows[:limit], columns=[d[0] for d in cursor.description], dtype=object)
    print(table.where(table.notna(), "NULL").to_string(index=False))  # show NULL as NULL
    more = f", showing {limit}" if len(rows) > limit else ""
    print(f"({len(rows)} row{'s' if len(rows) != 1 else ''}{more})")


def scalar(query):
    """Run a query that returns one value, and return that value."""
    return con.execute(query).fetchone()[0]

The data dictionary

These are the columns that the case study uses, in plain words. The last three are in the file, but not used.

Column	What it means	Use
Trip_Id	The trip’s number	The key; breaks ties in a bike’s order
Trip_Duration	Length of the trip in seconds	Quality rules
Start_Station_Id, Start_Station_Name	Where the trip started	Departures; the station names
Start_Time	When it started, YYYY-MM-DD HH:MM:SS, local time	Departures; a bike’s order
End_Station_Id, End_Time	Where and when it ended	Arrivals; moves without a rider
End_Station_Name	The end station’s name	Not used: it is wrong (see below)
Bike_Id	The bike’s number	A bike’s trips, in order
User_Type, Bike_Model	Member or casual rider; the bike’s model	Not used

First checks

Tomasz starts with the size: is Trip_Id unique, what dates does the file cover, and how many stations and bikes are there?

sql("""
SELECT COUNT(*) AS trips,
       COUNT(DISTINCT Trip_Id) AS trip_ids,
       MIN(Start_Time) AS first_start,
       MAX(Start_Time) AS last_start,
       COUNT(DISTINCT Start_Station_Id) AS stations,
       COUNT(DISTINCT Bike_Id) AS bikes
FROM trips_raw
""")

The output:

 trips trip_ids         first_start          last_start stations bikes
980624   980624 2025-06-01 00:00:15 2025-06-30 23:59:53      916  8874
(1 row)

What this tells us. Each trip has its own ID, so Trip_Id is the key. The file covers the whole of June, from just after midnight on 1 June to just before midnight on 30 June. It has 916 stations and 8,874 bikes. Note that the file is selected by start time. A trip that started on 31 May and ended on 1 June is not in it.

The encoding

A computer stores text as bytes, and an encoding is the table that turns bytes into characters. The city publishes this file in cp1252, an old Windows encoding. Most tools today expect UTF-8. The two agree on plain English letters, so a mistake shows only in the few names with other characters. Tomasz finds those names and shows what each one looks like with the wrong encoding. GLOB matches text against a pattern; [^ -~] is one character that is not between the space and ~, the range of plain ASCII letters, digits and signs:

names = pd.read_sql("""
SELECT DISTINCT Start_Station_Name AS name FROM trips_raw
WHERE Start_Station_Name GLOB '*[^ -~]*'   -- a character outside plain ASCII
""", con)["name"]
for name in names:
    print(name, " | read as cp1252:", name.encode("utf-8").decode("cp1252"))

city_bytes = "25 York St \u2013 Union Station".encode("cp1252")  # how the city's file stores the dash
try:
    city_bytes.decode("utf-8")
except UnicodeDecodeError as error:
    print("The city's file read as UTF-8:", error)

The output:

25 York St – Union Station South  | read as cp1252: 25 York St – Union Station South
Lundy Ave / Étienne Brûlé Park  | read as cp1252: Lundy Ave / Étienne Brûlé Park
The city's file read as UTF-8: 'utf-8' codec can't decode byte 0x96 in position 11: invalid start byte

What this tells us. Only two station names have characters outside plain English letters: a dash (–) and the letters É, û and é. The wrong encoding fails in one direction and is quiet in the other. The city’s cp1252 file read as UTF-8 stops with an error, which is the good case: you notice. Our UTF-8 copy read as cp1252 gives – and Étienne with no error, and the wrong names go into every report. If you download a fresh file from the city, read it with encoding="cp1252".

Empty values

The case study needs to know where each trip ended. Tomasz counts the empty values in the columns that matter:

sql("""
SELECT SUM(End_Station_Id = '') AS no_end_station,
       SUM(End_Time = '') AS no_end_time,
       SUM(End_Station_Id = '' AND End_Time = '') AS both,
       SUM(Bike_Id = '') AS no_bike,
       SUM(Start_Station_Id = '') AS no_start_station
FROM trips_raw
""")

The output:

no_end_station no_end_time both no_bike no_start_station
           830         130  106       0                0
(1 row)

What this tells us. 830 trips have no end station and 130 have no end time; 106 have neither. That is about 1 trip in 1,000. For those trips we cannot say where the bike arrived, so they cannot count as arrivals. Every trip has a bike and a start station.

The end-station names

The first row showed two stations with one name. Tomasz checks how often the end name equals the start name, and how often the end ID equals the start ID:

sql("""
SELECT COUNT(*) AS trips,
       SUM(End_Station_Name = Start_Station_Name) AS same_name,
       SUM(End_Station_Id = Start_Station_Id) AS same_station_id
FROM trips_raw
""")
sql("""
SELECT Start_Station_Id, End_Station_Id, Start_Station_Name, End_Station_Name
FROM trips_raw
WHERE End_Station_Id <> Start_Station_Id
LIMIT 3
""")

The output:

 trips same_name same_station_id
980624    980624           42604
(1 row)
Start_Station_Id End_Station_Id              Start_Station_Name                End_Station_Name
            7569           7753           Toronto Inukshuk Park           Toronto Inukshuk Park
            7043           7303 Queens Quay W / Lower Simcoe St Queens Quay W / Lower Simcoe St
            7007           7078           College St / Huron St           College St / Huron St
(3 rows)

What this tells us. End_Station_Name equals Start_Station_Name in every one of the 980,624 rows. But only 42,604 trips return to the station where they started. So the end-name column is a copy of the start name: a defect in the export. The end IDs are not affected, because they point to real, different stations.

This is a quiet mistake of the kind that this course is about. A report of “the busiest end stations” by name would run without an error and show the busiest start stations. The fix is to never use End_Station_Name. Each name comes from the start side, where each ID has its own name.

Your turn: count the trips with a wrong end name

How many trips end at a known station that is not the start station, yet carry the start’s name as End_Station_Name? (In the notebook, put the number in different_station_same_name.)

different_station_same_name = scalar("""
SELECT COUNT(*)
FROM trips_raw
WHERE End_Station_Id <> ''
  AND End_Station_Id <> Start_Station_Id
  AND End_Station_Name = Start_Station_Name
""")
print(different_station_same_name)

937190

Take all 980,624 trips. Leave out the 42,604 that return to their start and the 830 without an end station. The other 937,190 trips carry a wrong end name.

One name per station

The names come from the start side, so Tomasz builds a stations table there. He checks that each ID has one name and each name one ID. Then he looks for end IDs that never appear as a start, because those would get no name:

con.executescript("""
DROP TABLE IF EXISTS stations;
CREATE TABLE stations AS
SELECT Start_Station_Id AS station_id, MIN(Start_Station_Name) AS name
FROM trips_raw
GROUP BY Start_Station_Id;
""")
sql("""
SELECT (SELECT COUNT(*) FROM stations) AS stations,
       (SELECT COUNT(*) FROM (SELECT Start_Station_Id FROM trips_raw
            GROUP BY 1 HAVING COUNT(DISTINCT Start_Station_Name) > 1)) AS ids_with_two_names,
       (SELECT COUNT(*) FROM (SELECT Start_Station_Name FROM trips_raw
            GROUP BY 1 HAVING COUNT(DISTINCT Start_Station_Id) > 1)) AS names_with_two_ids
""")
sql("""
SELECT End_Station_Id, COUNT(*) AS trips
FROM trips_raw
WHERE End_Station_Id <> '' AND End_Station_Id NOT IN (SELECT station_id FROM stations)
GROUP BY End_Station_Id
""")

The output:

stations ids_with_two_names names_with_two_ids
     916                  0                  0
(1 row)
End_Station_Id trips
          7638     2
          7690     5
          7793     1
          8065     2
(4 rows)

What this tells us. On the start side, the IDs and names match one to one, so the stations table has one row per station: 916 rows. Four end IDs never appear as a start in June. Together they have 10 trips, probably at stations that opened or closed during the month. They are too few to change any result, so we note them and move on.

Durations

A rental has a length. Tomasz counts trips of 0 seconds, under one minute and over 24 hours. He also checks that the duration equals the end time minus the start time, with 2 seconds of tolerance:

sql("""
SELECT SUM(CAST(Trip_Duration AS INTEGER) = 0) AS zero_seconds,
       SUM(CAST(Trip_Duration AS INTEGER) < 60) AS under_60_s,
       SUM(CAST(Trip_Duration AS INTEGER) > 86400) AS over_24_h,
       SUM(End_Time <> '' AND ABS((julianday(End_Time) - julianday(Start_Time)) * 86400
           - CAST(Trip_Duration AS INTEGER)) > 2) AS duration_not_end_minus_start
FROM trips_raw
""")

The output:

zero_seconds under_60_s over_24_h duration_not_end_minus_start
         779       2426         5                            0
(1 row)

What this tells us. The duration agrees with the two times in every row, so the times are consistent with each other. The problem is the trips themselves. 779 trips last 0 seconds: a bike cannot go anywhere in no time. 2,426 last under a minute, and 5 last more than a day, which is a lost or badly returned bike, not a ride.

Clean the trips

A typed table

The raw table has text in every column. Tomasz makes a typed table trips: the ID and duration become integers, and NULLIF turns '' into NULL. NULL means “unknown” in SQL, so COUNT(end_id) and IS NULL now work as the course taught. He keeps the names out of this table; they live in stations.

con.executescript("""
DROP TABLE IF EXISTS trips;
CREATE TABLE trips AS
SELECT CAST(Trip_Id AS INTEGER) AS trip_id,
       CAST(Trip_Duration AS INTEGER) AS duration_s,
       Start_Station_Id AS start_id,
       Start_Time AS start_time,
       NULLIF(End_Station_Id, '') AS end_id,     -- '' becomes NULL: unknown
       NULLIF(End_Time, '') AS end_time,
       Bike_Id AS bike_id,
       User_Type AS user_type
FROM trips_raw;
""")
sql("SELECT COUNT(*) AS trips, COUNT(end_id) AS with_end_station, COUNT(end_time) AS with_end_time FROM trips")

The output:

 trips with_end_station with_end_time
980624           979794        980494
(1 row)

What this tells us. No row was lost: 980,624 trips. 979,794 have an end station and 980,494 an end time, which matches the 830 and 130 empty values above.

Quarantine with a reason

Not every trip can count as a rider trip. Tomasz does not delete the bad ones. He gives each one a reason, as in Handle messy values, and moves it to a quarantine table. The rules run in a fixed order, and the first one that matches wins, so each trip gets exactly one reason:

1. No end time, then no end station: we cannot place the arrival.
2. Over 24 hours: a lost bike, not a ride.
3. False start: the bike went back to the same station within 2 minutes. A rider took it, changed their mind or found a fault, and docked it again.
4. Under 60 seconds to another station: too fast to be a real ride.

con.executescript("""
DROP TABLE IF EXISTS checked;
CREATE TABLE checked AS
SELECT *,
       CASE
           WHEN end_time IS NULL THEN 'no_end_time'
           WHEN end_id IS NULL THEN 'no_end_station'
           WHEN duration_s > 86400 THEN 'over_24_hours'
           WHEN end_id = start_id AND duration_s < 120 THEN 'false_start'
           WHEN duration_s < 60 THEN 'under_60_seconds'
       END AS reason
FROM trips;
DROP TABLE IF EXISTS clean;
CREATE TABLE clean AS SELECT * FROM checked WHERE reason IS NULL;
DROP TABLE IF EXISTS quarantine;
CREATE TABLE quarantine AS SELECT * FROM checked WHERE reason IS NOT NULL;
""")
sql("SELECT COALESCE(reason, '(clean)') AS reason, COUNT(*) AS trips FROM checked GROUP BY reason ORDER BY trips DESC")
print("clean + quarantine =", scalar("SELECT COUNT(*) FROM clean") + scalar("SELECT COUNT(*) FROM quarantine"))

The output:

          reason  trips
         (clean) 974870
     false_start   3665
under_60_seconds   1233
  no_end_station    724
     no_end_time    130
   over_24_hours      2
(6 rows)
clean + quarantine = 980624

What this tells us. 974,870 trips are clean. 5,754 trips (0.6%) are in quarantine, and false starts are the largest group with 3,665. The last line is a check, not a decoration: the clean and the quarantined trips add up to the 980,624 rows of the raw table. No trip was lost, and none was counted twice.

Why keep the quarantine table at all? Because a reader of the results will ask “what did you leave out?”, and the answer is a query, not a memory.

Find the bikes that moved without a rider

The problem

A bike that ends a trip at station B stays at B until the next rider takes it. So its next trip should start at B. If it starts somewhere else, nobody rode it there. A van moved it, a mechanic took it for repair, or a trip is missing from the file. These moves matter to Leila: they show where the crews already work.

To find them, Tomasz must compare each trip with the same bike’s previous trip. A GROUP BY cannot do that, because it puts rows together and loses their order. A window function can. LAG(end_id) reads end_id from the row before. PARTITION BY bike_id makes each bike a separate window, so the row before is always the same bike’s previous trip. ORDER BY start_time, trip_id defines “before”; trip_id breaks a tie if two trips start in the same second.

LAG on a tiny example

First, four trips that you can check by hand. Bike B7 has three trips and bike B9 one:

tiny = sqlite3.connect(":memory:")
tiny.executescript("""
CREATE TABLE trips (trip_id INTEGER, bike_id TEXT, start_time TEXT, start_id TEXT, end_id TEXT);
INSERT INTO trips VALUES
    (1, 'B7', '2025-06-02 07:40', 'A', 'B'),
    (2, 'B7', '2025-06-02 12:10', 'B', 'C'),
    (3, 'B7', '2025-06-03 08:05', 'D', 'B'),
    (4, 'B9', '2025-06-02 09:00', 'C', 'A');
""")
sql("""
SELECT bike_id, trip_id, start_time, start_id, end_id,
       LAG(end_id) OVER (PARTITION BY bike_id ORDER BY start_time, trip_id) AS previous_end
FROM trips
""", db=tiny)

The output:

bike_id trip_id       start_time start_id end_id previous_end
     B7       1 2025-06-02 07:40        A      B         NULL
     B7       2 2025-06-02 12:10        B      C            B
     B7       3 2025-06-03 08:05        D      B            C
     B9       4 2025-06-02 09:00        C      A         NULL
(4 rows)

What this tells us. Trip 2 starts at B, where trip 1 ended: a normal trip. Trip 3 starts at D, but the bike was left at C: B7 moved from C to D with no rider. Trip 4 belongs to another bike, so its previous_end is NULL. The window never crosses from one bike to another, and that is the job of PARTITION BY.

All the bikes

Now the same query on all 980,624 trips. Tomasz uses all trips, not only clean ones, because a quarantined trip still says where the bike was. He also keeps the previous end time for a check, and a step number. Three functions use the same window, so WINDOW bike_order AS (...) gives it a name once, and each function says OVER bike_order. Then he counts what happened before each trip:

con.executescript("""
DROP TABLE IF EXISTS bike_steps;
CREATE TABLE bike_steps AS
SELECT trip_id, bike_id, start_time, start_id, end_id, end_time,
       LAG(end_id) OVER bike_order AS previous_end,
       LAG(end_time) OVER bike_order AS previous_end_time,
       ROW_NUMBER() OVER bike_order AS step
FROM trips
WINDOW bike_order AS (PARTITION BY bike_id ORDER BY start_time, trip_id);
""")
sql("""
SELECT CASE
           WHEN step = 1 THEN 'first trip of the bike'
           WHEN previous_end IS NULL THEN 'previous end unknown'
           WHEN previous_end = start_id THEN 'starts where it was left'
           ELSE 'moved: no rider trip'
       END AS what_happened,
       COUNT(*) AS trips,
       ROUND(100.0 * COUNT(*) / (SELECT COUNT(*) FROM trips), 1) AS pct
FROM bike_steps
GROUP BY what_happened
ORDER BY trips DESC
""")

The output:

           what_happened  trips   pct
starts where it was left 910507  92.8
    moved: no rider trip  60554   6.2
  first trip of the bike   8874   0.9
    previous end unknown    689   0.1
(4 rows)

What this tells us. 92.8% of trips start where the bike was left, as expected. 60,554 trips (6.2%) start after a move that no rider made: about one trip in 16. 8,874 trips are each bike’s first trip in June, so they have nothing to compare with. 689 follow a trip with no end station, so we cannot tell.

Your turn: trips that start before the previous one ended

A bike cannot be on two trips at once. How many trips start before the same bike’s previous trip ended? (In the notebook, put the number in overlapping_trips.)

overlapping_trips = scalar("""
SELECT COUNT(*)
FROM bike_steps
WHERE previous_end_time IS NOT NULL
  AND start_time < previous_end_time
""")
print(overlapping_trips)

43

43 trips overlap their bike’s previous trip. They are record errors, for example a clock that was wrong or a bike number typed twice. They are too few to change the results, so they stay, and the dataset card lists them.

When and where the moves happen

Tomasz saves the moves in a table and counts them by the hour when a rider next took the moved bike:

con.executescript("""
DROP TABLE IF EXISTS moves;
CREATE TABLE moves AS
SELECT trip_id, bike_id, previous_end AS from_id, previous_end_time AS last_seen,
       start_id AS to_id, start_time AS next_start
FROM bike_steps
WHERE previous_end IS NOT NULL AND previous_end <> start_id;
""")
moves_by_hour = pd.read_sql("""
SELECT CAST(strftime('%H', next_start) AS INTEGER) AS hour, COUNT(*) AS moves
FROM moves GROUP BY hour ORDER BY hour
""", con)
print(moves_by_hour.sort_values("moves", ascending=False).head(4).to_string(index=False))
morning_share = moves_by_hour.loc[moves_by_hour["hour"].between(6, 9), "moves"].sum() / moves_by_hour["moves"].sum()
print(f"moved bikes next taken from 6:00 to 9:59: {morning_share:.1%}")

The output:

 hour  moves
    8   6465
   17   4587
    9   4012
   16   3871
moved bikes next taken from 6:00 to 9:59: 23.7%

Bikes that moved without a rider in June 2025, by the hour of the next rider's trip. The peak is at 8:00, the morning commute.

Text description of the chart

A line chart with 24 points, one per hour from 0 to 23. The values are low at night, with the lowest at 4:00 (242). They rise steeply from 6:00 (1,266) and 7:00 (2,615) to a peak at 8:00 (6,465), then fall to 4,012 at 9:00. During the day they stay between about 3,300 and 3,900. They rise to a second, smaller peak at 17:00 (4,587), and fall in the evening to 1,326 at 22:00.

What this tells us. Moved bikes are most often taken at 8:00, and 23.7% of them are taken between 6:00 and 9:59. So the crews already put bikes where morning riders want them. The second peak at 17:00 is the trip home.

Where do the crews take bikes from, and where do they bring them?

sql("""
SELECT m.from_id AS station_id, s.name, COUNT(*) AS bikes_taken_away
FROM moves AS m JOIN stations AS s ON s.station_id = m.from_id
GROUP BY m.from_id ORDER BY bikes_taken_away DESC LIMIT 5
""")
sql("""
SELECT m.to_id AS station_id, s.name, COUNT(*) AS bikes_brought
FROM moves AS m JOIN stations AS s ON s.station_id = m.to_id
GROUP BY m.to_id ORDER BY bikes_brought DESC LIMIT 5
""")

The output:

station_id                             name bikes_taken_away
      7015   King St W / Bay St (West Side)             3179
      7019            Temperance St Station             2239
      8028         Ward's Island Ferry Dock             1702
      8027        Hanlan's Point Ferry Dock             1273
      7283 Front St W / Bay St (South Side)             1110
(5 rows)
station_id                     name bikes_brought
      8030 Centre Island Ferry Dock          2208
      7907        365 Lippincott St          1342
      7030  Bay St / Wellesley St W          1323
      7887     800 Fleet St (North)          1292
      7249          144 Harrison St          1192
(5 rows)

What this tells us. The vans take bikes away from downtown offices (King St W / Bay St, Temperance St, Front St W) and from two island ferry docks. They bring bikes to residential streets (365 Lippincott St, 144 Harrison St) and to the Centre Island ferry dock. That is the pattern of a working day: bikes collect downtown, and the crews carry them back.

Frame the problem

Before the station table, Tomasz writes down what exactly it predicts. These are the questions from Features and target timing:

• The unit: one station on one working morning.
• The target: the station’s net outflow from 7:00 to 10:00: rider departures minus rider arrivals, from clean trips. A station with 30 departures and 5 arrivals loses 25 bikes, so its net outflow is 25. A station that gains bikes has a negative net outflow.
• The prediction time: 22:00 the evening before, when the crew leaves.
• What is known at 22:00: every trip until then, including this morning’s. Tomorrow morning is not known.
• The decision: rank the stations and restock the top 20 (K = 20).

Two leaks are tempting, and both use tomorrow. A station’s mean over the whole month includes the mornings that we predict. A mean of “the last five days including today” includes the target itself when “today” is the target morning. The features must use earlier days only.

Why 7:00 to 10:00? It is the morning commute, and the bikes that the crew leaves at night must last through it. Why net outflow and not departures? A station with 40 departures and 38 arrivals stays full, while one with 25 departures and no arrivals empties.

Build the station table

The trap: a missing day

The natural first try is a table with one row for each station and morning that had trips. Then LAG(net_out) would give “yesterday”. But a quiet morning, with no trip at all, has no row. Here is station A with no row on 4 June:

tiny.executescript("""
CREATE TABLE mornings (station_id TEXT, day TEXT, net_out INTEGER);
INSERT INTO mornings VALUES ('A', '2025-06-02', 5), ('A', '2025-06-03', 6), ('A', '2025-06-05', 9);
""")
sql("""
SELECT station_id, day, net_out,
       LAG(day) OVER (PARTITION BY station_id ORDER BY day) AS row_before,
       LAG(net_out) OVER (PARTITION BY station_id ORDER BY day) AS yesterday_wrong
FROM mornings
""", db=tiny)

The output:

station_id        day net_out row_before yesterday_wrong
         A 2025-06-02       5       NULL            NULL
         A 2025-06-03       6 2025-06-02               5
         A 2025-06-05       9 2025-06-03               6
(3 rows)

What this tells us. On 5 June, the row before is 3 June, so the value called yesterday_wrong is two days old. LAG means “the row before”, not “the day before”, and it gives no warning when a day is missing.

The fix is a grid: every station on every day, made with CROSS JOIN, and a 0 for a morning with no trips. Departures count by the start time and arrivals by the end time. Each uses the half-open range from 07:00:00 up to, but not including, 10:00:00, as in Time and ranking. A working day is Monday to Friday; June 2025 has no public holiday in Ontario.

con.executescript("""
DROP TABLE IF EXISTS morning;
CREATE TABLE morning AS
WITH days AS (
    SELECT DISTINCT date(start_time) AS day FROM clean
),
grid AS (                -- every station on every day, so that no day is missing
    SELECT s.station_id, d.day FROM stations AS s CROSS JOIN days AS d
),
departures AS (
    SELECT start_id AS station_id, date(start_time) AS day, COUNT(*) AS n FROM clean
    WHERE time(start_time) >= '07:00:00' AND time(start_time) < '10:00:00'
    GROUP BY 1, 2
),
arrivals AS (
    SELECT end_id AS station_id, date(end_time) AS day, COUNT(*) AS n FROM clean
    WHERE time(end_time) >= '07:00:00' AND time(end_time) < '10:00:00'
    GROUP BY 1, 2
)
SELECT g.station_id, g.day,
       CAST(strftime('%w', g.day) AS INTEGER) BETWEEN 1 AND 5 AS working,
       COALESCE(d.n, 0) AS departures,
       COALESCE(a.n, 0) AS arrivals,
       COALESCE(d.n, 0) - COALESCE(a.n, 0) AS net_out
FROM grid AS g
LEFT JOIN departures AS d ON d.station_id = g.station_id AND d.day = g.day
LEFT JOIN arrivals AS a ON a.station_id = g.station_id AND a.day = g.day;
""")
sql("""
SELECT COUNT(*) AS station_days, COUNT(DISTINCT station_id) AS stations, COUNT(DISTINCT day) AS days,
       SUM(departures + arrivals = 0) AS quiet_mornings, SUM(departures) AS departures, SUM(arrivals) AS arrivals
FROM morning
""")

The output:

station_days stations days quiet_mornings departures arrivals
       27480      916   30           6777     129217   124284
(1 row)

What this tells us. The grid has 27,480 rows: 916 stations × 30 days, with no day missing. In 6,777 station-mornings nothing happened, and the grid keeps them as 0. There are more departures than arrivals (129,217 against 124,284) because some morning trips end after 10:00.

How often would the table without the grid have fooled LAG? Tomasz rebuilds it from the mornings with a trip and counts the rows whose row before is not the day before:

sql("""
WITH sparse AS (        -- the table without the grid: only mornings with a trip
    SELECT * FROM morning WHERE departures + arrivals > 0
),
stepped AS (
    SELECT day, LAG(day) OVER (PARTITION BY station_id ORDER BY day) AS row_before
    FROM sparse
)
SELECT COUNT(*) AS rows_with_a_row_before,
       SUM(julianday(day) - julianday(row_before) > 1) AS row_before_is_not_yesterday
FROM stepped WHERE row_before IS NOT NULL
""")

The output:

rows_with_a_row_before row_before_is_not_yesterday
                 19798                        1766
(1 row)

What this tells us. Without the grid, 1,766 values called “yesterday” would come from an older day. Nothing in the output would show it. The quiet stations, where the mistake happens, are the ones that a planner cares least about. That makes the mistake even harder to see in a spot check.

Explore what the decision needs

Tomasz asks two questions that the plan needs, and no others. He looks at weeks 1 to 3 only, because week 4 is the test. If he looked at the test week now, his choices could fit it by accident.

First: is the morning drain a problem on every day? For each morning, he adds up the bikes lost by the stations that lose bikes. With two values, MAX(net_out, 0) is not an aggregate: it returns the larger of the two, so a station that gains bikes counts as 0:

sql("""
WITH per_morning AS (
    SELECT day, working, SUM(MAX(net_out, 0)) AS drained_bikes, SUM(net_out > 0) AS draining_stations
    FROM morning
    WHERE day < '2025-06-23'          -- explore weeks 1 to 3 only: week 4 is the test
    GROUP BY day
)
SELECT CASE working WHEN 1 THEN 'working day' ELSE 'weekend' END AS morning,
       COUNT(*) AS mornings,
       ROUND(AVG(drained_bikes)) AS drained_bikes,
       ROUND(AVG(draining_stations)) AS draining_stations
FROM per_morning GROUP BY working
""")

The output:

    morning mornings drained_bikes draining_stations
    weekend        7         898.0             322.0
working day       15        2153.0             407.0
(2 rows)

What this tells us. On a working morning, about 407 stations lose bikes, 2,153 bikes in total. On a weekend morning it is 898 bikes, less than half. The commuter drain is a working-day problem, so the station table keeps working days only. Weekend nights need a different plan, which is outside this case study.

Second: is the drain spread out, or do a few stations carry it? And do the crews already serve the stations that drain most? moved_in_per_morning counts moved bikes that a rider took at that station before 10:00 on a working day, divided by the 15 working mornings:

sql("""
WITH ranked AS (
    SELECT day, net_out, ROW_NUMBER() OVER (PARTITION BY day ORDER BY net_out DESC, station_id) AS rank
    FROM morning WHERE working = 1 AND day < '2025-06-23'
)
SELECT ROUND(1.0 * SUM(CASE WHEN rank <= 20 THEN net_out END) / SUM(MAX(net_out, 0)), 3) AS top_20_share
FROM ranked
""")
sql("""
WITH moved_in AS (       -- moved bikes that a rider took before 10:00 on a working day
    SELECT to_id AS station_id, COUNT(*) AS bikes FROM moves
    WHERE time(next_start) < '10:00:00' AND next_start < '2025-06-23'
      AND CAST(strftime('%w', next_start) AS INTEGER) BETWEEN 1 AND 5
    GROUP BY to_id
)
SELECT m.station_id, s.name, ROUND(AVG(m.net_out), 1) AS mean_net_out,
       ROUND(COALESCE(MAX(v.bikes), 0) / 15.0, 1) AS moved_in_per_morning
FROM morning AS m
JOIN stations AS s USING (station_id)
LEFT JOIN moved_in AS v USING (station_id)
WHERE m.working = 1 AND m.day < '2025-06-23'
GROUP BY m.station_id ORDER BY mean_net_out DESC LIMIT 8
""")

The output:

top_20_share
       0.194
(1 row)
station_id                               name mean_net_out moved_in_per_morning
      7000       Fort York  Blvd / Capreol Ct         27.6                  0.3
      7682           Bathurst St / Front St W         26.7                  0.2
      7451 Western Battery Rd / Pirandello St         23.6                  1.5
      7887               800 Fleet St (North)         20.8                 21.7
      7524                        Lisgar Park         20.7                  1.3
      7549                  439 Sherbourne St         18.6                 11.0
      7249                    144 Harrison St         18.3                 19.4
      7042     Sherbourne St / Wellesley St E         18.1                 10.6
(8 rows)

What this tells us. On a working morning, the top 20 stations carry 19.4% of all the bikes lost. That is about a fifth of the problem, at 20 of the about 407 stations that lose bikes: one in 20. So a list of 20 is worth making well. The top stations are in residential areas around downtown, where many people ride to work. The crews already serve some of them well: 800 Fleet St (North) gets 21.7 moved bikes a morning and 144 Harrison St 19.4. But look at the two stations at the top: Fort York Blvd / Capreol Ct and Bathurst St / Front St W. Each loses about 27 bikes a morning and gets almost no moved bikes. That is the first finding for Leila.

Features from earlier days only

Each feature is a value known at 22:00 the evening before. The station table has four, and each is a window function over a station’s own rows:

Feature	Meaning	Window
yesterday	Net outflow on the calendar day before	LAG(net_out, 1) over every day
same_day_last_week	Net outflow 7 days before (same weekday)	LAG(net_out, 7) over every day
last_working_day	Net outflow on the working day before	LAG(net_out, 1) over working days only
mean_5_working	Mean net outflow of the 5 working days before	AVG with the frame 5 PRECEDING to 1 PRECEDING

The two kinds of window are the point of this step. In the first SELECT (the calendar step), the window sees every day, so one row back is the day before. The outer SELECT has WHERE working = 1, and SQL applies WHERE before the window functions. So that window sees working days only, and one row back is the working day before: on a Monday, that is Friday.

The frame ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING is the rows that AVG uses: the five rows before the current one, and not the current one. That stop at 1 PRECEDING is what keeps the target out of its own feature. history_days counts how many rows the frame really had, because the first rows of June have fewer than five.

con.executescript("""
DROP TABLE IF EXISTS station_day;
CREATE TABLE station_day AS
WITH calendar AS (       -- windows over every day: one row back = the day before
    SELECT *,
           LAG(net_out, 1) OVER (PARTITION BY station_id ORDER BY day) AS yesterday,
           LAG(net_out, 7) OVER (PARTITION BY station_id ORDER BY day) AS same_day_last_week
    FROM morning
)
SELECT station_id, day, net_out, yesterday, same_day_last_week,
       LAG(net_out, 1) OVER w AS last_working_day,
       AVG(net_out) OVER (w ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) AS mean_5_working,
       COUNT(net_out) OVER (w ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) AS history_days
FROM calendar
WHERE working = 1        -- windows over working days only: one row back = the working day before
WINDOW w AS (PARTITION BY station_id ORDER BY day);
""")
sql("""
SELECT day, net_out, yesterday, same_day_last_week, last_working_day,
       ROUND(mean_5_working, 1) AS mean_5_working, history_days
FROM station_day WHERE station_id = '7000' AND day >= '2025-06-02' AND day < '2025-06-11'
""")

The output:

       day net_out yesterday same_day_last_week last_working_day mean_5_working history_days
2025-06-02      18         3               NULL             NULL           NULL            0
2025-06-03      33        18               NULL               18           18.0            1
2025-06-04      40        33               NULL               33           25.5            2
2025-06-05      27        40               NULL               40           30.3            3
2025-06-06      18        27               NULL               27           29.5            4
2025-06-09      19         0                 18               18           27.2            5
2025-06-10      37        19                 33               19           27.4            5
(7 rows)

What this tells us. You can check every value by hand. This station, Fort York Blvd / Capreol Ct, lost 18, 33, 40, 27 and 18 bikes in the first working week. On Monday 9 June, yesterday is 0: the Sunday, with no commuters. last_working_day is 18, the value of Friday 6 June. same_day_last_week is 18, the Monday before. mean_5_working is (18 + 33 + 40 + 27 + 18) ÷ 5 = 27.2. The real value that Monday was 19. “Same as yesterday” said 0, so it would have left one of the busiest stations off the list. In week 1, history_days grows from 0 to 4, and the mean uses only the rows it has. That is why week 1 scores no rule.

Split and baselines

The rule will rank stations for future mornings, so the split is by time, as in Choose a split. Week 1 gives history only: its rows do not have five days before them. Weeks 2 and 3 are for choosing the rule. Week 4, from 23 to 30 June, is the test, used once at the end.

VALID = ("2025-06-09", "2025-06-21")   # weeks 2 and 3: choose the rule here
TEST = ("2025-06-23", "2025-07-01")    # week 4 (23 to 30 June): used once, at the end
sql("""
SELECT CASE WHEN day < '2025-06-09' THEN 'week 1 (history only)'
            WHEN day < '2025-06-21' THEN 'weeks 2-3 (choose)'
            ELSE 'week 4 (test)' END AS part,
       COUNT(DISTINCT day) AS mornings, MIN(day) AS first, MAX(day) AS last,
       SUM(history_days < 5) AS rows_without_5_days_of_history
FROM station_day GROUP BY part ORDER BY first
""")

The output:

                 part mornings      first       last rows_without_5_days_of_history
week 1 (history only)        5 2025-06-02 2025-06-06                           4580
   weeks 2-3 (choose)       10 2025-06-09 2025-06-20                              0
        week 4 (test)        6 2025-06-23 2025-06-30                              0
(3 rows)

What this tells us. 10 mornings for choosing and 6 for the test. Week 1 has 5 mornings and 4,580 rows without five days of history (916 × 5). No rule gets a score from them.

How to score a ranking

What is a good list? Each morning, the best possible list is the 20 stations that really lost the most bikes. Nobody knows it at 22:00, but afterwards we do. So Tomasz scores a rule by the bikes its 20 stations really lost, compared with the best possible 20. A station that gained bikes counts as 0, because restocking it helped nobody. He calls the result the share of the best possible: 1.0 means the perfect list.

coverage() does this in SQL. ROW_NUMBER() ranks the stations each morning twice: once by the rule and once by the real value. Ties are broken by station_id, so the result is the same on every run. The rule is a column name, so it cannot be a :name parameter as values are in Reproducible extraction. The function only accepts a plain name, to keep the query safe.

K = 20   # stations the night crew can restock


def coverage(rule, period, k=K, table="station_day"):
    """Rank the stations each morning by `rule` (a column), take the top k, and
    compare the bikes they lost 7:00-10:00 with the best possible top k."""
    if not (rule.isidentifier() and table.isidentifier()):
        raise ValueError("rule and table must be column and table names")
    query = f"""
        WITH ranked AS (
            SELECT day, net_out,
                   ROW_NUMBER() OVER (PARTITION BY day ORDER BY {rule} DESC, station_id) AS plan_rank,
                   ROW_NUMBER() OVER (PARTITION BY day ORDER BY net_out DESC, station_id) AS best_rank
            FROM {table} WHERE day >= :first AND day < :end
        )
        SELECT COUNT(DISTINCT day) AS mornings,
               SUM(CASE WHEN plan_rank <= :k THEN MAX(net_out, 0) ELSE 0 END) AS covered,
               SUM(CASE WHEN best_rank <= :k THEN MAX(net_out, 0) ELSE 0 END) AS best,
               SUM(plan_rank <= :k AND best_rank <= :k) AS hits
        FROM ranked"""
    row = con.execute(query, {"first": period[0], "end": period[1], "k": k}).fetchone()
    mornings, covered, best, hits = row
    return {"rule": rule, "mornings": mornings, "covered": covered, "best": best,
            "share": round(covered / best, 3), "hits_per_morning": round(hits / mornings, 1)}

The rules on weeks 2 and 3

The four features are four rules. “Same as yesterday” is Leila’s current method, so it is the baseline to beat. “The last working day” is a fairer version of it. The mean of five working days is the candidate:

RULES = ["yesterday", "last_working_day", "same_day_last_week", "mean_5_working"]
valid_results = pd.DataFrame([coverage(rule, VALID) for rule in RULES])
print(valid_results.to_string(index=False))

The output:

              rule  mornings  covered  best  share  hits_per_morning
         yesterday        10     3292  4217  0.781              10.2
  last_working_day        10     3474  4217  0.824              10.9
same_day_last_week        10     3541  4217  0.840              11.9
    mean_5_working        10     3655  4217  0.867              12.0

Bikes covered by each rule's top 20 stations, as a share of the best possible top 20, over the 10 working mornings of weeks 2 and 3. The mean of the last five working days is highest.

Text description of the chart

A column chart with four columns, on a scale from 0 to 1. Yesterday: 0.78. Workday (the working day before): 0.82. Week ago (the same weekday 7 days before): 0.84. 5-day mean (the mean of the 5 working days before), in another colour: 0.87.

What this tells us. “Same as yesterday” covers 0.781 of the best possible. The mean of five working days covers 0.867: 3,655 bikes against 3,292 over 10 mornings, about 36 more bikes each morning. Each step up the table removes one weakness. “The last working day” stops comparing a Monday with a Sunday. “Last week” compares like with like. The five-day mean also smooths out one odd morning, such as a rainy day or an event. A single day is noisy, and an average of several is steadier.

Tomasz also notes what the rule does not reach. Even the best rule leaves about 13% of the best possible on the table. On average, 12 of its 20 stations are among the real top 20; the other 8 still lose bikes, just fewer.

Your turn: is a shorter memory better?

Make a column mean_3_working, the mean of the 3 working days before, and score it on weeks 2 and 3. Predict first: higher or lower than 0.867? (In the notebook, the result goes in mean_3_result.)

con.executescript("""
DROP TABLE IF EXISTS station_day_3;
CREATE TABLE station_day_3 AS
SELECT *,
       AVG(net_out) OVER (PARTITION BY station_id ORDER BY day
                          ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) AS mean_3_working
FROM station_day;
""")
mean_3_result = coverage("mean_3_working", VALID, table="station_day_3")
print(mean_3_result)

{'rule': 'mean_3_working', 'mornings': 10, 'covered': 3513, 'best': 4217, 'share': 0.833, 'hits_per_morning': 10.9}

Three days cover 0.833, less than five days (0.867). A shorter memory reacts faster, but one odd day weighs more. Tomasz keeps five. He tried this on weeks 2 and 3, not on the test week, so the test stays clean.

Common mistake

A feature that knows the answer

Symptom. Tomasz’s colleague suggests two “better” features: each station’s mean over June, and a mean of the last five days including today. Both score higher than the honest rule.

con.executescript("""
DROP TABLE IF EXISTS station_day_leaky;
CREATE TABLE station_day_leaky AS
SELECT *,
       AVG(net_out) OVER (PARTITION BY station_id) AS june_mean,
       AVG(net_out) OVER (PARTITION BY station_id ORDER BY day
                          ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS mean_5_with_today
FROM station_day;
""")
leaky = pd.DataFrame([coverage(rule, VALID, table="station_day_leaky")
                      for rule in ["mean_5_working", "june_mean", "mean_5_with_today"]])
print(leaky.to_string(index=False))

The output:

             rule  mornings  covered  best  share  hits_per_morning
   mean_5_working        10     3655  4217  0.867              12.0
        june_mean        10     3745  4217  0.888              12.8
mean_5_with_today        10     3858  4217  0.915              13.6

Cause. Both features contain the answer. The June mean includes every target morning of June, also the ones it predicts. The frame 4 PRECEDING AND CURRENT ROW includes the current row, and the current row’s net_out is the target. At 22:00 the evening before, neither value exists. This is leakage: a feature that uses information from after the prediction time.

Fix. Write down the prediction time, and check that every feature’s window ends before it: 1 PRECEDING, not CURRENT ROW. Never average over a period that contains the rows you score.

Consequence. On paper, the leaky rule looks 5 points better (0.915 against 0.867). On the night, it cannot be computed, and the real list would be worse than promised. A score that looks too good is a reason to check the feature’s timing first.

The final test, once

The choice is made on weeks 2 and 3: mean_5_working, top 20. Tomasz now runs the four rules on week 4, which nothing so far has touched:

test_results = pd.DataFrame([coverage(rule, TEST) for rule in RULES])
print(test_results.to_string(index=False))

The output:

              rule  mornings  covered  best  share  hits_per_morning
         yesterday         6     1732  2400  0.722               9.0
  last_working_day         6     1887  2400  0.786              10.0
same_day_last_week         6     1913  2400  0.797              10.5
    mean_5_working         6     1999  2400  0.833              11.5

What this tells us. On the test week, the five-day mean covers 0.833 of the best possible. That is 1,999 bikes over 6 mornings, against 1,732 for “same as yesterday” (0.722). That is about 45 more bikes in the right place each morning. The order of the four rules is the same as on weeks 2 and 3, so the choice holds. All shares are a little lower than before, which is normal for data that played no part in the choice.

Where does the gain come from? Tomasz scores the three main rules on each test morning:

def next_day(day):
    return (pd.Timestamp(day) + pd.Timedelta(days=1)).strftime("%Y-%m-%d")


test_days = pd.read_sql("SELECT DISTINCT day FROM station_day WHERE day >= :first AND day < :end",
                        con, params={"first": TEST[0], "end": TEST[1]})["day"]
by_day = pd.DataFrame([{"day": day, "weekday": pd.Timestamp(day).day_name()[:3],
                        **{rule: coverage(rule, (day, next_day(day)))["share"]
                           for rule in ["yesterday", "last_working_day", "mean_5_working"]}}
                       for day in test_days])
print(by_day.to_string(index=False))

The output:

       day weekday  yesterday  last_working_day  mean_5_working
2025-06-23     Mon      0.407             0.685           0.818
2025-06-24     Tue      0.868             0.868           0.872
2025-06-25     Wed      0.852             0.852           0.877
2025-06-26     Thu      0.812             0.812           0.864
2025-06-27     Fri      0.732             0.732           0.794
2025-06-30     Mon      0.528             0.694           0.698

• Same as yesterday
• Last working day (dashed)
• Mean of 5 working days

Share of the best possible covered on each test morning. 'Same as yesterday' drops on the two Mondays; on Tuesday to Friday it equals 'last working day'.

Text description of the chart

A line chart with six mornings: Monday 23, Tuesday 24, Wednesday 25, Thursday 26, Friday 27 and Monday 30 June. Same as yesterday: 0.41, 0.87, 0.85, 0.81, 0.73, 0.53. Last working day: 0.69, 0.87, 0.85, 0.81, 0.73, 0.69. Mean of 5 working days: 0.82, 0.87, 0.88, 0.86, 0.79, 0.70. From Tuesday to Friday, the first two lines are the same. The mean is highest on every morning, by less than 0.01 on Tuesday 24 and Monday 30.

What this tells us. Most of the gain over “same as yesterday” comes from Mondays, when “yesterday” is a Sunday. On 23 June it covers 0.407, against 0.818 for the mean. That part of the gain is simple calendar sense. The fairer baseline is “the last working day”. The mean beats it on all 6 mornings, but on two of them by less than 0.01. Monday 30 June is hard for every rule. It is the day before Canada Day, and many people may have taken a long weekend.

Be honest about the size of the evidence: 6 test mornings, in one month, in summer. The result points the same way as weeks 2 and 3, which makes it more believable, but it is not a promise for November.

Your turn: change one thing, a third van

Leila may get a third van, and restock 40 stations. Score “same as yesterday” and the five-day mean on the test week with k=40. Predict first: does the gap grow or shrink? (In the notebook, the result goes in k40_results.)

k40_results = pd.DataFrame([coverage(rule, TEST, k=40) for rule in ["yesterday", "mean_5_working"]])
print(k40_results.to_string(index=False))

          rule  mornings  covered  best  share  hits_per_morning
     yesterday         6     2890  4027  0.718              19.8
mean_5_working         6     3330  4027  0.827              24.3

With 40 stations, the mean covers 0.827 against 0.718: the gap stays about 11 points. The mean covers 3,330 bikes over 6 mornings, 440 more than with “same as yesterday”. A bigger crew does not make the choice of rule matter less.

Checks before delivery

The station table will be used every night, so it gets rules, as in Validation rules. Each rule is a query that must return no rows. One line per rule says ok or FAIL:

rules = {
    "a station-day appears twice":
        "SELECT station_id, day FROM morning GROUP BY 1, 2 HAVING COUNT(*) > 1",
    "the grid is not stations x days":
        "SELECT 1 WHERE (SELECT COUNT(*) FROM morning) <> "
        "(SELECT COUNT(*) FROM stations) * (SELECT COUNT(DISTINCT day) FROM morning)",
    "a trip is lost or counted twice":
        "SELECT 1 WHERE (SELECT COUNT(*) FROM clean) + (SELECT COUNT(*) FROM quarantine) <> (SELECT COUNT(*) FROM trips_raw)",
    "net_out is not departures - arrivals":
        "SELECT * FROM morning WHERE net_out <> departures - arrivals",
    "an evaluated row has less than 5 days of history":
        "SELECT * FROM station_day WHERE day >= '2025-06-09' AND history_days < 5",
    "a weekend morning is in the station table":
        "SELECT * FROM station_day WHERE CAST(strftime('%w', day) AS INTEGER) NOT BETWEEN 1 AND 5",
}
for name, query in rules.items():
    bad = len(con.execute(query).fetchall())
    print(f"{'FAIL' if bad else 'ok  '}  {name}" + (f": {bad} rows" if bad else ""))

The output:

ok    a station-day appears twice
ok    the grid is not stations x days
ok    a trip is lost or counted twice
ok    net_out is not departures - arrivals
ok    an evaluated row has less than 5 days of history
ok    a weekend morning is in the station table

What this tells us. Every rule passes. The key (station, day) is unique, the grid is complete, no trip was lost between raw and clean, the target is computed the same way in every row, every scored row has five days of history, and no weekend is in the table. If a later file breaks one of these, the line says FAIL with the number of rows, and the plan should not go out.

The night plan

The data ends on Monday 30 June. Tuesday 1 July is Canada Day, a public holiday, so the next working morning is Wednesday 2 July. Its plan uses the last 5 working days, 24 to 30 June. Tomasz writes the plan and the whole station table to CSV files:

plan = pd.read_sql("""
WITH last_5 AS (         -- the 5 working days before Wednesday 2 July (1 July is Canada Day)
    SELECT DISTINCT day FROM station_day ORDER BY day DESC LIMIT 5
)
SELECT m.station_id, s.name, ROUND(AVG(m.net_out), 1) AS expected_net_out
FROM station_day AS m JOIN stations AS s USING (station_id)
WHERE m.day IN (SELECT day FROM last_5)
GROUP BY m.station_id ORDER BY expected_net_out DESC, m.station_id LIMIT 20
""", con)
plan.insert(0, "rank", range(1, len(plan) + 1))
plan.to_csv("night_plan_2025-07-02.csv", index=False)
pd.read_sql("SELECT * FROM station_day ORDER BY day, station_id", con).to_csv("station_day.csv", index=False)
print(plan.head(8).to_string(index=False))
print("station_day.csv:", scalar("SELECT COUNT(*) FROM station_day"), "rows")

The output:

 rank station_id                               name  expected_net_out
    1       7000       Fort York  Blvd / Capreol Ct              28.2
    2       7682           Bathurst St / Front St W              26.2
    3       7451 Western Battery Rd / Pirandello St              22.0
    4       7887               800 Fleet St (North)              21.0
    5       7239   Bloor St W / Manning Ave - SMART              19.6
    6       7249                    144 Harrison St              19.6
    7       7100     Dundas St E / Regent Park Blvd              17.8
    8       7524                        Lisgar Park              17.8
station_day.csv: 19236 rows

What this tells us. This is the list for the crew, ranked by the bikes each station is expected to lose. The top two are the stations from the exploration that the crews rarely serve today. station_day.csv has 19,236 rows (916 stations × 21 working days) with the target and the four features. It is the table behind the plan, ready for a model in a later course.

Recommendations

Tomasz writes to Leila in her words:

• Use the five-day list. Each evening, rank the stations by their mean morning loss over the last five working days. Restock the top 20. On the test week it put 1,999 bikes in the right place over six mornings, against 1,732 with “the stations that emptied this morning”.
• Never plan a Monday from a Sunday. If you keep a one-day rule, use the last working day. This alone fixes most of the gap on Mondays.
• Look at the top two now. Fort York Blvd / Capreol Ct and Bathurst St / Front St W lose about 27 bikes each working morning and get almost no moved bikes. Check whether they run empty, and why the crews do not visit them.
• Plan holidays and weekends apart. The list is for working mornings. A holiday morning, such as 1 July, needs its own plan.
• Fix the export. Ask the data team to fill End_Station_Name from the end station. Until then, take names from the station list, never from that column.

And what not to use the list for: it says which stations lose the most bikes, not how many bikes to bring. That needs the number of bikes in each station at 22:00, which this data does not have.

Limits

• Departures are not demand. A rider who finds an empty station leaves no row. At a station that empties early, the data shows fewer departures than people wanted, so the true drain is larger than the target.
• No station status. The file has no capacity and no count of bikes at 22:00. A station that loses 25 bikes but holds 40 at night needs no van. The system’s live station feed (GBFS, the General Bikeshare Feed Specification) has this, but no history of it is in this dataset.
• One month, in summer. Weeks 2 to 4 of June 2025. Weather, school holidays and events change the pattern; the rule should be checked again each season.
• The moves are inferred. A move without a rider can be a van, a repair or a missing record. The file cannot tell them apart.
• Small test. Six mornings, with one unusual Monday. The direction is clear; the exact size of the gain is not.
• Ranking, not a model. The rule is a mean. A model that uses weather or events might do better; that is for the machine learning course.

Make it a portfolio piece

This case study shows an employer that you can take a messy real export and find what is wrong with it. It also shows that you can build a checked table that supports a decision. Make it yours with one change, for example another month, another city’s open bike data, or the evening peak instead of the morning. Then make it public, and easy to read in five minutes.

What to put in the repository:

• README.md, the page that people read first (template below).
• The notebook, run from top to bottom, with its outputs.
• queries/: the main SQL steps as .sql files (stations, typed trips, quarantine, moves, the morning grid, the station table, the checks), so a reader can run them without Python.
• station_day.csv and night_plan_2025-07-02.csv, the two delivered files.
• DATASET.md: source, licence and attribution, the encoding change, the defects found, the quarantine counts, and the limits, as in Track lineage.
• Not the data itself: the licence allows it, but a link and a checksum are enough, and the file is 28 MB.

A README template:

# Which bike-share stations to restock overnight

## Problem
A night crew can restock 20 stations before the morning peak. Which 20?
Current rule: the stations that emptied this morning ("same as yesterday").

## Data and licence
Bike Share Toronto Ridership Data, June 2025, 980,624 trips (City of Toronto).
Contains information licensed under the Open Government Licence - Toronto.
Changes: cp1252 to UTF-8.

## What was wrong with the data
End_Station_Name copies the start name in 100% of rows (names taken from the
start side). 5,754 trips quarantined with a reason (no end, 0 s, false starts).
60,554 trips (6.2%) follow a move with no rider, found with LAG per bike.

## Approach
SQLite. A complete station x day grid (a sparse table gave 1,766 wrong
"yesterday" values). Target: net outflow 7:00-10:00 on working days.
Features from earlier days only. Rule chosen on weeks 2-3, tested once on week 4.

## Results (week 4, 6 mornings, top 20)
Mean of the last 5 working days: 0.833 of the best possible (1,999 bikes)
Same as yesterday: 0.722 (1,732 bikes). Most of the gain is on Mondays.

## Limits
Departures are not demand; no dock status; one summer month; small test.

How to describe it in an interview, in two sentences: “I took a month of real bike-share trips, found an export defect in the station names and 60,000 bike moves that no rider made, and built a validated station table in SQL with features from earlier days only. A simple five-day ranking for the night crew covered 83% of the best possible list on a held-out week, against 72% for the current ‘same as yesterday’ method, and I showed that most of that gain comes from Mondays.”

Go further:

• Run the same study on another month of the same zip file, and check whether the five-day rule still wins. Predict first: in winter, will the morning drain be larger or smaller?
• Add a weighted mean (more weight on recent days) as a fifth rule, and score it on weeks 2 and 3 only.
• Find each station’s coordinates in the system’s live station feed, and map the top 20 against the moved-bike destinations.
• Build the evening plan: which stations fill up after 16:00, so the crew can empty them?

Knowledge check

Check your understanding

Tomasz builds the morning table only from station-mornings that had at least one trip, and uses LAG(net_out) as 'yesterday'. The query runs without an error. What is wrong, and how did the case study fix it?

Show the answer

LAG returns the row before, not the day before. When a station had no trip on a morning, that morning has no row, so LAG reaches back to an older day. In June this happened in 1,766 rows, with no warning. The fix is a complete grid: every station on every day with CROSS JOIN, and 0 for a morning with no trip. Then one row back is always the day before, and LAG(net_out, 7) is always the same weekday a week before.

Check your understanding

A colleague's rule scores 0.915 on weeks 2 and 3, against 0.867 for the five-day mean. It uses the frame ROWS BETWEEN 4 PRECEDING AND CURRENT ROW. Should Leila use it?

Show the answer

No. The frame includes the current row, and the current row’s net_out is the target, the morning we want to predict. At 22:00 the evening before, that value does not exist, so the rule cannot run on a real night. Its high score comes from leakage. The honest frame ends at 1 PRECEDING. A score that is better than expected is a reason to check the timing of every feature.

Check your understanding

On the test week, the five-day mean beats 'same as yesterday' by 11 points (0.833 against 0.722). Leila asks: 'So the average is much smarter?' What is your honest answer?

Show the answer

Partly. Most of the gain comes from the two Mondays, when “yesterday” is a Sunday with no commuters (0.407 against 0.818 on 23 June). A one-day rule that uses the last working day gets much of that back (0.786). Against that fairer rule, the mean still wins on every test morning: by 13 points on 23 June, and by less than one point on two mornings. An average of five days is less noisy than one day. The evidence is six mornings in one summer month, and weeks 2 and 3 agree. So the answer is “use the mean, and never plan Monday from Sunday”, and check it again next season.

Recap

• You profiled a real export with 980,624 trips. The end-station names were a copy of the start names in every row, so you took the names from the start side.
• You read the encoding problem in both directions: cp1252 read as UTF-8 fails, and UTF-8 read as cp1252 is quietly wrong.
• You put 5,754 trips in quarantine with one reason each, and proved that clean plus quarantine equals raw.
• LAG per bike found 60,554 trips (6.2%) after a move with no rider, most often used at 8:00.
• A complete grid kept LAG honest; without it, 1,766 “yesterday” values were older days.
• Features came from earlier rows only, and a leak demonstration showed how a CURRENT ROW frame inflates a score.
• On the test week, used once, the five-day mean covered 0.833 of the best possible, against 0.722 for “same as yesterday”. Most of the gain was on Mondays.

Next, the final assignment of the course. It asks a new question of the Larkfield export. You make every decision of this course again, and deliver a dataset that another person can check.

Next: Final assignment