SQL Library

Purpose: Pair including/excluding-bitumen versions of the same road activity and period.

SELECT i.activity, i.pct_yoy AS incl_bitumen, e.pct_yoy AS excl_bitumen,
       ROUND(e.pct_yoy - i.pct_yoy, 1) AS gap_pp
FROM civil_material_index i
JOIN civil_material_index e
  ON e.activity=i.activity AND e.period=i.period
 AND e.bitumen_treatment='excluded'
WHERE i.bitumen_treatment='included'
  AND i.period='2025-10'
ORDER BY gap_pp DESC;

Purpose: Create YoY movement and 12-month rolling volatility without altering the official series.

WITH x AS (
  SELECT series_code, period, index_value,
         100.0*(index_value/LAG(index_value,12) OVER (PARTITION BY series_code ORDER BY period)-1) AS yoy_pct
  FROM material_index
)
SELECT series_code, period, yoy_pct,
       STDDEV_SAMP(yoy_pct) OVER (PARTITION BY series_code ORDER BY period ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) AS vol_12m
FROM x;

Purpose: Aggregate intensity-weighted road-project signals by month without using contract monetary values.

SELECT date_trunc('month', tender_start) AS month,
       SUM(asphalt_intensity_score) AS pressure_points,
       COUNT(*) FILTER (WHERE asphalt_intensity_score >= 0.75) AS high_intensity_projects
FROM road_pipeline
WHERE province='Western Cape'
GROUP BY 1
ORDER BY 1;

Purpose: Measure median tender-to-award time by civil work type.

SELECT work_type,
       COUNT(*) AS processes,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY award_date-tender_start) AS median_days_to_award
FROM contracting_process_latest
WHERE sector='Civil infrastructure' AND award_date IS NOT NULL
GROUP BY work_type
ORDER BY processes DESC;

Purpose: Identify packages with old unresolved RFIs using a robust median age metric.

SELECT package_code,
       COUNT(*) FILTER (WHERE status='Open') AS open_rfis,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY current_date-created_date)
         FILTER (WHERE status='Open') AS median_open_age_days
FROM rfi_fact
GROUP BY package_code
ORDER BY median_open_age_days DESC;

Purpose: Summarise last check, last success and recent failures for every data source.

SELECT source_id, MAX(checked_at) AS last_check,
       MAX(checked_at) FILTER (WHERE status='ok') AS last_success,
       COUNT(*) FILTER (WHERE status<>'ok' AND checked_at>current_timestamp-interval '7 days') AS failures_7d
FROM source_run
GROUP BY source_id;

Purpose: Keep release history but select the latest state of each contracting process for analysis.

WITH ranked AS (
  SELECT ocid, release_date, raw_json,
         ROW_NUMBER() OVER (PARTITION BY ocid ORDER BY release_date DESC, release_key DESC) AS rn
  FROM etender_release
)
SELECT * FROM ranked WHERE rn = 1;

Purpose: Count classified road processes by stage and likely asphalt intensity without using tender monetary values.

SELECT stage, asphalt_intensity, COUNT(*) AS processes
FROM etender_process_latest
WHERE is_western_cape = TRUE
  AND sector = 'Roads / pavement'
GROUP BY stage, asphalt_intensity
ORDER BY stage, asphalt_intensity;

Purpose: Select the latest state of every activity while retaining the monthly snapshot history.

WITH ranked AS (\n SELECT *, ROW_NUMBER() OVER(PARTITION BY activity_id ORDER BY snapshot_date DESC) rn\n FROM project_control_snapshot\n)\nSELECT * FROM ranked WHERE rn=1;

Purpose: Identify activities whose float is deteriorating between reporting periods.

SELECT activity_id, snapshot_date, total_float_days,\n total_float_days - LAG(total_float_days) OVER(PARTITION BY activity_id ORDER BY snapshot_date) AS float_change_days\nFROM project_control_snapshot;

Purpose: Summarise PASS/WARN/FAIL results without hiding warnings.

SELECT area, status, COUNT(*) AS tests\nFROM validation_result\nGROUP BY area, status\nORDER BY area, status;