lesson 44 Window functions · advanced

Share of total with windows

Compare each row with its group total.

the idea

SUM(x) OVER () is the total of x over the whole result; dividing each row by it gives its share of the total. SUM(x) OVER (PARTITION BY group) gives each group’s total for per-group shares. Aggregate to the right grain first, then compute shares.

Dataset: Energy

The tables the challenges use, with their first 6 rows. Under each column name: the kind of value it holds, and which table it links to.

worked example

Run these steps in order to see the idea before you write your own query. The step marked “the pattern” is what the challenges ask for. Running the example does not count toward progress.

1. 1

   See raw generation rows. Run this. Each line is one balancing authority and fuel type on 2024-04-15 before you roll up or compute shares.

   step-1.sql Run

   sql›

   SELECT ba_code, fuel_code, generation_mwh, hours_reported
   FROM generation_by_fuel
   WHERE date = '2024-04-15'
   ORDER BY ba_code, fuel_code
   LIMIT 20;

2. 2

   Turn totals into percentages. Run this. SUM() OVER () with empty parentheses is the day’s total across all authorities; dividing each group’s sum by that gives its share of generation.the pattern

   example.sql Run

   sql›

   SELECT
     ba_code,
     SUM(generation_mwh) AS mwh,
     ROUND(100.0 * SUM(generation_mwh) / SUM(SUM(generation_mwh)) OVER (), 2) AS share_pct
   FROM generation_by_fuel
   WHERE date = '2024-01-15'
   GROUP BY ba_code;

common mistake

Pumped storage can make a daily fuel total negative, so a share can be negative; a zero total needs NULLIF to avoid dividing by zero.