Aggregated content from clustered features II - Average Population

<!DOCTYPE html>
<html>
  <head>
    <title>Aggregated content from clustered features II - Average Population| CartoDB</title>
    <meta name="viewport" content="initial-scale=1.0, user-scalable=no" />
    <meta http-equiv="content-type" content="text/html; charset=UTF-8"/>
    <link rel="shortcut icon" href="https://cartodb.com/assets/favicon.ico" />
    
    <style>
      html, body, #map {
        height: 100%;
        padding: 0;
        margin: 0;
      }
    </style>

<script src="https://cartodb-libs.global.ssl.fastly.net/cartodb.js/v3/3.15/cartodb.js"></script>

<script type="infowindow/html" id="infowindow_template">
      <div class="cartodb-popup">
        <a href="#close" class="cartodb-popup-close-button close">x</a>

<div class="cartodb-popup-content-wrapper">
           <div class="cartodb-popup-content">
             
             <h4>Features Count</h4>
             <p>{{content.data.points_count}}</p>
             <h4>Average Population</h4>
             <p>{{content.data.average}}</p>
           </div>
         </div>
         <div class="cartodb-popup-tip-container"></div>
      </div>
    </script>

bigs AS (SELECT * FROM (SELECT ST_Centroid(ST_Collect(i.the_geom_webmercator)) as the_geom_webmercator, count(i.cartodb_id) as points_count, 1 as cartodb_id, array_agg(cartodb_id) AS id_list, avg(pop_max) AS average FROM hgridA, (select * from ne_10m_populated_places_simple_7_copy) i where ST_Intersects(i.the_geom_webmercator, hgridA.cell) GROUP BY hgridA.cell) t WHERE points_count > 100 ),

hgridB AS (SELECT CDB_HexagonGrid(ST_Expand(!bbox!, greatest(!pixel_width!,!pixel_height!) * 36), greatest(!pixel_width!,!pixel_height!) * 36) as cell),

mids AS (SELECT * FROM (SELECT ST_Centroid(ST_Collect(i.the_geom_webmercator)) as the_geom_webmercator, count(i.cartodb_id) as points_count, 1 as cartodb_id, array_agg(cartodb_id) AS id_list, avg(pop_max) AS average FROM hgridB, (select * from ne_10m_populated_places_simple_7_copy) i where ST_Intersects(i.the_geom_webmercator, hgridB.cell) AND cartodb_id NOT IN (SELECT unnest(id_list) FROM bigs) GROUP BY hgridB.cell) t WHERE points_count > 25 ),

hgridC AS (SELECT CDB_HexagonGrid(ST_Expand(!bbox!, greatest(!pixel_width!,!pixel_height!) * 12), greatest(!pixel_width!,!pixel_height!) * 12) as cell),

smalls AS (SELECT * FROM (SELECT ST_Centroid(ST_Collect(i.the_geom_webmercator)) as the_geom_webmercator, count(i.cartodb_id) as points_count, 1 as cartodb_id, array_agg(cartodb_id) AS id_list, avg(pop_max) AS average FROM hgridC, (select * from ne_10m_populated_places_simple_7_copy) i where ST_Intersects(i.the_geom_webmercator, hgridC.cell) AND cartodb_id NOT IN (SELECT unnest(id_list) FROM bigs) AND cartodb_id NOT IN (SELECT unnest(id_list) FROM mids) GROUP BY hgridC.cell) t WHERE points_count > 1 )

SELECT the_geom_webmercator, 1 points_count, cartodb_id, ARRAY[cartodb_id], pop_max AS average, 'origin' as src FROM ne_10m_populated_places_simple_7_copy WHERE cartodb_id NOT IN (select unnest(id_list) FROM bigs) AND cartodb_id NOT IN (select unnest(id_list) FROM mids) AND cartodb_id NOT IN (select unnest(id_list) FROM smalls)

UNION ALL
    SELECT *, 'bigs' as src FROM bigs
    UNION ALL 
    SELECT *, 'mids' as src FROM mids
    UNION ALL 
    SELECT *, 'smalls' as src FROM smalls
    </script>
    <style type="text/cartocss" id="cartoCSS">
    #layer{  
      marker-width: 12;  
      marker-fill: #5CA2D1;  
      marker-line-width: 1.5;  
      marker-fill-opacity: 0.8;  
      marker-line-opacity: 0;  
      marker-line-color: #fff; 
      marker-allow-overlap: true;

[src = 'smalls'] {marker-line-width: 0; marker-width: 12;  }   
      [src = 'mids'] {marker-line-width: 0; marker-width:36;  }  
      [src = 'bigs'] {marker-line-width: 0; marker-width: 48;  }

[ average <= 35676000] {   marker-fill: #BD0026;}  
      [ average <= 300424] {   marker-fill: #F03B20;}  
      [ average <= 111461] {   marker-fill: #FD8D3C;}  
      [ average <= 47653] {   marker-fill: #FECC5C;}  
      [ average <= 15601] {   marker-fill: #FFFFB2;}  
    }
    </style>

</script>

</body>
</html>

https://cartodb-libs.global.ssl.fastly.net/cartodb.js/v3/3.15/cartodb.js