How to write a efficient ES query for the below SQL query?

Elastic Stack Elasticsearch
2

If you have your documents ingested appropriately like mentioned here, you could use the translate endpoint of Elasticsearch SQL and convert the SQL query to equivalent Query DSL.

--
Aravind