blob: 5dabfb6c5128f3d7418f88fbd7182866db3a82d6 [file] [log] [blame]
SELECT
sum(ss_net_profit) / sum(ss_ext_sales_price) AS gross_margin,
i_category,
i_class,
grouping(i_category) + grouping(i_class) AS lochierarchy,
rank()
OVER (
PARTITION BY grouping(i_category) + grouping(i_class),
CASE WHEN grouping(i_class) = 0
THEN i_category END
ORDER BY sum(ss_net_profit) / sum(ss_ext_sales_price) ASC) AS rank_within_parent
FROM
store_sales, date_dim d1, item, store
WHERE
d1.d_year = 2001
AND d1.d_date_sk = ss_sold_date_sk
AND i_item_sk = ss_item_sk
AND s_store_sk = ss_store_sk
AND s_state IN ('TN', 'TN', 'TN', 'TN', 'TN', 'TN', 'TN', 'TN')
GROUP BY ROLLUP (i_category, i_class)
--ORDER BY
-- lochierarchy DESC
-- , CASE WHEN lochierarchy = 0
-- THEN i_category END
-- , rank_within_parent
--LIMIT 100