tpcds.query77.sql Maven / Gradle / Ivy

Go to download
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-23' as date)
              and date_add(cast('2000-08-23' as date), 30)
            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-23' as date)
              and date_add(cast('2000-08-23' as date), 30)
            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-23' as date)
              and date_add(cast('2000-08-23' as date), 30)
          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-23' as date)
              and date_add(cast('2000-08-23' as date), 30)
          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-23' as date)
              and date_add(cast('2000-08-23' as date), 30)
            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-23' as date)
              and date_add(cast('2000-08-23' as date), 30)
            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