TPC-H Q20

/* using 19620718 as a seed to the RNG */
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 'mint%'
      )
      AND ps_availqty > (
        SELECT
          0.5 * SUM(l_quantity)
        FROM lineitem
        WHERE
          l_partkey = ps_partkey
          AND l_suppkey = ps_suppkey
          AND l_shipdate >= CAST('1996-01-01' AS DATE)
          AND l_shipdate < DATEADD(YEAR, 1, CAST('1996-01-01' AS DATE))
      )
  )
  AND s_nationkey = n_nationkey
  AND n_name = 'MOROCCO'
ORDER BY
  s_name;