--When nesting queries you need to transform the_geom to webmercator for Carto to render it
SELECT *, ST_Transform(the_geom,3857) AS the_geom_webmercator
FROM the_table