Question Statement – From the given trips and users tables for a taxi service, write a query to return the cancellation rate in the first two days in October, rounded to two decimal places, for trips not involving banned riders or drivers.
Question code on Oracle SQL.
create table trips (trip_id int, rider_id int, driver_id int, status varchar2(200), request_date date); insert into trips values (1, 1, 10, 'completed', to_date ('2020-10-01', 'YYYY/MM/DD')); insert into trips values (2, 2, 11, 'cancelled_by_driver', to_date ('2020-10-01', 'YYYY/MM/DD')); insert into trips values (3, 3, 12, 'completed', to_date ('2020-10-01', 'YYYY/MM/DD')); insert into trips values (4, 4, 10, 'cancelled_by_driver', to_date ('2020-10-02', 'YYYY/MM/DD')); insert into trips values (5, 1, 11, 'completed', to_date ('2020-10-02', 'YYYY/MM/DD')); insert into trips values (6, 2, 12, 'completed', to_date ('2020-10-02', 'YYYY/MM/DD')); insert into trips values (7, 3, 11, 'completed', to_date ('2020-10-03', 'YYYY/MM/DD')); create table users (user_id int, banned varchar2(200), type varchar2(200)); insert into users values (1, 'no', 'rider'); insert into users values (2, 'yes', 'rider'); insert into users values (3, 'no', 'rider'); insert into users values (4, 'no', 'rider'); insert into users values (10, 'no', 'driver'); insert into users values (11, 'no', 'driver'); insert into users values (12, 'no', 'driver');
My Solution Code is below. However, I get the following error. Can someone pleas help?
ORA-00904: "TOTAL_TRIPS": invalid identifier
SOLUTION CODE:
select request_date, (1-(trips_completed/total_trips)) as "cancel_rate" from (( select request_date, sum(case when status = 'completed' then 1 else 0 end) as "trips_completed", sum(case when status = 'cancelled_by_driver' then 1 else 0 end) as "trips_cancelled", sum(case when status = 'cancelled_by_driver' then 1 when status= 'completed' then 1 else 0 end) as "total_trips" from ( select t.rider_id, t.driver_id, t.status, t.request_date, u.banned as "not_banned_rider", u.banned as "not_banned_driver" from trips t join users u on t.rider_id=u.user_id where u.banned='no' ) group by request_date having request_date <> to_date ('2020-10-03', 'YYYY/MM/DD') ));
Advertisement
Answer
ORA-00904: “TOTAL_TRIPS”: invalid identifier
just means what is written “total_trips” is invalid
Just use total_trips (without quote)