Explore

Analysis Blog on SQL Rental Database

Introduction

The rental database system we've developed serves as a comprehensive tool for managing and analyzing key aspects of a rental business. It tracks essential data points, including user profiles, transactions, vehicle details, and supplementary activities such as customer feedback and vehicle changes. This structured approach ensures a robust foundation for operational efficiency and insightful decision-making.

In this blog, we dive into a detailed analysis of the database, focusing on uncovering patterns and trends that drive business success. The key areas of exploration include:

User Transaction Patterns: Understanding customer behavior, such as frequency of rentals, popular booking times, and spending habits.

Vehicle Categories: Analyzing vehicle availability, preferences, and performance metrics to identify top-performing categories.

Customer Feedback: Examining comments and ratings to gauge satisfaction levels and pinpoint areas for improvement.

Each topic is paired with SQL queries that extract actionable insights from the data, enabling businesses to make data-driven decisions. Whether you’re a database enthusiast or a business strategist, this analysis highlights the potential of structured data to shape rental operations.

ER Diagram

⁠

Create table

Insert data

Clean data

⁠

⁠

Overall Effects of Cleaning:

Improved Data Quality:

Removed invalid, missing, or duplicate data.

Ensured only valid transactions remain.

More Reliable Insights:

Eliminated extreme outliers that could skew trends.

Created a meaningful new feature (rental_duration) for analysis.

Preparation for Visualization:

Cleaned data is now ready for accurate graphing and predictive analysis.

Analysis

The database consists of the following tables:

User: Stores information about users, including name, email, and creation date.

Vehicle: Contains vehicle details like name, type, availability, and rental rates.

Transaction: Tracks user-vehicle rentals, including rental duration, payment status, and total amount.

Comment_After_Rental: Holds user feedback and ratings for rented vehicles.

Change_Another_Vehicle: Records instances where users switched to another vehicle.

Insurance: (Newly added) Tracks insurance policies for transactions.

The data includes 15 users, 15 vehicles, 13 transactions, and several comments and vehicle changes. Below are the three topics we analyze in detail.

⁠

Topic 1: User Transactions Analysis

We aim to identify the most active users and their rental habits.

SQL Query

Result Interpretation

This query provides a summary of each user's rental activity, sorted by total spending. It highlights the top customers and their contribution to revenue.

⁠

Topic 2: Vehicle Category Analysis

We explore which vehicle types are most rented and their contribution to revenue.

SQL Query

Result Interpretation

This query breaks down the rental frequency and revenue by vehicle type, allowing us to identify the most profitable and popular categories.

⁠

Topic 3: Customer Feedback Analysis

We analyze the average rating for vehicles based on customer comments.

SQL Query

Result Interpretation

This query identifies the highest-rated vehicles and evaluates their customer satisfaction levels.

Topic 4: Cancellation and No-Show Rates

This query identifies transactions that are marked as not "Paid," which might indicate cancellations or no-shows.

The query calculates the number of transactions where the payment_status is not "Paid" (indicating potential cancellations or no-shows).

The total transactions are compared to the number of cancellations or no-shows to calculate the cancellation rate.

Expected Result:

total_transactions: The total number of transactions in the system.

cancellations_or_no_shows: Count of transactions marked as not "Paid."

cancellation_rate: The percentage of transactions that are cancellations or no-shows.

⁠

Topic 5: Revenue Trends

This query calculates revenue trends by month.

The query aggregates revenue (total_amount) from the Transaction table for transactions marked as "Paid."

Revenue is grouped by month (using DATE_FORMAT(transaction_date, '%Y-%m')).

Expected Result:

A table showing each month and the total revenue generated during that month.

This helps identify seasonal trends or high-revenue periods.

⁠

Topic 6: Maintenance and Vehicle Downtime

This query identifies vehicles that have had high downtime based on their availability status and calculates downtime percentages.

This query assumes a tracking system for vehicle downtime (where availability_status is FALSE).

It calculates:

The total days a vehicle is tracked.

The total number of days it was unavailable (downtime).

The downtime percentage relative to total days tracked.

Expected Result:

For each vehicle:

total_days_tracked: Total days the vehicle's status was monitored.

downtime_days: Days the vehicle was unavailable.

downtime_percentage: The proportion of downtime days to total days.

Note: If availability_status doesn’t capture downtime history, this query may require a separate downtime tracking table.

⁠

Topic 7: Regional or Location-Based Insights

This query identifies trends in transactions by region or branch. If location data isn’t directly in the database, it needs to be incorporated.

