• Home
• com.alibaba
• druid
• 1.2.17
• source code
• query24.sql

×

Project download

Many resources are needed to download a project. Please understand that we have to compensate our server costs. Thank you in advance.
Project price only 1 $

You can buy this project and download/modify it how often you want.

tpcds.query24.sql Maven / Gradle / Ivy

with ssales as
         (select c_last_name
               , c_first_name
               , s_store_name
               , ca_state
               , s_state
               , i_color
               , i_current_price
               , i_manager_id
               , i_units
               , i_size
               , sum(ss_net_paid) netpaid
          from store_sales
             , store_returns
             , store
             , item
             , customer
             , customer_address
          where ss_ticket_number = sr_ticket_number
            and ss_item_sk = sr_item_sk
            and ss_customer_sk = c_customer_sk
            and ss_item_sk = i_item_sk
            and ss_store_sk = s_store_sk
            and c_current_addr_sk = ca_address_sk
            and c_birth_country <> upper(ca_country)
            and s_zip = ca_zip
            and s_market_id = 8
          group by c_last_name
                 , c_first_name
                 , s_store_name
                 , ca_state
                 , s_state
                 , i_color
                 , i_current_price
                 , i_manager_id
                 , i_units
                 , i_size)
select c_last_name
     , c_first_name
     , s_store_name
     , sum(netpaid) paid
from ssales
where i_color = 'pale'
group by c_last_name
       , c_first_name
       , s_store_name
having sum(netpaid) > (select 0.05 * avg(netpaid)
                       from ssales)
order by c_last_name
       , c_first_name
       , s_store_name
;
with ssales as
         (select c_last_name
               , c_first_name
               , s_store_name
               , ca_state
               , s_state
               , i_color
               , i_current_price
               , i_manager_id
               , i_units
               , i_size
               , sum(ss_net_paid) netpaid
          from store_sales
             , store_returns
             , store
             , item
             , customer
             , customer_address
          where ss_ticket_number = sr_ticket_number
            and ss_item_sk = sr_item_sk
            and ss_customer_sk = c_customer_sk
            and ss_item_sk = i_item_sk
            and ss_store_sk = s_store_sk
            and c_current_addr_sk = ca_address_sk
            and c_birth_country <> upper(ca_country)
            and s_zip = ca_zip
            and s_market_id = 8
          group by c_last_name
                 , c_first_name
                 , s_store_name
                 , ca_state
                 , s_state
                 , i_color
                 , i_current_price
                 , i_manager_id
                 , i_units
                 , i_size)
select c_last_name
     , c_first_name
     , s_store_name
     , sum(netpaid) paid
from ssales
where i_color = 'chiffon'
group by c_last_name
       , c_first_name
       , s_store_name
having sum(netpaid) > (select 0.05 * avg(netpaid)
                       from ssales)
order by c_last_name
       , c_first_name
       , s_store_name