Aggregation of aggregation


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 (
        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!

any help appreciated

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.