redshiftTableauQueries.20.sql Maven / Gradle / Ivy

Go to download
SELECT "supplier"."s_address" AS "s_address",
  "supplier"."s_name" AS "s_name"
FROM "tpch10g"."lineitem" "lineitem"
  INNER JOIN "tpch10g"."supplier" "supplier" ON ("lineitem"."l_suppkey" = "supplier"."s_suppkey")
  INNER JOIN "tpch10g"."nation" "N2" ON ("supplier"."s_nationkey" = "N2"."n_nationkey")
  INNER JOIN "tpch10g"."part" "part" ON ("lineitem"."l_partkey" = "part"."p_partkey")
  INNER JOIN (
  SELECT COUNT(1) AS "_tableau_join_flag",
    "partsupp"."ps_partkey" AS "ps_partkey",
    "partsupp"."ps_suppkey" AS "ps_suppkey"
  FROM "tpch10g"."lineitem" "lineitem"
    INNER JOIN "tpch10g"."supplier" "supplier" ON ("lineitem"."l_suppkey" = "supplier"."s_suppkey")
    INNER JOIN "tpch10g"."partsupp" "partsupp" ON (("lineitem"."l_partkey" = "partsupp"."ps_partkey") AND ("lineitem"."l_suppkey" = "partsupp"."ps_suppkey"))
  WHERE ((NOT ("supplier"."s_nationkey" IS NULL)) AND (NOT ("lineitem"."l_partkey" IS NULL)))
  GROUP BY 2,
    3
  HAVING ((CASE WHEN (MIN("partsupp"."ps_availqty") > (0.5 * SUM((CASE WHEN (("lineitem"."l_shipdate" >= (DATE '1994-01-01')) AND ("lineitem"."l_shipdate" < DATEADD(YEAR,1,CAST((DATE '1994-01-01') AS TIMESTAMP WITHOUT TIME ZONE)))) THEN "lineitem"."l_quantity" ELSE NULL END)))) THEN 1 ELSE 0 END) = 1)
) "t0" ON (("lineitem"."l_partkey" = "t0"."ps_partkey") AND ("lineitem"."l_suppkey" = "t0"."ps_suppkey"))
WHERE (("N2"."n_name" = 'CANADA') AND (SUBSTRING("part"."p_name", 1, LENGTH('forest')) = 'forest'))
GROUP BY 1,
  2