diff options
author | Alexander Smirnov <alex@ydb.tech> | 2024-11-20 11:14:58 +0000 |
---|---|---|
committer | Alexander Smirnov <alex@ydb.tech> | 2024-11-20 11:14:58 +0000 |
commit | 31773f157bf8164364649b5f470f52dece0a4317 (patch) | |
tree | 33d0f7eef45303ab68cf08ab381ce5e5e36c5240 /yql/essentials/tests/sql/suites/tpch/q11.sql | |
parent | 2c7938962d8689e175574fc1e817c05049f27905 (diff) | |
parent | eff600952d5dfe17942f38f510a8ac2b203bb3a5 (diff) | |
download | ydb-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.sql | 62 |
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; |