German EV charging data in BigQuery

SELECT
  station_id,
  operator,
  name,
  address,
  dc_charge_point_count,
  max_power_kw,
  ROUND(ST_DISTANCE(geog, ST_GEOGPOINT(9.1817, 48.7838)) / 1000, 1) AS distance_km
FROM `dekart-ev-public-data.ev_charging_v1.stations`
WHERE ST_DWITHIN(geog, ST_GEOGPOINT(9.1817, 48.7838), 5000)  -- 5 km around Stuttgart Hauptbahnhof
  AND max_power_kw >= 150
ORDER BY distance_km
LIMIT 10

SELECT
  s.station_id,
  s.operator,
  s.city,
  s.dc_charge_point_count,
  ROUND(SAFE_DIVIDE(SUM(d.occupied_minutes), SUM(d.occupied_minutes + d.available_minutes)), 3) AS utilisation,
  SUM(d.estimated_sessions) AS estimated_sessions,
  ROUND(SAFE_DIVIDE(SUM(d.unknown_minutes),
    SUM(d.occupied_minutes + d.available_minutes + d.out_of_service_minutes + d.unknown_minutes)), 3) AS unknown_share
FROM `dekart-ev-public-data.ev_charging_v1.station_daily_metrics` AS d
JOIN `dekart-ev-public-data.ev_charging_v1.stations` AS s USING (station_id)
WHERE d.local_date BETWEEN DATE_SUB(CURRENT_DATE('Europe/Berlin'), INTERVAL 7 DAY)
                       AND DATE_SUB(CURRENT_DATE('Europe/Berlin'), INTERVAL 1 DAY)
  AND s.dc_charge_point_count >= 4
GROUP BY 1, 2, 3, 4
HAVING unknown_share < 0.2   -- skip stations whose status was mostly unknown
   AND estimated_sessions >= 7  -- skip plugs stuck in one status all week
ORDER BY utilisation DESC
LIMIT 10

SELECT
  EXTRACT(HOUR FROM DATETIME(h.hour_utc, 'Europe/Berlin')) AS local_hour,
  ROUND(SAFE_DIVIDE(SUM(h.occupied_minutes), SUM(h.occupied_minutes + h.available_minutes)), 3) AS utilisation,
  SUM(h.estimated_sessions) AS estimated_sessions
FROM `dekart-ev-public-data.ev_charging_v1.station_hourly_occupancy` AS h
JOIN `dekart-ev-public-data.ev_charging_v1.stations` AS s USING (station_id)
WHERE h.hour_utc >= TIMESTAMP_SUB(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY), INTERVAL 7 DAY)
  AND s.dc_charge_point_count > 0
GROUP BY local_hour
ORDER BY local_hour

WITH current_energy_price AS (
  -- Open price rows (valid_to IS NULL) without time-of-day or duration conditions.
  -- A few charge points have more than one such row; keep the lowest.
  SELECT charge_point_id, MIN(price_eur_per_kwh_incl_vat) AS price_eur_per_kwh
  FROM `dekart-ev-public-data.ev_charging_v1.prices`
  WHERE valid_to IS NULL
    AND price_eur_per_kwh_incl_vat IS NOT NULL
    AND start_time IS NULL
    AND min_duration_minutes IS NULL
    AND max_duration_minutes IS NULL
  GROUP BY charge_point_id
)
SELECT
  s.operator,
  COUNT(*) AS dc_charge_points_priced,
  ROUND(APPROX_QUANTILES(p.price_eur_per_kwh, 2)[OFFSET(1)], 2) AS median_eur_per_kwh
FROM current_energy_price AS p
JOIN `dekart-ev-public-data.ev_charging_v1.charge_points` AS c USING (charge_point_id)
JOIN `dekart-ev-public-data.ev_charging_v1.stations` AS s USING (station_id)
WHERE c.is_dc
GROUP BY s.operator
HAVING dc_charge_points_priced >= 200
ORDER BY dc_charge_points_priced DESC
LIMIT 10

SELECT
  d.local_date,
  s.source,
  ROUND(SAFE_DIVIDE(SUM(d.unknown_minutes),
    SUM(d.occupied_minutes + d.available_minutes + d.out_of_service_minutes + d.unknown_minutes)), 3) AS unknown_share
