ydb/yt/yql/tests/sql/suites/pg-tpcds/q02.sql

--!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