Merge branch 'rightlib' into mergelibs-241120-1113

1 files changed, 35 insertions, 0 deletions

diff --git a/yql/essentials/tests/sql/suites/pg-tpch/q09.sql b/yql/essentials/tests/sql/suites/pg-tpch/q09.sql
new file mode 100644
index 0000000000..7db204c44f
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/pg-tpch/q09.sql
@@ -0,0 +1,35 @@
+--!syntax_pg
+--TPC-H Q9
+
+
+select 
+nation, 
+o_year, 
+sum(amount) as sum_profit
+from (
+select 
+n_name as nation, 
+extract(year from o_orderdate) as o_year,
+l_extendedprice * (1::numeric - l_discount) - ps_supplycost * l_quantity as amount
+from 
+plato."part", 
+plato."supplier", 
+plato."lineitem", 
+plato."partsupp", 
+plato."orders", 
+plato."nation"
+where 
+s_suppkey = l_suppkey
+and ps_suppkey = l_suppkey
+and ps_partkey = l_partkey
+and p_partkey = l_partkey
+and o_orderkey = l_orderkey
+and s_nationkey = n_nationkey
+and p_name like '%green%'
+) as profit
+group by 
+nation, 
+o_year
+order by 
+nation, 
+o_year desc;