Last 25 queries of TPC-DS (#668)

24 files changed, 1102 insertions, 0 deletions

diff --git a/ydb/library/benchmarks/queries/tpcds/yql/q76.sql b/ydb/library/benchmarks/queries/tpcds/yql/q76.sql
new file mode 100644
index 0000000000..55ceea69e0
--- /dev/null
+++ b/ydb/library/benchmarks/queries/tpcds/yql/q76.sql
@@ -0,0 +1,33 @@
+{% include 'header.sql.jinja' %}
+
+-- NB: Subquerys
+-- start query 1 in stream 0 using template query76.tpl and seed 2031708268
+select  channel, col_name, d_year, d_qoy, i_category, COUNT(*) sales_cnt, SUM(ext_sales_price) sales_amt FROM (
+        SELECT 'store' as channel, 'ss_customer_sk' col_name, d_year, d_qoy, i_category, ss_ext_sales_price ext_sales_price
+         FROM {{store_sales}} as store_sales
+         cross join {{item}} as item
+         cross join {{date_dim}} as date_dim
+         WHERE ss_customer_sk IS NULL
+           AND ss_sold_date_sk=d_date_sk
+           AND ss_item_sk=i_item_sk
+        UNION ALL
+        SELECT 'web' as channel, 'ws_ship_addr_sk' col_name, d_year, d_qoy, i_category, ws_ext_sales_price ext_sales_price
+         FROM {{web_sales}} as web_sales
+         cross join {{item}} as item
+         cross join {{date_dim}} as date_dim
+         WHERE ws_ship_addr_sk IS NULL
+           AND ws_sold_date_sk=d_date_sk
+           AND ws_item_sk=i_item_sk
+        UNION ALL
+        SELECT 'catalog' as channel, 'cs_ship_mode_sk' col_name, d_year, d_qoy, i_category, cs_ext_sales_price ext_sales_price
+         FROM {{catalog_sales}} as catalog_sales
+         cross join {{item}} as item
+         cross join {{date_dim}} as date_dim
+         WHERE cs_ship_mode_sk IS NULL
+           AND cs_sold_date_sk=d_date_sk
+           AND cs_item_sk=i_item_sk) foo
+GROUP BY channel, col_name, d_year, d_qoy, i_category
+ORDER BY channel, col_name, d_year, d_qoy, i_category
+limit 100;
+
+-- end query 1 in stream 0 using template query76.tpl
diff --git a/ydb/library/benchmarks/queries/tpcds/yql/q77.sql b/ydb/library/benchmarks/queries/tpcds/yql/q77.sql
new file mode 100644
index 0000000000..3fbdd20dea
--- /dev/null
+++ b/ydb/library/benchmarks/queries/tpcds/yql/q77.sql
@@ -0,0 +1,111 @@
+{% include 'header.sql.jinja' %}
+
+-- NB: Subquerys
+$ss =
+ (select store.s_store_sk s_store_sk,
+         sum(ss_ext_sales_price) as sales,
+         sum(ss_net_profit) as profit
+ from {{store_sales}} as store_sales cross join
+      {{date_dim}} as date_dim cross join
+      {{store}} as store
+ where ss_sold_date_sk = d_date_sk
+       and cast(d_date as date) between cast('2000-08-16' as date)
+                  and (cast('2000-08-16' as date) +  DateTime::IntervalFromDays(30))
+       and ss_store_sk = s_store_sk
+ group by store.s_store_sk);
+
+ $sr =
+ (select store.s_store_sk s_store_sk,
+         sum(sr_return_amt) as returns,
+         sum(sr_net_loss) as profit_loss
+ from {{store_returns}} as store_returns cross join
+      {{date_dim}} as date_dim cross join
+      {{store}} as store
+ where sr_returned_date_sk = d_date_sk
+       and cast(d_date as date) between cast('2000-08-16' as date)
+                  and (cast('2000-08-16' as date) +  DateTime::IntervalFromDays(30))
+       and sr_store_sk = s_store_sk
+ group by store.s_store_sk);
+ $cs =
+ (select catalog_sales.cs_call_center_sk cs_call_center_sk,
+        sum(cs_ext_sales_price) as sales,
+        sum(cs_net_profit) as profit
+ from {{catalog_sales}} as catalog_sales cross join
+      {{date_dim}} as date_dim
+ where cs_sold_date_sk = d_date_sk
+       and cast(d_date as date) between cast('2000-08-16' as date)
+                  and (cast('2000-08-16' as date) +  DateTime::IntervalFromDays(30))
+ group by catalog_sales.cs_call_center_sk
+ );
+ $cr =
+ (select catalog_returns.cr_call_center_sk cr_call_center_sk,
+         sum(cr_return_amount) as returns,
+         sum(cr_net_loss) as profit_loss
+ from {{catalog_returns}} as catalog_returns cross join
+      {{date_dim}} as date_dim
+ where cr_returned_date_sk = d_date_sk
+       and cast(d_date as date) between cast('2000-08-16' as date)
+                  and (cast('2000-08-16' as date) +  DateTime::IntervalFromDays(30))
+ group by catalog_returns.cr_call_center_sk
+ );
+ $ws =
+ ( select web_page.wp_web_page_sk wp_web_page_sk,
+        sum(ws_ext_sales_price) as sales,
+        sum(ws_net_profit) as profit
+ from {{web_sales}} as web_sales cross join
+      {{date_dim}} as date_dim cross join
+      {{web_page}} as web_page
+ where ws_sold_date_sk = d_date_sk
+       and cast(d_date as date) between cast('2000-08-16' as date)
+                  and (cast('2000-08-16' as date) +  DateTime::IntervalFromDays(30))
+       and ws_web_page_sk = wp_web_page_sk
+ group by web_page.wp_web_page_sk);
+ $wr =
+ (select web_page.wp_web_page_sk wp_web_page_sk,
+        sum(wr_return_amt) as returns,
+        sum(wr_net_loss) as profit_loss
+ from {{web_returns}} as web_returns cross join
+      {{date_dim}} as date_dim cross join
+      {{web_page}} as web_page
+ where wr_returned_date_sk = d_date_sk
+       and cast(d_date as date) between cast('2000-08-16' as date)
+                  and (cast('2000-08-16' as date) +  DateTime::IntervalFromDays(30))
+       and wr_web_page_sk = wp_web_page_sk
+ group by web_page.wp_web_page_sk);
+-- start query 1 in stream 0 using template query77.tpl and seed 1819994127
+  select  channel
+        , id
+        , sum(sales) as sales
+        , sum(returns) as returns
+        , sum(profit) as profit
+ from
+ (select 'store channel' as channel
+        , ss.s_store_sk as id
+        , sales
+        , coalesce(returns, 0) as returns
+        , (profit - coalesce(profit_loss,0)) as profit
+ from   $ss ss left join $sr sr
+        on  ss.s_store_sk = sr.s_store_sk
+ union all
+ select 'catalog channel' as channel
+        , cs_call_center_sk as id
+        , sales
+        , returns
+        , (profit - profit_loss) as profit
+ from  $cs cs
+       cross join $cr cr
+ union all
+ select 'web channel' as channel
+        , ws.wp_web_page_sk as id
+        , sales
+        , coalesce(returns, 0) returns
+        , (profit - coalesce(profit_loss,0)) as profit
+ from   $ws ws left join $wr wr
+        on  ws.wp_web_page_sk = wr.wp_web_page_sk
+ ) x
+ group by rollup (channel, id)
+ order by channel
+         ,id
+ limit 100;
+
+-- end query 1 in stream 0 using template query77.tpl
diff --git a/ydb/library/benchmarks/queries/tpcds/yql/q78.sql b/ydb/library/benchmarks/queries/tpcds/yql/q78.sql
new file mode 100644
index 0000000000..877f105a06
--- /dev/null
+++ b/ydb/library/benchmarks/queries/tpcds/yql/q78.sql
@@ -0,0 +1,61 @@
+{% include 'header.sql.jinja' %}
+
+-- NB: Subquerys
+$ws =
+  (select date_dim.d_year AS ws_sold_year, web_sales.ws_item_sk ws_item_sk,
+    web_sales.ws_bill_customer_sk ws_customer_sk,
+    sum(ws_quantity) ws_qty,
+    sum(ws_wholesale_cost) ws_wc,
+    sum(ws_sales_price) ws_sp
+   from {{web_sales}} as web_sales
+   left join {{web_returns}} as web_returns on web_returns.wr_order_number=web_sales.ws_order_number and web_sales.ws_item_sk=web_returns.wr_item_sk
+   join {{date_dim}} as date_dim on web_sales.ws_sold_date_sk = date_dim.d_date_sk
+   where wr_order_number is null
+   group by date_dim.d_year, web_sales.ws_item_sk, web_sales.ws_bill_customer_sk
+   );
+$cs =
+  (select date_dim.d_year AS cs_sold_year, catalog_sales.cs_item_sk cs_item_sk,
+    catalog_sales.cs_bill_customer_sk cs_customer_sk,
+    sum(cs_quantity) cs_qty,
+    sum(cs_wholesale_cost) cs_wc,
+    sum(cs_sales_price) cs_sp
+   from {{catalog_sales}} as catalog_sales
+   left join {{catalog_returns}} as catalog_returns on catalog_returns.cr_order_number=catalog_sales.cs_order_number and catalog_sales.cs_item_sk=catalog_returns.cr_item_sk
+   join {{date_dim}} as date_dim on catalog_sales.cs_sold_date_sk = date_dim.d_date_sk
+   where cr_order_number is null
+   group by date_dim.d_year, catalog_sales.cs_item_sk, catalog_sales.cs_bill_customer_sk
+   );
+$ss=
+  (select date_dim.d_year AS ss_sold_year, store_sales.ss_item_sk ss_item_sk,
+    store_sales.ss_customer_sk ss_customer_sk,
+    sum(ss_quantity) ss_qty,
+    sum(ss_wholesale_cost) ss_wc,
+    sum(ss_sales_price) ss_sp
+   from {{store_sales}} as store_sales
+   left join {{store_returns}} as store_returns on store_returns.sr_ticket_number=store_sales.ss_ticket_number and store_sales.ss_item_sk=store_returns.sr_item_sk
+   join {{date_dim}} as date_dim on store_sales.ss_sold_date_sk = date_dim.d_date_sk
+   where sr_ticket_number is null
+   group by date_dim.d_year, store_sales.ss_item_sk, store_sales.ss_customer_sk
+   );
+-- start query 1 in stream 0 using template query78.tpl and seed 1819994127
+ select
+ss_customer_sk,
+Math::Round(cast(ss_qty as double)/(coalesce(ws_qty,0)+coalesce(cs_qty,0)),-2) ratio,
+ss_qty store_qty, ss_wc store_wholesale_cost, ss_sp store_sales_price,
+coalesce(ws_qty,0)+coalesce(cs_qty,0) other_chan_qty,
+coalesce(ws_wc,0)+coalesce(cs_wc,0) other_chan_wholesale_cost,
+coalesce(ws_sp,0)+coalesce(cs_sp,0) other_chan_sales_price
+from $ss ss
+left join $ws ws on (ws.ws_sold_year=ss.ss_sold_year and ws.ws_item_sk=ss.ss_item_sk and ws.ws_customer_sk=ss.ss_customer_sk)
+left join $cs cs on (cs.cs_sold_year=ss.ss_sold_year and cs.cs_item_sk=ss.ss_item_sk and cs.cs_customer_sk=ss.ss_customer_sk)
+where (coalesce(ws_qty,0)>0 or coalesce(cs_qty, 0)>0) and ss_sold_year=2001
+order by
+  ss_customer_sk,
+  store_qty desc, store_wholesale_cost desc, store_sales_price desc,
+  other_chan_qty,
+  other_chan_wholesale_cost,
+  other_chan_sales_price,
+  ratio
+limit 100;
+
+-- end query 1 in stream 0 using template query78.tpl
diff --git a/ydb/library/benchmarks/queries/tpcds/yql/q79.sql b/ydb/library/benchmarks/queries/tpcds/yql/q79.sql
new file mode 100644
index 0000000000..188f866490
--- /dev/null
+++ b/ydb/library/benchmarks/queries/tpcds/yql/q79.sql
@@ -0,0 +1,29 @@
+{% include 'header.sql.jinja' %}
+
+-- NB: Subquerys
+-- start query 1 in stream 0 using template query79.tpl and seed 2031708268
+select
+  c_last_name,c_first_name,substring(cast(s_city as string),1,30) bla,ss_ticket_number,amt,profit
+  from
+   (select store_sales.ss_ticket_number ss_ticket_number
+          ,store_sales.ss_customer_sk ss_customer_sk
+          ,store.s_city s_city
+          ,sum(ss_coupon_amt) amt
+          ,sum(ss_net_profit) profit
+    from {{store_sales}} as store_sales
+    cross join {{date_dim}} as date_dim
+    cross join {{store}} as store
+    cross join {{household_demographics}} as household_demographics
+    where store_sales.ss_sold_date_sk = date_dim.d_date_sk
+    and store_sales.ss_store_sk = store.s_store_sk
+    and store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk
+    and (household_demographics.hd_dep_count = 0 or household_demographics.hd_vehicle_count > 3)
+    and date_dim.d_dow = 1
+    and date_dim.d_year in (1998,1998+1,1998+2)
+    and store.s_number_employees between 200 and 295
+    group by store_sales.ss_ticket_number,store_sales.ss_customer_sk,store_sales.ss_addr_sk,store.s_city) ms cross join {{customer}} as customer
+    where ss_customer_sk = c_customer_sk
+ order by c_last_name,c_first_name,bla, profit
+limit 100;
+
+-- end query 1 in stream 0 using template query79.tpl
diff --git a/ydb/library/benchmarks/queries/tpcds/yql/q80.sql b/ydb/library/benchmarks/queries/tpcds/yql/q80.sql
new file mode 100644
index 0000000000..63365f98c5
--- /dev/null
+++ b/ydb/library/benchmarks/queries/tpcds/yql/q80.sql
@@ -0,0 +1,100 @@
+{% include 'header.sql.jinja' %}
+
+-- NB: Subquerys
+$ssr =
+ (select  store.s_store_id as store_id,
+          sum(ss_ext_sales_price) as sales,
+          sum(coalesce(sr_return_amt, 0)) as returns,
+          sum(ss_net_profit - coalesce(sr_net_loss, 0)) as profit
+  from {{store_sales}} as store_sales
+  left join {{store_returns}} as store_returns on
+         (store_sales.ss_item_sk = store_returns.sr_item_sk and store_sales.ss_ticket_number = store_returns.sr_ticket_number) cross join
+     {{date_dim}} as date_dim cross join
+     {{store}} as store cross join
+     {{item}} as item cross join
+     {{promotion}} as promotion
+ where ss_sold_date_sk = d_date_sk
+       and cast(d_date as date) between cast('2002-08-06' as date)
+                  and (cast('2002-08-06' as date) + DateTime::IntervalFromDays(30))
+       and ss_store_sk = s_store_sk
+       and ss_item_sk = i_item_sk
+       and i_current_price > 50
+       and ss_promo_sk = p_promo_sk
+       and p_channel_tv = 'N'
+ group by store.s_store_id);
+ $csr=
+ (select  catalog_page.cp_catalog_page_id as catalog_page_id,
+          sum(cs_ext_sales_price) as sales,
+          sum(coalesce(cr_return_amount, 0)) as returns,
+          sum(cs_net_profit - coalesce(cr_net_loss, 0)) as profit
+  from {{catalog_sales}} as catalog_sales left join {{catalog_returns}} as catalog_returns on
+         (catalog_sales.cs_item_sk = catalog_returns.cr_item_sk and catalog_sales.cs_order_number = catalog_returns.cr_order_number) cross join
+     {{date_dim}} as date_dim cross join
+     {{catalog_page}} as catalog_page cross join
+     {{item}} as item cross join
+     {{promotion}} as promotion
+ where cs_sold_date_sk = d_date_sk
+       and cast(d_date as date) between cast('2002-08-06' as date)
+                  and (cast('2002-08-06' as date) + DateTime::IntervalFromDays(30))
+        and cs_catalog_page_sk = cp_catalog_page_sk
+       and cs_item_sk = i_item_sk
+       and i_current_price > 50
+       and cs_promo_sk = p_promo_sk
+       and p_channel_tv = 'N'
+group by catalog_page.cp_catalog_page_id)
+ ;
+ $wsr =
+ (select  web_site.web_site_id web_site_id,
+          sum(ws_ext_sales_price) as sales,
+          sum(coalesce(wr_return_amt, 0)) as returns,
+          sum(ws_net_profit - coalesce(wr_net_loss, 0)) as profit
+  from {{web_sales}} as web_sales
+  left outer join {{web_returns}} as web_returns on
+         (web_sales.ws_item_sk = web_returns.wr_item_sk and web_sales.ws_order_number = web_returns.wr_order_number) cross join
+     {{date_dim}} as date_dim cross join
+     {{web_site}} as web_site cross join
+     {{item}} as item cross join
+     {{promotion}} as promotion
+ where ws_sold_date_sk = d_date_sk
+       and cast(d_date as date) between cast('2002-08-06' as date)
+                  and (cast('2002-08-06' as date) + DateTime::IntervalFromDays(30))
+        and ws_web_site_sk = web_site_sk
+       and ws_item_sk = i_item_sk
+       and i_current_price > 50
+       and ws_promo_sk = p_promo_sk
+       and p_channel_tv = 'N'
+group by web_site.web_site_id);
+-- start query 1 in stream 0 using template query80.tpl and seed 1819994127
+  select  channel
+        , id
+        , sum(sales) as sales
+        , sum(returns) as returns
+        , sum(profit) as profit
+ from
+ (select 'store channel' as channel
+        , 'store' || store_id as id
+        , sales
+        , returns
+        , profit
+ from   $ssr ssr
+ union all
+ select 'catalog channel' as channel
+        , 'catalog_page' || catalog_page_id as id
+        , sales
+        , returns
+        , profit
+ from  $csr csr
+ union all
+ select 'web channel' as channel
+        , 'web_site' || web_site_id as id
+        , sales
+        , returns
+        , profit
+ from   $wsr wsr
+ ) x
+ group by rollup (channel, id)
+ order by channel
+         ,id
+ limit 100;
+
+-- end query 1 in stream 0 using template query80.tpl
diff --git a/ydb/library/benchmarks/queries/tpcds/yql/q81.sql b/ydb/library/benchmarks/queries/tpcds/yql/q81.sql
new file mode 100644
index 0000000000..edfe8db593
--- /dev/null
+++ b/ydb/library/benchmarks/queries/tpcds/yql/q81.sql
@@ -0,0 +1,34 @@
+{% include 'header.sql.jinja' %}
+
+-- NB: Subquerys
+$customer_total_return =
+ (select catalog_returns.cr_returning_customer_sk as ctr_customer_sk
+        ,customer_address.ca_state as ctr_state,
+ 	sum(cr_return_amt_inc_tax) as ctr_total_return
+ from {{catalog_returns}} as catalog_returns
+      cross join {{date_dim}} as date_dim
+     cross join {{customer_address}} as customer_address
+ where cr_returned_date_sk = d_date_sk
+   and d_year =1998
+   and cr_returning_addr_sk = ca_address_sk
+ group by catalog_returns.cr_returning_customer_sk
+         ,customer_address.ca_state );
+$avg_ctr_total_return = (select ctr_state, avg(ctr_total_return) as ctr_total_return from $customer_total_return group by ctr_state);
+-- start query 1 in stream 0 using template query81.tpl and seed 1819994127
+  select  c_customer_id,c_salutation,c_first_name,c_last_name,ca_street_number,ca_street_name
+                   ,ca_street_type,ca_suite_number,ca_city,ca_county,ca_state,ca_zip,ca_country,ca_gmt_offset
+                  ,ca_location_type,ctr1.ctr_total_return
+ from $customer_total_return ctr1
+     join $avg_ctr_total_return ctr2 on (ctr1.ctr_state = ctr2.ctr_state)
+     cross join {{customer_address}} as customer_address
+cross join {{customer}} as customer
+ where ctr1.ctr_total_return > ctr2.ctr_total_return*1.2
+       and ca_address_sk = c_current_addr_sk
+       and ca_state = 'TX'
+       and ctr1.ctr_customer_sk = c_customer_sk
+ order by c_customer_id,c_salutation,c_first_name,c_last_name,ca_street_number,ca_street_name
+                   ,ca_street_type,ca_suite_number,ca_city,ca_county,ca_state,ca_zip,ca_country,ca_gmt_offset
+                  ,ca_location_type,ctr1.ctr_total_return
+ limit 100;
+
+-- end query 1 in stream 0 using template query81.tpl
diff --git a/ydb/library/benchmarks/queries/tpcds/yql/q82.sql b/ydb/library/benchmarks/queries/tpcds/yql/q82.sql
new file mode 100644
index 0000000000..311cd1e5e5
--- /dev/null
+++ b/ydb/library/benchmarks/queries/tpcds/yql/q82.sql
@@ -0,0 +1,23 @@
+{% include 'header.sql.jinja' %}
+
+-- NB: Subquerys
+-- start query 1 in stream 0 using template query82.tpl and seed 55585014
+select  item.i_item_id
+       ,item.i_item_desc
+       ,item.i_current_price
+ from {{item}} as item
+ cross join {{inventory}} as inventory
+ cross join {{date_dim}} as date_dim
+ cross join {{store_sales}} as store_sales
+ where i_current_price between 49 and 49+30
+ and inv_item_sk = i_item_sk
+ and d_date_sk=inv_date_sk
+ and cast(d_date as date) between cast('2001-01-28' as date) and (cast('2001-01-28' as date) + DateTime::IntervalFromDays(60))
+ and i_manufact_id in (80,675,292,17)
+ and inv_quantity_on_hand between 100 and 500
+ and ss_item_sk = i_item_sk
+ group by item.i_item_id,item.i_item_desc,item.i_current_price
+ order by item.i_item_id
+ limit 100;
+
+-- end query 1 in stream 0 using template query82.tpl
diff --git a/ydb/library/benchmarks/queries/tpcds/yql/q83.sql b/ydb/library/benchmarks/queries/tpcds/yql/q83.sql
new file mode 100644
index 0000000000..f6b4d63043
--- /dev/null
+++ b/ydb/library/benchmarks/queries/tpcds/yql/q83.sql
@@ -0,0 +1,70 @@
+{% include 'header.sql.jinja' %}
+
+-- NB: Subquerys
+$sr_items =
+ (select item.i_item_id item_id,
+        sum(sr_return_quantity) sr_item_qty
+ from {{store_returns}} as store_returns cross join
+      {{item}} as item cross join
+      {{date_dim}} as date_dim
+ where sr_item_sk = i_item_sk
+ and   d_date    in
+	(select d_date
+	from {{date_dim}} as date_dim
+	where d_week_seq in
+		(select d_week_seq
+		from {{date_dim}} as date_dim
+	  where d_date in ('2000-06-17','2000-08-22','2000-11-17')))
+ and   sr_returned_date_sk   = d_date_sk
+ group by item.i_item_id);
+ $cr_items =
+ (select item.i_item_id item_id,
+        sum(cr_return_quantity) cr_item_qty
+ from {{catalog_returns}} as catalog_returns cross join
+      {{item}} as item cross join
+      {{date_dim}} as date_dim
+ where cr_item_sk = i_item_sk
+ and   d_date    in
+	(select d_date
+	from {{date_dim}} as date_dim
+	where d_week_seq in
+		(select d_week_seq
+		from {{date_dim}} as date_dim
+	  where d_date in ('2000-06-17','2000-08-22','2000-11-17')))
+ and   cr_returned_date_sk   = d_date_sk
+ group by item.i_item_id);
+$wr_items =
+ (select item.i_item_id item_id,
+        sum(wr_return_quantity) wr_item_qty
+ from {{web_returns}} as web_returns cross join
+      {{item}} as item cross join
+      {{date_dim}} as date_dim
+ where wr_item_sk = i_item_sk
+ and   d_date    in
+	(select d_date
+	from {{date_dim}} as date_dim
+	where d_week_seq in
+		(select d_week_seq
+		from {{date_dim}} as date_dim
+		where d_date in ('2000-06-17','2000-08-22','2000-11-17')))
+ and   wr_returned_date_sk   = d_date_sk
+ group by item.i_item_id);
+-- start query 1 in stream 0 using template query83.tpl and seed 1930872976
+  select  sr_items.item_id
+       ,sr_item_qty
+       ,sr_item_qty/(sr_item_qty+cr_item_qty+wr_item_qty)/3.0 * 100 sr_dev
+       ,cr_item_qty
+       ,cr_item_qty/(sr_item_qty+cr_item_qty+wr_item_qty)/3.0 * 100 cr_dev
+       ,wr_item_qty
+       ,wr_item_qty/(sr_item_qty+cr_item_qty+wr_item_qty)/3.0 * 100 wr_dev
+       ,(sr_item_qty+cr_item_qty+wr_item_qty)/3.0 average
+ from $sr_items sr_items
+      cross join $cr_items cr_items
+      cross join $wr_items wr_items
+ where sr_items.item_id=cr_items.item_id
+   and sr_items.item_id=wr_items.item_id
+ order by sr_items.item_id
+         ,sr_item_qty
+ limit 100;
+
+-- end query 1 in stream 0 using template query83.tpl
diff --git a/ydb/library/benchmarks/queries/tpcds/yql/q84.sql b/ydb/library/benchmarks/queries/tpcds/yql/q84.sql
new file mode 100644
index 0000000000..a0bbd6a7c3
--- /dev/null
+++ b/ydb/library/benchmarks/queries/tpcds/yql/q84.sql
@@ -0,0 +1,24 @@
+{% include 'header.sql.jinja' %}
+
+-- NB: Subquerys
+-- start query 1 in stream 0 using template query84.tpl and seed 1819994127
+select  c_customer_id as customer_id
+       , coalesce(c_last_name,'') || ', ' || coalesce(c_first_name,'') as customername
+ from {{customer}} as customer
+    cross join {{customer_address}} as customer_address
+     cross join {{customer_demographics}} as customer_demographics
+     cross join {{household_demographics}} as household_demographics
+     cross join {{income_band}} as income_band
+     cross join {{store_returns}} as store_returns
+ where ca_city	        =  'Hopewell'
+   and c_current_addr_sk = ca_address_sk
+   and ib_lower_bound   >=  37855
+   and ib_upper_bound   <=  37855 + 50000
+   and ib_income_band_sk = hd_income_band_sk
+   and cd_demo_sk = c_current_cdemo_sk
+   and hd_demo_sk = c_current_hdemo_sk
+   and sr_cdemo_sk = cd_demo_sk
+ order by customer_id
+ limit 100;
+
+-- end query 1 in stream 0 using template query84.tpl
diff --git a/ydb/library/benchmarks/queries/tpcds/yql/q85.sql b/ydb/library/benchmarks/queries/tpcds/yql/q85.sql
new file mode 100644
index 0000000000..cd045815eb
--- /dev/null
+++ b/ydb/library/benchmarks/queries/tpcds/yql/q85.sql
@@ -0,0 +1,93 @@
+{% include 'header.sql.jinja' %}
+
+-- NB: Subquerys
+-- start query 1 in stream 0 using template query85.tpl and seed 622697896
+select  substring(cast(reason.r_reason_desc as string),1,20) bla
+       ,avg(ws_quantity) bla2
+       ,avg(wr_refunded_cash) bla3
+       ,avg(wr_fee) bla4
+ from {{web_sales}} as web_sales
+ cross join {{web_returns}} as web_returns 
+ cross join {{web_page}} as web_page
+ cross join {{customer_demographics}} cd1 cross join
+      {{customer_demographics}} cd2 
+      cross join {{customer_address}} as customer_address
+      cross join {{date_dim}} as date_dim
+      cross join {{reason}} as reason
+ where ws_web_page_sk = wp_web_page_sk
+   and ws_item_sk = wr_item_sk
+   and ws_order_number = wr_order_number
+   and ws_sold_date_sk = d_date_sk and d_year = 2001
+   and cd1.cd_demo_sk = wr_refunded_cdemo_sk
+   and cd2.cd_demo_sk = wr_returning_cdemo_sk
+   and ca_address_sk = wr_refunded_addr_sk
+   and r_reason_sk = wr_reason_sk
+   and
+   (
+    (
+     cd1.cd_marital_status = 'M'
+     and
+     cd1.cd_marital_status = cd2.cd_marital_status
+     and
+     cd1.cd_education_status = '4 yr Degree'
+     and
+     cd1.cd_education_status = cd2.cd_education_status
+     and
+     ws_sales_price between 100.00 and 150.00
+    )
+   or
+    (
+     cd1.cd_marital_status = 'S'
+     and
+     cd1.cd_marital_status = cd2.cd_marital_status
+     and
+     cd1.cd_education_status = 'College'
+     and
+     cd1.cd_education_status = cd2.cd_education_status
+     and
+     ws_sales_price between 50.00 and 100.00
+    )
+   or
+    (
+     cd1.cd_marital_status = 'D'
+     and
+     cd1.cd_marital_status = cd2.cd_marital_status
+     and
+     cd1.cd_education_status = 'Secondary'
+     and
+     cd1.cd_education_status = cd2.cd_education_status
+     and
+     ws_sales_price between 150.00 and 200.00
+    )
+   )
+   and
+   (
+    (
+     ca_country = 'United States'
+     and
+     ca_state in ('TX', 'VA', 'CA')
+     and ws_net_profit between 100 and 200
+    )
+    or
+    (
+     ca_country = 'United States'
+     and
+     ca_state in ('AR', 'NE', 'MO')
+     and ws_net_profit between 150 and 300
+    )
+    or
+    (
+     ca_country = 'United States'
+     and
+     ca_state in ('IA', 'MS', 'WA')
+     and ws_net_profit between 50 and 250
+    )
+   )
+group by reason.r_reason_desc
+order by bla
+        ,bla2
+        ,bla3
+        ,bla4
+limit 100;
+
+-- end query 1 in stream 0 using template query85.tpl
diff --git a/ydb/library/benchmarks/queries/tpcds/yql/q86.sql b/ydb/library/benchmarks/queries/tpcds/yql/q86.sql
new file mode 100644
index 0000000000..c5a0e3045f
--- /dev/null
+++ b/ydb/library/benchmarks/queries/tpcds/yql/q86.sql
@@ -0,0 +1,29 @@
+{% include 'header.sql.jinja' %}
+
+-- NB: Subquerys
+-- start query 1 in stream 0 using template query86.tpl and seed 1819994127
+select
+    sum(ws_net_paid) as total_sum
+   ,i_category
+   ,i_class
+   ,grouping(item.i_category)+grouping(item.i_class) as lochierarchy
+   ,rank() over (
+ 	partition by grouping(item.i_category)+grouping(item.i_class),
+ 	case when grouping(item.i_class) = 0 then i_category else null end
+ 	order by sum(ws_net_paid) desc) as rank_within_parent
+ from
+    {{web_sales}} as web_sales
+    cross join {{date_dim}}       d1
+   cross join {{item}} as item
+ where
+    d1.d_month_seq between 1215 and 1215+11
+ and d1.d_date_sk = ws_sold_date_sk
+ and i_item_sk  = ws_item_sk
+ group by rollup(item.i_category,item.i_class)
+ order by
+   lochierarchy desc,
+   case when lochierarchy = 0 then i_category else null end,
+   rank_within_parent
+ limit 100;
+
+-- end query 1 in stream 0 using template query86.tpl
diff --git a/ydb/library/benchmarks/queries/tpcds/yql/q87.sql b/ydb/library/benchmarks/queries/tpcds/yql/q87.sql
new file mode 100644
index 0000000000..86b79f4be3
--- /dev/null
+++ b/ydb/library/benchmarks/queries/tpcds/yql/q87.sql
@@ -0,0 +1,33 @@
+{% include 'header.sql.jinja' %}
+
+-- NB: Subquerys
+$bla1 = (select distinct c_last_name, c_first_name, d_date
+       from {{store_sales}} as store_sales
+       cross join {{date_dim}} as date_dim
+       cross join  {{customer}} as customer
+       where store_sales.ss_sold_date_sk = date_dim.d_date_sk
+         and store_sales.ss_customer_sk = customer.c_customer_sk
+         and d_month_seq between 1221 and 1221+11);
+
+$bla2 = ((select distinct c_last_name, c_first_name, d_date
+       from {{catalog_sales}} as catalog_sales
+       cross join {{date_dim}} as date_dim
+       cross join {{customer}} as customer
+       where catalog_sales.cs_sold_date_sk = date_dim.d_date_sk
+         and catalog_sales.cs_bill_customer_sk = customer.c_customer_sk
+         and d_month_seq between 1221 and 1221+11)
+       union all
+      (select distinct c_last_name, c_first_name, d_date
+       from {{web_sales}} as web_sales
+       cross join {{date_dim}} as date_dim
+       cross join {{customer}} as customer
+       where web_sales.ws_sold_date_sk = date_dim.d_date_sk
+         and web_sales.ws_bill_customer_sk = customer.c_customer_sk
+         and d_month_seq between 1221 and 1221+11));
+
+-- start query 1 in stream 0 using template query87.tpl and seed 1819994127
+select count(*)
+from $bla1 bla1 left semi join $bla2 bla2 using (c_last_name, c_first_name, d_date)
+;
+
+-- end query 1 in stream 0 using template query87.tpl
diff --git a/ydb/library/benchmarks/queries/tpcds/yql/q88.sql b/ydb/library/benchmarks/queries/tpcds/yql/q88.sql
new file mode 100644
index 0000000000..1c4f01d19d
--- /dev/null
+++ b/ydb/library/benchmarks/queries/tpcds/yql/q88.sql
@@ -0,0 +1,121 @@
+{% include 'header.sql.jinja' %}
+
+-- NB: Subquerys
+-- start query 1 in stream 0 using template query88.tpl and seed 318176889
+select  *
+from
+ (select count(*) h8_30_to_9
+ from {{store_sales}} as store_sales
+ cross join {{household_demographics}} as household_demographics
+ cross join {{time_dim}} as time_dim
+ cross join {{store}} as store
+ where ss_sold_time_sk = time_dim.t_time_sk
+     and ss_hdemo_sk = household_demographics.hd_demo_sk
+     and ss_store_sk = s_store_sk
+     and time_dim.t_hour = 8
+     and time_dim.t_minute >= 30
+     and ((household_demographics.hd_dep_count = 2 and household_demographics.hd_vehicle_count<=2+2) or
+          (household_demographics.hd_dep_count = 4 and household_demographics.hd_vehicle_count<=4+2) or
+          (household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2))
+     and store.s_store_name = 'ese') s1 cross join
+ (select count(*) h9_to_9_30
+ from {{store_sales}} as store_sales
+ cross join {{household_demographics}} as household_demographics
+ cross join {{time_dim}} as time_dim
+ cross join {{store}} as store
+ where ss_sold_time_sk = time_dim.t_time_sk
+     and ss_hdemo_sk = household_demographics.hd_demo_sk
+     and ss_store_sk = s_store_sk
+     and time_dim.t_hour = 9
+     and time_dim.t_minute < 30
+     and ((household_demographics.hd_dep_count = 2 and household_demographics.hd_vehicle_count<=2+2) or
+          (household_demographics.hd_dep_count = 4 and household_demographics.hd_vehicle_count<=4+2) or
+          (household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2))
+     and store.s_store_name = 'ese') s2 cross join
+ (select count(*) h9_30_to_10
+ from {{store_sales}} as store_sales
+ cross join {{household_demographics}} as household_demographics
+ cross join {{time_dim}} as time_dim
+ cross join {{store}} as store
+ where ss_sold_time_sk = time_dim.t_time_sk
+     and ss_hdemo_sk = household_demographics.hd_demo_sk
+     and ss_store_sk = s_store_sk
+     and time_dim.t_hour = 9
+     and time_dim.t_minute >= 30
+     and ((household_demographics.hd_dep_count = 2 and household_demographics.hd_vehicle_count<=2+2) or
+          (household_demographics.hd_dep_count = 4 and household_demographics.hd_vehicle_count<=4+2) or
+          (household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2))
+     and store.s_store_name = 'ese') s3 cross join
+ (select count(*) h10_to_10_30
+ from {{store_sales}} as store_sales
+ cross join {{household_demographics}} as household_demographics
+ cross join {{time_dim}} as time_dim 
+ cross join {{store}} as store
+ where ss_sold_time_sk = time_dim.t_time_sk
+     and ss_hdemo_sk = household_demographics.hd_demo_sk
+     and ss_store_sk = s_store_sk
+     and time_dim.t_hour = 10
+     and time_dim.t_minute < 30
+     and ((household_demographics.hd_dep_count = 2 and household_demographics.hd_vehicle_count<=2+2) or
+          (household_demographics.hd_dep_count = 4 and household_demographics.hd_vehicle_count<=4+2) or
+          (household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2))
+     and store.s_store_name = 'ese') s4 cross join
+ (select count(*) h10_30_to_11
+ from {{store_sales}} as store_sales
+ cross join {{household_demographics}} as household_demographics
+ cross join {{time_dim}} as time_dim
+ cross join {{store}} as store
+ where ss_sold_time_sk = time_dim.t_time_sk
+     and ss_hdemo_sk = household_demographics.hd_demo_sk
+     and ss_store_sk = s_store_sk
+     and time_dim.t_hour = 10
+     and time_dim.t_minute >= 30
+     and ((household_demographics.hd_dep_count = 2 and household_demographics.hd_vehicle_count<=2+2) or
+          (household_demographics.hd_dep_count = 4 and household_demographics.hd_vehicle_count<=4+2) or
+          (household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2))
+     and store.s_store_name = 'ese') s5 cross join
+ (select count(*) h11_to_11_30
+ from {{store_sales}} as store_sales
+ cross join {{household_demographics}} as household_demographics
+ cross join {{time_dim}} as time_dim
+ cross join {{store}} as store
+ where ss_sold_time_sk = time_dim.t_time_sk
+     and ss_hdemo_sk = household_demographics.hd_demo_sk
+     and ss_store_sk = s_store_sk
+     and time_dim.t_hour = 11
+     and time_dim.t_minute < 30
+     and ((household_demographics.hd_dep_count = 2 and household_demographics.hd_vehicle_count<=2+2) or
+          (household_demographics.hd_dep_count = 4 and household_demographics.hd_vehicle_count<=4+2) or
+          (household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2))
+     and store.s_store_name = 'ese') s6 cross join
+ (select count(*) h11_30_to_12
+ from {{store_sales}} as store_sales
+ cross join {{household_demographics}} as household_demographics
+ cross join {{time_dim}} as time_dim
+ cross join {{store}} as store
+ where ss_sold_time_sk = time_dim.t_time_sk
+     and ss_hdemo_sk = household_demographics.hd_demo_sk
+     and ss_store_sk = s_store_sk
+     and time_dim.t_hour = 11
+     and time_dim.t_minute >= 30
+     and ((household_demographics.hd_dep_count = 2 and household_demographics.hd_vehicle_count<=2+2) or
+          (household_demographics.hd_dep_count = 4 and household_demographics.hd_vehicle_count<=4+2) or
+          (household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2))
+     and store.s_store_name = 'ese') s7 cross join
+ (select count(*) h12_to_12_30
+ from {{store_sales}} as store_sales
+ cross join {{household_demographics}} as household_demographics
+ cross join {{time_dim}} as time_dim
+ cross join  {{store}} as store
+ where ss_sold_time_sk = time_dim.t_time_sk
+     and ss_hdemo_sk = household_demographics.hd_demo_sk
+     and ss_store_sk = s_store_sk
+     and time_dim.t_hour = 12
+     and time_dim.t_minute < 30
+     and ((household_demographics.hd_dep_count = 2 and household_demographics.hd_vehicle_count<=2+2) or
+          (household_demographics.hd_dep_count = 4 and household_demographics.hd_vehicle_count<=4+2) or
+          (household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2))
+     and store.s_store_name = 'ese') s8
+;
+
+-- end query 1 in stream 0 using template query88.tpl
diff --git a/ydb/library/benchmarks/queries/tpcds/yql/q89.sql b/ydb/library/benchmarks/queries/tpcds/yql/q89.sql
new file mode 100644
index 0000000000..9ded5dab03
--- /dev/null
+++ b/ydb/library/benchmarks/queries/tpcds/yql/q89.sql
@@ -0,0 +1,34 @@
+{% include 'header.sql.jinja' %}
+
+-- NB: Subquerys
+-- start query 1 in stream 0 using template query89.tpl and seed 1719819282
+select  *
+from(
+select item.i_category, item.i_class, item.i_brand,
+       store.s_store_name s_store_name, store.s_company_name,
+       date_dim.d_moy,
+       sum(ss_sales_price) sum_sales,
+       avg(sum(ss_sales_price)) over
+         (partition by item.i_category, item.i_brand, store.s_store_name, store.s_company_name)
+         avg_monthly_sales
+from {{item}} as item
+cross join {{store_sales}} as store_sales
+cross join {{date_dim}} as date_dim 
+cross join {{store}} as 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 in (2000) and
+        ((i_category in ('Home','Music','Books') and
+          i_class in ('glassware','classical','fiction')
+         )
+      or (i_category in ('Jewelry','Sports','Women') and
+          i_class in ('semi-precious','baseball','dresses')
+        ))
+group by item.i_category, item.i_class, item.i_brand,
+         store.s_store_name, store.s_company_name, date_dim.d_moy) tmp1
+where case when (avg_monthly_sales <> 0) then (abs(sum_sales - avg_monthly_sales) / avg_monthly_sales) else null end > 0.1
+order by sum_sales - avg_monthly_sales, s_store_name
+limit 100;
+
+-- end query 1 in stream 0 using template query89.tpl
diff --git a/ydb/library/benchmarks/queries/tpcds/yql/q90.sql b/ydb/library/benchmarks/queries/tpcds/yql/q90.sql
new file mode 100644
index 0000000000..e54a0497d6
--- /dev/null
+++ b/ydb/library/benchmarks/queries/tpcds/yql/q90.sql
@@ -0,0 +1,31 @@
+{% include 'header.sql.jinja' %}
+
+-- NB: Subquerys
+-- start query 1 in stream 0 using template query90.tpl and seed 2031708268
+select  cast(amc as decimal(15,4))/cast(pmc as decimal(15,4)) am_pm_ratio
+ from ( select count(*) amc
+       from {{web_sales}} as web_sales
+       cross join {{household_demographics}} as household_demographics
+       cross join {{time_dim}} as time_dim
+       cross join {{web_page}} as web_page
+       where ws_sold_time_sk = time_dim.t_time_sk
+         and ws_ship_hdemo_sk = household_demographics.hd_demo_sk
+         and ws_web_page_sk = web_page.wp_web_page_sk
+         and time_dim.t_hour between 9 and 9+1
+         and household_demographics.hd_dep_count = 3
+         and web_page.wp_char_count between 5000 and 5200) at cross join
+      ( select count(*) pmc
+       from {{web_sales}} as web_sales
+       cross join {{household_demographics}} as household_demographics
+       cross join {{time_dim}} as time_dim
+       cross join {{web_page}} as web_page
+       where ws_sold_time_sk = time_dim.t_time_sk
+         and ws_ship_hdemo_sk = household_demographics.hd_demo_sk
+         and ws_web_page_sk = web_page.wp_web_page_sk
+         and time_dim.t_hour between 16 and 16+1
+         and household_demographics.hd_dep_count = 3
+         and web_page.wp_char_count between 5000 and 5200) pt
+ order by am_pm_ratio
+ limit 100;
+
+-- end query 1 in stream 0 using template query90.tpl
diff --git a/ydb/library/benchmarks/queries/tpcds/yql/q91.sql b/ydb/library/benchmarks/queries/tpcds/yql/q91.sql
new file mode 100644
index 0000000000..cedb0693fb
--- /dev/null
+++ b/ydb/library/benchmarks/queries/tpcds/yql/q91.sql
@@ -0,0 +1,34 @@
+{% include 'header.sql.jinja' %}
+
+-- NB: Subquerys
+-- start query 1 in stream 0 using template query91.tpl and seed 1930872976
+select
+        call_center.cc_call_center_id Call_Center,
+        call_center.cc_name Call_Center_Name,
+        call_center.cc_manager Manager,
+        sum(cr_net_loss) Returns_Loss
+from
+        {{call_center}} as call_center cross join
+        {{catalog_returns}} as catalog_returns cross join
+        {{date_dim}} as date_dim cross join
+        {{customer}} as customer cross join
+        {{customer_address}} as customer_address cross join
+        {{customer_demographics}} as customer_demographics cross join
+        {{household_demographics}} as household_demographics
+where
+        cr_call_center_sk       = cc_call_center_sk
+and     cr_returned_date_sk     = d_date_sk
+and     cr_returning_customer_sk= c_customer_sk
+and     cd_demo_sk              = c_current_cdemo_sk
+and     hd_demo_sk              = c_current_hdemo_sk
+and     ca_address_sk           = c_current_addr_sk
+and     d_year                  = 2000
+and     d_moy                   = 12
+and     ( (cd_marital_status       = 'M' and cd_education_status     = 'Unknown')
+        or(cd_marital_status       = 'W' and cd_education_status     = 'Advanced Degree'))
+and     hd_buy_potential like 'Unknown%'
+and     ca_gmt_offset           = -7
+group by call_center.cc_call_center_id,call_center.cc_name,call_center.cc_manager,customer_demographics.cd_marital_status,customer_demographics.cd_education_status
+order by Returns_Loss desc;
+
+-- end query 1 in stream 0 using template query91.tpl
diff --git a/ydb/library/benchmarks/queries/tpcds/yql/q92.sql b/ydb/library/benchmarks/queries/tpcds/yql/q92.sql
new file mode 100644
index 0000000000..f9261de62d
--- /dev/null
+++ b/ydb/library/benchmarks/queries/tpcds/yql/q92.sql
@@ -0,0 +1,37 @@
+{% include 'header.sql.jinja' %}
+
+-- NB: Subquerys
+$bla = (
+         SELECT
+         web_sales.ws_item_sk bla_item_sk,
+            avg(ws_ext_discount_amt)  bla_ext_discount_amt
+         FROM
+            {{web_sales}} as web_sales
+           cross join {{date_dim}} as date_dim
+         WHERE
+        cast(d_date as date) between cast('2001-03-12' as date) and
+                             (cast('2001-03-12' as date) + DateTime::IntervalFromDays(90))
+          and d_date_sk = ws_sold_date_sk
+          group by web_sales.ws_item_sk
+      );
+
+-- start query 1 in stream 0 using template query92.tpl and seed 2031708268
+select
+   sum(ws_ext_discount_amt)  as `Excess Discount Amount`
+from
+    {{web_sales}} as web_sales
+   cross join {{item}} as item
+   cross join {{date_dim}} as date_dim
+   join $bla bla on (item.i_item_sk = bla.bla_item_sk)
+where
+i_manufact_id = 356
+and i_item_sk = ws_item_sk
+and cast(d_date as date) between cast('2001-03-12' as date) and
+        (cast('2001-03-12' as date) + DateTime::IntervalFromDays(90))
+and d_date_sk = ws_sold_date_sk
+and ws_ext_discount_amt
+     > 1.3 * bla.bla_ext_discount_amt
+order by `Excess Discount Amount`
+limit 100;
+
+-- end query 1 in stream 0 using template query92.tpl
diff --git a/ydb/library/benchmarks/queries/tpcds/yql/q93.sql b/ydb/library/benchmarks/queries/tpcds/yql/q93.sql
new file mode 100644
index 0000000000..2170965177
--- /dev/null
+++ b/ydb/library/benchmarks/queries/tpcds/yql/q93.sql
@@ -0,0 +1,22 @@
+{% include 'header.sql.jinja' %}
+
+-- NB: Subquerys
+-- start query 1 in stream 0 using template query93.tpl and seed 1200409435
+select  ss_customer_sk
+            ,sum(act_sales) sumsales
+      from (select ss_item_sk
+                  ,ss_ticket_number
+                  ,ss_customer_sk
+                  ,case when sr_return_quantity is not null then (ss_quantity-sr_return_quantity)*ss_sales_price
+                                                            else (ss_quantity*ss_sales_price) end act_sales
+            from {{store_sales}} as store_sales
+            left join {{store_returns}} as store_returns on (store_returns.sr_item_sk = store_sales.ss_item_sk
+                                                               and store_returns.sr_ticket_number = store_sales.ss_ticket_number)
+                cross join {{reason}} as reason
+            where sr_reason_sk = r_reason_sk
+              and r_reason_desc = 'reason 66') t
+      group by ss_customer_sk
+      order by sumsales, ss_customer_sk
+limit 100;
+
+-- end query 1 in stream 0 using template query93.tpl
diff --git a/ydb/library/benchmarks/queries/tpcds/yql/q94.sql b/ydb/library/benchmarks/queries/tpcds/yql/q94.sql
new file mode 100644
index 0000000000..26147a1f02
--- /dev/null
+++ b/ydb/library/benchmarks/queries/tpcds/yql/q94.sql
@@ -0,0 +1,32 @@
+{% include 'header.sql.jinja' %}
+
+-- NB: Subquerys
+$bla1 = (select ws_order_number
+            from {{web_sales}} as web_sales
+              group by ws_order_number
+              having COUNT(DISTINCT ws_warehouse_sk) > 1);
+
+-- start query 1 in stream 0 using template query94.tpl and seed 2031708268
+select
+   count(distinct ws1.ws_order_number) as `order count`
+  ,sum(ws_ext_ship_cost) as `total shipping cost`
+  ,sum(ws_net_profit) as `total net profit`
+from
+   {{web_sales}} ws1
+  cross join {{date_dim}} as date_dim
+  cross join {{customer_address}} as customer_address
+  cross join {{web_site}} as web_site
+  left semi join $bla1 bla1 on (ws1.ws_order_number = bla1.ws_order_number)
+  left only join {{web_returns}} as web_returns on (ws1.ws_order_number = web_returns.wr_order_number)
+where
+    cast(d_date as date) between cast('1999-4-01' as date) and
+           (cast('1999-4-01' as date) + DateTime::IntervalFromDays(60))
+and ws1.ws_ship_date_sk = d_date_sk
+and ws1.ws_ship_addr_sk = ca_address_sk
+and ca_state = 'NE'
+and ws1.ws_web_site_sk = web_site_sk
+and web_company_name = 'pri'
+order by `order count`
+limit 100;
+
+-- end query 1 in stream 0 using template query94.tpl
diff --git a/ydb/library/benchmarks/queries/tpcds/yql/q95.sql b/ydb/library/benchmarks/queries/tpcds/yql/q95.sql
new file mode 100644
index 0000000000..c3d614f63f
--- /dev/null
+++ b/ydb/library/benchmarks/queries/tpcds/yql/q95.sql
@@ -0,0 +1,35 @@
+{% include 'header.sql.jinja' %}
+
+-- NB: Subquerys
+$ws_wh =
+(select ws1.ws_order_number ws_order_number,ws1.ws_warehouse_sk wh1,ws2.ws_warehouse_sk wh2
+ from {{web_sales}} ws1 cross join {{web_sales}} ws2
+ where ws1.ws_order_number = ws2.ws_order_number
+   and ws1.ws_warehouse_sk <> ws2.ws_warehouse_sk);
+-- start query 1 in stream 0 using template query95.tpl and seed 2031708268
+ select
+   count(distinct ws1.ws_order_number) as `order count`
+  ,sum(ws_ext_ship_cost) as `total shipping cost`
+  ,sum(ws_net_profit) as `total net profit`
+from
+   {{web_sales}} ws1
+  cross join {{date_dim}} as date_dim
+  cross join {{customer_address}} as customer_address
+  cross join {{web_site}} as web_site
+where
+    cast(d_date as date) between cast('2002-4-01' as date) and
+           (cast('2002-4-01' as date) + DateTime::IntervalFromDays(60))
+and ws1.ws_ship_date_sk = d_date_sk
+and ws1.ws_ship_addr_sk = ca_address_sk
+and ca_state = 'AL'
+and ws1.ws_web_site_sk = web_site_sk
+and web_company_name = 'pri'
+and ws1.ws_order_number in (select ws_order_number
+                            from $ws_wh)
+and ws1.ws_order_number in (select wr_order_number
+                            from {{web_returns}} as web_returns cross join $ws_wh ws_wh
+                            where wr_order_number = ws_wh.ws_order_number)
+order by `order count`
+limit 100;
+
+-- end query 1 in stream 0 using template query95.tpl
diff --git a/ydb/library/benchmarks/queries/tpcds/yql/q96.sql b/ydb/library/benchmarks/queries/tpcds/yql/q96.sql
new file mode 100644
index 0000000000..1ca6a6cb73
--- /dev/null
+++ b/ydb/library/benchmarks/queries/tpcds/yql/q96.sql
@@ -0,0 +1,20 @@
+{% include 'header.sql.jinja' %}
+
+-- NB: Subquerys
+-- start query 1 in stream 0 using template query96.tpl and seed 1819994127
+select  count(*) bla
+from {{store_sales}} as store_sales
+    cross join {{household_demographics}} as household_demographics
+    cross join {{time_dim}} as time_dim 
+    cross join {{store}} as store
+where ss_sold_time_sk = time_dim.t_time_sk
+    and ss_hdemo_sk = household_demographics.hd_demo_sk
+    and ss_store_sk = s_store_sk
+    and time_dim.t_hour = 16
+    and time_dim.t_minute >= 30
+    and household_demographics.hd_dep_count = 6
+    and store.s_store_name = 'ese'
+order by bla
+limit 100;
+
+-- end query 1 in stream 0 using template query96.tpl
diff --git a/ydb/library/benchmarks/queries/tpcds/yql/q97.sql b/ydb/library/benchmarks/queries/tpcds/yql/q97.sql
new file mode 100644
index 0000000000..d0dec39075
--- /dev/null
+++ b/ydb/library/benchmarks/queries/tpcds/yql/q97.sql
@@ -0,0 +1,30 @@
+{% include 'header.sql.jinja' %}
+
+$ssci = (
+    SELECT ss.ss_customer_sk as customer_sk
+          ,ss.ss_item_sk as item_sk
+    FROM {{store_sales}} as ss
+    CROSS JOIN {{date_dim}} as dd
+    WHERE ss.ss_sold_date_sk = dd.d_date_sk
+          AND dd.d_month_seq BETWEEN 1190 AND 1190 + 11
+    GROUP BY ss.ss_customer_sk, ss.ss_item_sk
+);
+
+$csci = (
+    SELECT cs.cs_bill_customer_sk as customer_sk
+          ,cs.cs_item_sk as item_sk
+    FROM {{catalog_sales}} as cs
+    CROSS JOIN {{date_dim}} as dd
+    WHERE cs.cs_sold_date_sk = dd.d_date_sk
+        AND dd.d_month_seq BETWEEN 1190 AND 1190 + 11
+    GROUP BY cs.cs_bill_customer_sk
+            ,cs.cs_item_sk
+);
+
+SELECT SUM(case when ssci.customer_sk is not null and csci.customer_sk is null then 1 else 0 end) as store_only
+       ,SUM(case when ssci.customer_sk is null and csci.customer_sk is not null then 1 else 0 end) as catalog_only
+       ,SUM(case when ssci.customer_sk is not null and csci.customer_sk is not null then 1 else 0 end) as store_and_catalog
+FROM $ssci as ssci
+FULL JOIN $csci as csci ON (ssci.customer_sk = csci.customer_sk
+                            AND ssci.item_sk = csci.item_sk)
+LIMIT 100;
diff --git a/ydb/library/benchmarks/queries/tpcds/yql/q98.sql b/ydb/library/benchmarks/queries/tpcds/yql/q98.sql
new file mode 100644
index 0000000000..a4c9bc47cd
--- /dev/null
+++ b/ydb/library/benchmarks/queries/tpcds/yql/q98.sql
@@ -0,0 +1,32 @@
+{% include 'header.sql.jinja' %}
+
+select i.i_item_id
+      ,i.i_item_desc
+      ,i.i_category
+      ,i.i_class
+      ,i.i_current_price
+      ,sum(ss.ss_ext_sales_price) as itemrevenue
+      ,sum(ss.ss_ext_sales_price)*100/sum(sum(ss.ss_ext_sales_price)) over
+          (partition by i.i_class) as revenueratio
+from {{store_sales}} as ss
+cross join {{item}} as i
+cross join {{date_dim}} as d
+where
+	ss.ss_item_sk = i.i_item_sk
+  	and i.i_category in ('Home', 'Sports', 'Men')
+  	and ss.ss_sold_date_sk = d.d_date_sk
+	and cast(d.d_date as Date) between cast('2002-01-05' as Date)
+				               and (cast('2002-01-05' as Date) + DateTime::IntervalFromDays(30))
+group by
+	 i.i_item_id
+    ,i.i_item_desc
+    ,i.i_category
+    ,i.i_class
+    ,i.i_current_price
+order by
+	 i.i_category
+    ,i.i_class
+    ,i.i_item_id
+    ,i.i_item_desc
+    ,revenueratio;
+
diff --git a/ydb/library/benchmarks/queries/tpcds/yql/q99.sql b/ydb/library/benchmarks/queries/tpcds/yql/q99.sql
new file mode 100644
index 0000000000..f915d78b1d
--- /dev/null
+++ b/ydb/library/benchmarks/queries/tpcds/yql/q99.sql
@@ -0,0 +1,34 @@
+{% include 'header.sql.jinja' %}
+
+select
+    w_warehouse_name,
+    sm.sm_type,
+    cc.cc_name,
+    sum(case when (cs.cs_ship_date_sk - cs.cs_sold_date_sk <= 30 ) then 1 else 0 end)  as `30 days`,
+    sum(case when (cs.cs_ship_date_sk - cs.cs_sold_date_sk > 30) and
+                 (cs.cs_ship_date_sk - cs.cs_sold_date_sk <= 60) then 1 else 0 end )  as `31-60 days`,
+    sum(case when (cs.cs_ship_date_sk - cs.cs_sold_date_sk > 60) and
+                 (cs.cs_ship_date_sk - cs.cs_sold_date_sk <= 90) then 1 else 0 end)  as `61-90 days`,
+    sum(case when (cs.cs_ship_date_sk - cs.cs_sold_date_sk > 90) and
+                 (cs.cs_ship_date_sk - cs.cs_sold_date_sk <= 120) then 1 else 0 end)  as `91-120 days`,
+    sum(case when (cs.cs_ship_date_sk - cs.cs_sold_date_sk  > 120) then 1 else 0 end)  as `>120 days`
+from
+    {{catalog_sales}} as cs
+    cross join {{warehouse}} as w
+    cross join {{ship_mode}} as sm
+    cross join {{call_center}} as cc
+    cross join {{date_dim}} as d
+where
+    d.d_month_seq between 1178 and 1178 + 11
+    and cs.cs_ship_date_sk   = d.d_date_sk
+    and cs.cs_warehouse_sk   = w.w_warehouse_sk
+    and cs.cs_ship_mode_sk   = sm.sm_ship_mode_sk
+    and cs.cs_call_center_sk = cc.cc_call_center_sk
+group by
+    substring(cast(w.w_warehouse_name as String), 1, 20) as w_warehouse_name,
+    sm.sm_type,
+    cc.cc_name
+order by w_warehouse_name,
+         sm.sm_type,
+         cc.cc_name
+limit 100;