tpcds.query89.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 *
from (select i_category,
             i_class,
             i_brand,
             s_store_name,
             s_company_name,
             d_moy,
             sum(ss_sales_price)      sum_sales,
             avg(sum(ss_sales_price)) over (partition by i_category, i_brand, s_store_name, s_company_name)
         avg_monthly_sales
      from item,
           store_sales,
           date_dim,
           store
      where ss_item_sk = i_item_sk
        and ss_sold_date_sk = d_date_sk
        and ss_store_sk = s_store_sk
        and d_year in (1999)
        and ((i_category in ('Books', 'Electronics', 'Sports') and
              i_class in ('computers', 'stereo', 'football')
                 )
          or (i_category in ('Men', 'Jewelry', 'Women') and
              i_class in ('shirts', 'birdal', 'dresses')
                 ))
      group by i_category, i_class, i_brand,
               s_store_name, s_company_name, d_moy) tmp1
where case when (avg_monthly_sales <> 0) then (abs(sum_sales - avg_monthly_sales) / avg_monthly_sales) else null end >
      0.1
order by sum_sales - avg_monthly_sales, s_store_name limit 100