1 -- start query 1 in stream 0 using template query30.tpl and seed 1819994127
2 with customer_total_return
as
3 (select wr_returning_customer_sk
as ctr_customer_sk
4 ,ca_state
as ctr_state
,
5 sum(wr_return_amt
) as ctr_total_return
9 where wr_returned_date_sk
= d_date_sk
11 and wr_returning_addr_sk
= ca_address_sk
12 group by wr_returning_customer_sk
14 select c_customer_id
,c_salutation
,c_first_name
,c_last_name
,c_preferred_cust_flag
15 ,c_birth_day
,c_birth_month
,c_birth_year
,c_birth_country
,c_login
,c_email_address
16 ,c_last_review_date_sk
,ctr_total_return
17 from customer_total_return ctr1
20 where ctr1.ctr_total_return
> (select avg(ctr_total_return
)*1.2
21 from customer_total_return ctr2
22 where ctr1.ctr_state
= ctr2.ctr_state
)
23 and ca_address_sk
= c_current_addr_sk
25 and ctr1.ctr_customer_sk
= c_customer_sk
26 order by c_customer_id
,c_salutation
,c_first_name
,c_last_name
,c_preferred_cust_flag
27 ,c_birth_day
,c_birth_month
,c_birth_year
,c_birth_country
,c_login
,c_email_address
28 ,c_last_review_date_sk
,ctr_total_return
31 -- end query 1 in stream 0 using template query30.tpl