# Hand optimized query plan for TPC-1
#

#debug 3;

query 
  LINEITEM_25 
  loop[. feed  
    project [ L_RETURNFLAG, L_LINESTATUS, L_SHIPDATE, L_QUANTITY, L_EXTENDEDPRICE, L_DISCOUNT, L_TAX ] 
      filter[ .L_SHIPDATE < cmpdate1 ]  
        sortby[L_RETURNFLAG asc, L_LINESTATUS asc] 
          groupby[L_RETURNFLAG, L_LINESTATUS; Count_order: group feed  count , 
                  Sum_qty: group feed  sum[L_QUANTITY] , 
                  Sum_base_price: group feed  sum[L_EXTENDEDPRICE] , 
                  Sum_disc_price: group feed 
                    extend[xxxExprField: (.L_EXTENDEDPRICE * (1 - .L_DISCOUNT))] sum[xxxExprField] , 
                  Sum_charge: group feed 
                    extend[xxxExprField: ((.L_EXTENDEDPRICE * (1 - .L_DISCOUNT)) * (1 + .L_TAX))] sum[xxxExprField] , 
                  Avg_qty: group feed  avg[L_QUANTITY] , 
                  Avg_price: group feed  avg[L_EXTENDEDPRICE] , 
                  Avg_disc: group feed  avg[L_DISCOUNT] ] consume] 
              summarize {G}
                sortby[L_RETURNFLAG_G asc, L_LINESTATUS_G asc] 
                   groupby[L_RETURNFLAG_G, L_LINESTATUS_G; Count_order: group feed sum[Count_order_G] , 
                            Sum_qty: group feed  sum[Sum_qty_G] , 
                            Sum_base_price: group feed sum[Sum_base_price_G] , 
                            Sum_disc_price: group feed sum[Sum_disc_price_G] , 
                            Sum_charge: group feed sum[Sum_charge_G] , 
                            Avg_qty: group feed  avg[Avg_qty_G] , 
                            Avg_price: group feed  avg[Avg_price_G] , 
                            Avg_disc: group feed  avg[Avg_disc_G] ]
                      consume;