query_recursive.sql

-- recursive query
WITH RECURSIVE region_hierarchy AS (
    -- Basis Case: Ambil semua provinsi (parent_id IS NULL)
    SELECT id, name, parent_id, population
    FROM regions
    WHERE parent_id IS NULL
    UNION
    -- Recursive Case: Tambahkan semua kota (child) dalam provinsi
    SELECT r.id, r.name, r.parent_id, r.population
    FROM regions r
    INNER JOIN region_hierarchy rh ON r.parent_id = rh.id
)
SELECT 
    rh.provinsi_id,
    rp.name AS provinsi_name,
    SUM(rh.population) AS total_population
FROM (
    -- Determine provinsi_id for each region
    SELECT id, name, 
    COALESCE(parent_id, id) AS provinsi_id, 
    population
    FROM region_hierarchy
) rh
JOIN regions rp ON rh.provinsi_id = rp.id  -- Join to get province name
GROUP BY rh.provinsi_id, rp.name
ORDER BY total_population DESC;