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 Google BigQuery database, containing a schema called VEHICLES and a single table named CHARGING_SESSIONS with the following columns:
VEHICLES.CHARGING_SESSIONS
| Column | Definition | Data type |
|---|---|---|
garage_id | Identifier for the garage/building | STRING |
user_id | Identifier for the individual user | STRING |
user_type | Indicating whether the station is Shared or Private | STRING |
start_plugin | The date and time the session started | TIMESTAMP |
start_plugin_hour | The hour (in military time) that the session started | NUMERIC |
end_plugout | The date and time the session ended | TIMESTAMP |
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 | STRING |
weekdays_plugin | The day of the week that the session started | STRING |
Let’s get started!
Sources
-- Finding the unique shared users for each garage
SELECT
garage_id,
COUNT(DISTINCT user_id) AS num_unique_users
FROM
VEHICLES.CHARGING_SESSIONS
WHERE
user_type = 'Shared'
GROUP BY
garage_id
ORDER BY
num_unique_users DESC;-- Finding the top 10 most popular starting charge times by day and hour
SELECT
weekdays_plugin,
start_plugin_hour,
COUNT(*) AS num_charging_sessions
FROM
VEHICLES.CHARGING_SESSIONS
WHERE
user_type = 'Shared'
GROUP BY
weekdays_plugin,
start_plugin_hour
ORDER BY
num_charging_sessions DESC
LIMIT 10;-- Finding users with long average charging times
SELECT
user_id,
AVG(duration_hours) AS avg_charging_duration
FROM
VEHICLES.CHARGING_SESSIONS
WHERE
user_type = 'Shared'
GROUP BY
user_id
HAVING
AVG(duration_hours) > 10
ORDER BY
avg_charging_duration DESC;