redshiftTableauQueries.22.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 "t2"."calculation_5131031153053149" AS "calculation_5131031153053149",
  "t2"."cnt_c_custkey_ok" AS "cnt_c_custkey_ok",
  "t7"."__measure__4" AS "sum_sum_acctbal__copy__ok"
FROM (
  SELECT (CASE WHEN 2 >= 0 THEN SUBSTRING("customer"."c_phone", 1, CAST(FLOOR(2) AS INTEGER)) ELSE NULL END) AS "calculation_5131031153053149",
    COUNT("customer"."c_custkey") AS "cnt_c_custkey_ok"
  FROM "tpch10g"."orders" "orders"
    RIGHT JOIN "tpch10g"."customer" "customer" ON ("orders"."o_custkey" = "customer"."c_custkey")
    CROSS JOIN (
    SELECT AVG(CAST("t0"."__measure__0" AS DOUBLE PRECISION)) AS "__measure__1"
    FROM (
      SELECT "customer"."c_custkey" AS "c_custkey",
        MAX((CASE WHEN ("customer"."c_acctbal" > 0) THEN "customer"."c_acctbal" ELSE NULL END)) AS "__measure__0"
      FROM "tpch10g"."customer" "customer"
      WHERE ((CASE WHEN 2 >= 0 THEN SUBSTRING("customer"."c_phone", 1, CAST(FLOOR(2) AS INTEGER)) ELSE NULL END) IN ('13', '17', '18', '23', '29', '30', '31'))
      GROUP BY 1
    ) "t0"
    HAVING (COUNT(1) > 0)
  ) "t1"
  WHERE (((CASE WHEN 2 >= 0 THEN SUBSTRING("customer"."c_phone", 1, CAST(FLOOR(2) AS INTEGER)) ELSE NULL END) IN ('13', '17', '18', '23', '29', '30', '31')) AND (("customer"."c_acctbal" > "t1"."__measure__1") AND ("orders"."o_orderkey" IS NULL)))
  GROUP BY 1
) "t2"
  INNER JOIN (
  SELECT "t5"."calculation_5131031153053149" AS "calculation_5131031153053149",
    SUM("t6"."__measure__3") AS "__measure__4"
  FROM (
    SELECT "customer"."c_custkey" AS "c_custkey",
      (CASE WHEN 2 >= 0 THEN SUBSTRING("customer"."c_phone", 1, CAST(FLOOR(2) AS INTEGER)) ELSE NULL END) AS "calculation_5131031153053149"
    FROM "tpch10g"."orders" "orders"
      RIGHT JOIN "tpch10g"."customer" "customer" ON ("orders"."o_custkey" = "customer"."c_custkey")
      CROSS JOIN (
      SELECT AVG(CAST("t3"."__measure__0" AS DOUBLE PRECISION)) AS "__measure__1"
      FROM (
        SELECT "customer"."c_custkey" AS "c_custkey",
          MAX((CASE WHEN ("customer"."c_acctbal" > 0) THEN "customer"."c_acctbal" ELSE NULL END)) AS "__measure__0"
        FROM "tpch10g"."customer" "customer"
        WHERE ((CASE WHEN 2 >= 0 THEN SUBSTRING("customer"."c_phone", 1, CAST(FLOOR(2) AS INTEGER)) ELSE NULL END) IN ('13', '17', '18', '23', '29', '30', '31'))
        GROUP BY 1
      ) "t3"
      HAVING (COUNT(1) > 0)
    ) "t4"
    WHERE (((CASE WHEN 2 >= 0 THEN SUBSTRING("customer"."c_phone", 1, CAST(FLOOR(2) AS INTEGER)) ELSE NULL END) IN ('13', '17', '18', '23', '29', '30', '31')) AND (("customer"."c_acctbal" > "t4"."__measure__1") AND ("orders"."o_orderkey" IS NULL)))
    GROUP BY 1,
      2
  ) "t5"
    INNER JOIN (
    SELECT "customer"."c_custkey" AS "c_custkey",
      (CASE WHEN 2 >= 0 THEN SUBSTRING("customer"."c_phone", 1, CAST(FLOOR(2) AS INTEGER)) ELSE NULL END) AS "calculation_5131031153053149",
      MAX("customer"."c_acctbal") AS "__measure__3"
    FROM "tpch10g"."customer" "customer"
    WHERE ((CASE WHEN 2 >= 0 THEN SUBSTRING("customer"."c_phone", 1, CAST(FLOOR(2) AS INTEGER)) ELSE NULL END) IN ('13', '17', '18', '23', '29', '30', '31'))
    GROUP BY 1,
      2
  ) "t6" ON ((("t5"."c_custkey" = "t6"."c_custkey") OR (("t5"."c_custkey" IS NULL) AND ("t6"."c_custkey" IS NULL))) AND (("t5"."calculation_5131031153053149" = "t6"."calculation_5131031153053149") OR (("t5"."calculation_5131031153053149" IS NULL) AND ("t6"."calculation_5131031153053149" IS NULL))))
  GROUP BY 1
) "t7" ON (("t2"."calculation_5131031153053149" = "t7"."calculation_5131031153053149") OR (("t2"."calculation_5131031153053149" IS NULL) AND ("t7"."calculation_5131031153053149" IS NULL)))