SQLize Online / PHPize Online  /  SQLtest Online

WITH RECURSIVE dates AS ( SELECT '2005-07-01' AS date UNION ALL SELECT DATE_ADD(date, INTERVAL 1 DAY) FROM dates WHERE date < '2005-07-31'), store_1_rental AS ( SELECT DATE_FORMAT(rental_date, '%Y-%m-%d') as date, COUNT(rental_date) AS rented_at_store_1 FROM rental JOIN inventory USING (inventory_id) WHERE store_id = 1 GROUP BY date ), store_1_return AS ( SELECT DATE_FORMAT(return_date, '%Y-%m-%d') as date, COUNT(rental_id) AS returned_at_store_1 FROM rental JOIN inventory USING (inventory_id) WHERE store_id = 1 GROUP BY date ), store_2_rental AS ( SELECT DATE_FORMAT(rental_date, '%Y-%m-%d') as date, COUNT(rental_id) AS rented_at_store_2 FROM rental JOIN inventory USING (inventory_id) WHERE store_id = 2 GROUP BY date ), store_2_return AS ( SELECT DATE_FORMAT(return_date, '%Y-%m-%d') as date, COUNT(rental_id) AS returned_at_store_2 FROM rental JOIN inventory USING (inventory_id) WHERE store_id = 2 GROUP BY date ), total_rental AS ( SELECT DATE_FORMAT(rental_date, '%Y-%m-%d') as date, COUNT(rental_id) AS total_rented FROM rental JOIN inventory USING (inventory_id) GROUP BY date), total_return AS ( SELECT DATE_FORMAT(return_date, '%Y-%m-%d') as date, COUNT(rental_id) AS total_returned FROM rental JOIN inventory USING (inventory_id) GROUP BY date) SELECT date, COALESCE(rented_at_store_1, '0') AS rented_at_store_1, COALESCE(returned_at_store_1, '0') AS returned_at_store_1, COALESCE(rented_at_store_2, '0') AS rented_at_store_2, COALESCE(returned_at_store_2, '0') AS returned_at_store_2, COALESCE(total_rented, '0') AS total_rented, COALESCE(total_returned, '0') AS total_returned FROM dates LEFT JOIN store_1_rental USING (date) LEFT JOIN store_1_return USING (date) LEFT JOIN store_2_rental USING (date) LEFT JOIN store_2_return USING (date) LEFT JOIN total_rental USING (date) LEFT JOIN total_return USING (date) ; SHOW STATUS LIKE 'Last_query_cost';