Nested SQL select using elasticsearch

Question

How can I write this in elasticsearch:

    SELECT avg(sum) 
    FROM (
         SELECT sum(rev) as sum
         FROM table
         GROUP BY user_id
    )

Answer 1

If ES version you are using is 2.x use Avg Bucket Aggregation

{

"aggs": {
  "group_by_user": {
     "terms": {
        "field": "userId"
     },
     "aggs": {
        "rev_sum": {
           "sum": {
              "field": "rev"
           }
        }
     }
  },
  "avg_monthly_sales": {
     "avg_bucket": {
        "buckets_path": "group_by_user>rev_sum"
       }
     }
   }
 }

Hope it helps