Merge branch 'rightlib' into mergelibs-241120-1113

1 files changed, 45 insertions, 0 deletions

diff --git a/yql/essentials/tests/sql/suites/tpch/q12.sql b/yql/essentials/tests/sql/suites/tpch/q12.sql
new file mode 100644
index 0000000000..d8425b0b4a
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/tpch/q12.sql
@@ -0,0 +1,45 @@
+
+-- TPC-H/TPC-R Shipping Modes and Order Priority Query (Q12)
+-- TPC TPC-H Parameter Substitution (Version 2.17.2 build 0)
+-- using 1680793381 as a seed to the RNG
+
+$join = (
+    select
+        l.l_shipmode as l_shipmode,
+        o.o_orderpriority as o_orderpriority,
+        l.l_commitdate as l_commitdate,
+        l.l_shipdate as l_shipdate,
+        l.l_receiptdate as l_receiptdate
+    from
+        plato.orders as o
+        join plato.lineitem as l
+        on o.o_orderkey == l.l_orderkey
+);
+
+$border = Date("1994-01-01");
+
+select
+    l_shipmode,
+    sum(case
+        when o_orderpriority = '1-URGENT'
+            or o_orderpriority = '2-HIGH'
+            then 1
+        else 0
+    end) as high_line_count,
+    sum(case
+        when o_orderpriority <> '1-URGENT'
+            and o_orderpriority <> '2-HIGH'
+            then 1
+        else 0
+    end) as low_line_count
+from $join
+where
+    (l_shipmode = 'MAIL' or l_shipmode = 'TRUCK')
+    and l_commitdate < l_receiptdate
+    and l_shipdate < l_commitdate
+    and cast(l_receiptdate as timestamp) >= $border
+    and cast(l_receiptdate as timestamp) < ($border + Interval("P365D"))
+group by
+    l_shipmode
+order by
+    l_shipmode;