tpcds.query80.sql Maven / Gradle / Ivy

Show more of this group Show more artifacts with this name
Show all versions of druid Show documentation
An JDBC datasource implementation.
There is a newer version: 1.2.23
with ssr as
         (select s_store_id                                    as store_id,
                 sum(ss_ext_sales_price)                       as sales,
                 sum(coalesce(sr_return_amt, 0))               as returns,
                 sum(ss_net_profit - coalesce(sr_net_loss, 0)) as profit
          from store_sales
                   left outer join store_returns on
              (ss_item_sk = sr_item_sk and ss_ticket_number = sr_ticket_number),
               date_dim,
               store,
               item,
               promotion
          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
            and ss_item_sk = i_item_sk
            and i_current_price > 50
            and ss_promo_sk = p_promo_sk
            and p_channel_tv = 'N'
          group by s_store_id)
        ,
     csr as
         (select cp_catalog_page_id                            as catalog_page_id,
                 sum(cs_ext_sales_price)                       as sales,
                 sum(coalesce(cr_return_amount, 0))            as returns,
                 sum(cs_net_profit - coalesce(cr_net_loss, 0)) as profit
          from catalog_sales
                   left outer join catalog_returns on
              (cs_item_sk = cr_item_sk and cs_order_number = cr_order_number),
               date_dim,
               catalog_page,
               item,
               promotion
          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)
            and cs_catalog_page_sk = cp_catalog_page_sk
            and cs_item_sk = i_item_sk
            and i_current_price > 50
            and cs_promo_sk = p_promo_sk
            and p_channel_tv = 'N'
          group by cp_catalog_page_id)
        ,
     wsr as
         (select web_site_id,
                 sum(ws_ext_sales_price)                       as sales,
                 sum(coalesce(wr_return_amt, 0))               as returns,
                 sum(ws_net_profit - coalesce(wr_net_loss, 0)) as profit
          from web_sales
                   left outer join web_returns on
              (ws_item_sk = wr_item_sk and ws_order_number = wr_order_number),
               date_dim,
               web_site,
               item,
               promotion
          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_site_sk = web_site_sk
            and ws_item_sk = i_item_sk
            and i_current_price > 50
            and ws_promo_sk = p_promo_sk
            and p_channel_tv = 'N'
          group by web_site_id)
select channel
     , id
     , sum(sales)   as sales
     , sum(returns) as returns
     , sum(profit)  as profit
from (select 'store channel'           as channel
           , concat('store', store_id) as id
           , sales
           , returns
           , profit
      from ssr
      union all
      select 'catalog channel'                       as channel
           , concat('catalog_page', catalog_page_id) as id
           , sales
           , returns
           , profit
      from csr
      union all
      select 'web channel'                   as channel
           , concat('web_site', web_site_id) as id
           , sales
           , returns
           , profit
      from wsr) x
group by rollup (channel, id)
order by channel
       , id limit 100