--Первый select
WITH customer_count_info AS (
SELECT
s.country,
c.customer_id,
COUNT(r.rental_id) AS num_films_rented,
ROW_NUMBER() OVER (PARTITION BY s.country ORDER BY COUNT(r.rental_id) DESC) AS rn
FROM customer c
JOIN address a ON c.address_id = a.address_id
JOIN city g ON a.city_id = g.city_id
JOIN country s ON g.country_id = s.country_id
JOIN rental r ON c.customer_id = r.customer_id
JOIN payment p ON r.customer_id = p.customer_id and r.rental_id = p.rental_id
GROUP BY s.country, c.customer_id
), 

/*
SELECT
country,
customer_id,
num_films_rented
FROM customer_count_info
WHERE rn = 1
ORDER BY country;
*/



-- второй select
customer_rental_info AS (
SELECT
s.country,
c.customer_id,
sum(p.amount) AS max_sum_rented,
ROW_NUMBER() OVER (PARTITION BY s.country ORDER BY COUNT(r.rental_id) DESC) AS rn
FROM customer c
JOIN address a ON c.address_id = a.address_id
JOIN city g ON a.city_id = g.city_id
JOIN country s ON g.country_id = s.country_id
JOIN rental r ON c.customer_id = r.customer_id
JOIN payment p ON r.customer_id = p.customer_id and r.rental_id = p.rental_id
GROUP BY s.country, c.customer_id
), 

/*
SELECT
country,
customer_id,
max_sum_rented
FROM customer_rental_info
WHERE rn = 1
ORDER BY country;
*/




-- третий select
customer_last_rental AS (
SELECT
s.country,
c.customer_id,
r.rental_date,
ROW_NUMBER() OVER (PARTITION BY s.country ORDER BY r.rental_date DESC) AS rn
FROM customer c
JOIN address a ON c.address_id = a.address_id
JOIN city g ON a.city_id = g.city_id
JOIN country s ON g.country_id = s.country_id
JOIN rental r ON c.customer_id = r.customer_id
)

/*
SELECT
country,
customer_id,
rental_date
FROM customer_last_rental
WHERE rn = 1
ORDER BY country;
*/




--РАБОТАЕТ, ЕСЛИ РАЗОБРАТЬСЯ, ЧТО ИСПОЛЬЗУЕТСЯ В ЭТОМ ЗАПРОСЕ. 
SELECT c1.country, c.last_name, c.first_name, 
       coalesce(c1.num_films_rented, 0) as num_films_rented, 
       coalesce(c2.max_sum_rented, 0) as max_sum_rented, 
       c3.rental_date   /* -----    тут тоже надо типа coalesce(c3.rental_date, cast(0 as date) ) as rental_date, -----  */ 
  from customer_count_info c1
       left join customer_rental_info c2 ON c1.country = c2.country and c1.customer_id = c2.customer_id
       left join customer_last_rental c3 ON c1.country = c3.country and c1.customer_id = c3.customer_id
       left join customer c on c1.customer_id=c.customer_id 
where c1.rn=1 and c2.rn=1 and c3.rn=1
order by c1.country, c.last_name, c.first_name

--Первый select
WITH customer_count_info AS (
SELECT
s.country,
c.customer_id,
COUNT(r.rental_id) AS num_films_rented,
ROW_NUMBER() OVER (PARTITION BY s.country ORDER BY COUNT(r.rental_id) DESC) AS rn
FROM customer c
JOIN address a ON c.address_id = a.address_id
JOIN city g ON a.city_id = g.city_id
JOIN country s ON g.country_id = s.country_id
JOIN rental r ON c.customer_id = r.customer_id
JOIN payment p ON r.customer_id = p.customer_id and r.rental_id = p.rental_id
GROUP BY s.country, c.customer_id
)
,
-- второй select
with customer_rental_info AS (
SELECT
s.country,
c.customer_id,
sum(p.amount) AS max_sum_rented,
ROW_NUMBER() OVER (PARTITION BY s.country ORDER BY COUNT(r.rental_id) DESC) AS rn
FROM customer c
JOIN address a ON c.address_id = a.address_id
JOIN city g ON a.city_id = g.city_id
JOIN country s ON g.country_id = s.country_id
JOIN rental r ON c.customer_id = r.customer_id
JOIN payment p ON r.customer_id = p.customer_id and r.rental_id = p.rental_id
GROUP BY s.country, c.customer_id
)
,
-- третий select
with customer_last_rental AS (
SELECT
s.country,
c.customer_id,
r.rental_date,
ROW_NUMBER() OVER (PARTITION BY s.country ORDER BY r.rental_date DESC) AS rn
FROM customer c
JOIN address a ON c.address_id = a.address_id
JOIN city g ON a.city_id = g.city_id
JOIN country s ON g.country_id = s.country_id
JOIN rental r ON c.customer_id = r.customer_id
)
SELECT *
FROM
customer_count_info c1
left join customer_rental_info c2
ON c1.country = c2.country and c2.rn = 1 and c1.customer_id = c2.customer_id
left join customer_last_rental c3
ON c1.country = c3.country and c3.rn = 1 and c1.customer_id = c3.customer_id

where c1.rn = 1

;with