0001
0002 with results as (
0003 select
0004 i_product_name,
0005 i_brand,
0006 i_class,
0007 i_category,
0008 avg(inv_quantity_on_hand) qoh
0009 from
0010 inventory, date_dim, item, warehouse
0011 where
0012 inv_date_sk = d_date_sk
0013 and inv_item_sk = i_item_sk
0014 and inv_warehouse_sk = w_warehouse_sk
0015 and d_month_seq between 1212 and 1212 + 11
0016 group by
0017 i_product_name,
0018 i_brand,
0019 i_class,
0020 i_category),
0021 results_rollup as (
0022 select
0023 i_product_name,
0024 i_brand,
0025 i_class,
0026 i_category,
0027 avg(qoh) qoh
0028 from
0029 results
0030 group by
0031 i_product_name,
0032 i_brand,
0033 i_class,
0034 i_category
0035 union all
0036 select
0037 i_product_name,
0038 i_brand,
0039 i_class,
0040 null i_category,
0041 avg(qoh) qoh
0042 from
0043 results
0044 group by
0045 i_product_name,
0046 i_brand,
0047 i_class
0048 union all
0049 select
0050 i_product_name,
0051 i_brand,
0052 null i_class,
0053 null i_category,
0054 avg(qoh) qoh
0055 from
0056 results
0057 group by
0058 i_product_name,
0059 i_brand
0060 union all
0061 select
0062 i_product_name,
0063 null i_brand,
0064 null i_class,
0065 null i_category,
0066 avg(qoh) qoh
0067 from
0068 results
0069 group by
0070 i_product_name
0071 union all
0072 select
0073 null i_product_name,
0074 null i_brand,
0075 null i_class,
0076 null i_category,
0077 avg(qoh) qoh
0078 from
0079 results)
0080 select
0081 i_product_name,
0082 i_brand,
0083 i_class,
0084 i_category,
0085 qoh
0086 from
0087 results_rollup
0088 order by
0089 qoh,
0090 i_product_name,
0091 i_brand,
0092 i_class,
0093 i_category
0094 limit 100
