Skip to content

As electronic vehicles (EVs) become more popular, there is an increasing need for access to charging stations, also known as ports. To that end, many modern apartment buildings have begun retrofitting their parking garages to include shared charging stations. A charging station is shared if it is accessible by anyone in the building.

But with increasing demand comes competition for these ports — nothing is more frustrating than coming home to find no charging stations available! In this project, you will use a dataset to help apartment building managers better understand their tenants’ EV charging habits.

The data has been loaded into a PostgreSQL database with a table named charging_sessions with the following columns:

charging_sessions

ColumnDefinitionData type
garage_idIdentifier for the garage/buildingVARCHAR
user_idIdentifier for the individual userVARCHAR
user_typeIndicating whether the station is Shared or PrivateVARCHAR
start_pluginThe date and time the session startedDATETIME
start_plugin_hourThe hour (in military time) that the session startedNUMERIC
end_plugoutThe date and time the session endedDATETIME
end_plugout_hourThe hour (in military time) that the session endedNUMERIC
duration_hoursThe length of the session, in hoursNUMERIC
el_kwhAmount of electricity used (in Kilowatt hours)NUMERIC
month_pluginThe month that the session startedVARCHAR
weekdays_pluginThe day of the week that the session startedVARCHAR

Let’s get started!

Sources
  • Data: CC BY 4.0, via Kaggle,
  • Image: Julian Herzog, CC BY 4.0, via Wikimedia Commons
Spinner
DataFrameas
unique_users_per_garage
variable
-- Find the number of unique individuals that use each garage’s shared charging stations. 

SELECT garage_id, COUNT(DISTINCT user_id) AS num_unique_users
FROM charging_sessions
WHERE user_type = 'Shared'
GROUP BY garage_id
ORDER BY num_unique_users DESC;
Spinner
DataFrameas
most_popular_shared_start_times
variable
-- Find the top 10 most popular charging start times (by weekday and start hour) for sessions that use shared charging stations. 

SELECT weekdays_plugin, start_plugin_hour, COUNT (start_plugin_hour) AS num_charging_sessions
FROM charging_sessions
WHERE user_type = 'Shared'
GROUP BY weekdays_plugin, start_plugin_hour
ORDER BY num_charging_sessions DESC
LIMIT 10;
Spinner
DataFrameas
long_duration_shared_users
variable
-- Find the users whose average charging duration last longer than 10 hours when using shared charging stations.

SELECT user_id, ROUND(AVG(duration_hours),2) AS avg_charging_duration
FROM charging_sessions
WHERE user_type = 'Shared' 
	AND duration_hours IS NOT NULL
GROUP BY user_id
HAVING AVG(duration_hours) >= 10
ORDER BY avg_charging_duration DESC;
Spinner
DataFrameas
Ex_1
variable
--  Number of charging sessions per user per garage:

SELECT user_id, garage_id, COUNT(*) AS session_count
FROM charging_sessions
GROUP BY user_id, garage_id
ORDER BY session_count DESC, user_id ASC;
Spinner
DataFrameas
Ex_2
variable
-- Count Unique Users Per Garage

SELECT garage_id, COUNT(DISTINCT user_id) AS unique_users
FROM charging_sessions
GROUP BY garage_id
ORDER BY unique_users DESC;
Spinner
DataFrameas
Ex_3
variable
-- Total number of shared sessions to provide context for the counts

SELECT start_plugin_hour, 
       COUNT(start_plugin_hour) AS session_count, 
       ROUND(COUNT(start_plugin_hour) * 100.0 / SUM(COUNT(start_plugin_hour)) OVER (),2) AS percentage
FROM charging_sessions
WHERE user_type = 'Shared'
GROUP BY start_plugin_hour
ORDER BY session_count DESC;
Spinner
DataFrameas
Ex_4
variable
-- Add Percentage of Total Hours

SELECT user_id, 
       ROUND(SUM(duration_hours), 2) AS total_hours, 
       ROUND(SUM(duration_hours) * 100.0 / SUM(SUM(duration_hours)) OVER (), 2) AS percentage
FROM charging_sessions
WHERE user_type = 'Shared' AND duration_hours IS NOT NULL
GROUP BY user_id
ORDER BY total_hours DESC;