Load
Read the three sheets (consumption, rates, building master), lowercased the column names and loaded them into an energy schema.
← Back to all projects
SQL · PostgreSQL · Excel
Energy Consumption Analysis
Three Excel sheets became a relational PostgreSQL database. I unpivoted the data, joined consumption to yearly prices, and worked out where the energy and the money go.
528Monthly records
11Buildings
WaterMost expensive energy type each year
8 of 11Buildings above average cost
The build
From spreadsheet to database
Type and name fixes
Converted date to a real date type and renamed columns to snake_case so they can be queried without quotes.
Keys and relationships
Composite primary keys on consumption (date, building) and rates (year, energy_type), a primary key on building, and a foreign key from consumption to building.
Why: prevents duplicate months and orphan buildings
Unpivot
Water, electricity and gas sat in three columns. A UNION ALL view turned them into one type / consumption pair so every question needs one query, not three.
Why: one long table is far easier to group and join
Price the usage
Joined consumption to rates on both year and energy type, because prices change every year.
Why: joining on type alone would price every year at the same rate
Findings
What the data says
💧
Total consumption
Water 186.2M units · electricity 21.7M · gas 2.55M. Units differ per type, so compare cost, not volume.
🏢
Biggest consumer
B1006 uses the most (20.1M). B1009 uses the least (17.8M).
🌆
City view
New York leads on both consumption (57.3M) and cost (4.35M), but it has 3 buildings. Houston is lowest on cost (1.44M) with just 1 building.
💰
Cost by type
Water 10.85M · gas 3.01M · electricity 2.02M. Water is the most expensive every year, rising from 2.26M to 3.06M.
🧾
Highest cost building
B1008 at 1.50M. Eight of eleven buildings cost more than the average.
📅
Reusable views
monthly_energy_summary and dataset_calendar are ready for a BI tool.
💡 City totals depend on how many buildings each city has, so cost per building would be a fairer comparison. That is my next step with this dataset.
SQL
Key queries
sql · keys & relationships
alter table energy.consumption add constraint key1 primary key (date, building); alter table energy.rate add constraint key2 primary key (year, energy_type); alter table energy.building add constraint key3 primary key (building); alter table energy.consumption add constraint fk foreign key (building) references energy.building (building);
sql · unpivot view
create view energy.unpivoted as
with unpivoted as (
select date, building, 'water' as type, water_consumption as consumption from energy.consumption
union all
select date, building, 'electricity' as type, electricity_consumption as consumption from energy.consumption
union all
select date, building, 'gas' as type, gas_consumption as consumption from energy.consumption
)
select * from unpivoted;sql · cost by type
-- cost = consumption x the price for that energy type AND that year
select type,
round(sum(consumption * price_per_unit)::numeric, 2) as total_cost
from energy.unpivoted
join energy.rate
on energy.unpivoted.type = energy.rate.energy_type
and extract(year from energy.unpivoted.date) = energy.rate.year
group by type
order by total_cost desc;sql · CTE + subquery
-- buildings costing more than the average building
with total_cost_per_building as (
select building,
round(sum(consumption * price_per_unit)::numeric, 2) as total_cost
from energy.unpivoted
join energy.rate
on energy.rate.energy_type = energy.unpivoted.type
and extract(year from energy.unpivoted.date) = energy.rate.year
group by building
)
select building, total_cost
from total_cost_per_building
where total_cost > (select avg(total_cost) from total_cost_per_building)
order by total_cost desc;sql · window function
-- most expensive energy type in each year (window function)
with yearly_ranked as (
select year, type, total_cost,
rank() over (partition by year order by total_cost desc) as rank_in_year
from (
select year, type,
round(sum(consumption * price_per_unit)::numeric, 2) as total_cost
from energy.rate
join energy.unpivoted
on energy.rate.energy_type = energy.unpivoted.type
and extract(year from energy.unpivoted.date) = energy.rate.year
group by year, type
) as yearly_costs
)
select year, type, total_cost, rank_in_year
from yearly_ranked
where rank_in_year = 1
order by year;sql · calendar view
create view energy.dataset_calendar as
with dataset_calendar as (
select generate_series(
(select min(date) from energy.unpivoted),
(select max(date) from energy.unpivoted),
interval '1 day')::date as date
)
select date,
extract(year from date) as year,
extract(month from date) as month,
extract(day from date) as day,
trim(to_char(date, 'Month')) as month_name,
trim(to_char(date, 'Day')) as day_name
from dataset_calendar;
Project files