Changes between Version 5 and Version 6 of AdvancedDatabaseReports

Timestamp:

12/20/22 12:33:51 (2 years ago)

Author:

181085

Comment:

--

AdvancedDatabaseReports

@@ -9 +9 @@
 {{{#!sql:
 SELECT d.driver_id, SUM(d.km_travelled) as km_travelled_with_driver, 
-SUM(pay.total_sum_payed) as total_price_paid 
+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 
@@ -15 +15 @@
 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
+GROUP BY d.driver_id 
+ORDER BY price_per_km ASC
 }}}
+
+{{{#!sql:
+SELECT dr.driver_id, dr.email, (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(d.km_travelled) as total_km_travelled
+FROM 
+driver dr
+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 
+ORDER BY driver_grade DESC
+}}