FROM `dekart-ev-public-data.ev_charging_v1.station_daily_metrics` AS d
JOIN `dekart-ev-public-data.ev_charging_v1.stations` AS s USING (station_id)
WHERE d.local_date >= DATE_SUB(CURRENT_DATE('Europe/Berlin'), INTERVAL 7 DAY)
GROUP BY 1, 2
ORDER BY 1, 2

SELECT source, licence, attribution_text, url
FROM `dekart-ev-public-data.ev_charging_v1.sources`
ORDER BY source

-- High-power (150 kW+) stations: size = number of DC charge points, color = utilisation, 5-8 Nov 2026
SELECT
  s.station_id,
  s.operator,
  s.name,
  s.city,
  s.dc_charge_point_count,
  s.max_power_kw,
  ROUND(SAFE_DIVIDE(SUM(d.occupied_minutes), SUM(d.occupied_minutes + d.available_minutes)), 3) AS utilisation,
  SUM(d.estimated_sessions) AS estimated_sessions,
  ANY_VALUE(s.geog) AS geometry
FROM `dekart-ev-public-data.ev_charging_v1.station_daily_metrics` AS d
JOIN `dekart-ev-public-data.ev_charging_v1.stations` AS s USING (station_id)
WHERE d.local_date BETWEEN '2026-11-05' AND '2026-11-08'
  AND s.max_power_kw >= 150
GROUP BY 1, 2, 3, 4, 5, 6
HAVING SAFE_DIVIDE(SUM(d.unknown_minutes),
         SUM(d.occupied_minutes + d.available_minutes + d.out_of_service_minutes + d.unknown_minutes)) < 0.2
   AND estimated_sessions >= 4

-- One site and every fast-charging station within 3 km, with utilisation (5-8 Nov 2026) and current price.
-- Replace the station_id with your own site.
WITH my_site AS (
  SELECT station_id AS my_station_id, geog
  FROM `dekart-ev-public-data.ev_charging_v1.stations`
  WHERE station_id = 'DE:mobidata:310110'  -- EnBW, Keplerstraße 7, Stuttgart
),
nearby AS (
  SELECT s.*, mine.my_station_id
  FROM `dekart-ev-public-data.ev_charging_v1.stations` AS s, my_site AS mine
  WHERE TRUE
    AND ST_DWITHIN(s.geog, mine.geog, 3000)
    AND s.dc_charge_point_count > 0
),
usage AS (
  SELECT
    station_id,
    ROUND(SAFE_DIVIDE(SUM(occupied_minutes), SUM(occupied_minutes + available_minutes)), 3) AS utilisation,
    SUM(estimated_sessions) AS estimated_sessions
  FROM `dekart-ev-public-data.ev_charging_v1.station_daily_metrics`
  WHERE local_date BETWEEN '2026-11-05' AND '2026-11-08'
    AND station_id IN (SELECT station_id FROM nearby)
  GROUP BY station_id
),
price AS (
  SELECT c.station_id, MIN(p.price_eur_per_kwh_incl_vat) AS dc_price_eur_per_kwh
  FROM `dekart-ev-public-data.ev_charging_v1.prices` AS p
  JOIN `dekart-ev-public-data.ev_charging_v1.charge_points` AS c USING (charge_point_id)
  WHERE p.valid_to IS NULL AND c.is_dc
    AND p.price_eur_per_kwh_incl_vat IS NOT NULL
    AND p.start_time IS NULL AND p.min_duration_minutes IS NULL AND p.max_duration_minutes IS NULL
    AND c.station_id IN (SELECT station_id FROM nearby)
  GROUP BY c.station_id
)
SELECT
  IF(n.station_id = n.my_station_id, 'My site', 'Nearby network') AS role,
  n.station_id,
  n.operator,
  n.name,
  n.dc_charge_point_count,
  n.max_power_kw,
  u.utilisation,
  u.estimated_sessions,
  p.dc_price_eur_per_kwh,
  n.geog AS geometry
