Parking Fees
Start Timer
0:00:00
Given a table of parking sessions, write a query to calculate the total payable amount per user. The system records two event types: entry and departure.
Pricing Structure:
- Base fee: $3 for the first two hours
- Standard rate: $3 per hour after the first two hours
- Partial hours: $2 for any incomplete hour
- Overnight parking: $100 flat fee (when a session spans across midnight) and is additive to the current fee. Can be applied more than once in a single session.
Each user has at least one parking session.
Note: Overnight means a session crossing midnight.
Schema:
Input :
Table parking_sessions
| Column | Type |
|---|---|
| user_id | INTEGER |
| event_type | VARCHAR |
| timestamp | DATETIME |
Output :
| Column | Type |
|---|---|
| user_id | INTEGER |
| total_amount | DECIMAL |
Example:
Input:
parking_sessions table
| user_id | event_type | timestamp |
|---|---|---|
| 101 | entry | 2026-01-15 09:00:00 |
| 101 | departure | 2026-01-15 11:30:00 |
| 102 | entry | 2026-01-15 14:00:00 |
| 102 | departure | 2026-01-15 19:15:00 |
| 103 | entry | 2026-01-15 22:00:00 |
| 103 | departure | 2026-01-16 02:30:00 |
| 101 | entry | 2026-01-16 10:00:00 |
| 101 | departure | 2026-01-16 11:00:00 |
Output:
| user_id | total_amount |
|---|---|
| 101 | 8.00 |
| 102 | 14.00 |
| 103 | 111.00 |
Explanation:
- User 101
- Session 1: 2.5 hours → $3 (first 2 hrs) + $2 (0.5 partial hr) = $5
- Session 2: 1 hour → $3 (within first 2 hrs) = $3
- Total: $5 + $3 = $8.00
- User 102
- 5.25 hours → $3 (first 2 hrs) + $9 (3 full hrs × $3) + $2 (0.25 partial hr) = $14.00
- User 103
- 4.5 hours crossing midnight → $3 (first 2 hrs) + $6 (2 full hrs × $3) + $2 (0.5 partial hr) + $100 (overnight fee) = $111.00
.
.
.
.
.
.
.
.
.
Comments