---

WITH p AS (
    SELECT CAST(:contract_number AS text) AS contract_number
)
SELECT
    p.contract_number,
    (
        SELECT count(fj.id)
        FROM report.fns_reference_request_journal fj
        WHERE fj.contract_number = p.contract_number
    ) AS requests_found,
    (
        SELECT count(fj.id)
        FROM report.fns_reference_request_journal fj
        WHERE fj.contract_number = p.contract_number
          AND fj.response_received = true
    ) AS received_responses_found,
    (
        SELECT count(snv.contract_number)
        FROM ourpension.load_snv snv
        WHERE snv.contract_number = p.contract_number
    ) AS snv_found
FROM p;