-- $Id: q18-tpch10.sql 2657 2007-06-12 16:08:15Z rdempsey $
-- 18th query in 100 GB stream0 (tpch10).

SELECT 
	C_CUSTKEY,
	C_NAME,
	SUM(L_EXTENDEDPRICE * (1-L_DISCOUNT)) AS REVENUE,
	C_ACCTBAL,
	N_NAME,
	C_ADDRESS,
	C_PHONE,
	C_COMMENT
FROM 
	CUSTOMER,
	ORDERS,
	LINEITEM,
	NATION
WHERE 
	C_CUSTKEY = O_CUSTKEY AND
	L_ORDERKEY = O_ORDERKEY AND
	O_ORDERDATE >= date '1994-08-01' AND
	O_ORDERDATE < date '1997-08-01' + interval '3' month AND
	L_RETURNFLAG = 'R' AND
	C_NATIONKEY = N_NATIONKEY
GROUP BY 
	C_CUSTKEY,
	C_NAME,
	C_ACCTBAL,
	C_PHONE,
	N_NAME,
	C_ADDRESS,
	C_COMMENT
ORDER BY 
	REVENUE DESC;