title: How to perform complex query on aggregated fields in ElasticSearch
body: <p>I am trying to figure out how to perform a complex query in elastic search, let say I have the following table of data:</p>
  <p><a href="https://i.stack.imgur.com/7j9qn.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/7j9qn.png" alt="enter image description here" /></a></p>
  <p>Which I got from the following query</p>
  <pre><code>{
    &quot;aggs&quot;: {
      &quot;3&quot;: {
        &quot;terms&quot;: {
          &quot;field&quot;: &quot;ColumnA&quot;,
          &quot;order&quot;: {
            &quot;_key&quot;: &quot;desc&quot;
          },
          &quot;size&quot;: 50
        },
        &quot;aggs&quot;: {
          &quot;4&quot;: {
            &quot;terms&quot;: {
              &quot;field&quot;: &quot;ColumnB&quot;,
              &quot;order&quot;: {
                &quot;_key&quot;: &quot;desc&quot;
              },
              &quot;size&quot;: 50
            },
            &quot;aggs&quot;: {
              &quot;5&quot;: {
                &quot;terms&quot;: {
                  &quot;field&quot;: &quot;ColumnC&quot;,
                  &quot;order&quot;: {
                    &quot;_key&quot;: &quot;desc&quot;
                  },
                  &quot;size&quot;: 50
                },
                &quot;aggs&quot;: {
                  &quot;sum_of_views&quot;: {
                    &quot;sum&quot;: {
                      &quot;field&quot;: &quot;views&quot;
                    }
                  },
                  &quot;sum_of_costs&quot;: {
                    &quot;sum&quot;: {
                      &quot;field&quot;: &quot;cost&quot;
                    }
                  },
                  &quot;sum_of_clicks&quot;: {
                    &quot;sum&quot;: {
                      &quot;field&quot;: &quot;clicks&quot;
                    }
                  },
                  &quot;sum_of_earned&quot;: {
                    &quot;sum&quot;: {
                      &quot;field&quot;: &quot;earned&quot;
                    }
                  },
                  &quot;sum_of_adv_earned&quot;: {
                    &quot;sum&quot;: {
                      &quot;field&quot;: &quot;adv_earned&quot;
                    }
                  }
                }
              }
            }
          }
        }
      }
    },
    &quot;size&quot;: 0,
    &quot;_source&quot;: {
      &quot;excludes&quot;: []
    },
    &quot;stored_fields&quot;: [
      &quot;*&quot;
    ],
    &quot;script_fields&quot;: {},
    &quot;docvalue_fields&quot;: [
      {
        &quot;field&quot;: &quot;hour&quot;,
        &quot;format&quot;: &quot;date_time&quot;
      }
    ],
    &quot;query&quot;: {
      &quot;bool&quot;: {
        &quot;must&quot;: [],
        &quot;filter&quot;: [
          {
            &quot;match_all&quot;: {}
          },
          {
            &quot;range&quot;: {
              &quot;hour&quot;: {
                &quot;format&quot;: &quot;strict_date_optional_time&quot;,
                &quot;gte&quot;: &quot;2019-08-08T06:29:34.723Z&quot;,
                &quot;lte&quot;: &quot;2020-08-08T06:29:34.724Z&quot;
              }
            }
          }
        ],
        &quot;should&quot;: [],
        &quot;must_not&quot;: []
      }
    }
  }
  </code></pre>
  <p>Now for example, if I want to get the records that have the following condition</p>
  <pre><code>(sum_of_clicks / sum_of_views) * (sum_of_earned2 / sum_of_earned1) &lt; 0.5
  </code></pre>
  <p>What should I query?</p>