Merge pull request #11788 from ydb-platform/mergelibs-241120-1113

Library import 241120-1113

1 files changed, 41 insertions, 0 deletions

diff --git a/yql/essentials/tests/sql/suites/tpch/q17.sql b/yql/essentials/tests/sql/suites/tpch/q17.sql
new file mode 100644
index 0000000000..c775d585bb
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/tpch/q17.sql
@@ -0,0 +1,41 @@
+
+-- TPC-H/TPC-R Small-Quantity-Order Revenue Query (Q17)
+-- TPC TPC-H Parameter Substitution (Version 2.17.2 build 0)
+-- using 1680793381 as a seed to the RNG
+
+$threshold = (
+select
+    0.2 * avg(l_quantity) as threshold,
+    l_partkey
+from
+    plato.lineitem
+group by
+    l_partkey
+);
+
+$join1 = (
+select
+    p.p_partkey as p_partkey,
+    l.l_extendedprice as l_extendedprice,
+    l.l_quantity as l_quantity
+from
+    plato.lineitem as l
+join
+    plato.part as p
+on
+    p.p_partkey = l.l_partkey
+where
+    p.p_brand = 'Brand#35'
+    and p.p_container = 'LG DRUM'
+);
+
+select
+    sum(j.l_extendedprice) / 7.0 as avg_yearly
+from
+    $join1 as j
+join
+    $threshold as t
+on
+    j.p_partkey = t.l_partkey
+where
+    j.l_quantity < t.threshold;