Loading problem...
A mobility analytics team maintains parking visit logs across multiple parking facilities and wants a per-car financial and utilization summary.
Table: ParkingTransactions
Task: For every car_id, produce exactly one summary row with:
Duration definition:
Output requirements:
Supported submission environments:
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 |[
{"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.
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 |[
{"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.
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 |[
{"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