Untitled

SELECT 
    BusinessDate,
    
    COUNT(DISTINCT CASE WHEN calltypecode='INBOUND' AND IsActiveFlag = 'true' THEN dialerid END) 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
         AND BusinessDate = d.BusinessDate
         UNION ALL
         SELECT COUNT(DISTINCT dialerid)
         FROM `p-cdh`.primaryactive.dialer
         WHERE ringtimeduration > 180
         AND calltypecode = 'INBOUND'
         AND IsActiveFlag = 'true'
         AND talktimeduration >= 1
         AND BusinessDate = d.BusinessDate
         UNION ALL
         SELECT COUNT(DISTINCT dialerid)
         FROM `p-cdh`.primaryactive.dialer
         WHERE dialertype = 'cti_ivr'
         AND IsActiveFlag = 'true'
         AND talktimeduration >= 1
         AND BusinessDate = d.BusinessDate
     ) AS counts) AS total_count,

    ROUND(100.0 *
        COUNT(DISTINCT CASE WHEN calltypecode = 'INBOUND' AND (TalkTimeDuration IS NULL OR TalkTimeDuration = 0) AND IsActiveFlag = 'true' THEN dialerid END) /
        NULLIF(COUNT(DISTINCT CASE WHEN calltypecode = 'INBOUND' AND IsActiveFlag = 'true' THEN dialerid END), 0), 2) AS abandoned_rate,

    ROUND(100.0 *
        (SELECT COUNT(DISTINCT dialerid)
         FROM `p-cdh`.primaryactive.dialer
         WHERE ringtimeduration <= 60
         AND talktimeduration >= 1
         AND calltypecode = 'INBOUND'
         AND IsActiveFlag = 'true'
         AND BusinessDate = d.BusinessDate) +
        (SELECT COUNT(DISTINCT dialerid)
         FROM `p-cdh`.primaryactive.dialer
         WHERE ringtimeduration > 180
         AND ringtimeduration <= 240
         AND talktimeduration >= 1
         AND calltype = 'INBOUND'
         AND IsActiveFlag = 'true'
         AND BusinessDate = d.BusinessDate)
        ) / 
        NULLIF(COUNT(DISTINCT dialerid), 0) AS percentage_answered_within_60_seconds,

    ROUND(
        (SELECT COUNT(DISTINCT d.dialerid)
         FROM `p-cdh`.primaryactive.dialer d
         WHERE d.talktimeduration >= 1
         AND d.calltype = 'INBOUND'
         AND d.IsActiveFlag = 'true'
         AND BusinessDate = d.BusinessDate) * 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'
                AND BusinessDate = d.BusinessDate), 0), 2) AS calls_per_1000_borrowers,

    ROUND(100.0 *
        COUNT(DISTINCT CASE WHEN dialertype = 'cti_ivr' AND talktimeduration >= 1 THEN dialerid END) /
        NULLIF(COUNT(DISTINCT CASE WHEN talkTIMEDURAtiON >= 1 THEN dialerid END), 0), 2) AS percentage_of_answered_by_IVR

FROM `p-cdh`.primaryactive.dialer d
WHERE IsActiveFlag = 'true'
GROUP BY BusinessDate
ORDER BY BusinessDate;