get all customers who rented a movie last month, but not this month

            
              
            
            WITH last_month_rentals AS (
  SELECT DISTINCT customer_id
  FROM rental
  WHERE rental_date >= date_trunc('month', current_date - interval '1 month')
    AND rental_date < date_trunc('month', current_date)
),
this_month_rentals AS (
  SELECT DISTINCT customer_id
  FROM rental
  WHERE rental_date >= date_trunc('month', current_date)
    AND rental_date < date_trunc('month', current_date + interval '1 month')
)
SELECT c.*
FROM customer c
JOIN last_month_rentals lmr ON c.customer_id = lmr.customer_id
LEFT JOIN this_month_rentals tmr ON c.customer_id = tmr.customer_id
WHERE tmr.customer_id IS NULL;

The SQL statement uses two CTEs (Common Table Expressions) to find customers who rented a movie last month and this month.
It then joins the customer table with the last_month_rentals CTE and left joins with the this_month_rentals CTE.
The result includes all customers who rented a movie last month but not this month, as indicated by the WHERE tmr.customer_id IS NULL condition.