use dataverse tpch;
set import-private-functions 'true';
 
for $l in dataset('LineItem')
where $l.l_shipdate <= '1998-09-02'
/*+ hash*/
group by $l_returnflag := $l.l_returnflag,
         $l_linestatus := $l.l_linestatus  
  with $l
order by $l_returnflag, $l_linestatus
return {
  "l_returnflag": $l_returnflag,
  "l_linestatus": $l_linestatus,
  "sum_qty": sum(for $i in $l return $i.l_quantity),
  "sum_base_price": sum(for $i in $l return $i.l_extendedprice),
  "sum_disc_price": sum(for $i in $l return $i.l_extendedprice * (1 - $i.l_discount)),
  "sum_charge": sum(for $i in $l return $i.l_extendedprice * (1 - $i.l_discount) * (1 + $i.l_tax)),
  "ave_qty": avg(for $i in $l return $i.l_quantity),  
  "ave_price": avg(for $i in $l return $i.l_extendedprice),
  "ave_disc": avg(for $i in $l return $i.l_discount),
  "count_order": count($l)
}