github.com/apache/beam/sdks/v2@v2.48.2/java/testing/tpcds/src/main/resources/queries/query63.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  select  *
    18  from (select i_manager_id
    19               ,sum(ss_sales_price) sum_sales
    20               ,avg(sum(ss_sales_price)) over (partition by i_manager_id) avg_monthly_sales
    21        from item
    22            ,store_sales
    23            ,date_dim
    24            ,store
    25        where ss_item_sk = i_item_sk
    26          and ss_sold_date_sk = d_date_sk
    27          and ss_store_sk = s_store_sk
    28          and d_month_seq in (1212,1212+1,1212+2,1212+3,1212+4,1212+5,1212+6,1212+7,1212+8,1212+9,1212+10,1212+11)
    29          and ((    i_category in ('Books','Children','Electronics')
    30                and i_class in ('personal','portable','reference','self-help')
    31                and i_brand in ('scholaramalgamalg #14','scholaramalgamalg #7',
    32  		                  'exportiunivamalg #9','scholaramalgamalg #9'))
    33             or(    i_category in ('Women','Music','Men')
    34                and i_class in ('accessories','classical','fragrances','pants')
    35                and i_brand in ('amalgimporto #1','edu packscholar #1','exportiimporto #1',
    36  		                 'importoamalg #1')))
    37  group by i_manager_id, d_moy) tmp1
    38  where case when avg_monthly_sales > 0 then abs (sum_sales - avg_monthly_sales) / avg_monthly_sales else null end > 0.1
    39  order by i_manager_id
    40          ,avg_monthly_sales
    41          ,sum_sales
    42  limit 100