Untitled

WITH 
-- Count all distinct answered inbound calls from active customers
answered_calls AS (
    SELECT COUNT(DISTINCT d.dialerid) AS call_count
    FROM `p-cdh`.primaryactive.dialer d
    WHERE d.talktimeduration >= 1
      AND d.calltypecode = 'INBOUND'
      AND d.IsActiveFlag = 'true' 

),

-- Count distinct active customers with specified relationship types
active_customers AS (
    SELECT COUNT(DISTINCT e.partytaxregistrationnumber) AS customer_count
    FROM `p-cdh`.primaryactive.exposure e
    WHERE e.partyrelationshiptype IN ('Primary Owner', 'Co-Owner')
      AND e.IsActiveFlag = 'true'
)

-- Calculate calls per 1000 customers
SELECT 
    ROUND(
        (SELECT call_count FROM answered_calls) * 1000.0 / 
        NULLIF((SELECT customer_count FROM active_customers), 0),
        2
    ) AS calls_per_1000_customers
FROM (SELECT 1) AS dummy_table
LIMIT 1