plus/src/test/resources/sql/tpcds/49.sql

/*
 * Licensed to the Apache Software Foundation (ASF) under one or more
 * contributor license agreements.  See the NOTICE file distributed with
 * this work for additional information regarding copyright ownership.
 * The ASF licenses this file to you under the Apache License, Version 2.0
 * (the "License"); you may not use this file except in compliance with
 * the License.  You may obtain a copy of the License at
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

  select
 'web' as channel
 ,web.item
 ,web.return_ratio
 ,web.return_rank
 ,web.currency_rank
 from (
   select
    item
   ,return_ratio
   ,currency_ratio
   ,rank() over (order by return_ratio) as return_rank
   ,rank() over (order by currency_ratio) as currency_rank
   from
   (  select ws.ws_item_sk as item
     ,(cast(sum(coalesce(wr.wr_return_quantity,0)) as dec(15,4))/
     cast(sum(coalesce(ws.ws_quantity,0)) as dec(15,4) )) as return_ratio
     ,(cast(sum(coalesce(wr.wr_return_amt,0)) as dec(15,4))/
     cast(sum(coalesce(ws.ws_net_paid,0)) as dec(15,4) )) as currency_ratio
     from
      web_sales ws left outer join web_returns wr
       on (ws.ws_order_number = wr.wr_order_number and
       ws.ws_item_sk = wr.wr_item_sk)
                 ,date_dim
     where
       wr.wr_return_amt > 10000
       and ws.ws_net_profit > 1
                         and ws.ws_net_paid > 0
                         and ws.ws_quantity > 0
                         and ws_sold_date_sk = d_date_sk
                         and d_year = 1998
                         and d_moy = 12
     group by ws.ws_item_sk
   ) in_web
 ) web
 where
 (
 web.return_rank <= 10
 or
 web.currency_rank <= 10
 )
 union
 select
 'catalog' as channel
 ,catalog.item
 ,catalog.return_ratio
 ,catalog.return_rank
 ,catalog.currency_rank
 from (
   select
    item
   ,return_ratio
   ,currency_ratio
   ,rank() over (order by return_ratio) as return_rank
   ,rank() over (order by currency_ratio) as currency_rank
   from
   (  select
     cs.cs_item_sk as item
     ,(cast(sum(coalesce(cr.cr_return_quantity,0)) as dec(15,4))/
     cast(sum(coalesce(cs.cs_quantity,0)) as dec(15,4) )) as return_ratio
     ,(cast(sum(coalesce(cr.cr_return_amount,0)) as dec(15,4))/
     cast(sum(coalesce(cs.cs_net_paid,0)) as dec(15,4) )) as currency_ratio
     from
     catalog_sales cs left outer join catalog_returns cr
       on (cs.cs_order_number = cr.cr_order_number and
       cs.cs_item_sk = cr.cr_item_sk)
                ,date_dim
     where
       cr.cr_return_amount > 10000
       and cs.cs_net_profit > 1
                         and cs.cs_net_paid > 0
                         and cs.cs_quantity > 0
                         and cs_sold_date_sk = d_date_sk
                         and d_year = 1998
                         and d_moy = 12
                 group by cs.cs_item_sk
   ) in_cat
 ) catalog
 where
 (
 catalog.return_rank <= 10
 or
 catalog.currency_rank <=10
 )
 union
 select
 'store' as channel
 ,store.item
 ,store.return_ratio
 ,store.return_rank
 ,store.currency_rank
 from (
   select
    item
   ,return_ratio
   ,currency_ratio
   ,rank() over (order by return_ratio) as return_rank
   ,rank() over (order by currency_ratio) as currency_rank
   from
   (  select sts.ss_item_sk as item
     ,(cast(sum(coalesce(sr.sr_return_quantity,0)) as dec(15,4))/cast(sum(coalesce(sts.ss_quantity,0)) as dec(15,4) )) as return_ratio
     ,(cast(sum(coalesce(sr.sr_return_amt,0)) as dec(15,4))/cast(sum(coalesce(sts.ss_net_paid,0)) as dec(15,4) )) as currency_ratio
     from
     store_sales sts left outer join store_returns sr
       on (sts.ss_ticket_number = sr.sr_ticket_number and sts.ss_item_sk = sr.sr_item_sk)
                ,date_dim
     where
       sr.sr_return_amt > 10000
       and sts.ss_net_profit > 1
                         and sts.ss_net_paid > 0
                         and sts.ss_quantity > 0
                         and ss_sold_date_sk = d_date_sk
                         and d_year = 1998
                         and d_moy = 12
     group by sts.ss_item_sk
   ) in_store
 ) store
 where  (
 store.return_rank <= 10
 or
 store.currency_rank <= 10
 )
 order by 1,4,5
 LIMIT 100