ceph/src/s3select/TPCDS/sample-queries-tpcds/query4.sql

   1 -- start query 1 in stream 0 using template query4.tpl and seed 1819994127
   2 with year_total as (
   3  select c_customer_id customer_id
   4        ,c_first_name customer_first_name
   5        ,c_last_name customer_last_name
   6        ,c_preferred_cust_flag customer_preferred_cust_flag
   7        ,c_birth_country customer_birth_country
   8        ,c_login customer_login
   9        ,c_email_address customer_email_address
  10        ,d_year dyear
  11        ,sum(((ss_ext_list_price-ss_ext_wholesale_cost-ss_ext_discount_amt)+ss_ext_sales_price)/2) year_total
  12        ,'s' sale_type
  13  from customer
  14      ,store_sales
  15      ,date_dim
  16  where c_customer_sk = ss_customer_sk
  17    and ss_sold_date_sk = d_date_sk
  18  group by c_customer_id
  19          ,c_first_name
  20          ,c_last_name
  21          ,c_preferred_cust_flag
  22          ,c_birth_country
  23          ,c_login
  24          ,c_email_address
  25          ,d_year
  26  union all
  27  select c_customer_id customer_id
  28        ,c_first_name customer_first_name
  29        ,c_last_name customer_last_name
  30        ,c_preferred_cust_flag customer_preferred_cust_flag
  31        ,c_birth_country customer_birth_country
  32        ,c_login customer_login
  33        ,c_email_address customer_email_address
  34        ,d_year dyear
  35        ,sum((((cs_ext_list_price-cs_ext_wholesale_cost-cs_ext_discount_amt)+cs_ext_sales_price)/2) ) year_total
  36        ,'c' sale_type
  37  from customer
  38      ,catalog_sales
  39      ,date_dim
  40  where c_customer_sk = cs_bill_customer_sk
  41    and cs_sold_date_sk = d_date_sk
  42  group by c_customer_id
  43          ,c_first_name
  44          ,c_last_name
  45          ,c_preferred_cust_flag
  46          ,c_birth_country
  47          ,c_login
  48          ,c_email_address
  49          ,d_year
  50 union all
  51  select c_customer_id customer_id
  52        ,c_first_name customer_first_name
  53        ,c_last_name customer_last_name
  54        ,c_preferred_cust_flag customer_preferred_cust_flag
  55        ,c_birth_country customer_birth_country
  56        ,c_login customer_login
  57        ,c_email_address customer_email_address
  58        ,d_year dyear
  59        ,sum((((ws_ext_list_price-ws_ext_wholesale_cost-ws_ext_discount_amt)+ws_ext_sales_price)/2) ) year_total
  60        ,'w' sale_type
  61  from customer
  62      ,web_sales
  63      ,date_dim
  64  where c_customer_sk = ws_bill_customer_sk
  65    and ws_sold_date_sk = d_date_sk
  66  group by c_customer_id
  67          ,c_first_name
  68          ,c_last_name
  69          ,c_preferred_cust_flag
  70          ,c_birth_country
  71          ,c_login
  72          ,c_email_address
  73          ,d_year
  74          )
  75   select
  76                   t_s_secyear.customer_id
  77                  ,t_s_secyear.customer_first_name
  78                  ,t_s_secyear.customer_last_name
  79                  ,t_s_secyear.customer_birth_country
  80  from year_total t_s_firstyear
  81      ,year_total t_s_secyear
  82      ,year_total t_c_firstyear
  83      ,year_total t_c_secyear
  84      ,year_total t_w_firstyear
  85      ,year_total t_w_secyear
  86  where t_s_secyear.customer_id = t_s_firstyear.customer_id
  87    and t_s_firstyear.customer_id = t_c_secyear.customer_id
  88    and t_s_firstyear.customer_id = t_c_firstyear.customer_id
  89    and t_s_firstyear.customer_id = t_w_firstyear.customer_id
  90    and t_s_firstyear.customer_id = t_w_secyear.customer_id
  91    and t_s_firstyear.sale_type = 's'
  92    and t_c_firstyear.sale_type = 'c'
  93    and t_w_firstyear.sale_type = 'w'
  94    and t_s_secyear.sale_type = 's'
  95    and t_c_secyear.sale_type = 'c'
  96    and t_w_secyear.sale_type = 'w'
  97    and t_s_firstyear.dyear =  1999
  98    and t_s_secyear.dyear = 1999+1
  99    and t_c_firstyear.dyear =  1999
 100    and t_c_secyear.dyear =  1999+1
 101    and t_w_firstyear.dyear = 1999
 102    and t_w_secyear.dyear = 1999+1
 103    and t_s_firstyear.year_total > 0
 104    and t_c_firstyear.year_total > 0
 105    and t_w_firstyear.year_total > 0
 106    and case when t_c_firstyear.year_total > 0 then t_c_secyear.year_total / t_c_firstyear.year_total else null end
 107            > case when t_s_firstyear.year_total > 0 then t_s_secyear.year_total / t_s_firstyear.year_total else null end
 108    and case when t_c_firstyear.year_total > 0 then t_c_secyear.year_total / t_c_firstyear.year_total else null end
 109            > case when t_w_firstyear.year_total > 0 then t_w_secyear.year_total / t_w_firstyear.year_total else null end
 110  order by t_s_secyear.customer_id
 111          ,t_s_secyear.customer_first_name
 112          ,t_s_secyear.customer_last_name
 113          ,t_s_secyear.customer_birth_country
 114 limit 100;
 115
 116 -- end query 1 in stream 0 using template query4.tpl