Untitled

WITH emailll AS (
    WITH leadss AS (
        SELECT DISTINCT u."phone", u."email", l."createdAt", l."id", l."comment", l."source"
        FROM "Leads" l          
        JOIN "User" u ON l."userId" = u."id"        
        JOIN "MasterClassSlots" mcs ON l."masterclassSlotId" = mcs."id"        
        JOIN "MasterClass" mc ON mc."id" = mcs."masterClassId"        
        JOIN "Bootcamp" b ON mc."bootcampId" = b."id"        
        JOIN "User" u2 ON b."teacherId" = u2."id"        
        JOIN "Categories" cat ON u2."categoryId" = cat."id"        
        WHERE l."createdAt" BETWEEN ${from}::timestamp AND ${to}::timestamp 
        AND cat."name" = ${category}    
        AND u."email" IS NOT NULL
        AND u."email" <> ''
        AND l."source" NOT IN ('ret', 'arvind.tech', 'act*', 'retdm', 'null', 'dm', 'email', 'push-notification', 'sms', 'freshdm', 'retp', 'api-ops', 'act', 'Zoom Reschedule', 'Livekit_Reschedule')
    ),
    paymentss AS (
        SELECT DISTINCT u."phone", u."email", pi."createdAt", pi."id", pi."amount"
        FROM "PaymentIntent" pi          
        JOIN "User" u ON pi."userId" = u."id"               
        JOIN "Bootcamp" b ON pi."bootcampId" = b."id"        
        JOIN "User" u2 ON b."teacherId" = u2."id"        
        JOIN "Categories" cat ON u2."categoryId" = cat."id"        
        WHERE pi."createdAt" BETWEEN ${from}::timestamp AND ${to}::timestamp 
        AND cat."name" = ${category}    
        AND pi."status" = '1'
        AND CAST(pi."amount" AS int) > 39900
    )
    SELECT DISTINCT leadss."email", leadss."phone", paymentss."id", paymentss."amount" 
    FROM leadss
    JOIN paymentss ON leadss."email" = paymentss."email"
    WHERE leadss."createdAt" < paymentss."createdAt"
    ),
    phoneee AS (
    SELECT DISTINCT u."email", u."phone", pi."id", pi."amount"
    FROM "PaymentIntent" pi
    JOIN "User" u ON u."id" = pi."userId"
    JOIN "Leads" l ON l."userId" = pi."userId"
    JOIN "Bootcamp" b ON pi."bootcampId" = b."id"
    JOIN "User" u2 ON b."teacherId" = u2."id"
    JOIN "Categories" cat ON u2."categoryId" = cat."id"
    WHERE pi."createdAt" BETWEEN ${from}::timestamp AND ${to}::timestamp 
    AND l."createdAt" BETWEEN ${from}::timestamp AND ${to}::timestamp
    AND l."createdAt" < pi."createdAt"
    AND l."source" NOT IN ('ret', 'arvind.tech', 'act*', 'retdm', 'null', 'dm', 'email', 'push-notification', 'sms', 'freshdm', 'retp', 'api-ops', 'act', 'Zoom Reschedule', 'Livekit_Reschedule')
    AND cat."name" = ${category}     
    AND pi."status" = '1'
    AND CAST(pi."amount" AS int) > 39900
    ),
    onlyemail AS (
    SELECT emailll.*
    FROM emailll
    WHERE emailll."phone" NOT IN (
        SELECT ph."phone"
        FROM phoneee ph
    )
    ), 
    payment_all AS (
    SELECT DISTINCT pi."id", pi."amount"
    FROM "Leads" l
    JOIN "PaymentIntent" pi ON pi."userId" = l."userId"
    JOIN "Bootcamp" b ON pi."bootcampId" = b."id"
    JOIN "User" u ON b."teacherId" = u."id"
    JOIN "Categories" cat ON u."categoryId" = cat."id"
    WHERE 
        cat."name" = ${category}    
        AND l."createdAt" BETWEEN ${from}::timestamp AND ${to}::timestamp 
        AND pi."createdAt" BETWEEN ${from}::timestamp AND ${to}::timestamp 
        AND l."source" NOT IN ('ret', 'arvind.tech', 'act*', 'retdm', 'null', 'cal', 'dm', 'email', 'push-notification', 'sms', 'freshdm', 'retp', 'api-ops', 'act', 'Zoom Reschedule', 'Livekit_Reschedule')
        AND pi."createdAt" + INTERVAL '1 minute' > l."createdAt"
        AND pi."status" = '1'
                
        UNION ALL
        SELECT "id", "amount" 
        FROM "onlyemail")

    SELECT 
    count (id) 
    FROM payment_all;