SQL Interview Prep

Finding the Cheapest and Most Expensive Rentals in a Car Rental Service

As I was preparing for my Quality Engineer - SDET assessment, I came across a unique SQL problem that was both challenging and insightful. The task was to find the cheapest and most expensive car rentals for each rental location based on their daily rental prices from a given dataset. This problem not only tested my SQL skills but also helped me understand how to manipulate and analyze data effectively. In this blog post, I'll walk you through my thought process, the problem statement, the table structure, and the solution.

Problem Statements

The challenge was to write a SQL query that identifies the cheapest and most expensive car rentals in each location based on their daily rental prices. The query should return the location ID, the ID of the cheapest car, and the ID of the most expensive car.

Table Structure

The table, named car_rentals, has the following structure:

Sample Data

Here's the sample data from the car_rentals table that we will use for this problem:

Solution

To solve this problem, I needed to write a SQL query that identifies the cheapest and most expensive car rentals for each location based on their daily prices. This involved finding the minimum and maximum prices for daily rentals, and then retrieving the corresponding car IDs.

Solution Breakdown

Step 1: Identify Minimum and Maximum Prices

The first step is to create subqueries to identify the minimum and maximum daily prices for rentals at each location. We use the MIN() and MAX() functions to find these prices.

SELECT
    location_id,
    MIN(daily_price) AS min_price
FROM
    car_rentals
GROUP BY
    location_id;

This subquery groups the data by location_id and finds the minimum daily_price for each location.

SELECT
    location_id,
    MAX(daily_price) AS max_price
FROM
    car_rentals
GROUP BY
    location_id;

Similarly, this subquery groups the data by location_id and finds the maximum daily_price for each location.

Step 2: Retrieve Car IDs for Minimum and Maximum Prices

After identifying the minimum and maximum prices, the next step is to retrieve the corresponding car_id for these prices. We need to join these subqueries back to the original table to get the car IDs.

SELECT
    cr.location_id,
    cr.car_id
FROM
    car_rentals cr
JOIN (
    SELECT
        location_id,
        MIN(daily_price) AS min_price
    FROM
        car_rentals
    GROUP BY
        location_id
) min_prices ON cr.location_id = min_prices.location_id
AND cr.daily_price = min_prices.min_price;

This query joins the original table with the subquery for minimum prices to get the car_id for the cheapest rentals.

SELECT
    cr.location_id,
    cr.car_id
FROM
    car_rentals cr
JOIN (
    SELECT
        location_id,
        MAX(daily_price) AS max_price
    FROM
        car_rentals
    GROUP BY
        location_id
) max_prices ON cr.location_id = max_prices.location_id
AND cr.daily_price = max_prices.max_price;

Similarly, this query joins the original table with the subquery for maximum prices to get the car_id for the most expensive rentals.

Step 3: Joining Subqueries with Original Table

Finally, we need to join the results of the subqueries for minimum and maximum prices with the original table to produce the final result.

SELECT
    r.location_id,
    cheapest_cars.car_id AS cheapest_car,
    most_expensive_cars.car_id AS most_expensive_car
FROM
    car_rentals r
JOIN (
    SELECT
        cr.location_id,
        cr.car_id
    FROM
        car_rentals cr
    JOIN (
        SELECT
            location_id,
            MIN(daily_price) AS min_price
        FROM
            car_rentals
        GROUP BY
            location_id
    ) min_prices ON cr.location_id = min_prices.location_id
    AND cr.daily_price = min_prices.min_price
) AS cheapest_cars ON r.location_id = cheapest_cars.location_id
JOIN (
    SELECT
        cr.location_id,
        cr.car_id
    FROM
        car_rentals cr
    JOIN (
        SELECT
            location_id,
            MAX(daily_price) AS max_price
        FROM
            car_rentals
        GROUP BY
            location_id
    ) max_prices ON cr.location_id = max_prices.location_id
    AND cr.daily_price = max_prices.max_price
) AS most_expensive_cars ON r.location_id = most_expensive_cars.location_id
GROUP BY
    r.location_id,
    cheapest_cars.car_id,
    most_expensive_cars.car_id;

Resouces

Link: Group-By Explained

Link: Min-Max

Comments