I'm developing a car rental automation system in SQL, and I'm seeing unwanted data in my table that I've received and constantly updated. The query I need to write is as follows: 'Write a query that retrieves information about the last rented car for customers who have rented cars with the feature of a sunroof at least once.' I've been struggling with it for hours and couldn't solve it. Can you help me?
select * from car a
where exists(
    select * from car a2
    inner join customer m on m.customer_id=a2.customer_id
    inner join rent k on k.customer_id=m.customer_id
    inner join car_rent ak on ak.rent_id=k.rent_id
    inner join package_package_option pk on pk.package_id=a2.package_id
    where pk.option_id=2 and a.rent_sell=1
    group by ak.date
    having ak.date = max(ak.date)


SQL query for last rented car with sunroof feature in car rental system
The solution has two steps:

Identify all customers who have ever rented a car with a sunroof, and
For each such customer, look up the latest rental for each such customer.

The first step is pretty straight forward - Filter the rentals for sunroof and select distinct customer IDs.
The second step can be done a couple of ways. One is to feed the customer IDs into a CROSS APPLY (SELECT TOP 1 ... ORDER BY RentalDate DESC) construct to select the latest rental for each selected customer one at a time. Another is to lookup all rentals for the selected customers, assign sequence numbers using the ROW_NUMBER() window function OVER(... ORDER BY RentalDate DESC), and then filtering for row-number = 1.
Something like (pseudocode):
SELECT *
FROM (
    SELECT DISTINCT customer_id
    FROM rentals
    WHERE has-a-sunroof
    AND is-a-rental
) C
CROSS APPLY (
    SELECT TOP 1 rental-info
    FROM rentals R
    WHERE R.customer_id = C.customer_id
    AND is-a-rental
    ORDER BY R.Rentaldate DESC
) R

or
SELECT *
FROM (
    SELECT rental-info,
        ROW_NUMBER() OVER(PARTITION BY R.customer_id ORDER BY R.Rentaldate DESC) RowNum
    FROM rentals R
    WHERE R.customer_id IN (
        SELECT DISTINCT customer_id
        FROM rentals
        WHERE has-a-sunroof
        AND is-a-rental
    )
    AND is-a-rental
) A
WHERE A.RowNum = 1

You might try both to compare performance with large data sets.  I recommend also ensuring that you have an index on the rental that includes both customer_id and rental date for best performance.
