sql - SQL not using ALIAS column for calculation

Question

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')
));

标签： sqloracledate-formattingoracle-sql-data-modeler

Answer 1

First, don't put identifiers in double quotes. They just clutter up queries.

Some other things to fix:

• No need for two levels of subqueries.
• Learn to use proper date literal syntax.
• I think you want < rather than <>.

So that suggests:

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 trips t join
           users u
           on t.rider_id = u.user_id
      where u.banned = 'no' and
            t.request_date < date '2020-10-03'
      group by request_date 
     ) rd;

This can be further simplified using avg():

select request_date,
       avg(case when status = 'completed' then 1 else 0 end) as cancel_rate
from trips t join
     users u
     on t.rider_id = u.user_id
where u.banned = 'no' and
      request_date < date '2020-10-03'
group by request_date ;

Note: This addresses fixing the query in your question. It doesn't actually correctly answer the question, for the following reasons:

• I'm pretty sure the question entails one cancellation rate, not one for two dates.
• It doesn't take into account banned drivers.
• I'm not sure how "cancelled by user" would be handled.