SQL analysis with Electric_Vehicle_Population_Data

This dataset shows the Battery Electric Vehicles (BEVs) and Plug-in Hybrid Electric Vehicles (PHEVs) that are currently registered through Washington State Department.

You can get through the dataset through Dataset - Catalog

Based on the Dataset we will answer the following questions.

• No of Ev's for BMW and TESLA?
• Each state and each city no of Ev's?
• Which year has the most EV sales?
• Which company has manufactured the highest ev?
• Which model has sold for each brand and how many ev?
• How many companies make both EV type?
• Which company has manufactured most PHEV type vehicles?
• Each year which company has make the most of EV with count.

1. No of Ev's for BMW and TESLA?

SELECT MAKE, COUNT(*)  FROM EV_DATA WHERE MAKE IN ('BMW','TESLA')

GROUP BY MAKE

2.Each state and each city no of Ev's?

SELECT STATE,CITY,COUNT(*) FROM EV_DATA GROUP BY STATE,CITY

3.Which year has the most EV sales?

SELECT TOP 1 MODEL_YEAR,COUNT(*) AS CNT FROM EV_DATA GROUP BY MODEL_YEAR ORDER BY CNT DESC

4.Which company has manufactured the highest ev?

WITH CTE AS 

(SELECT MAKE,COUNT(*) AS CNT FROM EV_DATA GROUP BY MAKE )

SELECT  TOP 1 MAKE FROM CTE ORDER BY CNT DESC

5.Which model has sold for each brand and how many ev?

;WITH CTE AS 

(SELECT MAKE,MODEL,COUNT(1) AS CNT FROM EV_DATA GROUP BY MAKE,MODEL),

CTE1 AS 

(SELECT MAKE,MODEL,CNT, DENSE_RANK() OVER (PARTITION BY MAKE ORDER BY CNT DESC) AS ROWNUM FROM CTE)

SELECT MODEL,MAKE,CNT FROM CTE1 WHERE ROWNUM=1

6.How many companies make both EV type?

SELECT MAKE FROM (

SELECT MAKE,COUNT(DISTINCT ELECTRIC_VEHICLE_TYPE) AS CNT FROM EV_DATA 

GROUP BY MAKE ) AS A 

WHERE A.CNT=2

7.Which company has manufactured most PHEV type vehicles?

SELECT TOP 1 MAKE,COUNT(*) AS CNT FROM EV_DATA WHERE ELECTRIC_VEHICLE_TYPE='PLUG-IN HYBRID ELECTRIC VEHICLE (PHEV)'

GROUP BY MAKE

ORDER BY CNT DESC

8.Each year which company has make the most of EV with count.

SELECT MODEL_YEAR,MAKE,CNT FROM (

SELECT MODEL_YEAR,MAKE,COUNT(1) AS CNT ,

DENSE_RANK() OVER(PARTITION BY MODEL_YEAR ORDER BY COUNT(*) DESC) AS ROWNUM

FROM EV_DATA GROUP BY MODEL_YEAR,MAKE

) A WHERE A.ROWNUM=1

ORDER BY MODEL_YEAR,ROWNUM