Calculate Parking Fees and Duration - Practice Coding Problems

Calculate Parking Fees and Duration - Problem

Database Medium

🚗 Smart Parking Analytics System

You're working for a smart parking management company that operates multiple parking lots across the city. Your task is to analyze parking transaction data to generate comprehensive insights for each vehicle.

Given a ParkingTransactions table with parking records, you need to calculate:

Total fees paid by each car across all parking lots
Average hourly rate each car pays (rounded to 2 decimal places)
Preferred parking lot where each car spends the most total time

This analysis helps the company understand customer behavior, optimize pricing strategies, and identify the most popular parking locations.

Note: A car cannot be in multiple parking lots simultaneously, ensuring data integrity.

Input & Output

basic_parking_analysis.sql — Basic Example

$ 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 | total_fee_paid | avg_hourly_fee | most_time_lot | +--------+----------------+----------------+---------------+ | 1001 | 18.00 | 2.40 | 1 | | 1002 | 6.00 | 1.33 | 2 | +--------+----------------+----------------+---------------+

💡 Note: Car 1001 paid total $18.00 across 7.5 hours ($2.40/hour average), spending most time (4.25 hours) in lot 1. Car 1002 paid $6.00 across 4.5 hours ($1.33/hour average), spending most time (2.5 hours) in lot 2.

single_car_multiple_lots.sql — Multiple Lots

$ Input: ParkingTransactions: +--------+--------+---------------------+---------------------+----------+ | lot_id | car_id | entry_time | exit_time | fee_paid | +--------+--------+---------------------+---------------------+----------+ | 1 | 2001 | 2023-06-01 09:00:00 | 2023-06-01 11:00:00 | 4.00 | | 2 | 2001 | 2023-06-01 14:00:00 | 2023-06-01 17:00:00 | 9.00 | | 3 | 2001 | 2023-06-02 10:00:00 | 2023-06-02 12:00:00 | 8.00 | +--------+--------+---------------------+---------------------+----------+

› Output: +--------+----------------+----------------+---------------+ | car_id | total_fee_paid | avg_hourly_fee | most_time_lot | +--------+----------------+----------------+---------------+ | 2001 | 21.00 | 3.00 | 2 | +--------+--------+----------------+----------------+---------------+

💡 Note: Car 2001 used 3 different lots: 2 hours in lot 1 ($4), 3 hours in lot 2 ($9), 2 hours in lot 3 ($8). Total: $21 over 7 hours = $3.00/hour. Most time spent in lot 2 (3 hours).

tie_breaking_lots.sql — Lot Tie Breaking

› Output: +--------+----------------+----------------+---------------+ | car_id | total_fee_paid | avg_hourly_fee | most_time_lot | +--------+----------------+----------------+---------------+ | 3001 | 10.00 | 2.50 | 1 | +--------+----------------+----------------+---------------+

💡 Note: Car 3001 spent equal time (2 hours each) in lots 1 and 2. When there's a tie in hours, the smaller lot_id (1) is chosen as the most_time_lot. Total: $10 over 4 hours = $2.50/hour.

Constraints

1 ≤ Number of transactions ≤ 10⁵
1 ≤ lot_id ≤ 100
1000 ≤ car_id ≤ 9999
entry_time < exit_time for all transactions
0.01 ≤ fee_paid ≤ 999.99
Maximum parking duration per transaction: 24 hours
All timestamps are in format: YYYY-MM-DD HH:MM:SS

Visualization

Tap to expand

Understanding the Visualization

Data Collection

Each parking transaction is recorded with entry/exit times and fees

Duration Calculation

Calculate hours parked using time difference (like calculating shopping time)

Aggregation

Group by car to sum total fees and hours (like monthly expense summary)

Analysis

Find average rate and preferred location (like finding your most visited store)

Key Takeaway

🎯 Key Insight: Group parking transactions by car_id and use window functions to rank lots by time spent, enabling efficient calculation of all required metrics in a single database query.

Asked in

G Google 45 a Amazon 38 ⊞ Microsoft 32 f Meta 28 Apple 22

The optimal solution uses a single SQL query with GROUP BY and window functions to calculate all required metrics in one pass. Key steps: 1) Calculate parking duration using TIMESTAMPDIFF, 2) Group by car_id to aggregate total fees and hours, 3) Use ROW_NUMBER() window function to rank lots by time spent per car, 4) Join results to get the lot with most time for each car. Time complexity: O(n log n) due to sorting for window functions.

Common Approaches

Approach	Time	Space	Notes
✓ Nested Subqueries Approach	O(n²)	O(n)	Multiple separate subqueries for each calculation requirement
Single Query with Window Functions (Optimal)	O(n log n)	O(n)	Calculate all metrics in one pass using GROUP BY and window functions

Nested Subqueries Approach — Algorithm Steps

Create subquery to calculate total fees per car
Create subquery to calculate total hours per car
Create subquery to find lot with most time per car
Join all subqueries together
Calculate average hourly rate from totals

Visualization

Tap to expand

Step-by-Step Walkthrough

First Scan

Calculate total fees for each car

Second Scan

Calculate total hours for each car

Third Scan

Find most time lot for each car

Join Results

Combine all calculations

Code -

solution.c — C

// Simulating brute force SQL approach
#include <stdio.h>
#include <stdlib.h>
#include <string.h>

typedef struct {
    int lot_id;
    int car_id;
    double hours;
    double fee_paid;
} Transaction;

typedef struct {
    int car_id;
    double total_fee_paid;
    double avg_hourly_fee;
    int most_time_lot;
} Result;

// Simple hash table for demonstration
#define MAX_CARS 1000

int calculateParkingStatsBruteForce(Transaction* transactions, int count, Result* results) {
    double totalFees[MAX_CARS] = {0};
    double totalHours[MAX_CARS] = {0};
    double lotTimes[MAX_CARS][10] = {0}; // Assuming max 10 lots
    int carExists[MAX_CARS] = {0};
    
    // Multiple passes through data
    for (int i = 0; i < count; i++) {
        int carId = transactions[i].car_id;
        carExists[carId] = 1;
        
        // Pass 1: Total fees
        totalFees[carId] += transactions[i].fee_paid;
        
        // Pass 2: Total hours
        totalHours[carId] += transactions[i].hours;
        
        // Pass 3: Lot times
        lotTimes[carId][transactions[i].lot_id] += transactions[i].hours;
    }
    
    int resultCount = 0;
    for (int carId = 0; carId < MAX_CARS; carId++) {
        if (!carExists[carId]) continue;
        
        // Find most time lot
        int mostTimeLot = 0;
        double maxTime = 0;
        for (int lotId = 0; lotId < 10; lotId++) {
            if (lotTimes[carId][lotId] > maxTime) {
                maxTime = lotTimes[carId][lotId];
                mostTimeLot = lotId;
            }
        }
        
        results[resultCount] = (Result){
            .car_id = carId,
            .total_fee_paid = totalFees[carId],
            .avg_hourly_fee = totalFees[carId] / totalHours[carId],
            .most_time_lot = mostTimeLot
        };
        resultCount++;
    }
    
    return resultCount;
}

Time & Space Complexity

Time Complexity

⏱️

O(n²)

Multiple passes through the data for each calculation

⚠ Quadratic Growth

Space Complexity

O(n)

Temporary storage for multiple subquery results

⚡ Linearithmic Space

42.7K Views

Medium-High Frequency

~18 min Avg. Time

1.8K Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

🚗 Smart Parking Analytics System

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

Nested Subqueries Approach — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler