hi all. I have a question about how to best proceed with my usecase. Appreciate any suggestions you might have. I have my raw source data in the following format: Raw events:

id, type, dimension1, dimension2
 1    t1     a                  
 1    t1                        
 1    t2                  b

 2    t1     a

Rows with the same id are part of the same "session" so in the example above I have 2 sessions: one with 3 events and another with 1 event. If I were to reconstitute the sessions from these individual events, they would look like this: Sessions:

id, dimension1, dimension2, countT1, countT2
 1      a           b          2        1   
 2      a                      1        0

Questions I have to answer are "how many

t1

types in sessions where dimension1 is

a

?" or "how many sessions had more than one

t1

type?". As far as I can tell, my options are: 1. storing the raw data in pinot as is and figuring out the queries for the above questions. TBH this would be my preferred route but can you help me with a sample SQL for the questions above? Also, can these queries be reasonably fast? The bit I am struggling with (my sql skills are really rusty atm) is that a naive query like

select count(type) where dimension1='a' and type='t1'

would return 1 for id 1 yet it should be 2 (see the reconstituted session for id 1). So I probably need some sort of joins but I am not sure what's the best way to do it. 2. I could try to use the upsert feature of pinot to reconstitute and store the sessions in pinot instead of the raw data. This could work although I am not sure I can do the counts with upserts (countT1/countT2). Also, since I'll have to reconstitute multiple session types (based on various other ids) and pinot requires the topic in kafka to be keyed, it means I'll have to duplicate topics in kafka just to use a different key. It seems a bit wasteful to me atm. 3. I could try to reconstitute in a job outside of pinot and insert only the final version of the session in pinot. This has the downside that I will have to wait for a session to be complete before inserting it and this means less fresh data and even lost events if they come very late (there's no end-of-session marker and events can come out of order anyway). So what would you recommend and can you help with #1 above?