This message was deleted.

Here's my template SQL:

-- Aggregate date first
WITH Aggregated AS (
    SELECT
        *
    FROM table1
    WHERE __time >= '2023-12-01' AND __time < '2023-12-06'
),
-- Union date
DateSeries AS (
    SELECT DATE '2023-12-06' AS dt UNION ALL
    SELECT DATE '2023-12-05' UNION ALL
    SELECT DATE '2023-12-04' UNION ALL
    SELECT DATE '2023-12-03' UNION ALL
    SELECT DATE '2023-12-02'
)
-- Join two tables
SELECT
    ds.dt,
    ga.b_id,
    COUNT(DISTINCT r_id) AS count
FROM DateSeries ds
LEFT JOIN Aggregated ga ON 1 = 1 -- Druid only supports '=' in JOIN, so I move the conditions to WHERE, and leave one constant condition here to avoid errors
WHERE ga.__time >= TIMESTAMPADD(DAY, -1, ds.dt) AND ga.__time < ds.dt -- Apply date range filter after the join
GROUP BY ds.dt, ga.b_id
ORDER BY ds.dt, ga.b_id

Hi @Celine Zou, I tried this on Druid 28.0.0 using the new UNNEST functionality. Tested with the sample flight data. Load data:

REPLACE INTO "flights" OVERWRITE ALL
WITH "ext" AS (
  SELECT *
  FROM TABLE(
    EXTERN(
      '{"type":"http","uris":["<https://static.imply.io/example-data/flight_on_time/flights/On_Time_Reporting_Carrier_On_Time_Performance_(1987_present)_2005_11.csv.zip>"]}',
      '{"type":"csv","findColumnsFromHeader":true}'
    )
  ) EXTEND ("depaturetime" VARCHAR, "arrivalime" VARCHAR, "Year" BIGINT, "Quarter" BIGINT, "Month" BIGINT, "DayofMonth" BIGINT, "DayOfWeek" BIGINT, "FlightDate" VARCHAR, "Reporting_Airline" VARCHAR, "DOT_ID_Reporting_Airline" BIGINT, "IATA_CODE_Reporting_Airline" VARCHAR, "Tail_Number" VARCHAR, "Flight_Number_Reporting_Airline" BIGINT, "OriginAirportID" BIGINT, "OriginAirportSeqID" BIGINT, "OriginCityMarketID" BIGINT, "Origin" VARCHAR, "OriginCityName" VARCHAR, "OriginState" VARCHAR, "OriginStateFips" BIGINT, "OriginStateName" VARCHAR, "OriginWac" BIGINT, "DestAirportID" BIGINT, "DestAirportSeqID" BIGINT, "DestCityMarketID" BIGINT, "Dest" VARCHAR, "DestCityName" VARCHAR, "DestState" VARCHAR, "DestStateFips" BIGINT, "DestStateName" VARCHAR, "DestWac" BIGINT, "CRSDepTime" BIGINT, "DepTime" BIGINT, "DepDelay" BIGINT, "DepDelayMinutes" BIGINT, "DepDel15" BIGINT, "DepartureDelayGroups" BIGINT, "DepTimeBlk" VARCHAR, "TaxiOut" BIGINT, "WheelsOff" BIGINT, "WheelsOn" BIGINT, "TaxiIn" BIGINT, "CRSArrTime" BIGINT, "ArrTime" BIGINT, "ArrDelay" BIGINT, "ArrDelayMinutes" BIGINT, "ArrDel15" BIGINT, "ArrivalDelayGroups" BIGINT, "ArrTimeBlk" VARCHAR, "Cancelled" BIGINT, "CancellationCode" VARCHAR, "Diverted" BIGINT, "CRSElapsedTime" BIGINT, "ActualElapsedTime" BIGINT, "AirTime" BIGINT, "Flights" BIGINT, "Distance" BIGINT, "DistanceGroup" BIGINT, "CarrierDelay" BIGINT, "WeatherDelay" BIGINT, "NASDelay" BIGINT, "SecurityDelay" BIGINT, "LateAircraftDelay" BIGINT, "FirstDepTime" VARCHAR, "TotalAddGTime" VARCHAR, "LongestAddGTime" VARCHAR, "DivAirportLandings" VARCHAR, "DivReachedDest" VARCHAR, "DivActualElapsedTime" VARCHAR, "DivArrDelay" VARCHAR, "DivDistance" VARCHAR, "Div1Airport" VARCHAR, "Div1AirportID" VARCHAR, "Div1AirportSeqID" VARCHAR, "Div1WheelsOn" VARCHAR, "Div1TotalGTime" VARCHAR, "Div1LongestGTime" VARCHAR, "Div1WheelsOff" VARCHAR, "Div1TailNum" VARCHAR, "Div2Airport" VARCHAR, "Div2AirportID" VARCHAR, "Div2AirportSeqID" VARCHAR, "Div2WheelsOn" VARCHAR, "Div2TotalGTime" VARCHAR, "Div2LongestGTime" VARCHAR, "Div2WheelsOff" VARCHAR, "Div2TailNum" VARCHAR, "Div3Airport" VARCHAR, "Div3AirportID" VARCHAR, "Div3AirportSeqID" VARCHAR, "Div3WheelsOn" VARCHAR, "Div3TotalGTime" VARCHAR, "Div3LongestGTime" VARCHAR, "Div3WheelsOff" VARCHAR, "Div3TailNum" VARCHAR, "Div4Airport" VARCHAR, "Div4AirportID" VARCHAR, "Div4AirportSeqID" VARCHAR, "Div4WheelsOn" VARCHAR, "Div4TotalGTime" VARCHAR, "Div4LongestGTime" VARCHAR, "Div4WheelsOff" VARCHAR, "Div4TailNum" VARCHAR, "Div5Airport" VARCHAR, "Div5AirportID" VARCHAR, "Div5AirportSeqID" VARCHAR, "Div5WheelsOn" VARCHAR, "Div5TotalGTime" VARCHAR, "Div5LongestGTime" VARCHAR, "Div5WheelsOff" VARCHAR, "Div5TailNum" VARCHAR, "Unnamed: 109" VARCHAR)
)
SELECT
  TIME_PARSE("depaturetime") AS "__time",
  "arrivalime",
  "Year",
  "Quarter",
  "Month",
  "DayofMonth",
  "DayOfWeek",
  "FlightDate",
  "Reporting_Airline",
  "DOT_ID_Reporting_Airline",
  "IATA_CODE_Reporting_Airline",
  "Tail_Number",
  "Flight_Number_Reporting_Airline",
  "OriginAirportID",
  "OriginAirportSeqID",
  "OriginCityMarketID",
  "Origin",
  "OriginCityName",
  "OriginState",
  "OriginStateFips",
  "OriginStateName",
  "OriginWac",
  "DestAirportID",
  "DestAirportSeqID",
  "DestCityMarketID",
  "Dest",
  "DestCityName",
  "DestState",
  "DestStateFips",
  "DestStateName",
  "DestWac",
  "CRSDepTime",
  "DepTime",
  "DepDelay",
  "DepDelayMinutes",
  "DepDel15",
  "DepartureDelayGroups",
  "DepTimeBlk",
  "TaxiOut",
  "WheelsOff",
  "WheelsOn",
  "TaxiIn",
  "CRSArrTime",
  "ArrTime",
  "ArrDelay",
  "ArrDelayMinutes",
  "ArrDel15",
  "ArrivalDelayGroups",
  "ArrTimeBlk",
  "Cancelled",
  "CancellationCode",
  "Diverted",
  "CRSElapsedTime",
  "ActualElapsedTime",
  "AirTime",
  "Flights",
  "Distance",
  "DistanceGroup",
  "CarrierDelay",
  "WeatherDelay",
  "NASDelay",
  "SecurityDelay",
  "LateAircraftDelay",
  "FirstDepTime",
  "TotalAddGTime",
  "LongestAddGTime",
  "DivAirportLandings",
  "DivReachedDest",
  "DivActualElapsedTime",
  "DivArrDelay",
  "DivDistance",
  "Div1Airport",
  "Div1AirportID",
  "Div1AirportSeqID",
  "Div1WheelsOn",
  "Div1TotalGTime",
  "Div1LongestGTime",
  "Div1WheelsOff",
  "Div1TailNum",
  "Div2Airport",
  "Div2AirportID",
  "Div2AirportSeqID",
  "Div2WheelsOn",
  "Div2TotalGTime",
  "Div2LongestGTime",
  "Div2WheelsOff",
  "Div2TailNum",
  "Div3Airport",
  "Div3AirportID",
  "Div3AirportSeqID",
  "Div3WheelsOn",
  "Div3TotalGTime",
  "Div3LongestGTime",
  "Div3WheelsOff",
  "Div3TailNum",
  "Div4Airport",
  "Div4AirportID",
  "Div4AirportSeqID",
  "Div4WheelsOn",
  "Div4TotalGTime",
  "Div4LongestGTime",
  "Div4WheelsOff",
  "Div4TailNum",
  "Div5Airport",
  "Div5AirportID",
  "Div5AirportSeqID",
  "Div5WheelsOn",
  "Div5TotalGTime",
  "Div5LongestGTime",
  "Div5WheelsOff",
  "Div5TailNum",
  "Unnamed: 109"
