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

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