Problem

Table: ParkingTransactions

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

+--------------+-----------+
| 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 bycar_id **** inascending 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.

Examples

Example 1:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

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.

Solution

Method 1 – Aggregation and Window Functions

Intuition

We need to aggregate data per car: total fee, average hourly fee, and the lot with the most time spent. We use SUM for total fee, calculate total hours for average fee, and use window functions to find the lot with the maximum total time for each car.

Approach

  1. For each row, calculate the duration in hours as TIMESTAMPDIFF(MINUTE, entry_time, exit_time) / 60.
  2. Aggregate by car_id:
    • SUM(fee_paid) as total_fee
    • SUM(duration) as total_hours
    • AVG hourly fee = total_fee / total_hours (rounded to 2 decimals)
  3. For each car and lot, sum the total duration. Use ROW_NUMBER() to pick the lot with the most time for each car.
  4. Join the aggregates with the lot info.
  5. Return car_id, total_fee, average_hourly_fee, and lot_id with most time, ordered by car_id.

Code

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

WITH durations AS (
  SELECT
    car_id,
    lot_id,
    fee_paid,
    TIMESTAMPDIFF(MINUTE, entry_time, exit_time) / 60 AS hours
  FROM ParkingTransactions
),
car_agg AS (
  SELECT
    car_id,
    ROUND(SUM(fee_paid), 2) AS total_fee,
    SUM(hours) AS total_hours,
    ROUND(SUM(fee_paid) / NULLIF(SUM(hours), 0), 2) AS average_hourly_fee
  FROM durations
  GROUP BY car_id
),
lot_time AS (
  SELECT
    car_id,
    lot_id,
    SUM(hours) AS lot_hours,
    ROW_NUMBER() OVER (PARTITION BY car_id ORDER BY SUM(hours) DESC, lot_id ASC) AS rn
  FROM durations
  GROUP BY car_id, lot_id
)
SELECT
  c.car_id,
  c.total_fee,
  c.average_hourly_fee,
  l.lot_id AS most_time_lot
FROM car_agg c
JOIN lot_time l ON c.car_id = l.car_id AND l.rn = 1
ORDER BY c.car_id ASC;

Complexity

  • ⏰ Time complexity: O(n) — n is the number of rows in ParkingTransactions (single scan and group by).
  • 🧺 Space complexity: O(n) — For intermediate aggregates.