Merge branch 'rightlib' into mergelibs-241120-1113

1 files changed, 55 insertions, 0 deletions

diff --git a/yql/essentials/tests/sql/suites/tpch/q3.sql b/yql/essentials/tests/sql/suites/tpch/q3.sql
new file mode 100644
index 0000000000..668cb9441f
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/tpch/q3.sql
@@ -0,0 +1,55 @@
+
+-- TPC-H/TPC-R Shipping Priority Query (Q3)
+-- TPC TPC-H Parameter Substitution (Version 2.17.2 build 0)
+-- using 1680793381 as a seed to the RNG
+
+$join1 = (
+select
+    c.c_mktsegment as c_mktsegment,
+    o.o_orderdate as o_orderdate,
+    o.o_shippriority as o_shippriority,
+    o.o_orderkey as o_orderkey
+from
+    plato.customer as c
+join
+    plato.orders as o
+on
+    c.c_custkey = o.o_custkey
+);
+
+$join2 = (
+select
+    j1.c_mktsegment as c_mktsegment,
+    j1.o_orderdate as o_orderdate,
+    j1.o_shippriority as o_shippriority,
+    l.l_orderkey as l_orderkey,
+    l.l_discount as l_discount,
+    l.l_shipdate as l_shipdate,
+    l.l_extendedprice as l_extendedprice
+from
+    $join1 as j1
+join
+    plato.lineitem as l
+on
+    l.l_orderkey = j1.o_orderkey
+);
+
+select
+    l_orderkey,
+    sum(l_extendedprice * (1 - l_discount)) as revenue,
+    o_orderdate,
+    o_shippriority
+from
+    $join2
+where
+    c_mktsegment = 'MACHINERY'
+    and CAST(o_orderdate AS Timestamp) < Date('1995-03-08')
+    and CAST(l_shipdate AS Timestamp) > Date('1995-03-08')
+group by
+    l_orderkey,
+    o_orderdate,
+    o_shippriority
+order by
+    revenue desc,
+    o_orderdate
+limit 10;