Five busiest stops (ons + offs), ranked buy passenger traffic How busy is each r

Question

Five busiest stops (ons + offs), ranked buy passenger traffic

How busy is each route compared to the other routes?

How busy is each direction (north, south, east, west) compared to other directions?

table

create table passenger_data(
`index` int,
`date` date not null,
route_id int not null,
direction varchar(50) not null,
stop_id int not null,
on_number int not null default 0,
off_number int not null default 0,
vehicle_id int not null,
primary key(`index`),
foreign key(route_id) references routes(route_id),
foreign key(stop_id) references stops(stop_id),
foreign key(vehicle_id) references vehicles(vehicle_id)
);

Explanation / Answer

How busy is each route compared to the other routes?

SELECT route_id, index, COUNT(on_number) + COUNT(off_number) AS 'BUSY_STOP' FROM passenger_data GROUP BY (route_id, index) ORDER BY BUSY_STOP DESC;

How busy is each direction (north, south, east, west) compared to other directions?

SELECT route_id, index, direction, COUNT(on_number) + COUNT(off_number) AS 'BUSY_STOP' FROM passenger_data WHERE DIRECTION IS NOT NULL AND DIRECTION IN ('NORTH','SOUTH','EAST','WEST') GROUP BY (route_id, index, direction) ORDER BY BUSY_STOP DESC;

Please let me know in case of any clarifications required. Thanks!

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.