| ==== |
| ---- QUERY: TPCDS-Q75 |
| WITH all_sales AS ( |
| SELECT d_year |
| ,i_brand_id |
| ,i_class_id |
| ,i_category_id |
| ,i_manufact_id |
| ,SUM(sales_cnt) AS sales_cnt |
| ,SUM(sales_amt) AS sales_amt |
| FROM (SELECT d_year |
| ,i_brand_id |
| ,i_class_id |
| ,i_category_id |
| ,i_manufact_id |
| ,cs_quantity - COALESCE(cr_return_quantity,0) AS sales_cnt |
| ,cs_ext_sales_price - COALESCE(cr_return_amount,0.0) AS sales_amt |
| FROM catalog_sales JOIN item ON i_item_sk=cs_item_sk |
| JOIN date_dim ON d_date_sk=cs_sold_date_sk |
| LEFT JOIN catalog_returns ON (cs_order_number=cr_order_number |
| AND cs_item_sk=cr_item_sk) |
| WHERE i_category='Books' |
| UNION |
| SELECT d_year |
| ,i_brand_id |
| ,i_class_id |
| ,i_category_id |
| ,i_manufact_id |
| ,ss_quantity - COALESCE(sr_return_quantity,0) AS sales_cnt |
| ,ss_ext_sales_price - COALESCE(sr_return_amt,0.0) AS sales_amt |
| FROM store_sales JOIN item ON i_item_sk=ss_item_sk |
| JOIN date_dim ON d_date_sk=ss_sold_date_sk |
| LEFT JOIN store_returns ON (ss_ticket_number=sr_ticket_number |
| AND ss_item_sk=sr_item_sk) |
| WHERE i_category='Books' |
| UNION |
| SELECT d_year |
| ,i_brand_id |
| ,i_class_id |
| ,i_category_id |
| ,i_manufact_id |
| ,ws_quantity - COALESCE(wr_return_quantity,0) AS sales_cnt |
| ,ws_ext_sales_price - COALESCE(wr_return_amt,0.0) AS sales_amt |
| FROM web_sales JOIN item ON i_item_sk=ws_item_sk |
| JOIN date_dim ON d_date_sk=ws_sold_date_sk |
| LEFT JOIN web_returns ON (ws_order_number=wr_order_number |
| AND ws_item_sk=wr_item_sk) |
| WHERE i_category='Books') sales_detail |
| GROUP BY d_year, i_brand_id, i_class_id, i_category_id, i_manufact_id) |
| SELECT prev_yr.d_year AS prev_year |
| ,curr_yr.d_year AS year |
| ,curr_yr.i_brand_id |
| ,curr_yr.i_class_id |
| ,curr_yr.i_category_id |
| ,curr_yr.i_manufact_id |
| ,prev_yr.sales_cnt AS prev_yr_cnt |
| ,curr_yr.sales_cnt AS curr_yr_cnt |
| ,curr_yr.sales_cnt-prev_yr.sales_cnt AS sales_cnt_diff |
| ,curr_yr.sales_amt-prev_yr.sales_amt AS sales_amt_diff |
| FROM all_sales curr_yr, all_sales prev_yr |
| WHERE curr_yr.i_brand_id=prev_yr.i_brand_id |
| AND curr_yr.i_class_id=prev_yr.i_class_id |
| AND curr_yr.i_category_id=prev_yr.i_category_id |
| AND curr_yr.i_manufact_id=prev_yr.i_manufact_id |
| AND curr_yr.d_year=2002 |
| AND prev_yr.d_year=2002-1 |
| AND CAST(curr_yr.sales_cnt AS DECIMAL(17,2))/CAST(prev_yr.sales_cnt AS DECIMAL(17,2))<0.9 |
| ORDER BY sales_cnt_diff, sales_amt_diff |
| limit 100; |
| ---- RESULTS |
| 2001,2002,9011004,11,9,730,6136,3927,-2209,-64592.86 |
| 2001,2002,9004008,4,9,454,6130,4012,-2118,-137401.99 |
| 2001,2002,9006010,6,9,373,6584,4510,-2074,-56824.27 |
| 2001,2002,9003002,3,9,331,6314,4249,-2065,-162310.87 |
| 2001,2002,9016002,16,9,761,6098,4109,-1989,-75674.23 |
| 2001,2002,2001001,7,9,85,5689,3750,-1939,-79485.07 |
| 2001,2002,9011010,11,9,254,6635,4698,-1937,-108841.59 |
| 2001,2002,9003002,3,9,1000,6152,4220,-1932,-51341.52 |
| 2001,2002,9009010,1,9,311,6980,5065,-1915,-166624.10 |
| 2001,2002,9003004,3,9,25,6382,4612,-1770,-109080.80 |
| 2001,2002,9008008,8,9,322,5391,3664,-1727,-58283.91 |
| 2001,2002,3004001,1,9,89,6307,4616,-1691,-105539.10 |
| 2001,2002,9014004,12,9,423,6427,4742,-1685,-66528.45 |
| 2001,2002,1001001,5,9,172,5642,3998,-1644,-65808.52 |
| 2001,2002,8007009,9,9,322,5493,3906,-1587,-31458.78 |
| 2001,2002,9015002,15,9,303,5722,4211,-1511,-81847.95 |
| 2001,2002,8015003,4,9,289,6413,4905,-1508,-140884.23 |
| 2001,2002,9002008,2,9,202,5758,4287,-1471,-27415.18 |
| 2001,2002,9004002,4,9,55,5596,4196,-1400,-277.92 |
| 2001,2002,9010002,10,9,432,5153,3767,-1386,-56643.31 |
| 2001,2002,9015003,15,9,278,5120,3745,-1375,-61503.40 |
| 2001,2002,5001001,10,9,136,5271,3918,-1353,-52691.74 |
| 2001,2002,9007002,7,9,207,6416,5088,-1328,-28312.29 |
| 2001,2002,9016004,8,9,114,5560,4263,-1297,-65169.90 |
| 2001,2002,9007004,7,9,968,5625,4330,-1295,-86120.30 |
| 2001,2002,9008008,8,9,535,6155,4861,-1294,-66647.47 |
| 2001,2002,3003001,1,9,175,6170,4892,-1278,-72260.64 |
| 2001,2002,9010004,3,9,948,5640,4387,-1253,-34985.51 |
| 2001,2002,9012008,12,9,249,5254,4002,-1252,-15484.88 |
| 2001,2002,9015008,15,9,281,5558,4315,-1243,10022.91 |
| 2001,2002,9006004,6,9,620,4822,3594,-1228,-19309.88 |
| 2001,2002,9010008,10,9,171,6500,5279,-1221,-73654.23 |
| 2001,2002,9004008,4,9,256,5366,4164,-1202,-93628.46 |
| 2001,2002,9008008,8,9,82,6042,4852,-1190,-78558.05 |
| 2001,2002,9014010,2,9,727,6579,5396,-1183,-45368.94 |
| 2001,2002,10002014,1,9,12,5662,4515,-1147,-47170.70 |
| 2001,2002,9005002,5,9,105,5650,4508,-1142,-75927.61 |
| 2001,2002,1001001,1,9,183,5538,4417,-1121,-34086.49 |
| 2001,2002,8015005,11,9,898,5677,4557,-1120,-49916.13 |
| 2001,2002,9016010,16,9,662,5306,4197,-1109,-29664.55 |
| 2001,2002,2001001,1,9,151,6309,5200,-1109,-13881.98 |
| 2001,2002,7007001,12,9,221,6250,5147,-1103,8445.22 |
| 2001,2002,9010008,10,9,106,5739,4637,-1102,-39967.26 |
| 2001,2002,5001001,6,9,201,5714,4615,-1099,-2073.61 |
| 2001,2002,9002004,2,9,578,5976,4881,-1095,-26222.70 |
| 2001,2002,9013010,12,9,28,5672,4579,-1093,-61365.10 |
| 2001,2002,9003002,3,9,238,5784,4694,-1090,-52094.93 |
| 2001,2002,9012010,12,9,4,6214,5128,-1086,-41244.90 |
| 2001,2002,4003001,6,9,356,5890,4815,-1075,-19594.68 |
| 2001,2002,9009002,9,9,633,5839,4786,-1053,-40163.90 |
| 2001,2002,9006010,6,9,100,5322,4271,-1051,-65656.49 |
| 2001,2002,9003002,3,9,130,5708,4658,-1050,-121435.05 |
| 2001,2002,9011004,3,9,115,5101,4064,-1037,-38404.07 |
| 2001,2002,9006008,6,9,156,5824,4793,-1031,-16295.15 |
| 2001,2002,4004001,10,9,105,5560,4533,-1027,-29705.36 |
| 2001,2002,9009008,9,9,3,5731,4718,-1013,-62660.48 |
| 2001,2002,10005001,5,9,65,5890,4879,-1011,-57577.01 |
| 2001,2002,9007008,7,9,122,6085,5077,-1008,-108804.86 |
| 2001,2002,9001008,1,9,258,5714,4706,-1008,-73311.07 |
| 2001,2002,9003008,3,9,382,5145,4143,-1002,-23005.48 |
| 2001,2002,2001001,1,9,177,5711,4718,-993,-45422.40 |
| 2001,2002,8002001,13,9,268,5521,4545,-976,-50681.49 |
| 2001,2002,9010008,10,9,9,5549,4578,-971,45104.79 |
| 2001,2002,5002001,2,9,230,5856,4887,-969,-25537.87 |
| 2001,2002,10015001,1,9,399,6188,5225,-963,-76365.39 |
| 2001,2002,9010004,10,9,110,5035,4082,-953,-81282.12 |
| 2001,2002,9013010,3,9,235,5771,4821,-950,-81516.67 |
| 2001,2002,9003004,3,9,311,5414,4467,-947,-19498.98 |
| 2001,2002,9007008,7,9,766,5412,4470,-942,-43150.76 |
| 2001,2002,3003001,9,9,210,5703,4761,-942,-22794.72 |
| 2001,2002,9010008,10,9,621,5897,4955,-942,-1466.75 |
| 2001,2002,4001001,15,9,42,5385,4444,-941,-77081.27 |
| 2001,2002,9012010,12,9,268,5363,4425,-938,-13775.61 |
| 2001,2002,4002001,2,9,891,6168,5233,-935,-54473.40 |
| 2001,2002,10011013,11,9,123,6141,5207,-934,-77290.63 |
| 2001,2002,9005002,5,9,379,5673,4742,-931,-66550.24 |
| 2001,2002,9005002,5,9,35,6085,5166,-919,-23898.75 |
| 2001,2002,9014008,14,9,904,5637,4725,-912,-54696.64 |
| 2001,2002,9010002,10,9,351,5553,4668,-885,-58399.99 |
| 2001,2002,9002008,2,9,603,5399,4518,-881,-8944.74 |
| 2001,2002,9005002,5,9,226,6438,5559,-879,-78309.56 |
| 2001,2002,9016008,16,9,134,5482,4617,-865,-93343.25 |
| 2001,2002,7004007,2,9,248,5385,4527,-858,-59997.87 |
| 2001,2002,9002002,2,9,556,5690,4832,-858,2965.47 |
| 2001,2002,6012003,9,9,48,5468,4620,-848,17912.88 |
| 2001,2002,9013010,13,9,288,5473,4631,-842,22917.88 |
| 2001,2002,9009008,9,9,958,5837,5002,-835,-63092.62 |
| 2001,2002,9015008,15,9,384,5348,4519,-829,-29937.40 |
| 2001,2002,1003001,3,9,242,5504,4697,-807,-10853.56 |
| 2001,2002,9011002,11,9,372,5085,4279,-806,-78551.75 |
| 2001,2002,9015002,15,9,224,5915,5112,-803,-46136.85 |
| 2001,2002,9015002,15,9,531,5831,5029,-802,-10846.84 |
| 2001,2002,2004001,16,9,443,5269,4475,-794,-28869.68 |
| 2001,2002,9003010,3,9,269,6297,5505,-792,-53827.19 |
| 2001,2002,9007002,7,9,306,5398,4608,-790,-67953.64 |
| 2001,2002,9012004,12,9,808,5796,5006,-790,-43025.36 |
| 2001,2002,6015001,15,9,543,5041,4252,-789,3631.40 |
| 2001,2002,9004012,4,9,621,5974,5186,-788,52156.56 |
| 2001,2002,9010008,10,9,284,5382,4597,-785,22162.92 |
| 2001,2002,9011002,11,9,42,5750,4968,-782,-87125.33 |
| ---- TYPES |
| INT, INT, INT, INT, INT, INT, BIGINT, BIGINT, BIGINT, DECIMAL |
| ==== |
