redshiftTableauQueries.16.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 "part"."p_brand" AS "p_brand",
  "part"."p_size" AS "p_size",
  "part"."p_type" AS "p_type",
  COUNT(DISTINCT "partsupp"."ps_suppkey") AS "ctd_ps_suppkey_ok"
FROM "tpch10g"."partsupp" "partsupp"
  INNER JOIN "tpch10g"."part" "part" ON ("partsupp"."ps_partkey" = "part"."p_partkey")
  INNER JOIN (
  SELECT COUNT(1) AS "_tableau_join_flag",
    "supplier"."s_suppkey" AS "s_suppkey"
  FROM "tpch10g"."partsupp" "partsupp"
    INNER JOIN "tpch10g"."supplier" "supplier" ON ("partsupp"."ps_suppkey" = "supplier"."s_suppkey")
  WHERE (NOT ("partsupp"."ps_partkey" IS NULL))
  GROUP BY 2
  HAVING (SUM((CASE WHEN ((0 < STRPOS("supplier"."s_comment",CAST('Customer' AS TEXT))) AND (0 < (CASE
  	WHEN STRPOS("supplier"."s_comment",CAST('Customer' AS TEXT)) IS NULL THEN NULL
  	WHEN 0 = STRPOS(SUBSTRING("supplier"."s_comment",INT4LARGER(1,CAST(FLOOR(STRPOS("supplier"."s_comment",CAST('Customer' AS TEXT))) AS INTEGER))),CAST('Complaints' AS TEXT)) THEN 0
  	ELSE (STRPOS(SUBSTRING("supplier"."s_comment",INT4LARGER(1,CAST(FLOOR(STRPOS("supplier"."s_comment",CAST('Customer' AS TEXT)))AS INTEGER))),CAST('Complaints' AS TEXT)) + INT4LARGER(1,CAST(FLOOR(STRPOS("supplier"."s_comment",CAST('Customer' AS TEXT))) AS INTEGER)) - 1)	END))) THEN 1 ELSE 0 END)) = 0)
) "t0" ON ("partsupp"."ps_suppkey" = "t0"."s_suppkey")
WHERE ((NOT ("partsupp"."ps_suppkey" IS NULL)) AND ((NOT ("part"."p_brand" = 'Brand#45')) AND ("part"."p_size" IN (3, 9, 14, 19, 23, 36, 45, 49)) AND (("part"."p_type" IS NULL) OR (NOT (SUBSTRING("part"."p_type", 1, LENGTH('MEDIUM POLISHED')) = 'MEDIUM POLISHED')))))
GROUP BY 1,
  2,
  3