-- Yatify / The Approval Gap
-- Monthly descriptive pipeline view.
-- IMPORTANT: completed/plans in the same month is NOT a conversion rate.

SELECT
    month,
    plans_passed_current_r000,
    buildings_completed_current_r000,
    ROUND(
        100.0 * buildings_completed_current_r000
        / NULLIF(plans_passed_current_r000,0), 1
    ) AS same_period_completed_to_plans_pct
FROM approval_gap_monthly
ORDER BY month;

-- Real-price downstream/upstream movement.
SELECT
    month,
    plans_passed_constant_2019_r000,
    buildings_completed_constant_2019_r000,
    plans_passed_constant_2019_r000
      - buildings_completed_constant_2019_r000 AS real_value_gap_r000
FROM approval_gap_monthly
ORDER BY month;

-- Province-level direction check.
SELECT
    province,
    plans_yoy_pct,
    completed_yoy_pct,
    completed_yoy_pct - plans_yoy_pct AS direction_gap_pp
FROM approval_gap_province
ORDER BY ABS(completed_yoy_pct - plans_yoy_pct) DESC;