I’m struggling to create a query and dashboard for my specific scenario. I have a dataset of orders with the following structure:
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!