aboutsummaryrefslogtreecommitdiffstats
path: root/yt/yql/tests/sql/suites/pg-tpcds/q54.sql
diff options
context:
space:
mode:
authorvvvv <vvvv@yandex-team.com>2024-12-28 18:52:49 +0300
committervvvv <vvvv@yandex-team.com>2024-12-28 19:08:05 +0300
commit8edc031b752662cb60f411a2d7d3d6c162d801cf (patch)
treec78646d3f4c02c56d8e5659af94aa338df9e5447 /yt/yql/tests/sql/suites/pg-tpcds/q54.sql
parent4e51cb9d47a86fee833396ad0cdfb8e84ec8166c (diff)
downloadydb-8edc031b752662cb60f411a2d7d3d6c162d801cf.tar.gz
Change "devtools/contrib/piglet/projects/ydblib/config.yaml"
commit_hash:bf8be6eaf53bc3065d37ec3eeb7b664ca9d6b76d
Diffstat (limited to 'yt/yql/tests/sql/suites/pg-tpcds/q54.sql')
-rw-r--r--yt/yql/tests/sql/suites/pg-tpcds/q54.sql59
1 files changed, 59 insertions, 0 deletions
diff --git a/yt/yql/tests/sql/suites/pg-tpcds/q54.sql b/yt/yql/tests/sql/suites/pg-tpcds/q54.sql
new file mode 100644
index 0000000000..2396c50fe9
--- /dev/null
+++ b/yt/yql/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