Skip to content

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!

To determine the number of unique individuals using each garage’s shared charging stations, we must complete the following steps:

  1. Retrieve Data: Extract data from the charging_sessions table.
  2. Filter Data: The data is filtered to include only rows where user_type is ‘Shared’
  3. Group by Garage: Group the data by garage_id, creating a set of rows for each unique garage_id.
  4. Select and Count: For each group, select the garage_id and count the number of unique user_id values.
  5. Sort Results: Sort the resulting groups by the count of unique user_id values, from highest to lowest.
Spinner
DataFrameas
unique_users_per_garage
variable
-- unique_users_per_garage
-- Modify the code below
SELECT garage_id, COUNT(DISTINCT(user_id)) AS num_unique_users
FROM charging_sessions
WHERE user_type = 'Shared'
GROUP BY garage_id
ORDER BY COUNT(DISTINCT(user_id)) DESC;

To identify the top 10 most popular charging start times by weekday and start hour, we follow these steps:

  1. Retrieve Data: Extract records from the charging_sessions table.
  2. Filter Data: The data is filtered to include only rows where user_type is ‘Shared’
  3. Group Records: Group the data by weekdays_plugin and start_plugin_hour.
  4. Select and Count: For each group, select the weekdays_plugin, start_plugin_hour, and count the number of unique user_id values.
  5. Sort Results: Order the results by the count of unique user_id values in descending order.
  6. Limit Output: Limit the output to the top 10 entries.
Spinner
DataFrameas
most_popular_shared_start_times
variable
-- most_popular_shared_start_times

SELECT weekdays_plugin, start_plugin_hour, COUNT(user_id) AS num_charging_sessions
FROM charging_sessions
WHERE user_type = 'Shared'
GROUP BY weekdays_plugin, start_plugin_hour
ORDER BY COUNT(user_id) desc
LIMIT 10;

To identify users whose average charging duration exceeds 10 hours at shared charging stations, we follow these steps:

  1. Retrieve Data: Extract records from the charging_sessions table.
  2. Filter Data Pt. 1: The data is filtered to include only rows where user_type is ‘Shared’
  3. Group by User: Group the data by user_id.
  4. Filter Data Pt. 2: Restrict the query to include only those groups where the avg_duration_hours is greater than 10.
  5. SELECT: For each group, the user_id is selected, and the average duration_hours is calculated.
  6. Sort Results: Order the results by average duration_hours in descending order.
Spinner
DataFrameas
long_duration_shared_users
variable
-- long_duration_shared_users Find the users whose average charging duration last longer than 10 hours when using shared charging stations. --

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 (duration_hours) DESC;