Question 398305 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Why I am getting Missing From Keyword error in the following code?

The cancellation rate is computed by dividing the number of canceled
(by client or driver) requests with unbanned users by the total number
of requests with unbanned users on that day.

Write a solution to find the cancellation rate of requests with
unbanned users (both client and driver must not be banned) each day
between "2013-10-01" and "2013-10-03". Round Cancellation Rate to two
decimal points.

Return the result table in any order.

with ctes as 
(
    select *, count(*) as sums FROM 
    Trips join Users
    on client_id = users_id
    where banned = 'No'
    group by request_at 
)
select ctes.request_at as 'Day' ,round(count(ctes.id)/ctes.sums,2) as 'Cancellation Rate' 
FROM
ctes
where ctes.status in ('cancelled_by_driver','cancelled_by_client')
group by ctes.request_at

Why I am getting Missing From Keyword error in the following code?

Tags:

2

Answers


  1. 0

    There are few issues in your code. Here is the code that works both with Oracle (fiddle) and MySql (fiddle).

    WITH 
  ctes as 
    ( Select       t.request_at, t.status, u.user_id, Count(t.client_id) as sums 
      From         trips t 
      Inner Join   users u ON( t.client_id = u.user_id )
      Where        u.banned = 'No' 
      Group By     t.request_at, t.status, u.user_id
    )
Select   request_at "Day", status,  
         round(count(user_id)/Max(sums),2)  "Cancelation Rate"
From     ctes
Where    status in ('cancelled_by_driver','cancelled_by_client')
Group By request_at, status

    Issues:

    1. when selecting from more than 1 table use table aliases so the database knows which table you are selecting from in case of the same name in both tables or * (is it trips.* or users.*)
    2. when aggregating data group by all selected non-aggregated columns (* could be an additional issue)
    3. column aliases – declare them within double-quotes or like AS COLUMN_NAME with no quotes at all

    Sugestion – format your code consistently for better readability and maintainability.

    NOTE:
    I probably missed something cause there were no sample data nor expected result. You should adjust the code to your actual context. I didn’t bother with the math or aggregations. Where condition for status should probably be moved to ctes etc, etc… Just adjust it yourself.

    Login or Signup to reply.
  2. 0

    There are mainly two errors in your query.

    • First, you cannot group by request_at and select *. What would that even mean? "Give me one result row per request-id, but show me all values of every row regardless of the aggregation"?
    • Second, AS 'Day' etc. are invalid. Single quotes denote string literals. If you must use quotes for names, use double quotes (AS "Day"), but it is best to avoid them at all (AS day).

    But it also seems you have the wrong approach altogether.

    to find the […] each day between "2013-10-01" and "2013-10-03".

    Where is this in your query? You are probably supposed to generate three rows, one for each date in the results.

    The cancellation rate is computed by dividing […] requests with unbanned users by the total number of requests with unbanned users […]

    Do this step by step: Get those requests for unbanned users. Then aggregate.

    One thing to consider: There may be no trip on one of the three days. In that case you cannot calculate a rate, as the count is zero and you cannot divide by zero. Show NULL instead.

    Something along the lines of:

    WITH
  dates AS
  (
    SELECT DATE '2013-10-01' AS dt FROM dual UNION ALL
    SELECT DATE '2013-10-02' AS dt FROM dual UNION ALL
    SELECT DATE '2013-10-03' AS dt FROM dual
  ),
  requests AS
  (
    SELECT *
    FROM trips
    WHERE client_id IN (SELECT users_id FROM users WHERE banned = 'No')
  )
SELECT 
  d.dt,
  ROUND(COUNT(CASE WHEN r.status IN ('cancelled_by_driver', 'cancelled_by_client') THEN 1 END) 
         / NULLIF(COUNT(r.request_at), 0)
    , 2) AS rate
FROM dates d
LEFT OUTER JOIN requests r ON r.request_at = d.dt
GROUP BY d.dt
ORDER BY d.dt;

    In this code I assume, that request_at is a column containing mere dates. If it contains datetimes, then you must adjust this (e.g. by applying TRUNC(request_at).

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search