WITH RentalsForCustomer AS (
SELECT
f.title,
r.rental_date,
r.return_date,
p.payment_date,
f.rental_rate,
p.amount
FROM film f
JOIN inventory i ON f.film_id = i.film_id
JOIN rental r ON i.inventory_id = r.inventory_id
JOIN payment p ON r.rental_id = p.rental_id
JOIN customer c ON r.customer_id = c.customer_id
WHERE c.first_name = 'DOROTHY' AND c.last_name = 'TAYLOR'
AND r.rental_date >= '2005-08-01' AND r.rental_date < '2005-09-01'
),
RentalWithPenalty AS (
SELECT
rc.title,
rc.rental_date,
rc.return_date,
rc.payment_date,
rc.rental_rate,
rc.amount,
-- Пересчитываем штраф за просрочку:
CASE
WHEN DATEDIFF(rc.return_date, rc.rental_date) > 0 THEN
GREATEST(DATEDIFF(rc.return_date, rc.rental_date), 0) * rc.rental_rate
ELSE 0
END AS lateness_penalty
FROM RentalsForCustomer rc
)
SELECT
title,
rental_date,
return_date,
payment_date,
rental_rate,
lateness_penalty,
amount
FROM RentalWithPenalty
UNION ALL
(
SELECT
'Total',
NULL,
NULL,
NULL,
SUM(rental_rate),
SUM(lateness_penalty),
SUM(amount)
FROM RentalWithPenalty
)
ORDER BY
CASE
WHEN title = 'Total' THEN 1 -- Ставим итоговую строку последней
ELSE 0 -- Другие строки идут сверху
END DESC,
FIELD(title, 'WEST LION'), -- Сперва выводим WEST LION
rental_date ASC; -- Остальное сортируем по дате аренды
;
show status like 'Last_query_cost';
Telegram: DB developers channel