sql-tests.testcases.hive_tpch.q20.sql Maven / Gradle / Ivy

Go to download
-- database: presto; groups: tpch; tables: supplier,nation,partsupp,lineitem,part
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 'forest%'
      )
      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('1994-01-01')
          AND l_shipdate < date('1994-01-01') + interval '1' YEAR
)
)
AND s_nationkey = n_nationkey
AND n_name = 'CANADA'
ORDER BY s_name