select
	sum(l_extendedprice) / 7.0 as avg_yearly
from
	lineitem,
	part
where
	p_partkey = l_partkey
	and p_brand = ':1'
	and p_container = ':2'
	and l_quantity < (
		select
			0.2 * avg(l_quantity)
		from
			lineitem
		where
			l_partkey = p_partkey
	);

-- Q17 Start --

Operation: table access
Options: FULL
Object_type: TABLE
Other: 
Object_owner: CALUSER01
Search_columns: 
Projection: P_PARTKEY, P_BRAND, P_CONTAINER
Object_name: PART
Alias: PART@SEL$1
Extended_information:    FP:P_CONTAINER=':2' AND P_BRAND=':1'
Access_predicates: 
Filter_predicates: P_CONTAINER=':2'&P_BRAND=':1'
Select_level: SEL$DD8D4BD4
--------------------
Operation: table access
Options: FULL
Object_type: TABLE
Other: 
Object_owner: CALUSER01
Search_columns: 
Projection: L_PARTKEY, L_QUANTITY, L_EXTENDEDPRICE
Object_name: LINEITEM
Alias: LINEITEM@SEL$1
Extended_information: 
Access_predicates: 
Filter_predicates: 
Select_level: SEL$DD8D4BD4
--------------------
Operation: hash join
Options: 
Object_type: 
Other: 
Object_owner: 
Search_columns: 
Projection:  P_PARTKEY, L_PARTKEY, P_CONTAINER, P_BRAND, L_EXTENDEDPRICE, L_QUANTITY
Object_name: 
Alias: 
Extended_information:    AP:P_PARTKEY=L_PARTKEY
Access_predicates: P_PARTKEY=L_PARTKEY
Filter_predicates: 
Select_level: 
--------------------
Operation: window
Options: SORT
Object_type: 
Other: 
Object_owner: 
Search_columns: 
Projection:  L_PARTKEY, P_PARTKEY, L_QUANTITY, P_CONTAINER, P_BRAND, L_EXTENDEDPRICE, AVG(L_QUANTITY) OVER ( PARTITION BY L_PARTKEY)
Object_name: 
Alias: 
Extended_information: 
Access_predicates: 
Filter_predicates: 
Select_level: SEL$DD8D4BD4
--------------------
Operation: view
Options: 
Object_type: VIEW
Other: 
Object_owner: SYS
Search_columns: 
Projection: L_EXTENDEDPRICE
Object_name: 
Alias: VW_WIF_1@SEL$D99A5D2D
Extended_information: 
Access_predicates: 
Filter_predicates: 
Select_level: SEL$DD8D4BD4
--------------------
Operation: sort
Options: AGGREGATE
Object_type: 
Other: 
Object_owner: 
Search_columns: 
Projection:  SUM(L_EXTENDEDPRICE)
Object_name: 
Alias: 
Extended_information: 
Access_predicates: 
Filter_predicates: 
Select_level: SEL$C15DFC48
--------------------
Operation: select statement
Options: 
Object_type: 
Other: 
Object_owner: 
Search_columns: 
Projection: 
Object_name: 
Alias: 
Extended_information: 
Access_predicates: 
Filter_predicates: 
Select_level: 
--------------------

>SELECT MAIN
>>Returned Columns
ArithmeticColumn: AggregateColumn sum(L_EXTENDEDPRICE)
ArithmeticColumn: SimpleColumn L_EXTENDEDPRICE
  s/t/c/T/A: ///0/



>>Filters
SimpleFilter
  SimpleColumn tpch.PART.P_CONTAINER
  s/t/c/T/A: tpch/PART/P_CONTAINER/52/PART
  Operator: =  ConstantColumn: :2(l)
SimpleFilter
  SimpleColumn tpch.PART.P_BRAND
  s/t/c/T/A: tpch/PART/P_BRAND/49/PART
  Operator: =  ConstantColumn: :1(l)
Operator: AND
SimpleFilter
  SimpleColumn tpch.PART.P_PARTKEY
  s/t/c/T/A: tpch/PART/P_PARTKEY/46/PART
  Operator: =  SimpleColumn tpch.LINEITEM.L_PARTKEY
  s/t/c/T/A: tpch/LINEITEM/L_PARTKEY/26/LINEITEM

Operator: and
>>Group By Columns
ArithmeticColumn: AggregateColumn sum(L_EXTENDEDPRICE)
ArithmeticColumn: SimpleColumn L_EXTENDEDPRICE
  s/t/c/T/A: ///0/



SessionID: 2881
TxnID: 70
VerID: 70