Solution to SQL Puzzle

submit.sql

WITH
/* Step 1  Leaf level – every City that appears in the hierarchy
       gets a row, if there is no matching entry in the sales
       table we treat its sales as 0.                             */
city_sales AS (
    SELECT  g.country ,
            g.state   ,
            g.city    ,
            COALESCE(s.sales,0) AS sales
    FROM    geo_hierarchy      AS g
    LEFT    JOIN sales_by_market AS s
           ON s.market = g.city
),

/* Step 2 Totals sitting at each State level (whether or not that
       State holds any cities in the hierarchy).                  */
state_totals AS (
    SELECT  DISTINCT g.country , g.state ,
            COALESCE(s.sales,0) AS state_sales
    FROM    geo_hierarchy      AS g
    LEFT    JOIN sales_by_market AS s
           ON s.market = g.state
),

/* Step 3  Remainder per State  =  state-level sales
       MINUS the sum of its city rows calculated in 1️            */
state_remainder AS (
    SELECT  t.country ,
            t.state   ,
            CASE                                         -- label
               WHEN t.state_sales - SUM(c.sales) = 0
                    THEN 'Entire ' || t.state            -- nothing left
               ELSE        'Rest of ' || t.state
            END              AS city ,
            t.state_sales - SUM(c.sales) AS sales
    FROM    state_totals  AS t
    LEFT    JOIN city_sales  AS c
           ON c.country = t.country
          AND c.state   = t.state
    GROUP  BY t.country, t.state, t.state_sales
),

/* Step 4  Country-level remainder (everything that is still
       undistributed after city + state rows).                    */
country_remainder AS (
    SELECT  'India'         AS country ,
            NULL            AS state  ,
            'Rest of India' AS city   ,
            (
              SELECT sales
              FROM   sales_by_market
              WHERE  market = 'India'
            )
            - (SELECT COALESCE(SUM(sales),0) FROM city_sales)
            - (SELECT COALESCE(SUM(sales),0) FROM state_remainder)
            AS sales
)

/* Step 5  Flatten the hierarchy into a single, denormalised table   */
SELECT * FROM city_sales
UNION ALL
SELECT * FROM state_remainder
UNION ALL
SELECT * FROM country_remainder
ORDER  BY country, state, city ;