Putting it all together!
This is it! It's time to put everything you've learned to the test. The CEO of your company has come to you looking for a dashboard that gives him an in-depth view of each charging location's high-level metrics. This includes data about things like energy consumed and charging cost. Before you can build this dashboard, you'll need to create a dataset that surfaces this information.
This exercise is part of the course
Window Functions in Snowflake
Exercise instructions
- Provide a ranking for each charging session at a certain
charging_station_locationbased onenergy_consumed, from greatest to least. - Generate a running total of
charging_costbycharging_station_location. - Build a window frame using the two preceding and two following sessions to find a moving average of
energy_consumedbycharging_station_location.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
SELECT
charging_station_location,
TO_DATE(charging_start_time),
charging_cost,
energy_consumed,
-- Provide a ranking for each charging session based on energy consumed, from greatest to least
___() OVER(
PARTITION BY charging_station_location
ORDER BY ___
) AS rank_energy_consumed,
-- Generate a "running total" of charging costs by charging station location
SUM(___) OVER(
___ ___ ___
ORDER BY charging_start_time
ROWS BETWEEN ___ ___ AND ___ ___
) AS running_total_charging_cost,
-- Build a window frame using the two preceding and two following sessions to find a moving average of energy consumed
___() OVER(
PARTITION BY ___
ORDER BY charging_start_time
ROWS BETWEEN ___ ___ AND ___ ___
) AS moving_average_energy_consumed
FROM ELECTRIC_VEHICLES.charging
WHERE energy_consumed IS NOT NULL
ORDER BY charging_station_location, charging_start_time;