syrian_refugees.sql

-- Census tract population is column dp0010001

WITH op AS
  (SELECT the_geom,
          cartodb_id,
          dp0010001,
          Row_number() OVER (
                             ORDER BY Cdb_latlng(/* Starting latitude */, /* Starting longitude */) <-> the_geom) AS row_number
   FROM /* Table name */ LIMIT /* Max census tracts queryable */),
     sm AS
  (SELECT the_geom,
          cartodb_id,
          dp0010001,
          Sum(dp0010001) OVER (
                               ORDER BY row_number ASC) AS running_total
   FROM op LIMIT /* Max census tracts queryable */)
SELECT ST_AsGeoJson(ST_Union(the_geom)) AS geometry,
       'children' AS vector_group
FROM sm
WHERE running_total < /* Number of child refugees */
UNION ALL
SELECT ST_AsGeoJson(ST_Union(the_geom)) AS geometry,
       'adults' AS vector_group
FROM sm
WHERE running_total >= /* Number of child refugees */
  AND running_total < /* Number of child and adult refugees */
UNION ALL
SELECT ST_AsGeoJson(ST_Union(the_geom)) AS geometry,
       'idp' AS vector_group
FROM sm
WHERE running_total >= /* Number of child and adult refugees */
  AND running_total < /* Number of child and adult refugees and internally displaced people */