Bike-Share Hub Stations

A city bike-share scheme wants to send its maintenance crew to hub stations first. A station counts as a hub if it has at least 40 docks, or it handled at least 5000 trips last month (either condition is enough; meeting both is fine too).

Return station_id, station_name, dock_count and monthly_trips for every hub station, one row per station, in any order. Return no rows if there are no hubs.

Tables

stations — One row per docking station.

Example

stations

Expected output

Rows may be returned in any order. Your query also runs against hidden datasets with tricky edge cases.

SQL Path · Select & Filter · problem 1 of 70 · SQLite

The idea

This is a single-table filter with two independent thresholds. "At least 40" translates to dock_count >= 40 and "at least 5000" to monthly_trips >= 5000. Using > would drop stations sitting exactly on a threshold, which is the most common off-by-one mistake in filters.

Because either condition is enough, connect them with OR. A row passes when at least one side is true, and a station meeting both still appears once, since WHERE decides per row whether to keep it.

The alternative runs one query per threshold and combines them with UNION. UNION removes duplicate rows, so a station that meets both conditions is not repeated. UNION ALL would list it twice. Including station_id in the output matters for that approach: two different stations with the same name and numbers must stay separate rows.

Solution

SELECT station_id,
       station_name,
       dock_count,
       monthly_trips
FROM stations
WHERE dock_count >= 40
   OR monthly_trips >= 5000;

Another way

SELECT station_id, station_name, dock_count, monthly_trips
FROM stations
WHERE dock_count >= 40
UNION
SELECT station_id, station_name, dock_count, monthly_trips
FROM stations
WHERE monthly_trips >= 5000;

Why common answers fail

Uses strict > so stations with exactly 40 docks or exactly 5000 trips are wrongly left out.

SELECT station_id, station_name, dock_count, monthly_trips FROM stations WHERE dock_count > 40 OR monthly_trips > 5000;

Uses AND, so only stations meeting both thresholds are returned instead of either one.

SELECT station_id, station_name, dock_count, monthly_trips FROM stations WHERE dock_count >= 40 AND monthly_trips >= 5000;

UNION ALL keeps duplicates, so a station meeting both thresholds is listed twice.

SELECT station_id, station_name, dock_count, monthly_trips FROM stations WHERE dock_count >= 40
UNION ALL
SELECT station_id, station_name, dock_count, monthly_trips FROM stations WHERE monthly_trips >= 5000;

What the interviewer is checking

A warm-up that checks careful translation of business wording ("at least", "either") into comparison operators and boolean logic.

Mistakes to avoid

• Using > where the rule says "at least".
• Confusing OR with AND.
• Combining two filtered queries with UNION ALL and getting duplicates.

Follow-ups to expect

• Label each hub with the reason it qualified. (CASE expression in the SELECT list)
• Stations that meet exactly one of the two conditions. ((a OR b) AND NOT (a AND b))
• Make the thresholds configurable. (put them in a parameters CTE and join or reference it)

In real work

Operations teams tier sites, stores or servers with "big by capacity or big by traffic" rules exactly like this one.

Practice next