Double group by with timeseries

Say I have a table with dimensions “color” and “size”, and a metric “price”. I would like the top 10 color-size pairings based on price, but then broken down into 1 minute buckets.

I can get the top 10 easily enough with:
SELECT color, size, SUM(price) FROM table GROUP BY 1,2 ORDER BY 3 LIMIT 10 DESC

However, how do I get the SUM(price) of those top 10 pairings in 1 minute buckets?

I’ve tried numerous things (GROUPINGS, adding FLOOR(__time, MINUTE) as a group by), but nothing quite works.

Any help is appreciated!

Hey! I think you will want to GROUP BY TIME_FLOOR of something - that will give you a row per 10-minute interval.


(can’t remember it it’s " or '…!!)

Thanks for the response, Peter!

The issue is that if I do:
SELECT color, size, TIME_FLOOR(__time,‘PT10M’), SUM(price) FROM table GROUP BY 1,2,3 ORDER BY 3 LIMIT 10 DESC
it just gives me the top 10 but now includes the time bucket as part of that top 10 (it only returns 10 results), whereas I want the top 10 grouped by 1,2 and then have the timeseries for each of those results, if that makes sense.

Maybe a JOIN would work?

Yeah I think so… something like

red, big, 10:00, 6
red, big, 10:05, 4

red, big, 10:10, 1

red, small, 10:00, 54

red, small, 10:10, 19

blue, big, 10:00, 43

Subquery may work - inner being the TOP 10 and the outer being the TIME_FLOOR?

I wonder also if GROUPING SETS might give you something… hmmm… scratches head