SQLize | PHPize | SQLtest

WITH rental_d AS ( SELECT staff_id, DATE(rental_date) AS op_date, COUNT(rental_id) AS operation_count1 FROM rental WHERE YEAR(rental_date) = 2005 AND MONTH(rental_date) = 5 GROUP BY staff_id, op_date ), return_d AS ( SELECT staff_id, DATE(return_date) AS op_date, COUNT(rental_id) AS operation_count2 FROM rental WHERE YEAR(return_date) = 2005 AND MONTH(return_date) = 5 GROUP BY staff_id, op_date ), combined AS ( SELECT r.staff_id, r.op_date, r.operation_count1 + IFNULL(rt.operation_count2, 0) AS total_operations FROM rental_d r LEFT JOIN return_d rt ON r.staff_id = rt.staff_id AND r.op_date = rt.op_date UNION ALL SELECT rt.staff_id, rt.op_date, IFNULL(r.operation_count1, 0) + rt.operation_count2 AS total_operations FROM return_d rt LEFT JOIN rental_d r ON r.staff_id = rt.staff_id AND r.op_date = rt.op_date WHERE r.staff_id IS NULL ) SELECT s.staff_id, s.first_name, s.last_name, AVG(c.total_operations) AS average_operation_count FROM combined c JOIN staff s ON c.staff_id = s.staff_id GROUP BY s.staff_id, s.first_name, s.last_name ORDER BY s.staff_id; ; show status like 'Last_query_cost';