Count rows for each latest status of tickets
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.