github.com/apache/beam/sdks/v2@v2.48.2/java/testing/tpcds/src/main/resources/queries/query14b.sql (about) 1 -- Licensed to the Apache Software Foundation (ASF) under one 2 -- or more contributor license agreements. See the NOTICE file 3 -- distributed with this work for additional information 4 -- regarding copyright ownership. The ASF licenses this file 5 -- to you under the Apache License, Version 2.0 (the 6 -- "License"); you may not use this file except in compliance 7 -- with the License. You may obtain a copy of the License at 8 -- 9 -- http://www.apache.org/licenses/LICENSE-2.0 10 -- 11 -- Unless required by applicable law or agreed to in writing, software 12 -- distributed under the License is distributed on an "AS IS" BASIS, 13 -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 14 -- See the License for the specific language governing permissions and 15 -- limitations under the License. 16 17 with cross_items as 18 (select i_item_sk ss_item_sk 19 from item, 20 (select iss.i_brand_id brand_id 21 ,iss.i_class_id class_id 22 ,iss.i_category_id category_id 23 from store_sales 24 ,item iss 25 ,date_dim d1 26 where ss_item_sk = iss.i_item_sk 27 and ss_sold_date_sk = d1.d_date_sk 28 and d1.d_year between 1999 AND 1999 + 2 29 intersect 30 select ics.i_brand_id 31 ,ics.i_class_id 32 ,ics.i_category_id 33 from catalog_sales 34 ,item ics 35 ,date_dim d2 36 where cs_item_sk = ics.i_item_sk 37 and cs_sold_date_sk = d2.d_date_sk 38 and d2.d_year between 1999 AND 1999 + 2 39 intersect 40 select iws.i_brand_id 41 ,iws.i_class_id 42 ,iws.i_category_id 43 from web_sales 44 ,item iws 45 ,date_dim d3 46 where ws_item_sk = iws.i_item_sk 47 and ws_sold_date_sk = d3.d_date_sk 48 and d3.d_year between 1999 AND 1999 + 2) x 49 where i_brand_id = brand_id 50 and i_class_id = class_id 51 and i_category_id = category_id 52 ), 53 avg_sales as 54 (select avg(quantity*list_price) average_sales 55 from (select ss_quantity quantity 56 ,ss_list_price list_price 57 from store_sales 58 ,date_dim 59 where ss_sold_date_sk = d_date_sk 60 and d_year between 1998 and 1998 + 2 61 union all 62 select cs_quantity quantity 63 ,cs_list_price list_price 64 from catalog_sales 65 ,date_dim 66 where cs_sold_date_sk = d_date_sk 67 and d_year between 1998 and 1998 + 2 68 union all 69 select ws_quantity quantity 70 ,ws_list_price list_price 71 from web_sales 72 ,date_dim 73 where ws_sold_date_sk = d_date_sk 74 and d_year between 1998 and 1998 + 2) x) 75 select * from 76 (select 'store' channel, i_brand_id,i_class_id,i_category_id 77 ,sum(ss_quantity*ss_list_price) sales, count(*) number_sales 78 from store_sales 79 ,item 80 ,date_dim 81 where ss_item_sk in (select ss_item_sk from cross_items) 82 and ss_item_sk = i_item_sk 83 and ss_sold_date_sk = d_date_sk 84 and d_week_seq = (select d_week_seq 85 from date_dim 86 where d_year = 1998 + 1 87 and d_moy = 12 88 and d_dom = 16) 89 group by i_brand_id,i_class_id,i_category_id 90 having sum(ss_quantity*ss_list_price) > (select average_sales from avg_sales)) this_year, 91 (select 'store' channel, i_brand_id,i_class_id 92 ,i_category_id, sum(ss_quantity*ss_list_price) sales, count(*) number_sales 93 from store_sales 94 ,item 95 ,date_dim 96 where ss_item_sk in (select ss_item_sk from cross_items) 97 and ss_item_sk = i_item_sk 98 and ss_sold_date_sk = d_date_sk 99 and d_week_seq = (select d_week_seq 100 from date_dim 101 where d_year = 1998 102 and d_moy = 12 103 and d_dom = 16) 104 group by i_brand_id,i_class_id,i_category_id 105 having sum(ss_quantity*ss_list_price) > (select average_sales from avg_sales)) last_year 106 where this_year.i_brand_id= last_year.i_brand_id 107 and this_year.i_class_id = last_year.i_class_id 108 and this_year.i_category_id = last_year.i_category_id 109 order by this_year.channel, this_year.i_brand_id, this_year.i_class_id, this_year.i_category_id 110 limit 100