This query groups transactions by region (if a region column exists in your schema).

It calculates:

The total number of transactions in each region.

The total revenue generated from transactions in that region.

Expected Result:

A table showing:

region: The branch or area of operation.

total_transactions: The number of rentals in the region.

total_revenue: The revenue generated from the region.

If the region column isn’t available, the query must be adjusted or the data enriched with location information.

⁠

Topic 8: Cost Analysis

This query calculates the profitability of each vehicle by comparing revenue to an assumed daily cost.

The query compares revenue from rentals with operating costs (calculated as daily_rental_rate times the number of transactions).

It computes:

Total revenue.

Total operating cost.

Net profit (revenue - operating cost) for each vehicle.

Expected Result:

For each vehicle:

total_revenue: Total rental earnings.

total_operating_cost: Estimated operational cost (based on rental days and rates).

net_profit: The profitability of the vehicle.

⁠

Predictive data

⁠

⁠

The predictive graph displays the comparison between the actual values of total_amount (based on historical data) and the predicted values generated by the machine learning model. Here’s a detailed explanation:

Components of the Graph:

Blue Points:

Represent the actual total_amount values for test data.

These are derived directly from your dataset.

Red Points:

Represent the predicted total_amount values made by the model.

These values are the output of the trained Random Forest Regressor.

Dashed Lines:

Show the trends for both the actual and predicted values.

This helps visualize how closely the predictions follow the actual data.

X-Axis:

Represents the index of each data point in the test set. These indices correspond to different transactions.

Y-Axis:

Represents the total_amount value (e.g., the cost of rental transactions).

Interpretation:

Close Alignment:

If the red points are close to the blue points, it indicates that the model has made accurate predictions.

In areas where the dashed red and blue lines overlap or are very close, the model performs well.

Discrepancies:

Larger gaps between the blue and red points indicate prediction errors.

This shows where the model struggled to capture the relationship between the input features (e.g., vehicle_id, rental_duration) and the target (total_amount).

Trends:

The graph also reveals any overall trends or patterns in the data, such as whether higher rental_duration generally results in higher total_amount.

Evaluating the Model:

We also calculated evaluation metrics:

Mean Absolute Error (MAE):

The average absolute difference between actual and predicted values. A lower value indicates better performance.

Mean Squared Error (MSE):

The average squared difference between actual and predicted values. This metric penalizes larger errors more than MAE.

Example Scenarios:

If predictions align well:

The model can be trusted to predict rental prices for unseen data accurately.

If there are large discrepancies:

This indicates that the model may need improvement, such as additional features (e.g., seasonal demand, vehicle condition) or different algorithms.

Conclusion

Unlocking Insights from Rental Database Systems

In today’s fast-paced rental industry, leveraging data effectively is crucial for staying ahead of the competition. A well-designed rental database system offers immense potential to enhance operational efficiency, improve customer satisfaction, and boost profitability. Here’s a closer look at the key insights a rental database can provide and how they benefit the business:

Understanding User Transactions

By analyzing rental frequency and spending patterns, businesses can identify high-value customers and create tailored loyalty programs. This approach fosters customer retention and increases lifetime value.

Optimizing Vehicle Categories

Insights into the popularity and profitability of various vehicle types allow for informed inventory decisions. Businesses can prioritize high-demand categories, ensuring maximum utilization and revenue.

Enhancing Customer Experience

Customer feedback, including ratings and comments, sheds light on service quality and areas needing improvement. Proactively addressing these concerns leads to higher customer satisfaction and repeat business.

Minimizing Revenue Losses

Analyzing unpaid transactions and cancellation patterns helps refine policies and reduce no-shows. This ensures more consistent cash flow and better resource planning.

Identifying Revenue Trends

Seasonal revenue fluctuations provide valuable information for financial planning. Targeted promotions during high-demand periods can further maximize earnings.

Ensuring Fleet Efficiency

Tracking vehicle downtime and maintenance needs helps minimize disruptions. An optimized fleet means more availability, higher customer satisfaction, and reduced operational losses.

Conclusion

A robust rental database system is more than just a record-keeping tool it’s a gateway to actionable insights that can transform a business. By harnessing its full potential, companies can align their strategies with market demands, optimize operations, and foster sustainable growth in an increasingly competitive landscape.

Dataset Reference :

https://www.kaggle.com/datasets/janiobachmann/bank-marketing-dataset⁠

⁠

Want to print your doc?
This is not the way.

Try clicking the ⋯ next to your doc name or using a keyboard shortcut (

CtrlP

) instead.