Skip to main content
Talentd

Calculate Parking Fees and Duration - Solution & Explanation

MediumPremiumFree on FleetCodeDatabase5 min read
Practice this problem

Problem Statement

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:

  • For car ID 1001:
    • From 2023-06-01 08:00:00 to 2023-06-01 10:30:00 in lot 1: 2.5 hours, fee 5.00
    • From 2023-06-02 11:00:00 to 2023-06-02 12:45:00 in lot 1: 1.75 hours, fee 3.00
    • From 2023-06-01 10:45:00 to 2023-06-01 12:00:00 in lot 2: 1.25 hours, fee 6.00
    • From 2023-06-03 07:00:00 to 2023-06-03 09:00:00 in lot 3: 2 hours, fee 4.00
    Total fee paid: 18.00, total hours: 7.5, average hourly fee: 2.40, most time spent in lot 1: 4.25 hours.
  • For car ID 1002:
    • From 2023-06-01 09:00:00 to 2023-06-01 11:30:00 in lot 2: 2.5 hours, fee 4.00
    • From 2023-06-02 12:00:00 to 2023-06-02 14:00:00 in lot 3: 2 hours, fee 2.00
    Total fee paid: 6.00, total hours: 4.5, average hourly fee: 1.33, most time spent in lot 2: 2.5 hours.

Note: Output table is ordered by car_id in ascending order.

Approach Overview

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.

Solution

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.

Code

MySQL

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
Grouping + JoiningO(n log n)O(n)Best general solution for SQL problems where entry and exit rows must be paired and aggregated.
Nested SubqueriesO(n²)O(n)Useful for small datasets or quick prototypes but inefficient for large tables.

Video Solution

Leetcode MEDIUM 3166 - Parking Fees & Duration - Solved & Explained by Everyday Data Science • Everyday Data Science • 974 views views

Frequently Asked Questions

Is Calculate Parking Fees and Duration easy or hard?
The problem is classified as Medium because it requires combining multiple SQL concepts: joins, timestamp calculations, and aggregation. Developers comfortable with GROUP BY and JOIN operations usually solve it quickly, while beginners may struggle with correctly pairing entry and exit rows.
Calculate Parking Fees and Duration Python/Java solution
This problem is primarily designed for SQL. In Python or Java, you would simulate the logic using a hash map keyed by car ID, track entry and exit timestamps, compute durations, and accumulate total fees. The complexity would be roughly O(n) time and O(n) space.
How to solve Calculate Parking Fees and Duration in O(n)?
Achieving near O(n) performance depends on database indexing. Create indexes on the car identifier and timestamp columns so the join operation can be executed using indexed lookups. The query then pairs entry and exit rows, computes duration using a timestamp difference function, and aggregates results with GROUP BY.
What is the best approach for Calculate Parking Fees and Duration?
The most efficient solution uses SQL joins combined with GROUP BY aggregation. Entry and exit rows are paired using a join on the car identifier, the duration is calculated with timestamp difference functions, and results are aggregated per vehicle. This approach typically runs in O(n log n) time depending on database execution plans.
Is Calculate Parking Fees and Duration asked at Google/Amazon/Meta?
Database aggregation and log analysis problems like this frequently appear in interviews at large tech companies. Variations involving session reconstruction, event pairing, and duration calculations are common in SQL interview rounds at companies such as Amazon, Google, and Meta.
What data structure is used in Calculate Parking Fees and Duration?
The problem relies on relational database operations rather than traditional in-memory data structures. SQL joins act like hash or merge joins internally, and GROUP BY behaves similarly to hash maps that aggregate rows by key.
What is the time complexity of Calculate Parking Fees and Duration?
The typical SQL solution runs in O(n log n) time because the database must join rows and perform grouping operations. If indexes exist on the join columns and timestamps, the effective runtime can approach O(n). Space complexity is O(n) for intermediate joined rows.

Ready to solve this problem?

Practice Calculate Parking Fees and Duration with our built-in code editor and test cases.

Practice on FleetCode
Previous

Combine Two Tables

Next

Second Highest Salary