-- $Id: q13-tpch16.sql 2657 2007-06-12 16:08:15Z rdempsey $
-- 13th query in 100 GB stream0 (tpch16).

SELECT 
	P_BRAND,
	P_TYPE,
	P_SIZE,
	COUNT(DISTINCT PS_SUPPKEY) AS SUPPLIER_CNT
FROM 
	PARTSUPP,
	PART
WHERE 
	P_PARTKEY = PS_PARTKEY AND
	P_BRAND <> 'Brand#12' AND
	P_TYPE NOT LIKE 'SMALL BRUSHED%%' AND
	P_SIZE IN (36, 3, 24, 40, 37, 41, 15, 13) AND
	PS_SUPPKEY NOT IN ( 
		SELECT
			S_SUPPKEY
		FROM
			SUPPLIER
		WHERE
			S_COMMENT LIKE '%%Customer%%Complaints%%'
	)
GROUP BY 
	P_BRAND,
	P_TYPE,
	P_SIZE
ORDER BY 
	SUPPLIER_CNT DESC,
	P_BRAND,
	P_TYPE,
	P_SIZE;