0001 WITH my_customers AS (
0002 SELECT DISTINCT
0003 c_customer_sk,
0004 c_current_addr_sk
0005 FROM
0006 (SELECT
0007 cs_sold_date_sk sold_date_sk,
0008 cs_bill_customer_sk customer_sk,
0009 cs_item_sk item_sk
0010 FROM catalog_sales
0011 UNION ALL
0012 SELECT
0013 ws_sold_date_sk sold_date_sk,
0014 ws_bill_customer_sk customer_sk,
0015 ws_item_sk item_sk
0016 FROM web_sales
0017 ) cs_or_ws_sales,
0018 item,
0019 date_dim,
0020 customer
0021 WHERE sold_date_sk = d_date_sk
0022 AND item_sk = i_item_sk
0023 AND i_category = 'Women'
0024 AND i_class = 'maternity'
0025 AND c_customer_sk = cs_or_ws_sales.customer_sk
0026 AND d_moy = 12
0027 AND d_year = 1998
0028 )
0029 , my_revenue AS (
0030 SELECT
0031 c_customer_sk,
0032 sum(ss_ext_sales_price) AS revenue
0033 FROM my_customers,
0034 store_sales,
0035 customer_address,
0036 store,
0037 date_dim
0038 WHERE c_current_addr_sk = ca_address_sk
0039 AND ca_county = s_county
0040 AND ca_state = s_state
0041 AND ss_sold_date_sk = d_date_sk
0042 AND c_customer_sk = ss_customer_sk
0043 AND d_month_seq BETWEEN (SELECT DISTINCT d_month_seq + 1
0044 FROM date_dim
0045 WHERE d_year = 1998 AND d_moy = 12)
0046 AND (SELECT DISTINCT d_month_seq + 3
0047 FROM date_dim
0048 WHERE d_year = 1998 AND d_moy = 12)
0049 GROUP BY c_customer_sk
0050 )
0051 , segments AS
0052 (SELECT cast((revenue / 50) AS INT) AS segment
0053 FROM my_revenue)
0054 SELECT
0055 segment,
0056 count(*) AS num_customers,
0057 segment * 50 AS segment_base
0058 FROM segments
0059 GROUP BY segment
0060 ORDER BY segment, num_customers
0061 LIMIT 100
