| ==== |
| ---- QUERY: TPCDS-Q86A |
| -- RESULT MISMATCH FROM ORIGINAL |
| -- FIXED. TAKE ACTUAL RESULT AS EXPECTED |
| with results as |
| ( select sum(ws_net_paid) as total_sum, i_category, i_class, 0 as g_category, 0 as g_class |
| from |
| web_sales |
| ,date_dim d1 |
| ,item |
| where |
| d1.d_month_seq between 1200 and 1200+11 |
| and d1.d_date_sk = ws_sold_date_sk |
| and i_item_sk = ws_item_sk |
| group by i_category,i_class |
| ) , |
| results_rollup as |
| ( select total_sum ,i_category ,i_class, g_category, g_class, 0 as lochierarchy from results |
| union |
| select sum(total_sum) as total_sum, i_category, NULL as i_class, 0 as g_category, 1 as g_class, 1 as lochierarchy from results group by i_category |
| union |
| select sum(total_sum) as total_sum, NULL as i_category, NULL as i_class, 1 as g_category, 1 as g_class, 2 as lochierarchy from results) |
| select |
| total_sum ,i_category ,i_class, lochierarchy |
| ,rank() over ( |
| partition by lochierarchy, |
| case when g_class = 0 then i_category end |
| order by total_sum desc) as rank_within_parent |
| from |
| results_rollup |
| order by |
| lochierarchy desc, |
| case when lochierarchy = 0 then i_category end, |
| rank_within_parent |
| limit 100; |
| ---- RESULTS |
| 325552630.64,'NULL','NULL',2,1 |
| 33602545.41,'Shoes','NULL',1,1 |
| 33399717.80,'Music','NULL',1,2 |
| 33061835.62,'Women','NULL',1,3 |
| 32942681.04,'Books','NULL',1,4 |
| 32233369.67,'Men','NULL',1,5 |
| 32135907.22,'Electronics','NULL',1,6 |
| 32027085.12,'Jewelry','NULL',1,7 |
| 31908139.65,'Sports','NULL',1,8 |
| 31877378.15,'Children','NULL',1,9 |
| 31428816.29,'Home','NULL',1,10 |
| 935154.67,'NULL','NULL',1,11 |
| 2981755.34,'Books','history',0,1 |
| 2533681.90,'Books','romance',0,2 |
| 2513406.90,'Books','computers',0,3 |
| 2359621.74,'Books','fiction',0,4 |
| 2220829.36,'Books','home repair',0,5 |
| 2132619.93,'Books','reference',0,6 |
| 2023862.05,'Books','travel',0,7 |
| 1938302.12,'Books','parenting',0,8 |
| 1916049.65,'Books','science',0,9 |
| 1904474.64,'Books','business',0,10 |
| 1903280.30,'Books','sports',0,11 |
| 1819360.79,'Books','self-help',0,12 |
| 1817324.19,'Books','mystery',0,13 |
| 1698653.10,'Books','entertainments',0,14 |
| 1641874.01,'Books','cooking',0,15 |
| 1453516.79,'Books','arts',0,16 |
| 84068.23,'Books','NULL',0,17 |
| 8603692.12,'Children','infants',0,1 |
| 7982811.62,'Children','toddlers',0,2 |
| 7716135.48,'Children','school-uniforms',0,3 |
| 7529560.01,'Children','newborn',0,4 |
| 45178.92,'Children','NULL',0,5 |
| 2625503.90,'Electronics','dvd/vcr players',0,1 |
| 2351244.66,'Electronics','televisions',0,2 |
| 2283231.32,'Electronics','memory',0,3 |
| 2262599.89,'Electronics','stereo',0,4 |
| 2257811.83,'Electronics','karoke',0,5 |
| 2235218.27,'Electronics','monitors',0,6 |
| 2228844.29,'Electronics','scanners',0,7 |
| 1948029.88,'Electronics','wireless',0,8 |
| 1894729.95,'Electronics','disk drives',0,9 |
| 1838653.07,'Electronics','automotive',0,10 |
| 1793728.96,'Electronics','portable',0,11 |
| 1768939.49,'Electronics','cameras',0,12 |
| 1733994.95,'Electronics','musical',0,13 |
| 1730490.24,'Electronics','personal',0,14 |
| 1670511.23,'Electronics','camcorders',0,15 |
| 1512375.29,'Electronics','audio',0,16 |
| 2408305.34,'Home','paint',0,1 |
| 2349779.93,'Home','curtains/drapes',0,2 |
| 2306676.63,'Home','bedding',0,3 |
| 2284168.74,'Home','flatware',0,4 |
| 2269734.86,'Home','glassware',0,5 |
| 2112112.73,'Home','lighting',0,6 |
| 2085612.39,'Home','bathroom',0,7 |
| 1924834.23,'Home','mattresses',0,8 |
| 1868688.89,'Home','tables',0,9 |
| 1820604.22,'Home','furniture',0,10 |
| 1816997.04,'Home','decor',0,11 |
| 1805905.27,'Home','kids',0,12 |
| 1788142.30,'Home','blinds/shades',0,13 |
| 1570699.11,'Home','accent',0,14 |
| 1503088.13,'Home','rugs',0,15 |
| 1457642.85,'Home','wallpaper',0,16 |
| 55823.63,'Home','NULL',0,17 |
| 2754963.88,'Jewelry','jewelry boxes',0,1 |
| 2346470.04,'Jewelry','pendants',0,2 |
| 2308524.13,'Jewelry','rings',0,3 |
| 2216602.19,'Jewelry','custom',0,4 |
| 2141484.71,'Jewelry','gold',0,5 |
| 2105589.88,'Jewelry','estate',0,6 |
| 2105536.74,'Jewelry','womens watch',0,7 |
| 2057556.93,'Jewelry','mens watch',0,8 |
| 1964085.08,'Jewelry','costume',0,9 |
| 1838748.38,'Jewelry','birdal',0,10 |
| 1820456.21,'Jewelry','earings',0,11 |
| 1811297.48,'Jewelry','loose stones',0,12 |
| 1738088.37,'Jewelry','diamonds',0,13 |
| 1636759.38,'Jewelry','semi-precious',0,14 |
| 1625306.26,'Jewelry','bracelets',0,15 |
| 1457032.40,'Jewelry','consignment',0,16 |
| 98583.06,'Jewelry','NULL',0,17 |
| 8968117.21,'Men','shirts',0,1 |
| 8079270.31,'Men','sports-apparel',0,2 |
| 7734264.66,'Men','accessories',0,3 |
| 7451717.49,'Men','pants',0,4 |
| 9196797.97,'Music','classical',0,1 |
| 8755969.85,'Music','pop',0,2 |
| 8444376.89,'Music','country',0,3 |
| 6891047.77,'Music','rock',0,4 |
| 111525.32,'Music','NULL',0,5 |
| 8938639.40,'Shoes','mens',0,1 |
| 8290473.41,'Shoes','kids',0,2 |
| 8241359.70,'Shoes','womens',0,3 |
| 8132072.90,'Shoes','athletic',0,4 |
| 2530419.12,'Sports','golf',0,1 |
| 2513869.92,'Sports','athletic shoes',0,2 |
| 2340416.63,'Sports','outdoor',0,3 |
| ---- TYPES |
| DECIMAL, STRING, STRING, TINYINT, BIGINT |
| ==== |
