SELECT
	v.root_guid AS drso_guid,
    MAX(cancellation_date) AS cancellation_date,
	COUNT(DISTINCT house_code) AS house_count
FROM
	agrsm.agr_rso_documents v
	INNER JOIN agrsm.agr_rso_houses rso_houses ON v.root_guid = rso_houses.agreement_guid
WHERE
	v.cancellation_date IS NOT NULL
	AND v.cancellation_date BETWEEN '2022-07-25'::date AND '2022-08-04'::date
	AND v.version BETWEEN rso_houses.lo_doc_version AND rso_houses.hi_doc_version
GROUP BY
	v.root_guid
ORDER BY
	COUNT(DISTINCT house_code) DESC;