| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384 |
- --!syntax_pg
- --TPC-DS Q11
- -- start query 1 in stream 0 using template ../query_templates/query11.tpl
- with year_total as (
- select c_customer_id customer_id
- ,c_first_name customer_first_name
- ,c_last_name customer_last_name
- ,c_preferred_cust_flag customer_preferred_cust_flag
- ,c_birth_country customer_birth_country
- ,c_login customer_login
- ,c_email_address customer_email_address
- ,d_year dyear
- ,sum(ss_ext_list_price-ss_ext_discount_amt) year_total
- ,'s' sale_type
- from plato.customer
- ,plato.store_sales
- ,plato.date_dim
- where c_customer_sk = ss_customer_sk
- and ss_sold_date_sk = d_date_sk
- group by c_customer_id
- ,c_first_name
- ,c_last_name
- ,c_preferred_cust_flag
- ,c_birth_country
- ,c_login
- ,c_email_address
- ,d_year
- union all
- select c_customer_id customer_id
- ,c_first_name customer_first_name
- ,c_last_name customer_last_name
- ,c_preferred_cust_flag customer_preferred_cust_flag
- ,c_birth_country customer_birth_country
- ,c_login customer_login
- ,c_email_address customer_email_address
- ,d_year dyear
- ,sum(ws_ext_list_price-ws_ext_discount_amt) year_total
- ,'w' sale_type
- from plato.customer
- ,plato.web_sales
- ,plato.date_dim
- where c_customer_sk = ws_bill_customer_sk
- and ws_sold_date_sk = d_date_sk
- group by c_customer_id
- ,c_first_name
- ,c_last_name
- ,c_preferred_cust_flag
- ,c_birth_country
- ,c_login
- ,c_email_address
- ,d_year
- )
- select
- t_s_secyear.customer_id
- ,t_s_secyear.customer_first_name
- ,t_s_secyear.customer_last_name
- ,t_s_secyear.customer_email_address
- from year_total t_s_firstyear
- ,year_total t_s_secyear
- ,year_total t_w_firstyear
- ,year_total t_w_secyear
- where t_s_secyear.customer_id = t_s_firstyear.customer_id
- and t_s_firstyear.customer_id = t_w_secyear.customer_id
- and t_s_firstyear.customer_id = t_w_firstyear.customer_id
- and t_s_firstyear.sale_type = 's'
- and t_w_firstyear.sale_type = 'w'
- and t_s_secyear.sale_type = 's'
- and t_w_secyear.sale_type = 'w'
- and t_s_firstyear.dyear = 2001
- and t_s_secyear.dyear = 2001+1
- and t_w_firstyear.dyear = 2001
- and t_w_secyear.dyear = 2001+1
- and t_s_firstyear.year_total > 0::numeric
- and t_w_firstyear.year_total > 0::numeric
- and case when t_w_firstyear.year_total > 0::numeric then t_w_secyear.year_total / t_w_firstyear.year_total else 0.0::numeric end
- > case when t_s_firstyear.year_total > 0::numeric then t_s_secyear.year_total / t_s_firstyear.year_total else 0.0::numeric end
- order by t_s_secyear.customer_id
- ,t_s_secyear.customer_first_name
- ,t_s_secyear.customer_last_name
- ,t_s_secyear.customer_email_address
- limit 100;
- -- end query 1 in stream 0 using template ../query_templates/query11.tpl
|
