I'm mapping thousands of concert performances by Iron Maiden on a Carto map using Carto Editor. I was researching solutions to displaying multiple entries on the same point and came across the "stacking chips" solution as shared by Carto.
Using the answer to this related question on Stack Overflow, I have applied the SQL and get it to display and it looks great.
However, I would like to re-oder the chips in the columns to display in order of when the concerts happened, so the most recent concerts would be at the top of the stacks, the earliest at the bottom.
Here's my SQL query:
WITH data as ( SELECT cartodb_id, ST_SnapToGrid(the_geom,.5) as the_geom FROM iron_maiden ), m AS ( SELECT array_agg(cartodb_id) id_list, the_geom, ST_Y(the_geom) y FROM data GROUP BY the_geom ORDER BY y DESC ), f AS ( SELECT generate_series(1, array_length(id_list,1)) p, unnest(id_list) cartodb_id, the_geom FROM m ) SELECT ST_Transform(ST_Translate( f.the_geom, 0, f.p*.22 ),3857) the_geom_webmercator, f.cartodb_id, q.Header, q.Date, q.Tour, q.City, q.Country, q.Venue, q.Long, q.Lat, q.Wikipedia, q.Official_website, q.Footer FROM f, iron_maiden q WHERE f.cartodb_id = q.cartodb_id
Apologies if this is a simple question, but i'm not a programmer!