Get count by where in with distinct sql query

I made a query like this:

SELECT mit_rel, status, updatedate
FROM carts
WHERE mit_rel::integer in (60855, 60763, 60607)

The result is:

I want to get count(mit_rel) and get the latest status by updatedate.

>Solution :

One way is to get the maximum date and the number of entries per mit_rel. Then join the table again to get the date’s status.

SELECT
  c.mit_rel,
  c.status,
  c.updatedate,
  agg.number_of_carts
FROM
(
  SELECT 
    mit_rel,
    MAX(updatedate) AS max_updatedate,
    COUNT(*) AS number_of_carts
  FROM carts
  WHERE mit_rel in (60855, 60763, 60607)
  GROUP BY mit_rel
) agg
JOIN carts c ON c.mit_rel = agg.mit_rel AND c.updatedate = agg.max_updatedate
ORDER BY c.mit_rel;

Here is the same with window functions. We read the table just once, aggregate over the selected data and then pick the rows we want.

SELECT
  mit_rel,
  status,
  updatedate,
  number_of_carts
FROM
(
  SELECT 
    mit_rel,
    updatedate,
    status
    MAX(updatedate) OVER (PARTITION BY mit_rel) AS max_updatedate,
    COUNT(*) OVER (PARTITION BY mit_rel) AS number_of_carts
  FROM carts
  WHERE mit_rel in (60855, 60763, 60607)
) agg
WHERE updatedate = max_updatedate
ORDER BY mit_rel;