-- $Id: q04-tpch20.sql 2657 2007-06-12 16:08:15Z rdempsey $
-- 4th query in 100 GB stream0 (tpch20).

SELECT 
	S_NAME,
	S_ADDRESS
FROM 
	SUPPLIER,
	NATION
WHERE 
	S_SUPPKEY IN ( 
		SELECT
			PS_SUPPKEY
		FROM
			PARTSUPP
		WHERE
			PS_PARTKEY in ( 
				SELECT P_PARTKEY
				FROM 
					PART
				WHERE 
					P_NAME like 'orchid%%'
			) 
	AND PS_AVAILQTY > ( 
		SELECT 
			0.5 * sum(L_QUANTITY)
		FROM 
			LINEITEM
		WHERE 
			L_PARTKEY = PS_PARTKEY AND
			L_SUPPKEY = PS_SUPPKEY AND
			L_SHIPDATE >= date '1995-01-01' AND
			L_SHIPDATE < date '1995-01-01' + interval '1' year
		)
	) 
	AND S_NATIONKEY = N_NATIONKEY AND
	N_NAME = 'KENYA'
ORDER BY 
	S_NAME;