FROM "ext"
PARTITIONED BY DAY

The query:

SELECT
    ds.dt,
    ga."IATA_CODE_Reporting_Airline",
    COUNT(DISTINCT "Flight_Number_Reporting_Airline") AS "count"
FROM "flights" ga
CROSS JOIN UNNEST ( ARRAY ['2005-11-01','2005-11-02','2005-11-03','2005-11-04','2005-11-05','2005-11-06']) AS ds(dt) 
WHERE TIME_IN_INTERVAL(ga.__time, '2005-11-01/P6D') AND
      ga.__time >= TIME_PARSE(ds.dt) AND ga.__time < TIMESTAMPADD( DAY, 1, TIME_PARSE(ds.dt))
GROUP BY ds.dt, ga."IATA_CODE_Reporting_Airline"
ORDER BY ds.dt, ga."IATA_CODE_Reporting_Airline"

A few things about the query structure in your example that caused problems: • the cte that filters the large dataset causes Druid to solve a subquery first and then process the data in the broker, this isn't ideal and it will be limited in size, so in my example, the "flights" table is a part of the main query • The large table in a join should always be listed first, the reason is that the native engine uses Broadcast joins which means it will parallelize (distribute processing) on the first table in a join. • The expression name

count

needs to be double quoted or renamed to avoid the keyword.

Wow, that's amazing! Sorry for the late reply, I didn't check the channel so much. Let me try your method😁

😄 cool