Merge branch 'rightlib' into mergelibs-241120-1113

1 files changed, 41 insertions, 0 deletions

diff --git a/yql/essentials/tests/sql/suites/pg-tpcds/q70.sql b/yql/essentials/tests/sql/suites/pg-tpcds/q70.sql
new file mode 100644
index 0000000000..530cb0f973
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/pg-tpcds/q70.sql
@@ -0,0 +1,41 @@
+--!syntax_pg
+--TPC-DS Q70
+
+-- start query 1 in stream 0 using template ../query_templates/query70.tpl
+select  
+    sum(ss_net_profit) as total_sum
+   ,s_state
+   ,s_county
+   ,grouping(s_state)+grouping(s_county) as lochierarchy
+   ,rank() over (
+ 	partition by grouping(s_state)+grouping(s_county),
+ 	case when grouping(s_county) = 0 then s_state end 
+ 	order by sum(ss_net_profit) desc) as rank_within_parent
+ from
+    plato.store_sales
+   ,plato.date_dim       d1
+   ,plato.store
+ where
+    d1.d_month_seq between 1212 and 1212+11
+ and d1.d_date_sk = ss_sold_date_sk
+ and s_store_sk  = ss_store_sk
+ and s_state in
+             ( select s_state
+               from  (select s_state as s_state,
+ 			    rank() over ( partition by s_state order by sum(ss_net_profit) desc) as ranking
+                      from   plato.store_sales, plato.store, plato.date_dim
+                      where  d_month_seq between 1212 and 1212+11
+ 			    and d_date_sk = ss_sold_date_sk
+ 			    and s_store_sk  = ss_store_sk
+                      group by s_state
+                     ) tmp1 
+               where ranking <= 5
+             )
+ group by rollup(s_state,s_county)
+ order by
+   lochierarchy desc
+  ,case when lochierarchy = 0 then s_state end
+  ,rank_within_parent
+ limit 100;
+
+-- end query 1 in stream 0 using template ../query_templates/query70.tpl