BMW Used Cars
SQL Analysis

What Makes a BMW Cheap to Own?

The used car market is full of traps — low sticker prices that hide high running costs, and high-mileage cars priced as if brand new. This project uses T-SQL in Microsoft SQL Server to cut through the noise across 10,781 BMW listings, answering seven core questions about pricing, tax, depreciation, mileage, transmission, engine size, and fuel efficiency.

The central question: which BMW models offer the best value — not just at purchase, but over years of ownership?

Anomalies & Outliers

Before any analysis, the data was interrogated for anomalies — cars priced inconsistently with their age and mileage. Two queries surface the outliers:

A. Older Cars Priced Unusually High

SELECT TOP 20 model, [year], price
FROM Bmw
ORDER BY price DESC

B. Low Mileage Cars Priced Unusually Low

SELECT TOP 20 model, [year], price, mileage
FROM Bmw
ORDER BY mileage ASC

Low-mileage cars priced at the bottom of the market are candidates for further investigation — potential data errors, fleet vehicles, or genuine bargains worth isolating.

Cheapest Models to Own

Road tax is a recurring annual cost. Combined with purchase price, it gives a clearer picture of long-term ownership expense than price alone.

Average Tax by Model

SELECT model, AVG(tax) Tax
FROM Bmw
GROUP BY model
ORDER BY Tax ASC

10-Year Ownership Cost

By multiplying annual tax by 10 and adding purchase price, a true long-term cost emerges:

SELECT TOP 20 model, [year], tax, price,
       (price + (tax * 10)) [Total Cost]
FROM Bmw
GROUP BY model, [year], tax, price
ORDER BY [Total Cost]

Transmission Trends Over Time

Which transmission type dominates each year — and is that shifting over time? A subquery with RANK() isolates the most common transmission per year without guessing alphabetically.

SELECT [year], transmission AS [Highest Transmission],
       [Transmission Count]
FROM
  (
    SELECT [year], transmission,
           COUNT(transmission) [Transmission Count],
           RANK() OVER(PARTITION BY [year]
                        ORDER BY COUNT(transmission) DESC) AS [Rank]
    FROM Bmw
    GROUP BY [year], transmission
  ) AS [Transmission Figures]
WHERE [Rank] = 1
ORDER BY [year]

The same RANK logic is applied by model (section 4B) to find each model's most common transmission type — giving a cleaner answer than MAX(transmission), which returns the alphabetically last value rather than the most frequent.

Which Models Hold Their Value?

Depreciation is calculated by comparing the highest and lowest average prices per model across years — a subquery pattern that produces a single depreciation figure per model:

SELECT model,
       MIN(Avg_Price) AS Lowest_Price,
       MAX(Avg_Price) AS Highest_Price,
       MIN(Avg_Price) - MAX(Avg_Price) AS Total_Depreciation
FROM (
  SELECT model, [year], AVG(price) AS Avg_Price
  FROM Bmw
  GROUP BY model, [year]
) AS yearly_prices
GROUP BY model
ORDER BY Total_Depreciation ASC

Diesel vs Petrol vs Electric

Fuel type drives both running costs and resale value. The data shows a clear efficiency and tax hierarchy:

Diesel pays 25% less tax than petrol on average — a significant saving over multiple years. Electric and Hybrid models cut that further but come at a higher purchase price.

What the Data Reveals