New Wheels Data Analysis Using SQL

Data Analysis Using SQL for New Wheels Manufacturer



Libraries:


View the Project on GitHub SindujaSivan/NewWheelsDataTransformationUsingSQL

Overview

The New-Wheels Data Transformation project aimed to address the challenges faced by the New-Wheels app, focusing on sales descent, reputation hit, and a drop in new customers.

Objective:

Business Context:

Data Input:

The input data includes information related to city, country, customer, product, shipper, order, etc.

New Wheels Picture reference: [Project Internal]

Project Insights

1. Financial Overview

2. Customer Distribution Across States

Q1 Picture reference: [Project Microsoft Excel Report]

3. Average Customer Rating – By Quarter

Q3 Picture reference: [Project Microsoft Excel Report]

4. Trend of Customer Satisfaction

5. Top Vehicle Makers Preferred by Customers

Code Snippet: Top 5 Vehicle Makers by Total Customers
SELECT PRODUCT_T.VEHICLE_MAKER, COUNT(ORDER_T.CUSTOMER_ID) AS TOTAL_CUSTOMERS
FROM ORDER_T, PRODUCT_T
WHERE ORDER_T.PRODUCT_ID = PRODUCT_T.PRODUCT_ID
GROUP BY PRODUCT_T.VEHICLE_MAKER
ORDER BY TOTAL_CUSTOMERS DESC
LIMIT 5;

Q5 Picture reference: [Project Microsoft Excel Report]

6. Most Preferred Vehicle Make in Each State

7. Trend of Purchases by Quarter

Code Snippet: Quarter-on-Quarter Revenue Change
WITH RevenueByQuarter AS (
    SELECT
        quarter_number,
        SUM(o.VEHICLE_PRICE) AS total_revenue
    FROM
        order_t o
    GROUP BY
        quarter_number
)
SELECT
    quarter_number,
    total_revenue,
    LAG(total_revenue) OVER (ORDER BY quarter_number) AS previous_quarter_revenue,
    (total_revenue - LAG(total_revenue) OVER (ORDER BY quarter_number)) / LAG(total_revenue) OVER (ORDER BY quarter_number) * 100 AS qoq_percentage_change
FROM
    RevenueByQuarter;

8. Quarter on Quarter % Change in Revenue

Code Snippet: Quarterly Revenue and Order Count
SELECT quarter_number, SUM(vehicle_price) AS Revenue, COUNT(order_id) AS Orders
FROM order_t
GROUP BY quarter_number
ORDER BY quarter_number;

9. Trend of Revenue and Orders by Quarter

10. Average Discount Offered by Credit Card Type - Notable range in discount percentages across different credit card types. - Top three credit card types with the highest discount rates: ‘laser’ (64.38%), ‘mastercard’ (62.95%), ‘maestro’ (62.42%).

Q10 Picture reference: [Project Microsoft Excel Report]

11. Time Taken to Ship Orders by Quarter - Average shipment time consistently increases from 57.17 days in Q1 to 174.10 days in Q4. - Q1 demonstrates a relatively shorter average shipment time, while Q4 shows the longest time.

Recommendations