We have a usecase where we need to use common table expressions or subqueries to achieve below use case. 
1) Is there any alternate way in pinot to achieve the same at this time. We do not want to use presto/trino etc at this time.
2) Do we know when subquery / CTE support is planned for pinot.
3) Do we know when the feature to insert rows into pinot using a query will be made available. Do we have a way to create temp tables? So we can use these tables to query next step.
4) Do we have a way to output data of a query to s3 location. I only see INSERT INTO "baseballStats" FROM FILE '<s3://my-bucket/public_data_set/baseballStats/rawdata/>' in documentation.


weekly_agg as (
 Select 
  user, sum(req_daily_cap) as req_cnt,
  sum(total_day_cnt) as total_week_cnt
 from fcap_day_agg
Group by user
)

With fcap_weekly_agg as (
Select 
  user, 
   IF (weeklyFCAP >0, Min(req_cnt, weeklyFCAP), req_cnt ) as req_weekly_cap,
   total_week_cnt 
from weekly_agg
)

Select sum (req_weekly_cap)/sum(total_week_cnt) as fcap_factor from fcap_weekly_agg