SQLize | PHPize | SQLtest

WITH all_ops AS ( SELECT staff_id, DATE(rental_date) AS op_day FROM rental WHERE rental_date BETWEEN '2005-05-01' AND '2005-05-31 23:59:59' UNION ALL SELECT staff_id, DATE(return_date) FROM rental WHERE return_date BETWEEN '2005-05-01' AND '2005-05-31 23:59:59' ), daily_totals AS ( SELECT staff_id, op_day, COUNT(*) AS ops_per_day FROM all_ops GROUP BY staff_id, op_day ), avg_totals AS ( SELECT staff_id, AVG(ops_per_day) AS average_operation_count FROM daily_totals GROUP BY staff_id ) SELECT s.staff_id, s.first_name, s.last_name, a.average_operation_count AS average_operation_count FROM staff AS s JOIN avg_totals AS a USING (staff_id) ORDER BY s.staff_id;