tpcds.query67.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

An JDBC datasource implementation.

There is a newer version: 1.2.23

select *
from (select i_category
           , i_class
           , i_brand
           , i_product_name
           , d_year
           , d_qoy
           , d_moy
           , s_store_id
           , sumsales
           , rank() over (partition by i_category order by sumsales desc) rk
      from (select i_category
                 , i_class
                 , i_brand
                 , i_product_name
                 , d_year
                 , d_qoy
                 , d_moy
                 , s_store_id
                 , sum(coalesce(ss_sales_price * ss_quantity, 0)) sumsales
            from store_sales
               , date_dim
               , store
               , item
            where ss_sold_date_sk = d_date_sk
              and ss_item_sk = i_item_sk
              and ss_store_sk = s_store_sk
              and d_month_seq between 1200 and 1200 + 11
            group by rollup (i_category, i_class, i_brand, i_product_name, d_year, d_qoy, d_moy, s_store_id)) dw1) dw2
where rk <= 100
order by i_category
       , i_class
       , i_brand
       , i_product_name
       , d_year
       , d_qoy
       , d_moy
       , s_store_id
       , sumsales
       , rk limit 100