I have Two Tables Trips & Users.

I am trying to write a SQL query 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 in a percentage output

So in my case if Total Trips is 4 and Cancellations is 2 then I want to see .50 or if 3 and 1 then .33

Here is what I was trying to do so far...

With CTE AS (

 Select Request_at as [DAY], 
 SUM (CASE
            When Status= 'cancelled_by_driver'  or  Status='cancelled_by_client' THEN 1
            Else 0
        END)        
        AS Cancellations,
 Count(*) as TotalTrips
FROM Trips
Where Client_id NOT IN (SELECT [Users_id]   FROM [Study].[dbo].[Users] Where Banned='YES' and Roll='Client') or Driver_id NOT IN (SELECT [Users_id]   FROM [Study].[dbo].[Users] Where Banned='YES' and Roll='Driver')
Group By Request_at )


Select  DAY, ((TotalTrips/Cancellations) *100) as CancellationRate
From CTE

But the Divide Function is not working. I am not sure how else to approach this.

This is an error I am getting.

Any help is appreciated.

CodePudding user response：

Try this one:

With CTE AS (
 Select Request_at as [DAY], 
 SUM (CASE
            When Status= 'cancelled_by_driver'  or  Status='cancelled_by_client' THEN 1
            Else 0
        END)        
        AS Cancellations,
 Count(*) as TotalTrips
FROM Trips
Where Client_id NOT IN (SELECT [Users_id]   FROM [Study].[dbo].[Users] Where Banned='YES' and Roll='Client') or Driver_id NOT IN (SELECT [Users_id]   FROM [Study].[dbo].[Users] Where Banned='YES' and Roll='Driver')
Group By Request_at )

Select  DAY, convert(float, TotalTrips) * 100/NULLIF(Cancellations, 0) as CancellationRate
From CTE

CodePudding user response：

• The banned check needs to use AND instead of OR.
• The ratio is calculated by dividing the cancelled trip count by total trip count, not the other way round.

CodePudding user response：

Since we have to query users_id twice, we can put that in a CTE. No need of a second query to calculate the percent.

With Unbanned_users As (
    Select [users_id] FROM [Study].[dbo].[Users]
    Where Banned<>'YES'
)
Select Request_at as [DAY],
       (100*Count(CASE When Status Like 'cancelled%' Then 1 End) Count(*)/2)
        /Count(*) as Cancellation_percent
From Trips t
    Inner Join Unbanned_users cl ON t.client_id=cl.users_id
    Inner Join Unbanned_users dr ON t.driver_id=dr.users_id
Group By Request_at

This assumes that all of the user ids in Trips, both client and driver, are defined in the Users table.

The Count(*)/2 is to round 2 out of 3 to 67, instead of the truncated result of 66.