FROM nearby AS n
LEFT JOIN usage AS u USING (station_id)
LEFT JOIN price AS p USING (station_id)
ORDER BY role, u.utilisation DESC

-- Busiest local hour of each fast-charging station, 5-8 Nov 2026
WITH by_hour AS (
  SELECT
    station_id,
    EXTRACT(HOUR FROM DATETIME(hour_utc, 'Europe/Berlin')) AS local_hour,
    SAFE_DIVIDE(SUM(occupied_minutes), SUM(occupied_minutes + available_minutes)) AS utilisation,
    SUM(estimated_sessions) AS estimated_sessions
  FROM `dekart-ev-public-data.ev_charging_v1.station_hourly_occupancy`
  WHERE hour_utc >= '2026-11-04 23:00:00' AND hour_utc < '2026-11-08 23:00:00'  -- 5-8 Nov, Berlin time
  GROUP BY 1, 2
)
SELECT
  s.station_id,
  s.operator,
  s.name,
  s.city,
  s.dc_charge_point_count,
  h.local_hour AS busiest_hour,
  ROUND(h.utilisation, 3) AS busiest_hour_utilisation,
  s.geog AS geometry
FROM by_hour AS h
JOIN `dekart-ev-public-data.ev_charging_v1.stations` AS s USING (station_id)
WHERE s.dc_charge_point_count > 0
QUALIFY SUM(h.estimated_sessions) OVER (PARTITION BY h.station_id) >= 8   -- at least 2 sessions a day
    AND ROW_NUMBER() OVER (PARTITION BY h.station_id ORDER BY h.utilisation DESC, h.estimated_sessions DESC) = 1

WITH current_energy_price AS (
  SELECT charge_point_id, MIN(price_eur_per_kwh_incl_vat) AS price_eur_per_kwh
  FROM `dekart-ev-public-data.ev_charging_v1.prices`
  WHERE valid_to IS NULL
    AND price_eur_per_kwh_incl_vat IS NOT NULL
    AND start_time IS NULL
    AND min_duration_minutes IS NULL
    AND max_duration_minutes IS NULL
  GROUP BY charge_point_id
)
SELECT
  s.station_id,
  s.operator,
  s.name,
  s.city,
  s.dc_charge_point_count,
  ROUND(APPROX_QUANTILES(p.price_eur_per_kwh, 2)[OFFSET(1)], 2) AS price_eur_per_kwh,
  ANY_VALUE(s.geog) AS geometry
FROM current_energy_price AS p
JOIN `dekart-ev-public-data.ev_charging_v1.charge_points` AS c USING (charge_point_id)
JOIN `dekart-ev-public-data.ev_charging_v1.stations` AS s USING (station_id)
WHERE c.is_dc
GROUP BY 1, 2, 3, 4, 5
HAVING price_eur_per_kwh BETWEEN 0.1 AND 1.5

-- Demand versus supply per H3 cell: occupied hours and sessions per charge point per day, 5-8 Nov 2026
WITH demand AS (
  SELECT
    station_id,
    SUM(occupied_minutes) / 60 AS occupied_hours,
    SUM(estimated_sessions) AS estimated_sessions
  FROM `dekart-ev-public-data.ev_charging_v1.station_daily_metrics`
  WHERE local_date BETWEEN '2026-11-05' AND '2026-11-08'  -- 4 days
  GROUP BY station_id
)
SELECT
  jslibs.eu_h3.ST_H3(s.geog, 6) AS h3,
  COUNT(*) AS stations,
  SUM(s.charge_point_count) AS charge_points,
  ROUND(SUM(d.occupied_hours) / 4 / SUM(s.charge_point_count), 2) AS occupied_hours_per_charge_point_per_day,
  ROUND(SUM(d.estimated_sessions) / 4 / SUM(s.charge_point_count), 2) AS sessions_per_charge_point_per_day
FROM `dekart-ev-public-data.ev_charging_v1.stations` AS s
JOIN demand AS d USING (station_id)
WHERE s.charge_point_count > 0
  AND s.latitude BETWEEN 47.2 AND 55.1 AND s.longitude BETWEEN 5.8 AND 15.1
GROUP BY h3
HAVING charge_points >= 10   -- skip cells with too few charge points to compare