Compatible Carpool Pairs

Problem

A commuter app records the routes each rider takes each day, and which riders are already grouped into the same carpool.

Table: trips

| Column Name | Type |
|-------------|------|
| rider_id | int |
| route_id | int |
| trip_date | date |

Each row means the rider travelled that route on that date.

Table: carpool

| Column Name | Type |
|-------------|------|
| rider1_id | int |
| rider2_id | int |

(rider1_id, rider2_id) is the primary key for this table. Each row means the two riders share a carpool. Note that rider1_id < rider2_id.

A carpool pair (rider1_id, rider2_id) is highly compatible if:
- They already share a carpool.
- They travelled the same three or more distinct routes on the same day.

Return the highly compatible pairs in any order, with rider1_id < rider2_id and no duplicate rows.

Tables

Example rows — the live problem includes the full dataset.

Expected output

Your answer should return 2 rows with the columns rider1_id, rider2_id.

Starter code (SQL)

SELECT *
FROM trips;

Related SQL questions

• 3-Consecutive-Week Marketing StreakHard
• 30-Day Rolling Average Trading VolumeHard
• 7-Day Active User Rolling MetricHard
• 7-Day Revenue Moving AverageHard
• Always Mid-Pack BakersHard
• Annual Energy Output Growth by Panel ModelHard