City share of total revenue with running share

A regional head wants to know how concentrated revenue is across cities.

From city_sales, return city, city_revenue, share_pct (the city's percentage of total revenue, 1 decimal) and cumulative_share_pct (running total of the share when cities are sorted from highest to lowest revenue, 1 decimal). Order by city_revenue descending.

1. Step 1: Aggregate to one row per city first, then apply window functions on those totals.
2. Step 2: `SUM(city_revenue) OVER ()` with an empty window is the grand total, so every row can be divided by it without collapsing the rows.
3. Step 3: Adding `ORDER BY` and an explicit `ROWS` frame turns the same `SUM` into a running total; spelling out the frame avoids surprises when values tie.

WITH city_totals AS (
    SELECT city, SUM(revenue) AS city_revenue
    FROM city_sales
    GROUP BY city
)
SELECT city,
       city_revenue,
       ROUND(100.0 * city_revenue / SUM(city_revenue) OVER (), 1) AS share_pct,
       ROUND(100.0 * SUM(city_revenue) OVER (
                 ORDER BY city_revenue DESC
                 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
             / SUM(city_revenue) OVER (), 1)                       AS cumulative_share_pct
FROM city_totals
ORDER BY city_revenue DESC;