| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107 |
- -- start query 1 in stream 0 using template query24.tpl and seed 1220860970
- with ssales as
- (select c_last_name
- ,c_first_name
- ,s_store_name
- ,ca_state
- ,s_state
- ,i_color
- ,i_current_price
- ,i_manager_id
- ,i_units
- ,i_size
- ,sum(ss_sales_price) netpaid
- from store_sales
- ,store_returns
- ,store
- ,item
- ,customer
- ,customer_address
- where ss_ticket_number = sr_ticket_number
- and ss_item_sk = sr_item_sk
- and ss_customer_sk = c_customer_sk
- and ss_item_sk = i_item_sk
- and ss_store_sk = s_store_sk
- and c_current_addr_sk = ca_address_sk
- and c_birth_country <> upper(ca_country)
- and s_zip = ca_zip
- and s_market_id=10
- group by c_last_name
- ,c_first_name
- ,s_store_name
- ,ca_state
- ,s_state
- ,i_color
- ,i_current_price
- ,i_manager_id
- ,i_units
- ,i_size)
- select c_last_name
- ,c_first_name
- ,s_store_name
- ,sum(netpaid) paid
- from ssales
- where i_color = 'snow'
- group by c_last_name
- ,c_first_name
- ,s_store_name
- having sum(netpaid) > (select 0.05*avg(netpaid)
- from ssales)
- order by c_last_name
- ,c_first_name
- ,s_store_name
- ;
- with ssales as
- (select c_last_name
- ,c_first_name
- ,s_store_name
- ,ca_state
- ,s_state
- ,i_color
- ,i_current_price
- ,i_manager_id
- ,i_units
- ,i_size
- ,sum(ss_sales_price) netpaid
- from store_sales
- ,store_returns
- ,store
- ,item
- ,customer
- ,customer_address
- where ss_ticket_number = sr_ticket_number
- and ss_item_sk = sr_item_sk
- and ss_customer_sk = c_customer_sk
- and ss_item_sk = i_item_sk
- and ss_store_sk = s_store_sk
- and c_current_addr_sk = ca_address_sk
- and c_birth_country <> upper(ca_country)
- and s_zip = ca_zip
- and s_market_id = 10
- group by c_last_name
- ,c_first_name
- ,s_store_name
- ,ca_state
- ,s_state
- ,i_color
- ,i_current_price
- ,i_manager_id
- ,i_units
- ,i_size)
- select c_last_name
- ,c_first_name
- ,s_store_name
- ,sum(netpaid) paid
- from ssales
- where i_color = 'chiffon'
- group by c_last_name
- ,c_first_name
- ,s_store_name
- having sum(netpaid) > (select 0.05*avg(netpaid)
- from ssales)
- order by c_last_name
- ,c_first_name
- ,s_store_name
- ;
- -- end query 1 in stream 0 using template query24.tpl
|
