group by approach

.py

latest_query = session.query(Certificate.csr_id, sqlalchemy.func.max(Certificate.not_after).label('not_after'))\
    .group_by(Certificate.csr_id).subquery('latest')
session.query(Certificate.csr_id, Certificate.not_before, Certificate.not_after)\
    .join(latest_query, sqlalchemy.and_(Certificate.csr_id == latest_query.csr_id, Certificate.not_after == latest_query.not_after))

.sql

SELECT csr_id, not_before, not_after FROM certificate
JOIN (SELECT csr_id, max(not_after) AS not_after FROM certificate GROUP BY csr_id) as latest
ON certificate.csr_id = latest.csr_id AND certificate.not_after = latest.not_after;