github.com/apache/beam/sdks/v2@v2.48.2/java/testing/tpcds/src/main/resources/queries/query64.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 cs_ui as
    18   (select cs_item_sk
    19          ,sum(cs_ext_list_price) as sale,sum(cr_refunded_cash+cr_reversed_charge+cr_store_credit) as refund
    20    from catalog_sales
    21        ,catalog_returns
    22    where cs_item_sk = cr_item_sk
    23      and cs_order_number = cr_order_number
    24    group by cs_item_sk
    25    having sum(cs_ext_list_price)>2*sum(cr_refunded_cash+cr_reversed_charge+cr_store_credit)),
    26  cross_sales as
    27   (select i_product_name product_name
    28       ,i_item_sk item_sk
    29       ,s_store_name store_name
    30       ,s_zip store_zip
    31       ,ad1.ca_street_number b_street_number
    32       ,ad1.ca_street_name b_street_name
    33       ,ad1.ca_city b_city
    34       ,ad1.ca_zip b_zip
    35       ,ad2.ca_street_number c_street_number
    36       ,ad2.ca_street_name c_street_name
    37       ,ad2.ca_city c_city
    38       ,ad2.ca_zip c_zip
    39       ,d1.d_year as syear
    40       ,d2.d_year as fsyear
    41       ,d3.d_year s2year
    42       ,count(*) cnt
    43       ,sum(ss_wholesale_cost) s1
    44       ,sum(ss_list_price) s2
    45       ,sum(ss_coupon_amt) s3
    46    FROM   store_sales
    47          ,store_returns
    48          ,cs_ui
    49          ,date_dim d1
    50          ,date_dim d2
    51          ,date_dim d3
    52          ,store
    53          ,customer
    54          ,customer_demographics cd1
    55          ,customer_demographics cd2
    56          ,promotion
    57          ,household_demographics hd1
    58          ,household_demographics hd2
    59          ,customer_address ad1
    60          ,customer_address ad2
    61          ,income_band ib1
    62          ,income_band ib2
    63          ,item
    64    WHERE  ss_store_sk = s_store_sk AND
    65           ss_sold_date_sk = d1.d_date_sk AND
    66           ss_customer_sk = c_customer_sk AND
    67           ss_cdemo_sk= cd1.cd_demo_sk AND
    68           ss_hdemo_sk = hd1.hd_demo_sk AND
    69           ss_addr_sk = ad1.ca_address_sk and
    70           ss_item_sk = i_item_sk and
    71           ss_item_sk = sr_item_sk and
    72           ss_ticket_number = sr_ticket_number and
    73           ss_item_sk = cs_ui.cs_item_sk and
    74           c_current_cdemo_sk = cd2.cd_demo_sk AND
    75           c_current_hdemo_sk = hd2.hd_demo_sk AND
    76           c_current_addr_sk = ad2.ca_address_sk and
    77           c_first_sales_date_sk = d2.d_date_sk and
    78           c_first_shipto_date_sk = d3.d_date_sk and
    79           ss_promo_sk = p_promo_sk and
    80           hd1.hd_income_band_sk = ib1.ib_income_band_sk and
    81           hd2.hd_income_band_sk = ib2.ib_income_band_sk and
    82           cd1.cd_marital_status <> cd2.cd_marital_status and
    83           i_color in ('maroon','burnished','dim','steel','navajo','chocolate') and
    84           i_current_price between 35 and 35 + 10 and
    85           i_current_price between 35 + 1 and 35 + 15
    86  group by i_product_name
    87         ,i_item_sk
    88         ,s_store_name
    89         ,s_zip
    90         ,ad1.ca_street_number
    91         ,ad1.ca_street_name
    92         ,ad1.ca_city
    93         ,ad1.ca_zip
    94         ,ad2.ca_street_number
    95         ,ad2.ca_street_name
    96         ,ad2.ca_city
    97         ,ad2.ca_zip
    98         ,d1.d_year
    99         ,d2.d_year
   100         ,d3.d_year
   101  )
   102  select cs1.product_name
   103       ,cs1.store_name
   104       ,cs1.store_zip
   105       ,cs1.b_street_number
   106       ,cs1.b_street_name
   107       ,cs1.b_city
   108       ,cs1.b_zip
   109       ,cs1.c_street_number
   110       ,cs1.c_street_name
   111       ,cs1.c_city
   112       ,cs1.c_zip
   113       ,cs1.syear
   114       ,cs1.cnt
   115       ,cs1.s1 as s11
   116       ,cs1.s2 as s21
   117       ,cs1.s3 as s31
   118       ,cs2.s1 as s12
   119       ,cs2.s2 as s22
   120       ,cs2.s3 as s32
   121       ,cs2.syear
   122       ,cs2.cnt
   123  from cross_sales cs1,cross_sales cs2
   124  where cs1.item_sk=cs2.item_sk and
   125       cs1.syear = 2000 and
   126       cs2.syear = 2000 + 1 and
   127       cs2.cnt <= cs1.cnt and
   128       cs1.store_name = cs2.store_name and
   129       cs1.store_zip = cs2.store_zip
   130  order by cs1.product_name
   131         ,cs1.store_name
   132         ,cs2.cnt
   133         ,cs1.s1
   134         ,cs2.s1