Merge pull request #11788 from ydb-platform/mergelibs-241120-1113

Library import 241120-1113

1 files changed, 63 insertions, 0 deletions

diff --git a/yql/essentials/tests/sql/suites/pg-tpcds/q02.sql b/yql/essentials/tests/sql/suites/pg-tpcds/q02.sql
new file mode 100644
index 0000000000..0b1e03a631
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/pg-tpcds/q02.sql
@@ -0,0 +1,63 @@
+--!syntax_pg
+--TPC-DS Q2
+
+-- start query 1 in stream 0 using template ../query_templates/query2.tpl
+with wscs as
+ (select sold_date_sk
+        ,sales_price
+  from (select ws_sold_date_sk sold_date_sk
+              ,ws_ext_sales_price sales_price
+        from plato.web_sales 
+        union all
+        select cs_sold_date_sk sold_date_sk
+              ,cs_ext_sales_price sales_price
+        from plato.catalog_sales) a),
+ wswscs as 
+ (select d_week_seq,
+        sum(case when (d_day_name='Sunday') then sales_price else null::numeric end) sun_sales,
+        sum(case when (d_day_name='Monday') then sales_price else null::numeric end) mon_sales,
+        sum(case when (d_day_name='Tuesday') then sales_price else null::numeric end) tue_sales,
+        sum(case when (d_day_name='Wednesday') then sales_price else null::numeric end) wed_sales,
+        sum(case when (d_day_name='Thursday') then sales_price else null::numeric end) thu_sales,
+        sum(case when (d_day_name='Friday') then sales_price else null::numeric end) fri_sales,
+        sum(case when (d_day_name='Saturday') then sales_price else null::numeric end) sat_sales
+ from wscs
+     ,plato.date_dim
+ where d_date_sk = sold_date_sk
+ group by d_week_seq)
+ select d_week_seq1
+       ,round(sun_sales1/sun_sales2,2) r1
+       ,round(mon_sales1/mon_sales2,2) r2
+       ,round(tue_sales1/tue_sales2,2) r3
+       ,round(wed_sales1/wed_sales2,2) r4
+       ,round(thu_sales1/thu_sales2,2) r5
+       ,round(fri_sales1/fri_sales2,2) r6
+       ,round(sat_sales1/sat_sales2,2) r7
+ from
+ (select wswscs.d_week_seq d_week_seq1
+        ,sun_sales sun_sales1
+        ,mon_sales mon_sales1
+        ,tue_sales tue_sales1
+        ,wed_sales wed_sales1
+        ,thu_sales thu_sales1
+        ,fri_sales fri_sales1
+        ,sat_sales sat_sales1
+  from wswscs,plato.date_dim 
+  where date_dim.d_week_seq = wswscs.d_week_seq and
+        d_year = 2001) y,
+ (select wswscs.d_week_seq d_week_seq2
+        ,sun_sales sun_sales2
+        ,mon_sales mon_sales2
+        ,tue_sales tue_sales2
+        ,wed_sales wed_sales2
+        ,thu_sales thu_sales2
+        ,fri_sales fri_sales2
+        ,sat_sales sat_sales2
+  from wswscs
+      ,plato.date_dim 
+  where date_dim.d_week_seq = wswscs.d_week_seq and
+        d_year = 2001+1) z
+ where d_week_seq1=d_week_seq2-53
+ order by d_week_seq1;
+
+-- end query 1 in stream 0 using template ../query_templates/query2.tpl