Sosto
user_3592770
sql
11 days ago
4.1 kB
4
Indexable
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;
Editor is loading...
Leave a Comment