tpcds.query17.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.
select i_item_id
, i_item_desc
, s_state
, count(ss_quantity) as store_sales_quantitycount
, avg(ss_quantity) as store_sales_quantityave
, stddev_samp(ss_quantity) as store_sales_quantitystdev
, stddev_samp(ss_quantity) / avg(ss_quantity) as store_sales_quantitycov
, count(sr_return_quantity) as store_returns_quantitycount
, avg(sr_return_quantity) as store_returns_quantityave
, stddev_samp(sr_return_quantity) as store_returns_quantitystdev
, stddev_samp(sr_return_quantity) / avg(sr_return_quantity) as store_returns_quantitycov
, count(cs_quantity) as catalog_sales_quantitycount
, avg(cs_quantity) as catalog_sales_quantityave
, stddev_samp(cs_quantity) as catalog_sales_quantitystdev
, stddev_samp(cs_quantity) / avg(cs_quantity) as catalog_sales_quantitycov
from store_sales
, store_returns
, catalog_sales
, date_dim d1
, date_dim d2
, date_dim d3
, store
, item
where d1.d_quarter_name = '2001Q1'
and d1.d_date_sk = ss_sold_date_sk
and i_item_sk = ss_item_sk
and s_store_sk = ss_store_sk
and ss_customer_sk = sr_customer_sk
and ss_item_sk = sr_item_sk
and ss_ticket_number = sr_ticket_number
and sr_returned_date_sk = d2.d_date_sk
and d2.d_quarter_name in ('2001Q1', '2001Q2', '2001Q3')
and sr_customer_sk = cs_bill_customer_sk
and sr_item_sk = cs_item_sk
and cs_sold_date_sk = d3.d_date_sk
and d3.d_quarter_name in ('2001Q1', '2001Q2', '2001Q3')
group by i_item_id
, i_item_desc
, s_state
order by i_item_id
, i_item_desc
, s_state limit 100