Count rows for each latest status of tickets

Question

 ticket_number |          datetime          | status
---------------+----------------------------+---------
             1 | 2020-08-03 03:52:58.048196 | replied
             1 | 2020-08-02 19:55:49.121455 | new
             2 | 2020-07-30 03:52:58.048196 | pending
             2 | 2020-07-28 20:15:41.213842 | replied
             2 | 2020-07-26 03:52:58.482911 | new
             3 | 2020-06-17 19:55:49.394628 | closed
             3 | 2020-06-14 03:52:58.513141 | replied
             3 | 2020-06-11 19:55:49.242859 | new
             4 | 2020-05-14 07:13:50.527481 | new
             5 | 2020-05-13 11:24:38.558921 | new

The above table ordered by datetime. After ordering I need to group by ticket_number so that each ticket appears only once, and pick the status for the latest datetime per ticket. Then I need to count rows for each status.

Ticket 1 would have a status of replied - replied_count = 1,
Ticket 2 would have a status of pending - pending_count = 1,
Ticket 3 would have a status of closed - closed_count = 1,
Ticket 4 would have a status of new - new_count = 1,
Ticket 5 would have a status of mew - new_count = 2

Expected Result:

replied_count  |  pending_count  |  closed_count  |  new_count
---------------+-----------------+----------------+------------
             1 |               1 |              1 |          2

Answer 1

Use

WITH cte AS ( SELECT DISTINCT
                     ticket_number, 
                     FIRST_VALUE(status) OVER (PARTITION BY ticket_number 
                                               ORDER BY datetime DESC) last_status 
              FROM test )
SELECT last_status, COUNT(last_status) 
FROM cte
GROUP BY last_status

fiddle

for to obtain needed statistic in vertical form. If you need pivotted output strictly then use CROSSTAB() or similar, or emulate it using 4 CASEs.

Answer 2

This should be faster, yet:

SELECT status, count(*)
FROM  (
   SELECT DISTINCT ON (ticket_number) status
   FROM   test
   ORDER  BY ticket_number, datetime DESC
   ) sub
GROUP  BY 1;

db<>fiddle here

Assuming datetime is defined NOT NULL.
See:

• Select first row in each GROUP BY group?

If there are many rows per ticket, one of these techniques will be (much) faster, yet:

• Optimize GROUP BY query to retrieve latest row per user

A table ticket with one row per relevant ticket would help.