SQLize Online / PHPize Online  /  SQLtest Online

WITH RentalDelays AS ( SELECT c.store_id, SUM(CASE WHEN r.return_date IS NULL THEN 1 WHEN DATEDIFF(COALESCE(r.return_date, NOW()), r.rental_date) > f.rental_duration THEN 1 ELSE 0 END) AS delayed_count, COUNT(r.rental_id) AS total_rentals FROM rental r JOIN inventory i ON r.inventory_id = i.inventory_id JOIN film f ON i.film_id = f.film_id JOIN customer c ON r.customer_id = c.customer_id GROUP BY c.store_id ) SELECT store_id, ROUND((delayed_count * 100.0 / NULLIF(total_rentals, 0)), 5) AS delayed_percent FROM RentalDelays;