• Home
• com.alibaba
• druid
• 1.2.17
• source code
• query51.sql

×

Project download

Many resources are needed to download a project. Please understand that we have to compensate our server costs. Thank you in advance.
Project price only 1 $

You can buy this project and download/modify it how often you want.

tpcds.query51.sql Maven / Gradle / Ivy

WITH web_v1 as (select ws_item_sk item_sk,
                       d_date,
                       sum(sum(ws_sales_price))
                                  over (partition by ws_item_sk order by d_date rows between unbounded preceding and current row) cume_sales
                from web_sales
                   , date_dim
                where ws_sold_date_sk = d_date_sk
                  and d_month_seq between 1200 and 1200 + 11
                  and ws_item_sk is not NULL
                group by ws_item_sk, d_date),
     store_v1 as (select ss_item_sk item_sk,
                         d_date,
                         sum(sum(ss_sales_price))
                                    over (partition by ss_item_sk order by d_date rows between unbounded preceding and current row) cume_sales
                  from store_sales
                     , date_dim
                  where ss_sold_date_sk = d_date_sk
                    and d_month_seq between 1200 and 1200 + 11
                    and ss_item_sk is not NULL
                  group by ss_item_sk, d_date)
select *
from (select item_sk
           , d_date
           , web_sales
           , store_sales
           , max(web_sales)
        over
          (partition by item_sk order by d_date rows between unbounded preceding and current row) web_cumulative
     ,max(store_sales)
            over (partition by item_sk order by d_date rows between unbounded preceding and current row) store_cumulative
      from (select case when web.item_sk is not null then web.item_sk else store.item_sk end item_sk
                 , case when web.d_date is not null then web.d_date else store.d_date end    d_date
                 , web.cume_sales                                                            web_sales
                 , store.cume_sales                                                          store_sales
            from web_v1 web
                     full outer join store_v1 store on (web.item_sk = store.item_sk
                and web.d_date = store.d_date)) x) y
where web_cumulative > store_cumulative
order by item_sk
       , d_date limit 100