Identifying inefficient chargers
Over time, chargers become less efficient. When this is the case, these chargers either need maintenance or to be replaced. A charger is determined to be efficient or inefficient based on charging rate. To help identify chargers that might need a refresh, you'll need to build a query that uses window frames to find the average charging rate and remaining number sessions for a charger. Good luck!
Cet exercice fait partie du cours
Window Functions in Snowflake
Instructions
- Find the average
charging_rate
, bycharging_station_location
using a window frame between the first row and the current row. - Count the number of records by
charging_station_location
. - Create a window frame between the current row and the last row, ordered by
charging_start_time
.
Exercice interactif pratique
Essayez cet exercice en complétant cet exemple de code.
SELECT
user_id,
TO_DATE(charging_start_time),
charging_station_location,
charging_rate,
-- Find the average charging rate, by charging station location
-- using a window frame between the first row and current row
___(___) OVER(
PARTITION BY ___
ORDER BY charging_start_time
ROWS BETWEEN ___ ___ AND ___ ___
) AS running_average_charging_rate,
-- Count the number of records by charging station location
___(*) OVER(
PARTITION BY charging_station_location
-- Create a window frame between the current row and the
-- last row, ordered by charging start time
ORDER BY ___
ROWS BETWEEN ___ ___ AND ___ ___
) AS remaining_charges
FROM ELECTRIC_VEHICLES.charging
ORDER BY charging_station_location, charging_start_time;