0001 SELECT
0002 promotions,
0003 total,
0004 cast(promotions AS DECIMAL(15, 4)) / cast(total AS DECIMAL(15, 4)) * 100
0005 FROM
0006 (SELECT sum(ss_ext_sales_price) promotions
0007 FROM store_sales, store, promotion, date_dim, customer, customer_address, item
0008 WHERE ss_sold_date_sk = d_date_sk
0009 AND ss_store_sk = s_store_sk
0010 AND ss_promo_sk = p_promo_sk
0011 AND ss_customer_sk = c_customer_sk
0012 AND ca_address_sk = c_current_addr_sk
0013 AND ss_item_sk = i_item_sk
0014 AND ca_gmt_offset = -5
0015 AND i_category = 'Jewelry'
0016 AND (p_channel_dmail = 'Y' OR p_channel_email = 'Y' OR p_channel_tv = 'Y')
0017 AND s_gmt_offset = -5
0018 AND d_year = 1998
0019 AND d_moy = 11) promotional_sales,
0020 (SELECT sum(ss_ext_sales_price) total
0021 FROM store_sales, store, date_dim, customer, customer_address, item
0022 WHERE ss_sold_date_sk = d_date_sk
0023 AND ss_store_sk = s_store_sk
0024 AND ss_customer_sk = c_customer_sk
0025 AND ca_address_sk = c_current_addr_sk
0026 AND ss_item_sk = i_item_sk
0027 AND ca_gmt_offset = -5
0028 AND i_category = 'Jewelry'
0029 AND s_gmt_offset = -5
0030 AND d_year = 1998
0031 AND d_moy = 11) all_sales
0032 ORDER BY promotions, total
0033 LIMIT 100
