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';
Telegram: DB developers channel