Sosto

SELECT 
    (SELECT COUNT(DISTINCT dialerid) 
     FROM `p-cdh`.primaryactive.dialer 
     WHERE calltypecode='INBOUND' 
     AND IsActiveFlag = 'true') AS num_of_incoming_calls,

    (SELECT SUM(count_value) 
     FROM (
         SELECT COUNT(DISTINCT dialerid) AS count_value 
         FROM `p-cdh`.primaryactive.dialer 
         WHERE ringtimeduration <= 180 
         AND calltypecode = 'INBOUND' 
         AND IsActiveFlag = 'true' 
         AND talktimeduration >= 1
         UNION ALL 
         SELECT COUNT(DISTINCT dialerid) 
         FROM `p-cdh`.primaryactive.dialer 
         WHERE ringtimeduration > 180 
         AND calltypecode = 'INBOUND' 
         AND IsActiveFlag = 'true' 
         AND talktimeduration >= 1
         UNION ALL 
         SELECT COUNT(DISTINCT dialerid) 
         FROM `p-cdh`.primaryactive.dialer 
         WHERE dialertype = 'cti_ivr' 
         AND IsActiveFlag = 'true' 
         AND talktimeduration >= 1
     ) AS counts) AS total_count,

    (SELECT ROUND(100.0 * 
        (SELECT COUNT(DISTINCT dialerid) 
         FROM `p-cdh`.primaryactive.dialer 
         WHERE calltypecode = 'INBOUND' 
         AND (TalkTimeDuration IS NULL OR TalkTimeDuration = 0) 
         AND IsActiveFlag = 'true') / 
        (SELECT COUNT(DISTINCT dialerid) 
         FROM `p-cdh`.primaryactive.dialer 
         WHERE calltypecode = 'INBOUND' 
         AND IsActiveFlag = 'true'), 2) 
    ) AS abandoned_rate,

     (WITH
    -- Calls answered by CEPAL within 60 seconds (ring time ≤ 60)
    cepal_answered AS (
        SELECT COUNT(DISTINCT dialerid) AS cepal_count
        FROM `p-cdh`.primaryactive.dialer
        WHERE ringtimeduration <= 60
        AND talktimeduration >= 1
        AND calltypecode = 'INBOUND'
        AND IsActiveFlag = 'true'
    ),

    -- Calls answered by external within 60 seconds of being presented the call
    -- (after CEPAL didn't answer in 180 seconds, so total ring time 180-240)
    ext_answered_within_60 AS (
        SELECT COUNT(DISTINCT dialerid) AS ext_count
        FROM `p-cdh`.primaryactive.dialer
        WHERE ringtimeduration > 180
        AND ringtimeduration <= 240
        AND talktimeduration >= 1
        AND calltypecode = 'INBOUND'
        AND IsActiveFlag = 'true'
    ),

    -- Total calls answered within effective 60 seconds (either by CEPAL directly
    -- or by external within their 60-second window)
    total_answered_60 AS (
        SELECT
        (SELECT cepal_count FROM cepal_answered) +
        (SELECT ext_count FROM ext_answered_within_60) AS total_answered_60_count
    ),

    -- All answered inbound calls (denominator)
    total_answered_calls AS (
        SELECT COUNT(DISTINCT dialerid) AS total_answered_count
        FROM `p-cdh`.primaryactive.dialer
        WHERE calltypecode = 'INBOUND'
        AND IsActiveFlag = 'true'
        AND talktimeduration >= 1
    )

    SELECT
    ROUND(100.0 *
    (SELECT total_answered_60_count FROM total_answered_60) /
    (SELECT total_answered_count FROM total_answered_calls), 2) AS percentage_answered_within_60_seconds
    
    ) AS percentage_answered_within_60_seconds,

    (SELECT ROUND(
        (SELECT COUNT(DISTINCT d.dialerid) 
         FROM `p-cdh`.primaryactive.dialer d 
         WHERE d.talktimeduration >= 1 
         AND d.calltypecode = 'INBOUND' 
         AND d.IsActiveFlag = 'true') * 1000.0 / 
        NULLIF((SELECT COUNT(DISTINCT e.partytaxregistrationnumber) 
                FROM `p-cdh`.primaryactive.exposure e 
                WHERE e.partyrelationshiptype IN ('Primary Owner', 'Co-Owner') 
                AND e.IsActiveFlag = 'true'), 0), 2) 
    ) AS calls_per_1000_borrowers,

    (SELECT ROUND(100.0 * 
        COUNT(DISTINCT CASE WHEN dialertype = 'cti_ivr' AND talktimeduration >= 1 THEN dialerid END) / 
        COUNT(DISTINCT CASE WHEN talkTIMEDURAtiON >= 1 THEN dialerid END), 2) 
    FROM `p-cdh`.primaryactive.dialer 
    WHERE IsActiveFlag ='true') AS percentage_of_answered_by_IVR;