Untitled
user_3592770
plain_text
5 days ago
3.1 kB
2
Indexable
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;
Editor is loading...
Leave a Comment