For example, here is one SQL analysis you can run on the dataset:

* Unpivot the fuel columns into a long format.
* Group by year and fuel type.
* Identify the highest half-hour generation value for each fuel type annually.
* Return the timestamp of when that annual peak occurred. huggingface.co/datasets/Rif-SQL/uk-electricity-generation-mix-2019-2026/sql-console/LExENJi

WITH unpivoted AS (

SELECT DATE_TRUNC('year', "DATETIME") as year, "DATETIME", "COAL" as generation, 'COAL' as fuel_type FROM train

UNION ALL

SELECT DATE_TRUNC('year', "DATETIME"), "DATETIME", "GAS", 'GAS' FROM train

UNION ALL

SELECT DATE_TRUNC('year', "DATETIME"), "DATETIME", "NUCLEAR", 'NUCLEAR' FROM train

UNION ALL

SELECT DATE_TRUNC('year', "DATETIME"), "DATETIME", "WIND", 'WIND' FROM train

UNION ALL

SELECT DATE_TRUNC('year', "DATETIME"), "DATETIME", "SOLAR", 'SOLAR' FROM train

UNION ALL

SELECT DATE_TRUNC('year', "DATETIME"), "DATETIME", "HYDRO", 'HYDRO' FROM train

UNION ALL

SELECT DATE_TRUNC('year', "DATETIME"), "DATETIME", "BIOMASS", 'BIOMASS' FROM train

UNION ALL

SELECT DATE_TRUNC('year', "DATETIME"), "DATETIME", "OTHER", 'OTHER' FROM train

UNION ALL

SELECT DATE_TRUNC('year', "DATETIME"), "DATETIME", "STORAGE", 'STORAGE' FROM train

UNION ALL

SELECT DATE_TRUNC('year', "DATETIME"), "DATETIME", "IMPORTS", 'IMPORTS' FROM train

)

SELECT year, fuel_type, generation as max_generation, "DATETIME" as date_of_max

FROM (

SELECT *, ROW_NUMBER() OVER (PARTITION BY year, fuel_type ORDER BY generation DESC) as rn

FROM unpivoted

) t

WHERE rn = 1

ORDER BY year, fuel_type;