redshiftTableauQueries.15.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 "lineitem"."l_suppkey" AS "l_suppkey",
  "supplier"."s_address" AS "s_address",
  "supplier"."s_name" AS "s_name",
  "supplier"."s_phone" AS "s_phone",
  SUM(("lineitem"."l_extendedprice" * (1 - "lineitem"."l_discount"))) AS "sum_calculation_7060711085256495_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")
  INNER JOIN (
  SELECT "lineitem"."l_suppkey" AS "l_suppkey",
    SUM(("lineitem"."l_extendedprice" * (1 - "lineitem"."l_discount"))) AS "__measure__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" "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)))) AND (("lineitem"."l_shipdate" >= DATEADD(MONTH,0,CAST(DATE_TRUNC( 'MONTH', CAST((TIMESTAMP '1996-01-01 00:00:00.000') AS TIMESTAMP WITHOUT TIME ZONE) ) AS TIMESTAMP WITHOUT TIME ZONE))) AND ("lineitem"."l_shipdate" < DATEADD(MONTH,3,CAST(DATE_TRUNC( 'MONTH', CAST((TIMESTAMP '1996-01-01 00:00:00.000') AS TIMESTAMP WITHOUT TIME ZONE) ) AS TIMESTAMP WITHOUT TIME ZONE)))))
  GROUP BY 1
) "t0" ON (("lineitem"."l_suppkey" = "t0"."l_suppkey") OR (("lineitem"."l_suppkey" IS NULL) AND ("t0"."l_suppkey" IS NULL)))
  CROSS JOIN (
  SELECT MAX("t1"."__measure__1") AS "__measure__2"
  FROM (
    SELECT "lineitem"."l_suppkey" AS "l_suppkey",
      SUM(("lineitem"."l_extendedprice" * (1 - "lineitem"."l_discount"))) AS "__measure__1"
    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)))) AND (("lineitem"."l_shipdate" >= DATEADD(MONTH,0,CAST(DATE_TRUNC( 'MONTH', CAST((TIMESTAMP '1996-01-01 00:00:00.000') AS TIMESTAMP WITHOUT TIME ZONE) ) AS TIMESTAMP WITHOUT TIME ZONE))) AND ("lineitem"."l_shipdate" < DATEADD(MONTH,3,CAST(DATE_TRUNC( 'MONTH', CAST((TIMESTAMP '1996-01-01 00:00:00.000') AS TIMESTAMP WITHOUT TIME ZONE) ) AS TIMESTAMP WITHOUT TIME ZONE)))))
    GROUP BY 1
  ) "t1"
  HAVING (COUNT(1) > 0)
) "t2"
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)))) AND (("lineitem"."l_shipdate" >= DATEADD(MONTH,0,CAST(DATE_TRUNC( 'MONTH', CAST((TIMESTAMP '1996-01-01 00:00:00.000') AS TIMESTAMP WITHOUT TIME ZONE) ) AS TIMESTAMP WITHOUT TIME ZONE))) AND ("lineitem"."l_shipdate" < DATEADD(MONTH,3,CAST(DATE_TRUNC( 'MONTH', CAST((TIMESTAMP '1996-01-01 00:00:00.000') AS TIMESTAMP WITHOUT TIME ZONE) ) AS TIMESTAMP WITHOUT TIME ZONE))))) AND ("t0"."__measure__0" = "t2"."__measure__2"))
GROUP BY 1,
  2,
  3,
  4