Select only rows with max date

Solution - 1 :

SELECT Z._id,
       Z._status_set_at
  FROM 
(
SELECT _id, 
       _status_set_at, 
       max(_status_set_at) OVER ( PARTITION BY _id ORDER BY _status_set_at DESC ) AS rnk
FROM pikta.candidates_states
) Z
WHERE Z.rnk = 1;

Solution - 2 :

SELECT A._id,
       A._status_set_at  
  FROM pikta.candidates_states A
CROSS JOIN
       ( 
         SELECT _id, 
                MAX(_status_set_at) AS max_status_set_dt         
           FROM pikta.candidates_states
         GROUP BY _id
        ) B
WHERE A._id = B._id
  AND A._status_set_at = B.max_status_set_dt; 

Your query returns what you need - only one row for each _id where column _status_set_at has its max value. You do not need to change anything in your original query.

count(_id) shows how many rows for each _id in the original table, but not in a query result. Query result has only one row for each _id because you group by _id.

This query shows that in your query result there is only one row for each _id

SELECT _id, max_status_set_at, count(_id) FROM (
SELECT _id, max(_status_set_at) max_status_set_at
FROM pikta.candidates_states
GROUP BY _id) t
GROUP BY _id

If you need apply a condition on max(_status_set_at) you can use HAVING