Table: ParkingTransactions
+--------------+-----------+ | Column Name | Type | +--------------+-----------+ | lot_id | int | | car_id | int | | entry_time | datetime | | exit_time | datetime | | fee_paid | decimal | +--------------+-----------+ (lot_id, car_id, entry_time) is the primary key (combination of columns with unique values) for this table. Each row of this table contains the ID of the parking lot, the ID of the car, the entry and exit times, and the fee paid for the parking duration.
Write a solution to find the total parking fee paid by each car across all parking lots, and the average hourly fee (rounded to 2 decimal places) paid by each car. Also, find the parking lot where each car spent the most total time.
Return the result table ordered by car_id in ascending order.
Note: Test cases are generated in such a way that an individual car cannot be in multiple parking lots at the same time.
The result format is in the following example.
Example:
Input:
ParkingTransactions table:
+--------+--------+---------------------+---------------------+----------+ | lot_id | car_id | entry_time | exit_time | fee_paid | +--------+--------+---------------------+---------------------+----------+ | 1 | 1001 | 2023-06-01 08:00:00 | 2023-06-01 10:30:00 | 5.00 | | 1 | 1001 | 2023-06-02 11:00:00 | 2023-06-02 12:45:00 | 3.00 | | 2 | 1001 | 2023-06-01 10:45:00 | 2023-06-01 12:00:00 | 6.00 | | 2 | 1002 | 2023-06-01 09:00:00 | 2023-06-01 11:30:00 | 4.00 | | 3 | 1001 | 2023-06-03 07:00:00 | 2023-06-03 09:00:00 | 4.00 | | 3 | 1002 | 2023-06-02 12:00:00 | 2023-06-02 14:00:00 | 2.00 | +--------+--------+---------------------+---------------------+----------+
Output:
+--------+----------------+----------------+---------------+ | car_id | total_fee_paid | avg_hourly_fee | most_time_lot | +--------+----------------+----------------+---------------+ | 1001 | 18.00 | 2.40 | 1 | | 1002 | 6.00 | 1.33 | 2 | +--------+----------------+----------------+---------------+
Explanation:
Note: Output table is ordered by car_id in ascending order.
Problem Overview: You are given parking entry and exit records and need to compute how long each car stayed and the corresponding parking fee. The result requires pairing entry and exit events correctly, calculating the duration, and aggregating the total fee per vehicle.
Approach 1: Grouping + Joining (O(n log n) time, O(n) space)
The core idea is to reconstruct each car's parking session by matching entry and exit rows. In SQL, this is handled with a self join or derived table join where entry events are paired with their corresponding exit events using the car identifier. After pairing the rows, calculate the duration using a timestamp difference function and derive the fee from that duration. Finally, use GROUP BY to aggregate results per vehicle and return the total parking duration and fees.
This approach relies on relational database primitives rather than procedural logic. The JOIN step aligns entry and exit records, while aggregation condenses multiple sessions into a single row per car. Most SQL engines internally sort or hash during grouping, which typically leads to O(n log n) execution time depending on indexing and query plan. Memory usage is O(n) because intermediate joined rows must be materialized.
Using joins keeps the query expressive and scalable. Instead of scanning the dataset multiple times or relying on nested subqueries, the database optimizer can push filters, reuse indexes, and efficiently execute grouping operations. If the table has indexes on the car identifier and timestamps, the join becomes significantly faster.
Conceptually, the solution combines three common SQL techniques: matching rows with joins, computing derived values using timestamp arithmetic, and aggregating with GROUP BY. These patterns appear frequently in analytics-style database interview questions.
Recommended for interviews: The grouping + joining approach is the expected solution. Interviewers want to see that you can reconstruct relationships between rows using joins and perform aggregation correctly. A naive approach that processes rows individually shows understanding of the problem, but the SQL grouping solution demonstrates real database query design skills.
We can first group by car_id and lot_id to calculate the parking duration for each car in each parking lot. Then, we use the RANK() function to rank the parking duration of each car in each parking lot to find the parking lot where each car has the longest parking duration.
Finally, we can group by car_id to calculate the total parking fee, average hourly fee, and the parking lot with the longest parking duration for each car.
MySQL
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Grouping + Joining | O(n log n) | O(n) | Best general solution for SQL problems where entry and exit rows must be paired and aggregated. |
| Nested Subqueries | O(n²) | O(n) | Useful for small datasets or quick prototypes but inefficient for large tables. |
Leetcode MEDIUM 3166 - Parking Fees & Duration - Solved & Explained by Everyday Data Science • Everyday Data Science • 934 views views
Practice Calculate Parking Fees and Duration with our built-in code editor and test cases.
Practice on FleetCodePractice this problem
Open in Editor