0001 SELECT
0002 asceding.rnk,
0003 i1.i_product_name best_performing,
0004 i2.i_product_name worst_performing
0005 FROM (SELECT *
0006 FROM (SELECT
0007 item_sk,
0008 rank()
0009 OVER (
0010 ORDER BY rank_col ASC) rnk
0011 FROM (SELECT
0012 ss_item_sk item_sk,
0013 avg(ss_net_profit) rank_col
0014 FROM store_sales ss1
0015 WHERE ss_store_sk = 4
0016 GROUP BY ss_item_sk
0017 HAVING avg(ss_net_profit) > 0.9 * (SELECT avg(ss_net_profit) rank_col
0018 FROM store_sales
0019 WHERE ss_store_sk = 4
0020 AND ss_addr_sk IS NULL
0021 GROUP BY ss_store_sk)) V1) V11
0022 WHERE rnk < 11) asceding,
0023 (SELECT *
0024 FROM (SELECT
0025 item_sk,
0026 rank()
0027 OVER (
0028 ORDER BY rank_col DESC) rnk
0029 FROM (SELECT
0030 ss_item_sk item_sk,
0031 avg(ss_net_profit) rank_col
0032 FROM store_sales ss1
0033 WHERE ss_store_sk = 4
0034 GROUP BY ss_item_sk
0035 HAVING avg(ss_net_profit) > 0.9 * (SELECT avg(ss_net_profit) rank_col
0036 FROM store_sales
0037 WHERE ss_store_sk = 4
0038 AND ss_addr_sk IS NULL
0039 GROUP BY ss_store_sk)) V2) V21
0040 WHERE rnk < 11) descending,
0041 item i1, item i2
0042 WHERE asceding.rnk = descending.rnk
0043 AND i1.i_item_sk = asceding.item_sk
0044 AND i2.i_item_sk = descending.item_sk
0045 ORDER BY asceding.rnk
0046 LIMIT 100
