| ==== |
| ---- 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 |
| ==== |