github.com/apache/beam/sdks/v2@v2.48.2/java/testing/tpcds/src/main/resources/queries/query14a.sql

-- Licensed to the Apache Software Foundation (ASF) under one
-- or more contributor license agreements.  See the NOTICE file
-- distributed with this work for additional information
-- regarding copyright ownership.  The ASF licenses this file
-- to you under the Apache License, Version 2.0 (the
-- "License"); you may not use this file except in compliance
-- with the License.  You may obtain a copy of the License at
--
--     http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.

with cross_items as
 (select i_item_sk ss_item_sk
 from item,
    (select iss.i_brand_id brand_id, iss.i_class_id class_id, iss.i_category_id category_id
     from store_sales, item iss, date_dim d1
     where ss_item_sk = iss.i_item_sk
                    and ss_sold_date_sk = d1.d_date_sk
       and d1.d_year between 1999 AND 1999 + 2
   intersect
     select ics.i_brand_id, ics.i_class_id, ics.i_category_id
     from catalog_sales, item ics, date_dim d2
     where cs_item_sk = ics.i_item_sk
       and cs_sold_date_sk = d2.d_date_sk
       and d2.d_year between 1999 AND 1999 + 2
   intersect
     select iws.i_brand_id, iws.i_class_id, iws.i_category_id
     from web_sales, item iws, date_dim d3
     where ws_item_sk = iws.i_item_sk
       and ws_sold_date_sk = d3.d_date_sk
       and d3.d_year between 1999 AND 1999 + 2) x
 where i_brand_id = brand_id
   and i_class_id = class_id
   and i_category_id = category_id
),
 avg_sales as
 (select avg(quantity*list_price) average_sales
  from (
     select ss_quantity quantity, ss_list_price list_price
     from store_sales, date_dim
     where ss_sold_date_sk = d_date_sk
       and d_year between 1999 and 2001
   union all
     select cs_quantity quantity, cs_list_price list_price
     from catalog_sales, date_dim
     where cs_sold_date_sk = d_date_sk
       and d_year between 1999 and 1999 + 2
   union all
     select ws_quantity quantity, ws_list_price list_price
     from web_sales, date_dim
     where ws_sold_date_sk = d_date_sk
       and d_year between 1999 and 1999 + 2) x)
 select channel, i_brand_id,i_class_id,i_category_id,sum(sales), sum(number_sales)
 from(
     select 'store' channel, i_brand_id,i_class_id
             ,i_category_id,sum(ss_quantity*ss_list_price) sales
             , count(*) number_sales
     from store_sales, item, date_dim
     where ss_item_sk in (select ss_item_sk from cross_items)
       and ss_item_sk = i_item_sk
       and ss_sold_date_sk = d_date_sk
       and d_year = 1999+2
       and d_moy = 11
     group by i_brand_id,i_class_id,i_category_id
     having sum(ss_quantity*ss_list_price) > (select average_sales from avg_sales)
   union all
     select 'catalog' channel, i_brand_id,i_class_id,i_category_id, sum(cs_quantity*cs_list_price) sales, count(*) number_sales
     from catalog_sales, item, date_dim
     where cs_item_sk in (select ss_item_sk from cross_items)
       and cs_item_sk = i_item_sk
       and cs_sold_date_sk = d_date_sk
       and d_year = 1999+2
       and d_moy = 11
     group by i_brand_id,i_class_id,i_category_id
     having sum(cs_quantity*cs_list_price) > (select average_sales from avg_sales)
   union all
     select 'web' channel, i_brand_id,i_class_id,i_category_id, sum(ws_quantity*ws_list_price) sales , count(*) number_sales
     from web_sales, item, date_dim
     where ws_item_sk in (select ss_item_sk from cross_items)
       and ws_item_sk = i_item_sk
       and ws_sold_date_sk = d_date_sk
       and d_year = 1999+2
       and d_moy = 11
     group by i_brand_id,i_class_id,i_category_id
     having sum(ws_quantity*ws_list_price) > (select average_sales from avg_sales)
 ) y
 group by rollup (channel, i_brand_id,i_class_id,i_category_id)
 order by channel,i_brand_id,i_class_id,i_category_id
 limit 100