Merge branch 'rightlib' into mergelibs-241120-1113

1 files changed, 59 insertions, 0 deletions

diff --git a/yql/essentials/tests/sql/suites/pg-tpcds/q54.sql b/yql/essentials/tests/sql/suites/pg-tpcds/q54.sql
new file mode 100644
index 0000000000..2396c50fe9
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/pg-tpcds/q54.sql
@@ -0,0 +1,59 @@
+--!syntax_pg
+--TPC-DS Q54
+
+-- start query 1 in stream 0 using template ../query_templates/query54.tpl
+with my_customers as (
+ select distinct c_customer_sk
+        , c_current_addr_sk
+ from   
+        ( select cs_sold_date_sk sold_date_sk,
+                 cs_bill_customer_sk customer_sk,
+                 cs_item_sk item_sk
+          from   plato.catalog_sales
+          union all
+          select ws_sold_date_sk sold_date_sk,
+                 ws_bill_customer_sk customer_sk,
+                 ws_item_sk item_sk
+          from   plato.web_sales
+         ) cs_or_ws_sales,
+         plato.item,
+         plato.date_dim,
+         plato.customer
+ where   sold_date_sk = d_date_sk
+         and item_sk = i_item_sk
+         and i_category = 'Jewelry'
+         and i_class = 'consignment'
+         and c_customer_sk = cs_or_ws_sales.customer_sk
+         and d_moy = 3
+         and d_year = 1999
+ )
+ , my_revenue as (
+ select c_customer_sk,
+        sum(ss_ext_sales_price) as revenue
+ from   my_customers,
+        plato.store_sales,
+        plato.customer_address,
+        plato.store,
+        plato.date_dim
+ where  c_current_addr_sk = ca_address_sk
+        and ca_county = s_county
+        and ca_state = s_state
+        and ss_sold_date_sk = d_date_sk
+        and c_customer_sk = ss_customer_sk
+        and d_month_seq between (select distinct d_month_seq+1
+                                 from   plato.date_dim where d_year = 1999 and d_moy = 3)
+                           and  (select distinct d_month_seq+3
+                                 from   plato.date_dim where d_year = 1999 and d_moy = 3)
+ group by c_customer_sk
+ )
+ , segments as
+ (select cast((revenue/50::numeric) as int) as segment
+  from   my_revenue
+ )
+  select  segment, count(*) as num_customers, segment*50 as segment_base
+ from segments
+ group by segment
+ order by segment, num_customers
+ limit 100;
+
+-- end query 1 in stream 0 using template ../query_templates/query54.tpl