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
| Column | Definition | Data type |
|---|---|---|
garage_id | Identifier for the garage/building | VARCHAR |
user_id | Identifier for the individual user | VARCHAR |
user_type | Indicating whether the station is Shared or Private | VARCHAR |
start_plugin | The date and time the session started | DATETIME |
start_plugin_hour | The hour (in military time) that the session started | NUMERIC |
end_plugout | The date and time the session ended | DATETIME |
end_plugout_hour | The hour (in military time) that the session ended | NUMERIC |
duration_hours | The length of the session, in hours | NUMERIC |
el_kwh | Amount of electricity used (in Kilowatt hours) | NUMERIC |
month_plugin | The month that the session started | VARCHAR |
weekdays_plugin | The day of the week that the session started | VARCHAR |
Let’s get started!
Sources
Find the number of unique individuals that use each garage’s shared charging stations.
The output should contain two columns: garage_id and num_unique_users. Sort your results by the number of unique users from highest to lowest. Save the result as unique_users_per_garage.
-- unique_users_per_garage
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;| index | garage_id | num_unique_users |
|---|---|---|
| 0 | Bl2 | 18 |
| 1 | AsO2 | 17 |
| 2 | UT9 | 16 |
| 3 | AdO3 | 3 |
| 4 | MS1 | 2 |
| 5 | SR2 | 2 |
| 6 | AdA1 | 1 |
| 7 | Ris | 1 |
Find the top 10 most popular charging start times (by weekday and start hour) for sessions that use shared charging stations.
Your result should contain three columns: weekdays_plugin, start_plugin_hour, and a column named num_charging_sessions containing the number of plugins on that weekday at that hour. Sort your results from the most to the least number of sessions. Save the result as most_popular_shared_start_times.
-- most_popular_shared_start_times
SELECT weekdays_plugin, start_plugin_hour, COUNT(*) 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;| index | weekdays_plugin | start_plugin_hour | num_charging_sessions |
|---|---|---|---|
| 0 | Sunday | 17 | 30 |
| 1 | Friday | 15 | 28 |
| 2 | Thursday | 19 | 26 |
| 3 | Thursday | 16 | 26 |
| 4 | Wednesday | 19 | 25 |
| 5 | Sunday | 18 | 25 |
| 6 | Sunday | 15 | 25 |
| 7 | Monday | 15 | 24 |
| 8 | Friday | 16 | 24 |
| 9 | Tuesday | 16 | 23 |
Find the users whose average charging duration last longer than 10 hours when using shared charging stations.
Your result should contain two columns: user_id and avg_charging_duration. Sort your result from highest to lowest average charging duration. Save the result as long_duration_shared_users.
-- long_duration_shared_users
SELECT user_id, AVG(duration_hours) AS avg_charging_duration
FROM charging_sessions
WHERE user_type = 'Shared'
GROUP BY user_id
HAVING AVG(duration_hours) > 10
ORDER BY avg_charging_duration DESC| index | user_id | avg_charging_duration |
|---|---|---|
| 0 | Share-9 | 16.845833335 |
| 1 | Share-17 | 12.8945555511 |
| 2 | Share-25 | 12.2144747466 |
| 3 | Share-18 | 12.0888071898 |
| 4 | Share-8 | 11.5504308392 |
| 5 | AdO3-1 | 10.3693869729 |