Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • How to Use Calculation of an Aggregate Function as Filter Criteria?

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 345
    Answer it

    I have tables on booking orders for trips.
    Bookings (booking_id, booking_time, driver_id, customer_id)
    Drivers (driver_id, name)
    I need to identify all customers who have had at least 60% of their bookings completed by the same driver within the last 30 days. Then, I need to return name of those drivers along with their booking_ID which where caught by the threshold limits and not all bookings


    Customer ABC had 10 bookings, of which 6 of them were completed by driver_01. Customer DEF had 10 bookings, of which 4 of them were completed by driver_02. Customer GHJ had 1 booking, of which 1 was completed by driver_03 

    The output would return driver_01 and driver_03, plus their names with booking_id

    driver_01, B1
    driver_01, B2
    driver_01, B3
    driver_01, B4
    driver_01, B5
    driver_01, B6
    driver_03, B7

    I can generate the the driver_id and customer_id list. To generate the order_id should I use more subquery? or any other solution?

     

    select b.driver_id,d.name,b.customer_id,count(*) pair_count, count_customer, count(*) / scount * 100 percent
    from bookings b
    join (select customer_id,count(*) count_customer from bookings group by customer_id) c 
            on c.customer_id = b.customer_id
    join  drivers d on d.driver_id = b.driver_id
    group by driver_id,d.name,customer_id having count(*) / count_customer * 100 >= 60;

 0 Answer(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: