Strategic Cascade Tool

Stalelast run 2025-07-01 02:00· 17,765 rows

models/analytics/int_kpi_actuals.sql

with work_orders as (
    select * from {{ ref('stg_work_orders') }}
),
complaints as (
    select * from {{ ref('stg_complaints') }}
),
districts as (
    select * from {{ ref('stg_districts') }}
),

-- Response time KPI
resp_kpi as (
    select
        district_id,
        to_char(date_trunc('quarter', opened_at), 'YYYY-"Q"Q') as period,
        'KPI-RESP-02'                                           as kpi_code,
        avg(resolution_hours)                                   as actual_value,
        3.0                                                     as target_value
    from work_orders
    where status = 'closed'
    group by 1, 2
),

-- Complaint resolution KPI
comp_kpi as (
    select
        district_id,
        to_char(date_trunc('quarter', _landed_at), 'YYYY-"Q"Q') as period,
        'KPI-SAFE-01'                                             as kpi_code,
        100.0 * sum(case when status = 'closed' then 1 else 0 end)
              / nullif(count(*), 0)                              as actual_value,
        95.0                                                      as target_value
    from complaints
    group by 1, 2
),

combined as (
    select * from resp_kpi
    union all
    select * from comp_kpi
)

select
    d.district_name,
    c.district_id,
    c.period,
    c.kpi_code,
    round(c.actual_value::numeric, 1)   as actual_value,
    c.target_value
from combined c
join districts d using (district_id)

Console

-- int_kpi_actuals · open in dbt Studio