Proper way to calculate percentage of?

user_8797830 0
Viewed 100

I am trying to figure out the proper way to calculate the % of in sql based on following question:

Assume there are two kinds of deliveries: “Immediate” and “Scheduled.” A delivery is “Immediate” if the requested delivery time is within an hour of when the delivery is created, while it is “Scheduled” if it is beyond an hour. What % of total deliveries are “Immediate?”

Table name: deliveries

delivery_id                 int        
order_created_at            timestamp  
predicted_store_pickup_time timestamp  
actual_store_pickup_at      timestamp  
requested_delivery_time     timestamp  
dasher_id                   int        
restaurant_id               int        
consumer_id                 int

I have something like below but not sure if there is a more efficent way to calculate this?

 with immediate as (
select *
from deliveries
where datediff(hour, order_created_at, requested_delivery_time) <= 1

)

select count(i.*) / count(d.*) as ratio
from immediate i
full outer join deliveries d
on i.delivery_id = d.delivery_id
presto count group-by sql
1 Answers

I would use avg():

select 
    avg(
        case 
            when requested_delivery_time <= order_created_at + interval '1' hour
            then 1.0
            else 0
        end
    ) on_time_ratio
from deliveries
user_106767160
Related