diff options
Diffstat (limited to 'src/s3select/TPCDS/sample-queries-tpcds/query77.sql')
-rw-r--r-- | src/s3select/TPCDS/sample-queries-tpcds/query77.sql | 108 |
1 files changed, 108 insertions, 0 deletions
diff --git a/src/s3select/TPCDS/sample-queries-tpcds/query77.sql b/src/s3select/TPCDS/sample-queries-tpcds/query77.sql new file mode 100644 index 000000000..9da7766b1 --- /dev/null +++ b/src/s3select/TPCDS/sample-queries-tpcds/query77.sql @@ -0,0 +1,108 @@ +-- start query 1 in stream 0 using template query77.tpl and seed 1819994127 +with ss as + (select s_store_sk, + sum(ss_ext_sales_price) as sales, + sum(ss_net_profit) as profit + from store_sales, + date_dim, + store + where ss_sold_date_sk = d_date_sk + and d_date between cast('2000-08-16' as date) + and (cast('2000-08-16' as date) + interval '30' day) + and ss_store_sk = s_store_sk + group by s_store_sk) + , + sr as + (select s_store_sk, + sum(sr_return_amt) as returns, + sum(sr_net_loss) as profit_loss + from store_returns, + date_dim, + store + where sr_returned_date_sk = d_date_sk + and d_date between cast('2000-08-16' as date) + and (cast('2000-08-16' as date) + interval '30' day) + and sr_store_sk = s_store_sk + group by s_store_sk), + cs as + (select cs_call_center_sk, + sum(cs_ext_sales_price) as sales, + sum(cs_net_profit) as profit + from catalog_sales, + date_dim + where cs_sold_date_sk = d_date_sk + and d_date between cast('2000-08-16' as date) + and (cast('2000-08-16' as date) + interval '30' day) + group by cs_call_center_sk + ), + cr as + (select cr_call_center_sk, + sum(cr_return_amount) as returns, + sum(cr_net_loss) as profit_loss + from catalog_returns, + date_dim + where cr_returned_date_sk = d_date_sk + and d_date between cast('2000-08-16' as date) + and (cast('2000-08-16' as date) + interval '30' day) + group by cr_call_center_sk + ), + ws as + ( select wp_web_page_sk, + sum(ws_ext_sales_price) as sales, + sum(ws_net_profit) as profit + from web_sales, + date_dim, + web_page + where ws_sold_date_sk = d_date_sk + and d_date between cast('2000-08-16' as date) + and (cast('2000-08-16' as date) + interval '30' day) + and ws_web_page_sk = wp_web_page_sk + group by wp_web_page_sk), + wr as + (select wp_web_page_sk, + sum(wr_return_amt) as returns, + sum(wr_net_loss) as profit_loss + from web_returns, + date_dim, + web_page + where wr_returned_date_sk = d_date_sk + and d_date between cast('2000-08-16' as date) + and (cast('2000-08-16' as date) + interval '30' day) + and wr_web_page_sk = wp_web_page_sk + group by wp_web_page_sk) + 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 left join 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 + , 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 left join 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 |