my solution


  • 0
    S

    select cc.request_at as Day,round((sum(cc.cancell_state)/count(cc.cancell_state)),2) as 'Cancellation Rate'
    from
    (select aa.request_at,aa.cancell_state
    from (select a.request_at,a.status,a.client_id,a.driver_id,a.city_id,c.Banned,
    case when a.status like 'cancelled%' then 1 else 0 end cancell_state
    from trips a
    left join (select users_id,Banned,role from users)c on a.client_id=c.users_id
    where a.request_at between '2013-10-01' and '2013-10-03' and c.Banned='No'
    ) aa
    inner join
    (select a.request_at,a.status,a.client_id,a.driver_id,a.city_id,c.Banned,
    case when a.status like 'cancelled%' then 1 else 0 end cancell_state
    from trips a
    left join (select users_id,Banned,role from users)c on a.driver_id=c.users_id
    where a.request_at between '2013-10-01' and '2013-10-03' and c.Banned='No'
    ) bb
    on (aa.request_at=bb.request_at and aa.status=bb.status and aa.client_id=bb.client_id
    and aa.driver_id=bb.driver_id and aa.city_id=bb.city_id and aa.Banned=bb.Banned)
    ) cc
    group by cc.request_at
    order by cc.request_at


Log in to reply
 

Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.