redshiftTableauQueries.12.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 (CASE WHEN ("orders"."o_orderpriority" IN ('1-URGENT', '2-HIGH')) THEN '1-URGENT' ELSE (CASE WHEN ("orders"."o_orderpriority" IN ('3-MEDIUM', '4-NOT SPECIFIED', '5-LOW')) THEN '3-MEDIUM' ELSE "orders"."o_orderpriority" END) END) AS "o_orderpriority__group_",
  "lineitem"."l_shipmode" AS "l_shipmode",
  SUM(1) AS "sum_number_of_records_ok"
FROM "tpch10g"."lineitem" "lineitem"
  INNER JOIN "tpch10g"."orders" "orders" ON ("lineitem"."l_orderkey" = "orders"."o_orderkey")
WHERE (("lineitem"."l_commitdate" < "lineitem"."l_receiptdate") AND ("lineitem"."l_shipdate" < "lineitem"."l_commitdate") AND (("lineitem"."l_receiptdate" >= DATEADD(YEAR,0,CAST(DATE_TRUNC( 'YEAR', CAST((TIMESTAMP '1994-01-01 00:00:00.000') AS TIMESTAMP WITHOUT TIME ZONE) ) AS TIMESTAMP WITHOUT TIME ZONE))) AND ("lineitem"."l_receiptdate" < DATEADD(YEAR,1,CAST(DATE_TRUNC( 'YEAR', CAST((TIMESTAMP '1994-01-01 00:00:00.000') AS TIMESTAMP WITHOUT TIME ZONE) ) AS TIMESTAMP WITHOUT TIME ZONE)))) AND ("lineitem"."l_shipmode" IN ('MAIL', 'SHIP')))
GROUP BY 1,
  2