Напредни извештаи од базата

Листа на извештаи

• Патникот може да побара историјат за одредено време кој ќе се состои од тоа колку километри поминал со секој возач и колку вкупно платил.

SQL прашалници

SELECT d.driver_id, SUM(d.km_travelled) as km_travelled_with_driver, 
SUM(pay.total_sum_payed) as total_price_paid, SUM(d.km_travelled)/SUM(pay.total_sum_payed) as price_per_km
FROM request r
JOIN drive d ON r.request_id = d.request_id 
JOIN payment pay ON pay.passenger_id = r.passenger_id
WHERE r.passenger_id = '77d9c766-e0c1-4e53-86d0-bafc3101a1ac' AND 
d.start_time between now() and now() - interval '2 months' 
GROUP BY d.driver_id 
ORDER BY price_per_km ASC 

SELECT dr.driver_id, dr.email, c.make, c.model, (CASE WHEN num_grades > 2 THEN AVG(de.grade) ELSE NULL END) as driver_grade, COUNT(*) as number_of_drives, 
SUM(p.total_sum_payed) as total_money_made, COUNT(DISTINCT de.request_id) as number_of_different_requests,
COUNT(DISTINCT r.passenger_id) as number_of_different_passengers, (SUM(p.total_sum_payed))/COUNT(DISTINCT de.request_id) as average_money_per_request,
SUM(de.km_travelled) as total_km_travelled
FROM 
driver dr
LEFT JOIN car c ON dr.driver_id = c.driver_id 
JOIN drive de ON dr.driver_id = de.driver_id 
JOIN payment p ON de.drive_id = p.drive_id 
LEFT JOIN request r ON de.request_id = r.request_id 
GROUP BY dr.driver_id, dr.email, c.make, c.model
ORDER BY driver_grade DESC