I’ve a PostgreSQL question:

SELECT DISTINCT ON ("contract"."contract_id") "contract"."id" 
FROM "contract_versions" "contract" 
WHERE "contract"."client_id" = 1 
GROUP BY "contract"."contract_id", "contract"."id" 
ORDER BY "contract"."contract_id", "contract"."change_effective_date" DESC

I need to add one thing like if contract_id happens greater than as soon as then change_effective_date >= now()

I attempted utilizing:

SELECT DISTINCT ON ("contract"."contract_id") "contract"."id", 
      COUNT("contract"."contract_id") AS cnt 
FROM "contract_versions" "contract" 
WHERE "contract"."client_id" = 1 
AND CASE WHEN "cnt" > 1 THEN "contract"."change_effective_date" <= now() END 
GROUP BY "contract"."contract_id", "contract"."id" 
ORDER BY "contract"."contract_id", "contract"."change_effective_date" DESC

however its throwing an error column “cnt” doesn’t exist

Thanks