github.com/apache/beam/sdks/v2@v2.48.2/java/testing/tpcds/src/main/resources/queries/query67.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_category
    19              ,i_class
    20              ,i_brand
    21              ,i_product_name
    22              ,d_year
    23              ,d_qoy
    24              ,d_moy
    25              ,s_store_id
    26              ,sumsales
    27              ,rank() over (partition by i_category order by sumsales desc) rk
    28        from (select i_category
    29                    ,i_class
    30                    ,i_brand
    31                    ,i_product_name
    32                    ,d_year
    33                    ,d_qoy
    34                    ,d_moy
    35                    ,s_store_id
    36                    ,sum(coalesce(ss_sales_price*ss_quantity,0)) sumsales
    37              from store_sales
    38                  ,date_dim
    39                  ,store
    40                  ,item
    41         where  ss_sold_date_sk=d_date_sk
    42            and ss_item_sk=i_item_sk
    43            and ss_store_sk = s_store_sk
    44            and d_month_seq between 1212 and 1212+11
    45         group by  rollup(i_category, i_class, i_brand, i_product_name, d_year, d_qoy, d_moy,s_store_id))dw1) dw2
    46  where rk <= 100
    47  order by i_category
    48          ,i_class
    49          ,i_brand
    50          ,i_product_name
    51          ,d_year
    52          ,d_qoy
    53          ,d_moy
    54          ,s_store_id
    55          ,sumsales
    56          ,rk
    57  limit 100