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

Library import 241120-1113

1 files changed, 62 insertions, 0 deletions

diff --git a/yql/essentials/tests/sql/suites/tpch/q11.sql b/yql/essentials/tests/sql/suites/tpch/q11.sql
new file mode 100644
index 0000000000..2a80fb6128
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/tpch/q11.sql
@@ -0,0 +1,62 @@
+
+-- TPC-H/TPC-R Important Stock Identification Query (Q11)
+-- TPC TPC-H Parameter Substitution (Version 2.17.2 build 0)
+-- using 1680793381 as a seed to the RNG
+
+PRAGMA DisableSimpleColumns; 
+
+$join1 = (
+select
+    ps.ps_partkey as ps_partkey,
+    ps.ps_supplycost as ps_supplycost,
+    ps.ps_availqty as ps_availqty,
+    s.s_nationkey as s_nationkey
+from
+    plato.partsupp as ps
+join
+    plato.supplier as s
+on
+    ps.ps_suppkey = s.s_suppkey
+);
+$join2 = (
+select
+    j.ps_partkey as ps_partkey,
+    j.ps_supplycost as ps_supplycost,
+    j.ps_availqty as ps_availqty,
+    j.s_nationkey as s_nationkey
+from
+    $join1 as j
+join
+    plato.nation as n
+on
+    n.n_nationkey = j.s_nationkey
+where
+    n.n_name = 'CANADA'
+);
+$threshold = (
+select
+    sum(ps_supplycost * ps_availqty) * 0.0001000000 as threshold
+from
+    $join2
+);
+$values = (
+select
+    ps_partkey,
+    sum(ps_supplycost * ps_availqty) as value
+from
+    $join2
+group by
+    ps_partkey
+);
+
+select
+    v.ps_partkey as ps_partkey,
+    v.value as value
+from
+    $values as v
+cross join
+    $threshold as t
+where
+    v.value > t.threshold
+order by
+    value desc;