tpch.q16.sql Maven / Gradle / Ivy

Show more of this group Show more artifacts with this name
Show all versions of druid Show documentation

A JDBC datasource implementation.

There is a newer version: 1.2.23

-- database: presto; groups: tpch; tables: partsupp,part,supplier
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#45'
  AND p_type NOT LIKE 'MEDIUM POLISHED%'
  AND p_size IN (49, 14, 23, 45, 19, 3, 36, 9)
  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