Merge branch 'rightlib' into mergelibs-241120-1113

1 files changed, 102 insertions, 0 deletions

diff --git a/yql/essentials/tests/sql/suites/tpch/q5.sql b/yql/essentials/tests/sql/suites/tpch/q5.sql
new file mode 100644
index 0000000000..62e7acc7d1
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/tpch/q5.sql
@@ -0,0 +1,102 @@
+
+-- TPC-H/TPC-R Local Supplier Volume Query (Q5)
+-- TPC TPC-H Parameter Substitution (Version 2.17.2 build 0)
+-- using 1680793381 as a seed to the RNG
+
+$join1 = (
+select
+    o.o_orderkey as o_orderkey,
+    o.o_orderdate as o_orderdate,
+    c.c_nationkey as c_nationkey
+from
+    plato.customer as c
+join
+    plato.orders as o
+on
+    c.c_custkey = o.o_custkey
+);
+
+$join2 = (
+select
+    j.o_orderkey as o_orderkey,
+    j.o_orderdate as o_orderdate,
+    j.c_nationkey as c_nationkey,
+    l.l_extendedprice as l_extendedprice,
+    l.l_discount as l_discount,
+    l.l_suppkey as l_suppkey
+from
+    $join1 as j
+join
+    plato.lineitem as l
+on
+    l.l_orderkey = j.o_orderkey
+);
+
+$join3 = (
+select
+    j.o_orderkey as o_orderkey,
+    j.o_orderdate as o_orderdate,
+    j.c_nationkey as c_nationkey,
+    j.l_extendedprice as l_extendedprice,
+    j.l_discount as l_discount,
+    j.l_suppkey as l_suppkey,
+    s.s_nationkey as s_nationkey
+from
+    $join2 as j
+join
+    plato.supplier as s
+on
+    j.l_suppkey = s.s_suppkey
+);
+$join4 = (
+select
+    j.o_orderkey as o_orderkey,
+    j.o_orderdate as o_orderdate,
+    j.c_nationkey as c_nationkey,
+    j.l_extendedprice as l_extendedprice,
+    j.l_discount as l_discount,
+    j.l_suppkey as l_suppkey,
+    j.s_nationkey as s_nationkey,
+    n.n_regionkey as n_regionkey,
+    n.n_name as n_name
+from
+    $join3 as j
+join
+    plato.nation as n
+on
+    j.s_nationkey = n.n_nationkey
+    and j.c_nationkey = n.n_nationkey
+);
+$join5 = (
+select
+    j.o_orderkey as o_orderkey,
+    j.o_orderdate as o_orderdate,
+    j.c_nationkey as c_nationkey,
+    j.l_extendedprice as l_extendedprice,
+    j.l_discount as l_discount,
+    j.l_suppkey as l_suppkey,
+    j.s_nationkey as s_nationkey,
+    j.n_regionkey as n_regionkey,
+    j.n_name as n_name,
+    r.r_name as r_name
+from
+    $join4 as j
+join
+    plato.region as r
+on
+    j.n_regionkey = r.r_regionkey
+);
+$border = Date("1995-01-01");
+select
+    n_name,
+    sum(l_extendedprice * (1 - l_discount)) as revenue
+from
+    $join5
+where
+    r_name = 'AFRICA'
+    and CAST(o_orderdate AS Timestamp) >= $border
+    and CAST(o_orderdate AS Timestamp) < ($border + Interval("P365D"))
+group by
+    n_name
+order by
+    revenue desc;