Merge branch 'rightlib' into mergelibs-241120-1113

1 files changed, 54 insertions, 0 deletions

diff --git a/yql/essentials/tests/sql/suites/pg-tpcds/q47.sql b/yql/essentials/tests/sql/suites/pg-tpcds/q47.sql
new file mode 100644
index 0000000000..126f80e1eb
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/pg-tpcds/q47.sql
@@ -0,0 +1,54 @@
+--!syntax_pg
+--TPC-DS Q47
+
+-- start query 1 in stream 0 using template ../query_templates/query47.tpl
+with v1 as(
+ select i_category, i_brand,
+        s_store_name, s_company_name,
+        d_year, d_moy,
+        sum(ss_sales_price) sum_sales,
+        avg(sum(ss_sales_price)) over
+          (partition by i_category, i_brand,
+                     s_store_name, s_company_name, d_year)
+          avg_monthly_sales,
+        rank() over
+          (partition by i_category, i_brand,
+                     s_store_name, s_company_name
+           order by d_year, d_moy) rn
+ from plato.item, plato.store_sales, plato.date_dim, plato.store
+ where ss_item_sk = i_item_sk and
+       ss_sold_date_sk = d_date_sk and
+       ss_store_sk = s_store_sk and
+       (
+         d_year = 2000 or
+         ( d_year = 2000-1 and d_moy =12) or
+         ( d_year = 2000+1 and d_moy =1)
+       )
+ group by i_category, i_brand,
+          s_store_name, s_company_name,
+          d_year, d_moy),
+ v2 as(
+ select v1.i_category, v1.i_brand
+        ,v1.d_year, v1.d_moy
+        ,v1.avg_monthly_sales
+        ,v1.sum_sales, v1_lag.sum_sales psum, v1_lead.sum_sales nsum
+ from v1, v1 v1_lag, v1 v1_lead
+ where v1.i_category = v1_lag.i_category and
+       v1.i_category = v1_lead.i_category and
+       v1.i_brand = v1_lag.i_brand and
+       v1.i_brand = v1_lead.i_brand and
+       v1.s_store_name = v1_lag.s_store_name and
+       v1.s_store_name = v1_lead.s_store_name and
+       v1.s_company_name = v1_lag.s_company_name and
+       v1.s_company_name = v1_lead.s_company_name and
+       v1.rn = v1_lag.rn + 1 and
+       v1.rn = v1_lead.rn - 1)
+  select  *
+ from v2
+ where  d_year = 2000 and    
+        avg_monthly_sales > 0::numeric and
+        case when avg_monthly_sales > 0::numeric then abs(sum_sales - avg_monthly_sales) / avg_monthly_sales else null::numeric end > 0.1::numeric
+ order by sum_sales - avg_monthly_sales, nsum
+ limit 100;
+
+-- end query 1 in stream 0 using template ../query_templates/query47.tpl