github.com/apache/beam/sdks/v2@v2.48.2/java/testing/tpcds/src/main/resources/queries/query61.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  promotions,total,cast(promotions as decimal(15,4))/cast(total as decimal(15,4))*100
    18  from
    19    (select sum(ss_ext_sales_price) promotions
    20     from  store_sales
    21          ,store
    22          ,promotion
    23          ,date_dim
    24          ,customer
    25          ,customer_address 
    26          ,item
    27     where ss_sold_date_sk = d_date_sk
    28     and   ss_store_sk = s_store_sk
    29     and   ss_promo_sk = p_promo_sk
    30     and   ss_customer_sk= c_customer_sk
    31     and   ca_address_sk = c_current_addr_sk
    32     and   ss_item_sk = i_item_sk 
    33     and   ca_gmt_offset = -7
    34     and   i_category = 'Books'
    35     and   (p_channel_dmail = 'Y' or p_channel_email = 'Y' or p_channel_tv = 'Y')
    36     and   s_gmt_offset = -7
    37     and   d_year = 1999
    38     and   d_moy  = 11) promotional_sales,
    39    (select sum(ss_ext_sales_price) total
    40     from  store_sales
    41          ,store
    42          ,date_dim
    43          ,customer
    44          ,customer_address
    45          ,item
    46     where ss_sold_date_sk = d_date_sk
    47     and   ss_store_sk = s_store_sk
    48     and   ss_customer_sk= c_customer_sk
    49     and   ca_address_sk = c_current_addr_sk
    50     and   ss_item_sk = i_item_sk
    51     and   ca_gmt_offset = -7
    52     and   i_category = 'Books'
    53     and   s_gmt_offset = -7
    54     and   d_year = 1999
    55     and   d_moy  = 11) all_sales
    56  order by promotions, total
    57  limit 100