Fleet Parking Fee and Duration Summary

Medium

A mobility analytics team maintains parking visit logs across multiple parking facilities and wants a per-car financial and utilization summary. Table: ParkingTransactions lot_id: parking lot identifier car_id: vehicle identifier entry_time: visit start timestamp (YYYY-MM-DD HH:MM:SS) exit_time: visit end timestamp (YYYY-MM-DD HH:MM:SS) fee_paid: amount charged for that visit Task: For every car_id, produce exactly one summary row with: total_fee_paid: sum of all visit fees for that car avg_hourly_fee: effective hourly parking spend for that car avg_hourly_fee = total_fee_paid / total_hours_parked round to exactly 2 decimal places most_time_lot: the lot where the car accumulated the highest total parked duration if multiple lots tie on total parked time, return the smallest lot_id Duration definition: Visit duration is (exit_time - entry_time) measured in seconds. total_hours_parked = total_duration_seconds / 3600. Output requirements: Return columns in this exact order: car_id, total_fee_paid, avg_hourly_fee, most_time_lot Return rows ordered by car_id ascending. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

ParkingTransactions:
| 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":1001,"total_fee_paid":18.0,"avg_hourly_fee":2.4,"most_time_lot":1},
  {"car_id":1002,"total_fee_paid":6.0,"avg_hourly_fee":1.33,"most_time_lot":2}
]

Explanation

Car 1001 spends the most total time in lot 1; hourly fee is computed from total fee divided by total parked hours.

Example 2

Input

ParkingTransactions:
| lot_id | car_id | entry_time           | exit_time            | fee_paid |
|--------|--------|----------------------|----------------------|----------|
| 3      | 2001   | 2024-01-01 08:00:00  | 2024-01-01 10:00:00  | 5.00     |
| 5      | 2001   | 2024-01-01 11:00:00  | 2024-01-01 14:00:00  | 4.50     |
| 3      | 2001   | 2024-01-02 09:00:00  | 2024-01-02 10:00:00  | 3.00     |

Output

[
  {"car_id":2001,"total_fee_paid":12.5,"avg_hourly_fee":2.08,"most_time_lot":3}
]

Explanation

Lot 3 and lot 5 both have 3 total hours. The tie-breaker selects the smaller lot_id, so most_time_lot is 3.

Example 3

Input

ParkingTransactions:
| lot_id | car_id | entry_time           | exit_time            | fee_paid |
|--------|--------|----------------------|----------------------|----------|
| 9      | 3001   | 2024-02-10 06:00:00  | 2024-02-10 07:00:00  | 10.00    |
| 9      | 3001   | 2024-02-10 09:00:00  | 2024-02-10 18:00:00  | 9.00     |
| 8      | 3001   | 2024-02-11 10:00:00  | 2024-02-11 12:00:00  | 2.00     |

Output

[
  {"car_id":3001,"total_fee_paid":21.0,"avg_hourly_fee":1.75,"most_time_lot":9}
]

Explanation

Average hourly fee must use total fee and total duration, not an unweighted average of per-visit hourly fees.

Constraints

1 <= number of rows in ParkingTransactions <= 300000
1 <= lot_id <= 2000000000
1 <= car_id <= 2000000000
fee_paid is non-negative and can include cents
entry_time and exit_time follow YYYY-MM-DD HH:MM:SS
For every row, exit_time is strictly later than entry_time
(lot_id, car_id, entry_time) is unique
For each car, visits do not overlap in time across lots
If two or more lots tie on total parked time for a car, choose the smallest lot_id
Output columns must be exactly: car_id, total_fee_paid, avg_hourly_fee, most_time_lot
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns [car_id, total_fee_paid, avg_hourly_fee, most_time_lot]

ParkingTransactions: | 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 |

ParkingTransactions: | lot_id | car_id | entry_time | exit_time | fee_paid | |--------|--------|----------------------|----------------------|----------| | 3 | 2001 | 2024-01-01 08:00:00 | 2024-01-01 10:00:00 | 5.00 | | 5 | 2001 | 2024-01-01 11:00:00 | 2024-01-01 14:00:00 | 4.50 | | 3 | 2001 | 2024-01-02 09:00:00 | 2024-01-02 10:00:00 | 3.00 |

ParkingTransactions: | lot_id | car_id | entry_time | exit_time | fee_paid | |--------|--------|----------------------|----------------------|----------| | 9 | 3001 | 2024-02-10 06:00:00 | 2024-02-10 07:00:00 | 10.00 | | 9 | 3001 | 2024-02-10 09:00:00 | 2024-02-10 18:00:00 | 9.00 | | 8 | 3001 | 2024-02-11 10:00:00 | 2024-02-11 12:00:00 | 2.00 |