resources/tpcds-modifiedQueries/q43.sql

0001 -- start query 43 in stream 0 using template query43.tpl
0002 select
0003   s_store_name,
0004   s_store_id,
0005   sum(case when (d_day_name = 'Sunday') then ss_sales_price else null end) sun_sales,
0006   sum(case when (d_day_name = 'Monday') then ss_sales_price else null end) mon_sales,
0007   sum(case when (d_day_name = 'Tuesday') then ss_sales_price else null end) tue_sales,
0008   sum(case when (d_day_name = 'Wednesday') then ss_sales_price else null end) wed_sales,
0009   sum(case when (d_day_name = 'Thursday') then ss_sales_price else null end) thu_sales,
0010   sum(case when (d_day_name = 'Friday') then ss_sales_price else null end) fri_sales,
0011   sum(case when (d_day_name = 'Saturday') then ss_sales_price else null end) sat_sales
0012 from
0013   date_dim,
0014   store_sales,
0015   store
0016 where
0017   d_date_sk = ss_sold_date_sk
0018   and s_store_sk = ss_store_sk
0019   and s_gmt_offset = -5
0020   and d_year = 1998
0021   and ss_sold_date_sk between 2450816 and 2451179  -- partition key filter
0022 group by
0023   s_store_name,
0024   s_store_id
0025 order by
0026   s_store_name,
0027   s_store_id,
0028   sun_sales,
0029   mon_sales,
0030   tue_sales,
0031   wed_sales,
0032   thu_sales,
0033   fri_sales,
0034   sat_sales
0035 limit 100
0036 -- end query 43 in stream 0 using template query43.tpl