Calculate Parking Fees and Duration - Problem
You are given a table ParkingTransactions that contains information about parking transactions for different cars across multiple parking lots.
Write a solution to find:
- The total parking fee paid by each car across all parking lots
- The average hourly fee (rounded to 2 decimal places) paid by each car
- 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 ensure that an individual car cannot be in multiple parking lots at the same time.
Table Schema
ParkingTransactions
| Column Name | Type | Description |
|---|---|---|
lot_id
PK
|
int | ID of the parking lot |
car_id
PK
|
int | ID of the car |
entry_time
PK
|
datetime | Entry timestamp for parking |
exit_time
|
datetime | Exit timestamp for parking |
fee_paid
|
decimal | Fee paid for the parking duration |
Primary Key: (lot_id, car_id, entry_time)
Note: Each row represents a parking transaction with unique combination of lot_id, car_id, and entry_time
Input & Output
Example 1 — Multiple Cars, Multiple Lots
Input 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 |
| 1 | 1001 | 2023-06-02 11:00:00 | 2023-06-02 12:45:00 | 3 |
| 2 | 1001 | 2023-06-01 10:45:00 | 2023-06-01 12:00:00 | 6 |
| 2 | 1002 | 2023-06-01 09:00:00 | 2023-06-01 11:30:00 | 4 |
| 3 | 1001 | 2023-06-03 07:00:00 | 2023-06-03 09:00:00 | 4 |
| 3 | 1002 | 2023-06-02 12:00:00 | 2023-06-02 14:00:00 | 2 |
Output:
| car_id | total_fee_paid | avg_hourly_fee | most_time_lot |
|---|---|---|---|
| 1001 | 18 | 2.4 | 1 |
| 1002 | 6 | 1.33 | 2 |
💡 Note:
Car 1001 has 4 transactions totaling $18.00 across 7.5 hours (avg $2.40/hour). Most time spent in lot 1 (4.25 hours). Car 1002 has 2 transactions totaling $6.00 across 4.5 hours (avg $1.33/hour). Most time spent in lot 2 (2.5 hours).
Example 2 — Single Transaction Per Car
Input Table:
| lot_id | car_id | entry_time | exit_time | fee_paid |
|---|---|---|---|---|
| 1 | 2001 | 2023-06-01 14:00:00 | 2023-06-01 16:00:00 | 8 |
| 2 | 2002 | 2023-06-01 10:00:00 | 2023-06-01 10:30:00 | 2.5 |
Output:
| car_id | total_fee_paid | avg_hourly_fee | most_time_lot |
|---|---|---|---|
| 2001 | 8 | 4 | 1 |
| 2002 | 2.5 | 5 | 2 |
💡 Note:
Each car has only one transaction. Car 2001 paid $8.00 for 2 hours ($4.00/hour) in lot 1. Car 2002 paid $2.50 for 0.5 hours ($5.00/hour) in lot 2. Most time lot equals the only lot used.
Constraints
-
1 ≤ lot_id ≤ 100 -
1 ≤ car_id ≤ 10000 -
entry_time < exit_time -
fee_paid ≥ 0 - A car cannot be in multiple parking lots at the same time
Visualization
Tap to expand
Understanding the Visualization
1
Input Data
Parking transactions with times and fees
2
Calculate Hours
Convert time differences to hours
3
Aggregate Results
Sum fees, calculate averages, find max lots
Key Takeaway
🎯 Key Insight: Use CTEs to break down complex calculations into manageable steps: duration calculation, aggregation by car, and finding maximum values per group.
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code