github.com/apache/beam/sdks/v2@v2.48.2/java/testing/tpcds/src/main/resources/queries/query54.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 my_customers as (
    18   select distinct c_customer_sk
    19          , c_current_addr_sk
    20   from   
    21          ( select cs_sold_date_sk sold_date_sk,
    22                   cs_bill_customer_sk customer_sk,
    23                   cs_item_sk item_sk
    24            from   catalog_sales
    25            union all
    26            select ws_sold_date_sk sold_date_sk,
    27                   ws_bill_customer_sk customer_sk,
    28                   ws_item_sk item_sk
    29            from   web_sales
    30           ) cs_or_ws_sales,
    31           item,
    32           date_dim,
    33           customer
    34   where   sold_date_sk = d_date_sk
    35           and item_sk = i_item_sk
    36           and i_category = 'Jewelry'
    37           and i_class = 'consignment'
    38           and c_customer_sk = cs_or_ws_sales.customer_sk
    39           and d_moy = 3
    40           and d_year = 1999
    41   )
    42   , my_revenue as (
    43   select c_customer_sk,
    44          sum(ss_ext_sales_price) as revenue
    45   from   my_customers,
    46          store_sales,
    47          customer_address,
    48          store,
    49          date_dim
    50   where  c_current_addr_sk = ca_address_sk
    51          and ca_county = s_county
    52          and ca_state = s_state
    53          and ss_sold_date_sk = d_date_sk
    54          and c_customer_sk = ss_customer_sk
    55          and d_month_seq between (select distinct d_month_seq+1
    56                                   from   date_dim where d_year = 1999 and d_moy = 3)
    57                             and  (select distinct d_month_seq+3
    58                                   from   date_dim where d_year = 1999 and d_moy = 3)
    59   group by c_customer_sk
    60   )
    61   , segments as
    62   (select cast((revenue/50) as int) as segment
    63    from   my_revenue
    64   )
    65    select  segment, count(*) as num_customers, segment*50 as segment_base
    66   from segments
    67   group by segment
    68   order by segment, num_customers
    69   limit 100