Aggregation of aggregation

Elastic Stack Elasticsearch
1

Hello,

I’m struggling to create a query and dashboard for my specific scenario. I have a dataset of orders with the following structure:

order_id order_status timestamp
1 started 01.01.2023
1 in_progress 02.01.2023
2 started 02.01.2023
1 complete 03.01.2023
2 in_progress 04.01.2023

What I need is the latest status of each order along with the count of those statuses. In SQL terms, the query would look like this:

WITH ranked AS (
    SELECT 
        order_id, 
        order_status, 
        ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY timestamp DESC) AS r
    FROM orders
)
SELECT order_status, COUNT(*) 
FROM ranked
WHERE r = 1
GROUP BY order_status;

I appreciate any guidance on how to achieve this in elasticsearch query and then kibana. Thank you!

2

any help appreciated