WITH nyc_rentals AS (
    SELECT
        r.rental_id,
        r.rental_date,
        r.return_date,
        r.customer_id,
        c.first_name,
        c.last_name,
        a.latitude,
        a.longitude,
        i.store_id,
        f.film_id,
        f.title
    FROM rental AS r
    JOIN customer AS c
        ON c.customer_id = r.customer_id
    JOIN address AS a
        ON a.address_id = c.address_id  -- Customer's address, not store location.
    JOIN inventory AS i
        ON i.inventory_id = r.inventory_id
    JOIN film AS f
        ON f.film_id = i.film_id
    -- Include the lower time boundary, but exclude the current instant.
    WHERE r.rental_date >= CURRENT_TIMESTAMP - INTERVAL '6 months'
      AND r.rental_date < CURRENT_TIMESTAMP
      -- Inclusive rectangular bounds approximating NYC.
      AND a.latitude BETWEEN 40.4774 AND 40.9176
      AND a.longitude BETWEEN -74.2591 AND -73.7004
),
top_films AS (
    SELECT
        film_id,
        COUNT(*) AS rental_count_past_6_months -- Count rental events per film.
    FROM nyc_rentals
    GROUP BY film_id
    ORDER BY rental_count_past_6_months DESC, film_id
    LIMIT 10 -- At most 10 films; ties do not expand this limit.
)
SELECT
    nr.film_id,
    nr.title,
    tf.rental_count_past_6_months,
    nr.rental_id,
    nr.rental_date,
    nr.return_date,
    nr.customer_id,
    nr.first_name,
    nr.last_name,
    nr.latitude,
    nr.longitude,
    nr.store_id
FROM nyc_rentals AS nr
JOIN top_films AS tf
    ON tf.film_id = nr.film_id -- Keep every qualifying rental of a top film.
ORDER BY
    tf.rental_count_past_6_months DESC,
    nr.film_id,
    nr.rental_date DESC,
    nr.rental_id; -- Final tie-breaker for rentals with the same timestamp.