Read-only
Can you get the rental data for the past 6 months for the top films and only for the customers who lives in New York city using lat/lng?
●Reviewing your schemadone0ms

Assuming “top films” means the 10 most-rented films among NYC customers during the rolling past six months, this returns individual rental records with each film’s rental count.

NYC is approximated using a latitude/longitude bounding box; it can include nearby locations outside city limits. Exact filtering requires an NYC boundary polygon. Results need a live database run.

sql
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
    JOIN inventory AS i
        ON i.inventory_id = r.inventory_id
    JOIN film AS f
        ON f.film_id = i.film_id
    WHERE r.rental_date >= CURRENT_TIMESTAMP - INTERVAL '6 months'
      AND r.rental_date < CURRENT_TIMESTAMP
      -- Approximate bounding box for New York City's five boroughs.
      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
    FROM nyc_rentals
    GROUP BY film_id
    ORDER BY rental_count_past_6_months DESC, film_id
    LIMIT 10
)
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
ORDER BY
    tf.rental_count_past_6_months DESC,
    nr.film_id,
    nr.rental_date DESC,
    nr.rental_id;

12s

Lets expand it to a whole year
●Reviewing your schemadone0ms

Updated to the past 12 months, keeping the top 10 films ranked by rentals among customers within the same approximate NYC latitude/longitude bounding box. The bounding box may include nearby areas outside city limits. Results require a live database run.

sql
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
    JOIN inventory AS i
        ON i.inventory_id = r.inventory_id
    JOIN film AS f
        ON f.film_id = i.film_id
    WHERE r.rental_date >= CURRENT_TIMESTAMP - INTERVAL '1 year'
      AND r.rental_date < CURRENT_TIMESTAMP
      -- Approximate bounding box for New York City's five boroughs.
      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_year
    FROM nyc_rentals
    GROUP BY film_id
    ORDER BY rental_count_past_year DESC, film_id
    LIMIT 10
)
SELECT
    nr.film_id,
    nr.title,
    tf.rental_count_past_year,
    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
ORDER BY
    tf.rental_count_past_year DESC,
    nr.film_id,
    nr.rental_date DESC,
    nr.rental_id;

6s

And all locations
●Reviewing your schemadone0ms

Removed the NYC filter: this returns rental records for the top 10 most-rented films across all locations over the past year, retaining customer latitude/longitude. Results require a live database run.

sql
WITH past_year_rentals AS (
    SELECT
        r.rental_id,
        r.rental_date,
        r.return_date,
        r.customer_id,
        i.store_id,
        i.film_id
    FROM rental AS r
    JOIN inventory AS i
        ON i.inventory_id = r.inventory_id
    WHERE r.rental_date >= CURRENT_TIMESTAMP - INTERVAL '1 year'
      AND r.rental_date < CURRENT_TIMESTAMP
),
top_films AS (
    SELECT
        film_id,
        COUNT(*) AS rental_count_past_year
    FROM past_year_rentals
    GROUP BY film_id
    ORDER BY rental_count_past_year DESC, film_id
    LIMIT 10
)
SELECT
    pr.film_id,
    f.title,
    tf.rental_count_past_year,
    pr.rental_id,
    pr.rental_date,
    pr.return_date,
    pr.customer_id,
    c.first_name,
    c.last_name,
    a.latitude,
    a.longitude,
    pr.store_id
FROM past_year_rentals AS pr
JOIN top_films AS tf
    ON tf.film_id = pr.film_id
JOIN film AS f
    ON f.film_id = pr.film_id
JOIN customer AS c
    ON c.customer_id = pr.customer_id
LEFT JOIN address AS a
    ON a.address_id = c.address_id
ORDER BY
    tf.rental_count_past_year DESC,
    pr.film_id,
    pr.rental_date DESC,
    pr.rental_id;

7s

Shared threads are read-only. Start your own chat to keep working.

Start a new chat