redshiftTableauQueries.21.sql Maven / Gradle / Ivy

Go to download
Show more of this group Show more artifacts with this name
Show all versions of verdictdb-core Show documentation
Platform-independent, interactive-speed data analytics system
There is a newer version: 0.5.8
SELECT "supplier"."s_name" AS "s_name",
  SUM(1) AS "sum_number_of_records_ok"
FROM "tpch10g"."lineitem" "lineitem"
  INNER JOIN "tpch10g"."orders" "orders" ON ("lineitem"."l_orderkey" = "orders"."o_orderkey")
  INNER JOIN "tpch10g"."supplier" "supplier" ON ("lineitem"."l_suppkey" = "supplier"."s_suppkey")
  INNER JOIN "tpch10g"."customer" "customer" ON ("orders"."o_custkey" = "customer"."c_custkey")
  INNER JOIN "tpch10g"."nation" "N2" ON ("supplier"."s_nationkey" = "N2"."n_nationkey")
  INNER JOIN "tpch10g"."nation" "N1" ON ("customer"."c_nationkey" = "N1"."n_nationkey")
  INNER JOIN (
  SELECT COUNT(1) AS "_tableau_join_flag",
    "lineitem"."l_orderkey" AS "none_l_orderkey__copy__ok"
  FROM "tpch10g"."lineitem" "lineitem"
    INNER JOIN "tpch10g"."orders" "orders" ON ("lineitem"."l_orderkey" = "orders"."o_orderkey")
    INNER JOIN "tpch10g"."supplier" "supplier" ON ("lineitem"."l_suppkey" = "supplier"."s_suppkey")
    INNER JOIN "tpch10g"."customer" "customer" ON ("orders"."o_custkey" = "customer"."c_custkey")
    INNER JOIN "tpch10g"."nation" "N1" ON ("customer"."c_nationkey" = "N1"."n_nationkey")
  WHERE ((NOT ("supplier"."s_nationkey" IS NULL)) AND (((NOT ("N1"."n_regionkey" IS NULL)) AND (NOT ("lineitem"."l_partkey" IS NULL))) AND (NOT ("lineitem"."l_partkey" IS NULL))))
  GROUP BY 2
  HAVING (COUNT(DISTINCT (CASE WHEN ("lineitem"."l_commitdate" < "lineitem"."l_receiptdate") THEN "lineitem"."l_suppkey" ELSE NULL END)) = 1)
) "t0" ON ("lineitem"."l_orderkey" = "t0"."none_l_orderkey__copy__ok")
  INNER JOIN (
  SELECT COUNT(1) AS "_tableau_join_flag",
    "lineitem"."l_orderkey" AS "none_l_orderkey_ok"
  FROM "tpch10g"."lineitem" "lineitem"
    INNER JOIN "tpch10g"."orders" "orders" ON ("lineitem"."l_orderkey" = "orders"."o_orderkey")
    INNER JOIN "tpch10g"."supplier" "supplier" ON ("lineitem"."l_suppkey" = "supplier"."s_suppkey")
    INNER JOIN "tpch10g"."customer" "customer" ON ("orders"."o_custkey" = "customer"."c_custkey")
    INNER JOIN "tpch10g"."nation" "N1" ON ("customer"."c_nationkey" = "N1"."n_nationkey")
  WHERE ((NOT ("supplier"."s_nationkey" IS NULL)) AND (((NOT ("N1"."n_regionkey" IS NULL)) AND (NOT ("lineitem"."l_partkey" IS NULL))) AND (NOT ("lineitem"."l_partkey" IS NULL))))
  GROUP BY 2
  HAVING (COUNT(DISTINCT "lineitem"."l_suppkey") > 1)
) "t1" ON ("lineitem"."l_orderkey" = "t1"."none_l_orderkey_ok")
  INNER JOIN (
  SELECT TOP 100 "supplier"."s_name" AS "s_name",
    COUNT(1) AS "_tableau_join_flag",
    SUM(1) AS "___alias__0"
  FROM "tpch10g"."lineitem" "lineitem"
    INNER JOIN "tpch10g"."orders" "orders" ON ("lineitem"."l_orderkey" = "orders"."o_orderkey")
    INNER JOIN "tpch10g"."supplier" "supplier" ON ("lineitem"."l_suppkey" = "supplier"."s_suppkey")
    INNER JOIN "tpch10g"."customer" "customer" ON ("orders"."o_custkey" = "customer"."c_custkey")
    INNER JOIN "tpch10g"."nation" "N2" ON ("supplier"."s_nationkey" = "N2"."n_nationkey")
    INNER JOIN "tpch10g"."nation" "N1" ON ("customer"."c_nationkey" = "N1"."n_nationkey")
    INNER JOIN (
    SELECT COUNT(1) AS "_tableau_join_flag",
      "lineitem"."l_orderkey" AS "none_l_orderkey__copy__ok"
    FROM "tpch10g"."lineitem" "lineitem"
      INNER JOIN "tpch10g"."orders" "orders" ON ("lineitem"."l_orderkey" = "orders"."o_orderkey")
      INNER JOIN "tpch10g"."supplier" "supplier" ON ("lineitem"."l_suppkey" = "supplier"."s_suppkey")
      INNER JOIN "tpch10g"."customer" "customer" ON ("orders"."o_custkey" = "customer"."c_custkey")
      INNER JOIN "tpch10g"."nation" "N1" ON ("customer"."c_nationkey" = "N1"."n_nationkey")
    WHERE ((NOT ("supplier"."s_nationkey" IS NULL)) AND (((NOT ("N1"."n_regionkey" IS NULL)) AND (NOT ("lineitem"."l_partkey" IS NULL))) AND (NOT ("lineitem"."l_partkey" IS NULL))))
    GROUP BY 2
    HAVING (COUNT(DISTINCT (CASE WHEN ("lineitem"."l_commitdate" < "lineitem"."l_receiptdate") THEN "lineitem"."l_suppkey" ELSE NULL END)) = 1)
  ) "t2" ON ("lineitem"."l_orderkey" = "t2"."none_l_orderkey__copy__ok")
    INNER JOIN (
    SELECT COUNT(1) AS "_tableau_join_flag",
      "lineitem"."l_orderkey" AS "none_l_orderkey_ok"
    FROM "tpch10g"."lineitem" "lineitem"
      INNER JOIN "tpch10g"."orders" "orders" ON ("lineitem"."l_orderkey" = "orders"."o_orderkey")
      INNER JOIN "tpch10g"."supplier" "supplier" ON ("lineitem"."l_suppkey" = "supplier"."s_suppkey")
      INNER JOIN "tpch10g"."customer" "customer" ON ("orders"."o_custkey" = "customer"."c_custkey")
      INNER JOIN "tpch10g"."nation" "N1" ON ("customer"."c_nationkey" = "N1"."n_nationkey")
    WHERE ((NOT ("supplier"."s_nationkey" IS NULL)) AND (((NOT ("N1"."n_regionkey" IS NULL)) AND (NOT ("lineitem"."l_partkey" IS NULL))) AND (NOT ("lineitem"."l_partkey" IS NULL))))
    GROUP BY 2
    HAVING (COUNT(DISTINCT "lineitem"."l_suppkey") > 1)
  ) "t3" ON ("lineitem"."l_orderkey" = "t3"."none_l_orderkey_ok")
  WHERE ((((NOT ("N1"."n_regionkey" IS NULL)) AND (NOT ("lineitem"."l_partkey" IS NULL))) AND (NOT ("lineitem"."l_partkey" IS NULL))) AND (("lineitem"."l_commitdate" < "lineitem"."l_receiptdate") AND ("N2"."n_name" = 'SAUDI ARABIA') AND ("orders"."o_orderstatus" = 'F')))
  GROUP BY 1
  ORDER BY 3 DESC
) "t4" ON ("supplier"."s_name" = "t4"."s_name")
WHERE ((((NOT ("N1"."n_regionkey" IS NULL)) AND (NOT ("lineitem"."l_partkey" IS NULL))) AND (NOT ("lineitem"."l_partkey" IS NULL))) AND (("lineitem"."l_commitdate" < "lineitem"."l_receiptdate") AND ("N2"."n_name" = 'SAUDI ARABIA') AND ("orders"."o_orderstatus" = 'F')))
GROUP BY 1