Count based on status on latest association I have two tables in PostgreSQL like this:
Process 
- id : uuid

Log
 - created : date
 - status : string
 - parentProcess : uuid

A process can have several logs related to it, and their status can be 'SUCCESS', 'FAILED', 'PENDING'.
I would like to group all of my processes based on the latest associated Log status:




PENDING
FAILED
SUCCESS




7
4
23




For example, if a Process has 3 Logs with the following data
Process a {
   id: 1
}

Log 1 {
 - created : 2020-05-18 11:02:23.035+00
 - status: &quot;PENDING&quot;
 - parentProcess: 1
}

Log 2 {
 - created : 2020-05-18 17:57:44.002+00
 - status: &quot;SUCCESS&quot;
 - parentProcess: 1
}

Log 3 {
 - created : 2020-05-18 09:31:02.081+00
 - status: &quot;FAILED&quot;
 - parentProcess: 1
}

this process should be increase the 'SUCCESS' column because its latest log (Log 2) has 'SUCCESS' status.
How would I write the SELECT to be able to do this?
One way to accomplish this is with a window function and grouping applied using the GROUP BY clause and an aggregate function with a case statement like so:
WITH CTE_Log_Sorted AS -- Creates a CTE (can do this in a subquery too) to generate unique row numbers for each status within a partition of parentProcess starting with the latest status first (ordered by created descending).
(
    SELECT status, ROW_NUMBER() OVER (PARTITION BY parentProcess ORDER BY created DESC) AS SortId
    FROM Log
)

SELECT
    SUM(CASE WHEN status = 'PENDING' THEN 1 ELSE 0 END) AS PENDING, -- Case statements to sum up only the status we want to count in this column
    SUM(CASE WHEN status = 'FAILED' THEN 1 ELSE 0 END) AS FAILED, -- Case statements to sum up only the status we want to count in this column
    SUM(CASE WHEN status = 'SUCCESS' THEN 1 ELSE 0 END) AS SUCCESS -- Case statements to sum up only the status we want to count in this column
FROM CTE_Log_Sorted
WHERE SortId = 1 -- Filter out everything but the latest status of each parentProcess
GROUP BY status -- Group on each status so we can sum up the count of each in our SELECT clause