0001 SELECT
0002 s_store_name,
0003 i_item_desc,
0004 sc.revenue,
0005 i_current_price,
0006 i_wholesale_cost,
0007 i_brand
0008 FROM store, item,
0009 (SELECT
0010 ss_store_sk,
0011 avg(revenue) AS ave
0012 FROM
0013 (SELECT
0014 ss_store_sk,
0015 ss_item_sk,
0016 sum(ss_sales_price) AS revenue
0017 FROM store_sales, date_dim
0018 WHERE ss_sold_date_sk = d_date_sk AND d_month_seq BETWEEN 1176 AND 1176 + 11
0019 GROUP BY ss_store_sk, ss_item_sk) sa
0020 GROUP BY ss_store_sk) sb,
0021 (SELECT
0022 ss_store_sk,
0023 ss_item_sk,
0024 sum(ss_sales_price) AS revenue
0025 FROM store_sales, date_dim
0026 WHERE ss_sold_date_sk = d_date_sk AND d_month_seq BETWEEN 1176 AND 1176 + 11
0027 GROUP BY ss_store_sk, ss_item_sk) sc
0028 WHERE sb.ss_store_sk = sc.ss_store_sk AND
0029 sc.revenue <= 0.1 * sb.ave AND
0030 s_store_sk = sc.ss_store_sk AND
0031 i_item_sk = sc.ss_item_sk
0032 ORDER BY s_store_name, i_item_desc
0033 LIMIT 100
