WITH RECURSIVE dates AS (
SELECT CAST('2005-07-01' AS DATE) date
UNION ALL
SELECT date_add(date, INTERVAL 1 DAY)
FROM dates
WHERE date < '2005-07-31'
),
store_1_discs_issued AS (
SELECT DATE_FORMAT(rental_date, '%Y-%m-%d') AS date, COUNT(inventory_id) AS rented_at_store_1
FROM rental JOIN inventory USING(inventory_id)
WHERE DATE_FORMAT(rental_date, '%Y-%m-%d') IN (SELECT * FROM dates) AND store_id = 1
GROUP BY DATE_FORMAT(rental_date, '%Y-%m-%d')),
store_2_discs_issued AS (
SELECT DATE_FORMAT(rental_date, '%Y-%m-%d') AS date, COUNT(inventory_id) AS rented_at_store_2
FROM rental JOIN inventory USING(inventory_id)
WHERE DATE_FORMAT(rental_date, '%Y-%m-%d') IN (SELECT * FROM dates) AND store_id = 2
GROUP BY DATE_FORMAT(rental_date, '%Y-%m-%d')),
store_1_discs_returned AS (
SELECT DATE_FORMAT(return_date, '%Y-%m-%d') AS date, COUNT(inventory_id) AS returned_at_store_1
FROM rental JOIN inventory USING(inventory_id)
WHERE DATE_FORMAT(return_date, '%Y-%m-%d') IN (SELECT * FROM dates) AND store_id = 1
GROUP BY DATE_FORMAT(return_date, '%Y-%m-%d')),
store_2_discs_returned AS (
SELECT DATE_FORMAT(return_date, '%Y-%m-%d') AS date, COUNT(inventory_id) AS returned_at_store_2
FROM rental JOIN inventory USING(inventory_id)
WHERE DATE_FORMAT(return_date, '%Y-%m-%d') IN (SELECT * FROM dates) AND store_id = 2
GROUP BY DATE_FORMAT(return_date, '%Y-%m-%d'))
SELECT date, IFNULL(rented_at_store_1, 0) AS rented_at_store_1, IFNULL(returned_at_store_1, 0) AS returned_at_store_1, IFNULL(rented_at_store_2, 0) AS rented_at_store_2, IFNULL(returned_at_store_2, 0) AS returned_at_store_2, (IFNULL(rented_at_store_1, 0) + IFNULL(rented_at_store_2, 0)) AS total_rented, (IFNULL(returned_at_store_1, 0) + IFNULL(returned_at_store_2, 0)) AS total_returned
FROM dates LEFT JOIN store_1_discs_issued USING(date) LEFT JOIN store_2_discs_issued USING(date) LEFT JOIN store_1_discs_returned USING(date) LEFT JOIN store_2_discs_returned USING(date)
ORDER BY date;
Telegram: DB developers channel