tpcds.query54.sql Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of druid Show documentation
Show all versions of druid Show documentation
An JDBC datasource implementation.
with my_customers as (select distinct c_customer_sk
, c_current_addr_sk
from (select cs_sold_date_sk sold_date_sk,
cs_bill_customer_sk customer_sk,
cs_item_sk item_sk
from catalog_sales
union all
select ws_sold_date_sk sold_date_sk,
ws_bill_customer_sk customer_sk,
ws_item_sk item_sk
from web_sales) cs_or_ws_sales,
item,
date_dim,
customer
where sold_date_sk = d_date_sk
and item_sk = i_item_sk
and i_category = 'Women'
and i_class = 'maternity'
and c_customer_sk = cs_or_ws_sales.customer_sk
and d_moy = 12
and d_year = 1998)
, my_revenue as (select c_customer_sk,
sum(ss_ext_sales_price) as revenue
from my_customers,
store_sales,
customer_address,
store,
date_dim
where c_current_addr_sk = ca_address_sk
and ca_county = s_county
and ca_state = s_state
and ss_sold_date_sk = d_date_sk
and c_customer_sk = ss_customer_sk
and d_month_seq between (select distinct d_month_seq + 1
from date_dim
where d_year = 1998
and d_moy = 12)
and (select distinct d_month_seq + 3
from date_dim
where d_year = 1998
and d_moy = 12)
group by c_customer_sk)
, segments as
(select cast((revenue / 50) as int) as segment
from my_revenue)
select segment, count(*) as num_customers, segment * 50 as segment_base
from segments
group by segment
order by segment, num_customers limit 100