tpcds.query29.sql Maven / Gradle / Ivy

Show more of this group Show more artifacts with this name
Show all versions of druid Show documentation

An JDBC datasource implementation.

There is a newer version: 1.2.23

select i_item_id
     , i_item_desc
     , s_store_id
     , s_store_name
     , sum(ss_quantity)        as store_sales_quantity
     , sum(sr_return_quantity) as store_returns_quantity
     , sum(cs_quantity)        as catalog_sales_quantity
from store_sales
   , store_returns
   , catalog_sales
   , date_dim d1
   , date_dim d2
   , date_dim d3
   , store
   , item
where d1.d_moy = 9
  and d1.d_year = 1999
  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_moy between 9 and 9 + 3
  and d2.d_year = 1999
  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_year in (1999, 1999 + 1, 1999 + 2)
group by i_item_id
       , i_item_desc
       , s_store_id
       , s_store_name
order by i_item_id
       , i_item_desc
       , s_store_id
       , s_store_name limit 100