diaolimin commented on issue #8365:
URL:
https://github.com/apache/incubator-gluten/issues/8365#issuecomment-2563799176
@taiyang-li
**-----------DDL**
create table store_returns
(
sr_return_time_sk bigint
, sr_item_sk bigint
, sr_customer_sk bigint
, sr_cdemo_sk bigint
, sr_hdemo_sk bigint
, sr_addr_sk bigint
, sr_store_sk bigint
, sr_reason_sk bigint
, sr_ticket_number bigint
, sr_return_quantity int
, sr_return_amt decimal(7,2)
, sr_return_tax decimal(7,2)
, sr_return_amt_inc_tax decimal(7,2)
, sr_fee decimal(7,2)
, sr_return_ship_cost decimal(7,2)
, sr_refunded_cash decimal(7,2)
, sr_reversed_charge decimal(7,2)
, sr_store_credit decimal(7,2)
, sr_net_loss decimal(7,2)
)
partitioned by (sr_returned_date_sk bigint)
stored as parquet;
create table store(
s_store_sk bigint
, s_store_id char(16)
, s_rec_start_date date
, s_rec_end_date date
, s_closed_date_sk bigint
, s_store_name varchar(50)
, s_number_employees int
, s_floor_space int
, s_hours char(20)
, S_manager varchar(40)
, S_market_id int
, S_geography_class varchar(100)
, S_market_desc varchar(100)
, s_market_manager varchar(40)
, s_division_id int
, s_division_name varchar(50)
, s_company_id int
, s_company_name varchar(50)
, s_street_number varchar(10)
, s_street_name varchar(60)
, s_street_type char(15)
, s_suite_number char(10)
, s_city varchar(60)
, s_county varchar(30)
, s_state char(2)
, s_zip char(10)
, s_country varchar(20)
, s_gmt_offset decimal(5,2)
, s_tax_percentage decimal(5,2)
) stored as parquet;
create table customer(
c_customer_sk bigint
, c_customer_id char(16)
, c_current_cdemo_sk bigint
, c_current_hdemo_sk bigint
, c_current_addr_sk bigint
, c_first_shipto_date_sk bigint
, c_first_sales_date_sk bigint
, c_salutation char(10)
, c_first_name char(20)
, c_last_name char(30)
, c_preferred_cust_flag char(1)
, c_birth_day int
, c_birth_month int
, c_birth_year int
, c_birth_country varchar(20)
, c_login char(13)
, c_email_address char(50)
, c_last_review_date_sk bigint
) stored as parquet;
create table date_dim(
d_date_sk bigint
, d_date_id char(16)
, d_date date
, d_month_seq int
, d_week_seq int
, d_quarter_seq int
, d_year int
, d_dow int
, d_moy int
, d_dom int
, d_qoy int
, d_fy_year int
, d_fy_quarter_seq int
, d_fy_week_seq int
, d_day_name char(9)
, d_quarter_name char(6)
, d_holiday char(1)
, d_weekend char(1)
, d_following_holiday char(1)
, d_first_dom int
, d_last_dom int
, d_same_day_ly int
, d_same_day_lq int
, d_current_day char(1)
, d_current_week char(1)
, d_current_month char(1)
, d_current_quarter char(1)
, d_current_year char(1)
) stored as parquet;
**-----------q1.sql**
WITH customer_total_return AS
( SELECT
sr_customer_sk AS ctr_customer_sk,
sr_store_sk AS ctr_store_sk,
sum(sr_return_amt) AS ctr_total_return
FROM store_returns, date_dim
WHERE sr_returned_date_sk = d_date_sk AND d_year = 2000
GROUP BY sr_customer_sk, sr_store_sk)
SELECT c_customer_id
FROM customer_total_return ctr1, store, customer
WHERE ctr1.ctr_total_return >
(SELECT avg(ctr_total_return) * 1.2
FROM customer_total_return ctr2
WHERE ctr1.ctr_store_sk = ctr2.ctr_store_sk)
AND s_store_sk = ctr1.ctr_store_sk
AND s_state = 'TN'
AND ctr1.ctr_customer_sk = c_customer_sk
ORDER BY c_customer_id
LIMIT 100
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
