SELECT
	v.root_guid AS drso_guid,
    MAX(supply_period_end) AS supply_period_end,
	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
	INNER JOIN agrsm.agr_rso_house_services house_serv ON house_serv.rso_house_guid = rso_houses.guid
WHERE
	house_serv.supply_period_end IS NOT NULL
	AND house_serv.supply_period_end 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;