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
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;