aboutsummaryrefslogtreecommitdiffstats
path: root/yql/essentials/tests/sql/suites/tpch/q11.sql
diff options
context:
space:
mode:
authorAlexander Smirnov <alex@ydb.tech>2024-11-20 11:14:58 +0000
committerAlexander Smirnov <alex@ydb.tech>2024-11-20 11:14:58 +0000
commit31773f157bf8164364649b5f470f52dece0a4317 (patch)
tree33d0f7eef45303ab68cf08ab381ce5e5e36c5240 /yql/essentials/tests/sql/suites/tpch/q11.sql
parent2c7938962d8689e175574fc1e817c05049f27905 (diff)
parenteff600952d5dfe17942f38f510a8ac2b203bb3a5 (diff)
downloadydb-31773f157bf8164364649b5f470f52dece0a4317.tar.gz
Merge branch 'rightlib' into mergelibs-241120-1113
Diffstat (limited to 'yql/essentials/tests/sql/suites/tpch/q11.sql')
-rw-r--r--yql/essentials/tests/sql/suites/tpch/q11.sql62
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;