====
---- QUERY: TPCDS-Q49
SELECT channel,
       item,
       return_ratio,
       return_rank,
       currency_rank
FROM
  (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 decimal(15,4))/ cast(sum(coalesce(ws.ws_quantity,0)) AS decimal(15,4))) AS return_ratio,
                (cast(sum(coalesce(wr.wr_return_amt,0)) AS decimal(15,4))/ cast(sum(coalesce(ws.ws_net_paid,0)) AS decimal(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 = 2001
           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 decimal(15,4))/ cast(sum(coalesce(cs.cs_quantity,0)) AS decimal(15,4))) AS return_ratio,
                (cast(sum(coalesce(cr.cr_return_amount,0)) AS decimal(15,4))/ cast(sum(coalesce(cs.cs_net_paid,0)) AS decimal(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 = 2001
           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 decimal(15,4))/cast(sum(coalesce(sts.ss_quantity,0)) AS decimal(15,4))) AS return_ratio,
                (cast(sum(coalesce(sr.sr_return_amt,0)) AS decimal(15,4))/cast(sum(coalesce(sts.ss_net_paid,0)) AS decimal(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 = 2001
           AND d_moy = 12
         GROUP BY sts.ss_item_sk) in_store) store
   WHERE (store.return_rank <= 10
          OR store.currency_rank <= 10) ) sq1
ORDER BY 1,
         4,
         5,
         2
LIMIT 100
---- RESULTS
'catalog',17543,0.57142857142857142857,1,1
'catalog',14513,0.63541666666666666667,2,2
'catalog',12577,0.65591397849462365591,3,3
'catalog',3411,0.71641791044776119403,4,4
'catalog',361,0.74647887323943661972,5,5
'catalog',8189,0.74698795180722891566,6,6
'catalog',8929,0.76250000000000000000,7,7
'catalog',14869,0.77173913043478260870,8,8
'catalog',9295,0.77894736842105263158,9,9
'catalog',16215,0.79069767441860465116,10,10
'store',9471,0.77500000000000000000,1,1
'store',9797,0.80000000000000000000,2,2
'store',12641,0.81609195402298850575,3,3
'store',15839,0.81632653061224489796,4,4
'store',1171,0.82417582417582417582,5,5
'store',11589,0.82653061224489795918,6,6
'store',6661,0.92207792207792207792,7,7
'store',13013,0.94202898550724637681,8,8
'store',14925,0.96470588235294117647,9,9
'store',4063,1.00000000000000000000,10,10
'store',9029,1.00000000000000000000,10,10
'web',7539,0.59000000000000000000,1,1
'web',3337,0.62650602409638554217,2,2
'web',15597,0.66197183098591549296,3,3
'web',2915,0.69863013698630136986,4,4
'web',11933,0.71717171717171717172,5,5
'web',3305,0.73750000000000000000,6,16
'web',483,0.80000000000000000000,7,6
'web',85,0.85714285714285714286,8,7
'web',97,0.90361445783132530120,9,8
'web',117,0.92500000000000000000,10,9
'web',5299,0.92708333333333333333,11,10
---- TYPES
STRING, BIGINT, DECIMAL, BIGINT, BIGINT
====