collection rate query

collection rate

WITH total_loans AS (
    SELECT 
        branch_id, 
        SUM(amount) AS amount
    FROM loans
    WHERE disbursement_date::date BETWEEN '2025-08-01' AND DATE '2025-08-12'
    GROUP BY branch_id
),
cycles_summary AS (
    SELECT 
        loans.branch_id,
        SUM(CASE WHEN cycle_number = 0 THEN cycles.amount ELSE 0 END) AS processing_fees,
        SUM(CASE WHEN cycle_number >= 1 THEN cycles.amount ELSE 0 END) AS amount,
        SUM(cycles.interest) AS interest
    FROM loans
    LEFT JOIN cycles ON loans.id = cycles.loan_id
    WHERE date::date BETWEEN '2025-08-01' AND DATE '2025-08-12'
    GROUP BY loans.branch_id
),
payments_summary AS (
    SELECT
    loans.branch_id,
    SUM(CASE WHEN payment_type_id IN ('2') THEN payments.reference_amount::FLOAT ELSE 0 END) as overall_collection,          
    SUM(CASE WHEN payment_type_id IN ('2') AND (cycles.date + INTERVAL '1 DAY') >= payments.date::date THEN payments.reference_amount::FLOAT ELSE 0 END) as on_time_collection,
    SUM(CASE WHEN payment_type_id IN ('2') AND payments.date::date > (cycles.date + INTERVAL '1 DAY') AND (cycles.date + INTERVAL '7 DAY') <= payments.date::date THEN payments.reference_amount::FLOAT ELSE 0 END) as dd7,
    SUM(CASE WHEN payment_type_id IN ('2') AND payments.date::date > (cycles.date + INTERVAL '1 DAY') AND (cycles.date + INTERVAL '7 DAY') > payments.date::date THEN payments.reference_amount::FLOAT ELSE 0 END) as cg7
    FROM loans
    LEFT JOIN cycles ON loans.id = cycles.loan_id
    LEFT JOIN payments ON cycles.id = payments.cycle_id
    WHERE payments.date::date BETWEEN '2025-08-01' AND DATE '2025-08-12'
    GROUP BY loans.branch_id
)
SELECT
    branches.id AS branch_id,
    branches.name AS branch_name,
    COALESCE(total_loans.amount, 0) AS total_amount_disbursed,
    COALESCE(cycles_summary.processing_fees, 0) AS processing_fees,
    COALESCE(cycles_summary.amount, 0) AS installments_amount,
    COALESCE(cycles_summary.interest, 0) AS installments_interest,
    -- COALESCE(payments_summary.charges, 0) AS payment_charges,
    -- COALESCE(payments_summary.penalties, 0) AS payment_penalties,
    COALESCE(payments_summary.overall_collection, 0) AS overall_collection,
    COALESCE(payments_summary.on_time_collection, 0) AS on_time_collection,
    COALESCE(payments_summary.dd7, 0) AS dd7,
    COALESCE(payments_summary.cg7, 0) AS cg7
FROM branches
LEFT JOIN total_loans ON branches.id = total_loans.branch_id
LEFT JOIN cycles_summary ON branches.id = cycles_summary.branch_id
LEFT JOIN payments_summary ON branches.id = payments_summary.branch_id;