-- $Id: q11-tpch03.sql 2657 2007-06-12 16:08:15Z rdempsey $
-- 11th query in 100 GB stream0 (tpch03).

SELECT 
	L_ORDERKEY,
	SUM(L_EXTENDEDPRICE * (1-L_DISCOUNT)) AS REVENUE,
	O_ORDERDATE,
	O_SHIPPRIORITY
FROM 
	CUSTOMER,
	ORDERS,
	LINEITEM
WHERE 
	C_MKTSEGMENT = 'BUILDING' AND
	C_CUSTKEY = O_CUSTKEY AND
	L_ORDERKEY = O_ORDERKEY AND
	O_ORDERDATE < date '1995-03-03' AND
	L_SHIPDATE > date '1995-03-03'
GROUP BY 
	L_ORDERKEY,
	O_ORDERDATE,
	O_SHIPPRIORITY
ORDER BY 
	REVENUE DESC,
	O_ORDERDATE;