List Top Daily Rentals Per Customer using a CTE I've been trying to learn CTEs and having a bit or trouble getting the hang of them.  I wrote a query against the Sakila Sample database that lists information about horror movie rentals for each day. 

Here is the overblown (and redundant) SQL that I came up with:

SELECT CONCAT(CU.last_name, ', ', CU.first_name) AS customer,
        A.phone, 
        F.title, 
        date(R.rental_date) AS rental_date
 FROM sakila.rental R 
     LEFT JOIN sakila.inventory I ON R.inventory_id = I.inventory_id 
     LEFT JOIN sakila.film F ON I.film_id = F.film_id 
     LEFT JOIN sakila.film_category FC on F.film_id = FC.film_id
     LEFT JOIN sakila.category C ON FC.category_id = C.category_id 
     LEFT JOIN sakila.customer CU ON R.customer_id = CU.customer_id
     LEFT JOIN sakila.address A ON CU.address_id = A.address_id
 WHERE CU.customer_id in 
       (SELECT CU.customer_id
        FROM rental R
        LEFT JOIN sakila.customer CU ON R.customer_id = CU.customer_id
        LEFT JOIN sakila.inventory I ON R.inventory_id = I.inventory_id 
        LEFT JOIN sakila.film F ON I.film_id = F.film_id 
        LEFT JOIN sakila.film_category FC on F.film_id = FC.film_id
        LEFT JOIN sakila.category C ON FC.category_id = C.category_id 
        WHERE C.name = "Horror"
        GROUP BY CU.customer_id
        HAVING COUNT(CU.customer_id) &gt;= 3)
 AND C.name = "Horror"
 ORDER BY customer, title, rental_date DESC;


And here are some of the results in my database client (Navicat for MySQL):



Is there a way to rewrite the query using a CTE?  

It seems like just the type of query that a CTE would be perfect for, if only I could figure it out!

Thanks!
# horror film rental(answer)
select cus_dtl.name, cus_dtl.phone, hf_cus.title, hf_cus.rental_date, hf_cus.name
from (select c.customer_id, concat(c.first_name, ',', c.last_name) as name, a.phone
      from customer as c, address as a
      where c.address_id = a.address_id) as cus_dtl, (select r.customer_id, r.rental_date, hf_inve.title, hf_inve.name
                                                      from rental as r, (select i.inventory_id, hf_title.film_id, hf_title.title, hf_title.name
                                                                         from inventory as i, (select f.title, horror_FID.film_id, horror_FID.name
                                                                                               from film as f, (select fc.film_id, horror.name
                                                                                                                from film_category as fc, (select category_id, name
                                                                                                                                           from category
                                                                                                                                           where name = 'horror') as horror
                                                                                                                where fc.category_id = horror.category_id) as horror_FID
                                                                                               where f.film_id = horror_FID.film_id) as hf_title
                                                                         where i.film_id = hf_title.film_id) as hf_inve
                                                      where r.inventory_id = hf_inve.inventory_id) as hf_cus
where hf_cus.customer_id = cus_dtl.customer_id
order by cus_dtl.name, hf_cus.title, hf_cus.rental_date desc;


using sub queries..