neilconway commented on code in PR #22474:
URL: https://github.com/apache/datafusion/pull/22474#discussion_r3313273233
##########
datafusion/core/tests/tpc-ds/5.sql:
##########
@@ -1,129 +1,146 @@
--- Copyright 2015 Transaction Processing Performance Council
-
-with ssr as
- (select s_store_id,
- sum(sales_price) as sales,
- sum(profit) as profit,
- sum(return_amt) as returns,
- sum(net_loss) as profit_loss
- from
- ( select ss_store_sk as store_sk,
- ss_sold_date_sk as date_sk,
- ss_ext_sales_price as sales_price,
- ss_net_profit as profit,
- cast(0 as decimal(7,2)) as return_amt,
- cast(0 as decimal(7,2)) as net_loss
- from store_sales
- union all
- select sr_store_sk as store_sk,
- sr_returned_date_sk as date_sk,
- cast(0 as decimal(7,2)) as sales_price,
- cast(0 as decimal(7,2)) as profit,
- sr_return_amt as return_amt,
- sr_net_loss as net_loss
- from store_returns
- ) salesreturns,
- date_dim,
- store
- where date_sk = d_date_sk
- and d_date between cast('1998-08-04' as date)
- and (cast('1998-08-04' as date) + INTERVAL '14 days')
- and store_sk = s_store_sk
- group by s_store_id)
- ,
- csr as
- (select cp_catalog_page_id,
- sum(sales_price) as sales,
- sum(profit) as profit,
- sum(return_amt) as returns,
- sum(net_loss) as profit_loss
- from
- ( select cs_catalog_page_sk as page_sk,
- cs_sold_date_sk as date_sk,
- cs_ext_sales_price as sales_price,
- cs_net_profit as profit,
- cast(0 as decimal(7,2)) as return_amt,
- cast(0 as decimal(7,2)) as net_loss
- from catalog_sales
- union all
- select cr_catalog_page_sk as page_sk,
- cr_returned_date_sk as date_sk,
- cast(0 as decimal(7,2)) as sales_price,
- cast(0 as decimal(7,2)) as profit,
- cr_return_amount as return_amt,
- cr_net_loss as net_loss
- from catalog_returns
- ) salesreturns,
- date_dim,
- catalog_page
- where date_sk = d_date_sk
- and d_date between cast('1998-08-04' as date)
- and (cast('1998-08-04' as date) + INTERVAL '14 days')
- and page_sk = cp_catalog_page_sk
- group by cp_catalog_page_id)
- ,
- wsr as
- (select web_site_id,
- sum(sales_price) as sales,
- sum(profit) as profit,
- sum(return_amt) as returns,
- sum(net_loss) as profit_loss
- from
- ( select ws_web_site_sk as wsr_web_site_sk,
- ws_sold_date_sk as date_sk,
- ws_ext_sales_price as sales_price,
- ws_net_profit as profit,
- cast(0 as decimal(7,2)) as return_amt,
- cast(0 as decimal(7,2)) as net_loss
- from web_sales
- union all
- select ws_web_site_sk as wsr_web_site_sk,
- wr_returned_date_sk as date_sk,
- cast(0 as decimal(7,2)) as sales_price,
- cast(0 as decimal(7,2)) as profit,
- wr_return_amt as return_amt,
- wr_net_loss as net_loss
- from web_returns left outer join web_sales on
- ( wr_item_sk = ws_item_sk
- and wr_order_number = ws_order_number)
- ) salesreturns,
- date_dim,
- web_site
- where date_sk = d_date_sk
- and d_date between cast('1998-08-04' as date)
- and (cast('1998-08-04' as date) + INTERVAL '14 days')
- and wsr_web_site_sk = web_site_sk
- group by web_site_id)
- select channel
- , id
- , sum(sales) as sales
- , sum(returns) as returns
- , sum(profit) as profit
- from
- (select 'store channel' as channel
- , 'store' || s_store_id as id
- , sales
- , returns
- , (profit - profit_loss) as profit
- from ssr
- union all
- select 'catalog channel' as channel
- , 'catalog_page' || cp_catalog_page_id as id
- , sales
- , returns
- , (profit - profit_loss) as profit
- from csr
- union all
- select 'web channel' as channel
- , 'web_site' || web_site_id as id
- , sales
- , returns
- , (profit - profit_loss) as profit
- from wsr
- ) x
- group by rollup (channel, id)
- order by channel
- ,id
- limit 100;
-
-
+WITH
+ ssr AS (
+ SELECT
+ s_store_id,
+ sum(sales_price) AS sales,
+ sum(profit) AS profit,
+ sum(return_amt) AS returns,
+ sum(net_loss) AS profit_loss
+ FROM
+ (
+ SELECT
+ ss_store_sk AS store_sk,
+ ss_sold_date_sk AS date_sk,
+ ss_ext_sales_price AS sales_price,
+ ss_net_profit AS profit,
+ cast(0 AS decimal(7, 2)) AS return_amt,
+ cast(0 AS decimal(7, 2)) AS net_loss
+ FROM store_sales
+ UNION ALL
+ SELECT
+ sr_store_sk AS store_sk,
+ sr_returned_date_sk AS date_sk,
+ cast(0 AS decimal(7, 2)) AS sales_price,
+ cast(0 AS decimal(7, 2)) AS profit,
+ sr_return_amt AS return_amt,
+ sr_net_loss AS net_loss
+ FROM store_returns
+ ) salesreturns,
+ date_dim,
+ store
+ WHERE
+ date_sk = d_date_sk
+ AND d_date BETWEEN cast('2000-08-23' AS date) AND (
+ cast('2000-08-23' AS date) + INTERVAL '14 days'
+ )
+ AND store_sk = s_store_sk
+ GROUP BY s_store_id
+ ),
+ csr AS (
+ SELECT
+ cp_catalog_page_id,
+ sum(sales_price) AS sales,
+ sum(profit) AS profit,
+ sum(return_amt) AS returns,
+ sum(net_loss) AS profit_loss
+ FROM
+ (
+ SELECT
+ cs_catalog_page_sk AS page_sk,
+ cs_sold_date_sk AS date_sk,
+ cs_ext_sales_price AS sales_price,
+ cs_net_profit AS profit,
+ cast(0 AS decimal(7, 2)) AS return_amt,
+ cast(0 AS decimal(7, 2)) AS net_loss
+ FROM catalog_sales
+ UNION ALL
+ SELECT
+ cr_catalog_page_sk AS page_sk,
+ cr_returned_date_sk AS date_sk,
+ cast(0 AS decimal(7, 2)) AS sales_price,
+ cast(0 AS decimal(7, 2)) AS profit,
+ cr_return_amount AS return_amt,
+ cr_net_loss AS net_loss
+ FROM catalog_returns
+ ) salesreturns,
+ date_dim,
+ catalog_page
+ WHERE
+ date_sk = d_date_sk
+ AND d_date BETWEEN cast('2000-08-23' AS date) AND (
+ cast('2000-08-23' AS date) + INTERVAL '14 days'
+ )
+ AND page_sk = cp_catalog_page_sk
+ GROUP BY cp_catalog_page_id
+ ),
+ wsr AS (
+ SELECT
+ web_site_id,
+ sum(sales_price) AS sales,
+ sum(profit) AS profit,
+ sum(return_amt) AS returns,
+ sum(net_loss) AS profit_loss
+ FROM
+ (
+ SELECT
+ ws_web_site_sk AS wsr_web_site_sk,
+ ws_sold_date_sk AS date_sk,
+ ws_ext_sales_price AS sales_price,
+ ws_net_profit AS profit,
+ cast(0 AS decimal(7, 2)) AS return_amt,
+ cast(0 AS decimal(7, 2)) AS net_loss
+ FROM web_sales
+ UNION ALL
+ SELECT
+ ws_web_site_sk AS wsr_web_site_sk,
+ wr_returned_date_sk AS date_sk,
+ cast(0 AS decimal(7, 2)) AS sales_price,
+ cast(0 AS decimal(7, 2)) AS profit,
+ wr_return_amt AS return_amt,
+ wr_net_loss AS net_loss
+ FROM web_returns
+ LEFT OUTER JOIN
+ web_sales
+ ON (wr_item_sk = ws_item_sk AND wr_order_number =
ws_order_number)
+ ) salesreturns,
+ date_dim,
+ web_site
+ WHERE
+ date_sk = d_date_sk
+ AND d_date BETWEEN cast('2000-08-23' AS date) AND (
+ cast('2000-08-23' AS date) + INTERVAL '14 days'
+ )
+ AND wsr_web_site_sk = web_site_sk
+ GROUP BY web_site_id
+ )
+SELECT channel, id, sum(sales) AS sales, sum(returns) AS returns, sum(profit)
AS profit
+FROM
+ (
+ SELECT
+ 'store channel' AS channel,
+ concat('store', s_store_id) AS id,
+ sales,
+ returns,
+ (profit - profit_loss) AS profit
+ FROM ssr
+ UNION ALL
+ SELECT
+ 'catalog channel' AS channel,
+ concat('catalog_page', cp_catalog_page_id) AS id,
Review Comment:
The spec uses `||` here, not `concat`.
##########
datafusion/core/tests/tpc-ds/66.sql:
##########
@@ -1,221 +1,257 @@
--- Copyright 2015 Transaction Processing Performance Council
-
-select
- w_warehouse_name
- ,w_warehouse_sq_ft
- ,w_city
- ,w_county
- ,w_state
- ,w_country
- ,ship_carriers
- ,year
- ,sum(jan_sales) as jan_sales
- ,sum(feb_sales) as feb_sales
- ,sum(mar_sales) as mar_sales
- ,sum(apr_sales) as apr_sales
- ,sum(may_sales) as may_sales
- ,sum(jun_sales) as jun_sales
- ,sum(jul_sales) as jul_sales
- ,sum(aug_sales) as aug_sales
- ,sum(sep_sales) as sep_sales
- ,sum(oct_sales) as oct_sales
- ,sum(nov_sales) as nov_sales
- ,sum(dec_sales) as dec_sales
- ,sum(jan_sales/w_warehouse_sq_ft) as jan_sales_per_sq_foot
- ,sum(feb_sales/w_warehouse_sq_ft) as feb_sales_per_sq_foot
- ,sum(mar_sales/w_warehouse_sq_ft) as mar_sales_per_sq_foot
- ,sum(apr_sales/w_warehouse_sq_ft) as apr_sales_per_sq_foot
- ,sum(may_sales/w_warehouse_sq_ft) as may_sales_per_sq_foot
- ,sum(jun_sales/w_warehouse_sq_ft) as jun_sales_per_sq_foot
- ,sum(jul_sales/w_warehouse_sq_ft) as jul_sales_per_sq_foot
- ,sum(aug_sales/w_warehouse_sq_ft) as aug_sales_per_sq_foot
- ,sum(sep_sales/w_warehouse_sq_ft) as sep_sales_per_sq_foot
- ,sum(oct_sales/w_warehouse_sq_ft) as oct_sales_per_sq_foot
- ,sum(nov_sales/w_warehouse_sq_ft) as nov_sales_per_sq_foot
- ,sum(dec_sales/w_warehouse_sq_ft) as dec_sales_per_sq_foot
- ,sum(jan_net) as jan_net
- ,sum(feb_net) as feb_net
- ,sum(mar_net) as mar_net
- ,sum(apr_net) as apr_net
- ,sum(may_net) as may_net
- ,sum(jun_net) as jun_net
- ,sum(jul_net) as jul_net
- ,sum(aug_net) as aug_net
- ,sum(sep_net) as sep_net
- ,sum(oct_net) as oct_net
- ,sum(nov_net) as nov_net
- ,sum(dec_net) as dec_net
- from (
- select
- w_warehouse_name
- ,w_warehouse_sq_ft
- ,w_city
- ,w_county
- ,w_state
- ,w_country
- ,'DIAMOND' || ',' || 'AIRBORNE' as ship_carriers
- ,d_year as year
- ,sum(case when d_moy = 1
- then ws_sales_price* ws_quantity else 0 end) as jan_sales
- ,sum(case when d_moy = 2
- then ws_sales_price* ws_quantity else 0 end) as feb_sales
- ,sum(case when d_moy = 3
- then ws_sales_price* ws_quantity else 0 end) as mar_sales
- ,sum(case when d_moy = 4
- then ws_sales_price* ws_quantity else 0 end) as apr_sales
- ,sum(case when d_moy = 5
- then ws_sales_price* ws_quantity else 0 end) as may_sales
- ,sum(case when d_moy = 6
- then ws_sales_price* ws_quantity else 0 end) as jun_sales
- ,sum(case when d_moy = 7
- then ws_sales_price* ws_quantity else 0 end) as jul_sales
- ,sum(case when d_moy = 8
- then ws_sales_price* ws_quantity else 0 end) as aug_sales
- ,sum(case when d_moy = 9
- then ws_sales_price* ws_quantity else 0 end) as sep_sales
- ,sum(case when d_moy = 10
- then ws_sales_price* ws_quantity else 0 end) as oct_sales
- ,sum(case when d_moy = 11
- then ws_sales_price* ws_quantity else 0 end) as nov_sales
- ,sum(case when d_moy = 12
- then ws_sales_price* ws_quantity else 0 end) as dec_sales
- ,sum(case when d_moy = 1
- then ws_net_paid_inc_tax * ws_quantity else 0 end) as jan_net
- ,sum(case when d_moy = 2
- then ws_net_paid_inc_tax * ws_quantity else 0 end) as feb_net
- ,sum(case when d_moy = 3
- then ws_net_paid_inc_tax * ws_quantity else 0 end) as mar_net
- ,sum(case when d_moy = 4
- then ws_net_paid_inc_tax * ws_quantity else 0 end) as apr_net
- ,sum(case when d_moy = 5
- then ws_net_paid_inc_tax * ws_quantity else 0 end) as may_net
- ,sum(case when d_moy = 6
- then ws_net_paid_inc_tax * ws_quantity else 0 end) as jun_net
- ,sum(case when d_moy = 7
- then ws_net_paid_inc_tax * ws_quantity else 0 end) as jul_net
- ,sum(case when d_moy = 8
- then ws_net_paid_inc_tax * ws_quantity else 0 end) as aug_net
- ,sum(case when d_moy = 9
- then ws_net_paid_inc_tax * ws_quantity else 0 end) as sep_net
- ,sum(case when d_moy = 10
- then ws_net_paid_inc_tax * ws_quantity else 0 end) as oct_net
- ,sum(case when d_moy = 11
- then ws_net_paid_inc_tax * ws_quantity else 0 end) as nov_net
- ,sum(case when d_moy = 12
- then ws_net_paid_inc_tax * ws_quantity else 0 end) as dec_net
- from
- web_sales
- ,warehouse
- ,date_dim
- ,time_dim
- ,ship_mode
- where
- ws_warehouse_sk = w_warehouse_sk
- and ws_sold_date_sk = d_date_sk
- and ws_sold_time_sk = t_time_sk
- and ws_ship_mode_sk = sm_ship_mode_sk
- and d_year = 2002
- and t_time between 49530 and 49530+28800
- and sm_carrier in ('DIAMOND','AIRBORNE')
- group by
- w_warehouse_name
- ,w_warehouse_sq_ft
- ,w_city
- ,w_county
- ,w_state
- ,w_country
- ,d_year
- union all
- select
- w_warehouse_name
- ,w_warehouse_sq_ft
- ,w_city
- ,w_county
- ,w_state
- ,w_country
- ,'DIAMOND' || ',' || 'AIRBORNE' as ship_carriers
- ,d_year as year
- ,sum(case when d_moy = 1
- then cs_ext_sales_price* cs_quantity else 0 end) as jan_sales
- ,sum(case when d_moy = 2
- then cs_ext_sales_price* cs_quantity else 0 end) as feb_sales
- ,sum(case when d_moy = 3
- then cs_ext_sales_price* cs_quantity else 0 end) as mar_sales
- ,sum(case when d_moy = 4
- then cs_ext_sales_price* cs_quantity else 0 end) as apr_sales
- ,sum(case when d_moy = 5
- then cs_ext_sales_price* cs_quantity else 0 end) as may_sales
- ,sum(case when d_moy = 6
- then cs_ext_sales_price* cs_quantity else 0 end) as jun_sales
- ,sum(case when d_moy = 7
- then cs_ext_sales_price* cs_quantity else 0 end) as jul_sales
- ,sum(case when d_moy = 8
- then cs_ext_sales_price* cs_quantity else 0 end) as aug_sales
- ,sum(case when d_moy = 9
- then cs_ext_sales_price* cs_quantity else 0 end) as sep_sales
- ,sum(case when d_moy = 10
- then cs_ext_sales_price* cs_quantity else 0 end) as oct_sales
- ,sum(case when d_moy = 11
- then cs_ext_sales_price* cs_quantity else 0 end) as nov_sales
- ,sum(case when d_moy = 12
- then cs_ext_sales_price* cs_quantity else 0 end) as dec_sales
- ,sum(case when d_moy = 1
- then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as
jan_net
- ,sum(case when d_moy = 2
- then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as
feb_net
- ,sum(case when d_moy = 3
- then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as
mar_net
- ,sum(case when d_moy = 4
- then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as
apr_net
- ,sum(case when d_moy = 5
- then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as
may_net
- ,sum(case when d_moy = 6
- then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as
jun_net
- ,sum(case when d_moy = 7
- then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as
jul_net
- ,sum(case when d_moy = 8
- then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as
aug_net
- ,sum(case when d_moy = 9
- then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as
sep_net
- ,sum(case when d_moy = 10
- then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as
oct_net
- ,sum(case when d_moy = 11
- then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as
nov_net
- ,sum(case when d_moy = 12
- then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as
dec_net
- from
- catalog_sales
- ,warehouse
- ,date_dim
- ,time_dim
- ,ship_mode
- where
- cs_warehouse_sk = w_warehouse_sk
- and cs_sold_date_sk = d_date_sk
- and cs_sold_time_sk = t_time_sk
- and cs_ship_mode_sk = sm_ship_mode_sk
- and d_year = 2002
- and t_time between 49530 AND 49530+28800
- and sm_carrier in ('DIAMOND','AIRBORNE')
- group by
- w_warehouse_name
- ,w_warehouse_sq_ft
- ,w_city
- ,w_county
- ,w_state
- ,w_country
- ,d_year
- ) x
- group by
- w_warehouse_name
- ,w_warehouse_sq_ft
- ,w_city
- ,w_county
- ,w_state
- ,w_country
- ,ship_carriers
- ,year
- order by w_warehouse_name
- limit 100;
-
-
+SELECT
+ w_warehouse_name,
+ w_warehouse_sq_ft,
+ w_city,
+ w_county,
+ w_state,
+ w_country,
+ ship_carriers,
+ year,
+ sum(jan_sales) AS jan_sales,
+ sum(feb_sales) AS feb_sales,
+ sum(mar_sales) AS mar_sales,
+ sum(apr_sales) AS apr_sales,
+ sum(may_sales) AS may_sales,
+ sum(jun_sales) AS jun_sales,
+ sum(jul_sales) AS jul_sales,
+ sum(aug_sales) AS aug_sales,
+ sum(sep_sales) AS sep_sales,
+ sum(oct_sales) AS oct_sales,
+ sum(nov_sales) AS nov_sales,
+ sum(dec_sales) AS dec_sales,
+ sum(jan_sales / w_warehouse_sq_ft) AS jan_sales_per_sq_foot,
+ sum(feb_sales / w_warehouse_sq_ft) AS feb_sales_per_sq_foot,
+ sum(mar_sales / w_warehouse_sq_ft) AS mar_sales_per_sq_foot,
+ sum(apr_sales / w_warehouse_sq_ft) AS apr_sales_per_sq_foot,
+ sum(may_sales / w_warehouse_sq_ft) AS may_sales_per_sq_foot,
+ sum(jun_sales / w_warehouse_sq_ft) AS jun_sales_per_sq_foot,
+ sum(jul_sales / w_warehouse_sq_ft) AS jul_sales_per_sq_foot,
+ sum(aug_sales / w_warehouse_sq_ft) AS aug_sales_per_sq_foot,
+ sum(sep_sales / w_warehouse_sq_ft) AS sep_sales_per_sq_foot,
+ sum(oct_sales / w_warehouse_sq_ft) AS oct_sales_per_sq_foot,
+ sum(nov_sales / w_warehouse_sq_ft) AS nov_sales_per_sq_foot,
+ sum(dec_sales / w_warehouse_sq_ft) AS dec_sales_per_sq_foot,
+ sum(jan_net) AS jan_net,
+ sum(feb_net) AS feb_net,
+ sum(mar_net) AS mar_net,
+ sum(apr_net) AS apr_net,
+ sum(may_net) AS may_net,
+ sum(jun_net) AS jun_net,
+ sum(jul_net) AS jul_net,
+ sum(aug_net) AS aug_net,
+ sum(sep_net) AS sep_net,
+ sum(oct_net) AS oct_net,
+ sum(nov_net) AS nov_net,
+ sum(dec_net) AS dec_net
+FROM
+ (
+ SELECT
+ w_warehouse_name,
+ w_warehouse_sq_ft,
+ w_city,
+ w_county,
+ w_state,
+ w_country,
+ concat('DHL', ',', 'BARIAN') AS ship_carriers,
Review Comment:
`||` here as well
##########
datafusion/core/tests/tpc-ds/78.sql:
##########
@@ -1,59 +1,90 @@
--- Copyright 2015 Transaction Processing Performance Council
-
-with ws as
- (select d_year AS ws_sold_year, ws_item_sk,
- ws_bill_customer_sk ws_customer_sk,
- sum(ws_quantity) ws_qty,
- sum(ws_wholesale_cost) ws_wc,
- sum(ws_sales_price) ws_sp
- from web_sales
- left join web_returns on wr_order_number=ws_order_number and
ws_item_sk=wr_item_sk
- join date_dim on ws_sold_date_sk = d_date_sk
- where wr_order_number is null
- group by d_year, ws_item_sk, ws_bill_customer_sk
- ),
-cs as
- (select d_year AS cs_sold_year, cs_item_sk,
- cs_bill_customer_sk cs_customer_sk,
- sum(cs_quantity) cs_qty,
- sum(cs_wholesale_cost) cs_wc,
- sum(cs_sales_price) cs_sp
- from catalog_sales
- left join catalog_returns on cr_order_number=cs_order_number and
cs_item_sk=cr_item_sk
- join date_dim on cs_sold_date_sk = d_date_sk
- where cr_order_number is null
- group by d_year, cs_item_sk, cs_bill_customer_sk
- ),
-ss as
- (select d_year AS ss_sold_year, ss_item_sk,
+WITH
+ ws AS (
+ SELECT
+ d_year AS ws_sold_year,
+ ws_item_sk,
+ ws_bill_customer_sk ws_customer_sk,
+ sum(ws_quantity) ws_qty,
+ sum(ws_wholesale_cost) ws_wc,
+ sum(ws_sales_price) ws_sp
+ FROM web_sales
+ LEFT JOIN
+ web_returns ON wr_order_number = ws_order_number AND ws_item_sk =
wr_item_sk
+ JOIN date_dim ON ws_sold_date_sk = d_date_sk
+ WHERE wr_order_number IS NULL
+ GROUP BY d_year, ws_item_sk, ws_bill_customer_sk
+ ),
+ cs AS (
+ SELECT
+ d_year AS cs_sold_year,
+ cs_item_sk,
+ cs_bill_customer_sk cs_customer_sk,
+ sum(cs_quantity) cs_qty,
+ sum(cs_wholesale_cost) cs_wc,
+ sum(cs_sales_price) cs_sp
+ FROM catalog_sales
+ LEFT JOIN
+ catalog_returns
+ ON cr_order_number = cs_order_number
+ AND cs_item_sk = cr_item_sk
+ JOIN date_dim ON cs_sold_date_sk = d_date_sk
+ WHERE cr_order_number IS NULL
+ GROUP BY d_year, cs_item_sk, cs_bill_customer_sk
+ ),
+ ss AS (
+ SELECT
+ d_year AS ss_sold_year,
+ ss_item_sk,
+ ss_customer_sk,
+ sum(ss_quantity) ss_qty,
+ sum(ss_wholesale_cost) ss_wc,
+ sum(ss_sales_price) ss_sp
+ FROM store_sales
+ LEFT JOIN
+ store_returns
+ ON sr_ticket_number = ss_ticket_number
+ AND ss_item_sk = sr_item_sk
+ JOIN date_dim ON ss_sold_date_sk = d_date_sk
+ WHERE sr_ticket_number IS NULL
+ GROUP BY d_year, ss_item_sk, ss_customer_sk
+ )
+SELECT
+ ss_sold_year,
+ ss_item_sk,
ss_customer_sk,
- sum(ss_quantity) ss_qty,
- sum(ss_wholesale_cost) ss_wc,
- sum(ss_sales_price) ss_sp
- from store_sales
- left join store_returns on sr_ticket_number=ss_ticket_number and
ss_item_sk=sr_item_sk
- join date_dim on ss_sold_date_sk = d_date_sk
- where sr_ticket_number is null
- group by d_year, ss_item_sk, ss_customer_sk
- )
- select
-ss_sold_year, ss_item_sk, ss_customer_sk,
-round(ss_qty/(coalesce(ws_qty,0)+coalesce(cs_qty,0)),2) ratio,
-ss_qty store_qty, ss_wc store_wholesale_cost, ss_sp store_sales_price,
-coalesce(ws_qty,0)+coalesce(cs_qty,0) other_chan_qty,
-coalesce(ws_wc,0)+coalesce(cs_wc,0) other_chan_wholesale_cost,
-coalesce(ws_sp,0)+coalesce(cs_sp,0) other_chan_sales_price
-from ss
-left join ws on (ws_sold_year=ss_sold_year and ws_item_sk=ss_item_sk and
ws_customer_sk=ss_customer_sk)
-left join cs on (cs_sold_year=ss_sold_year and cs_item_sk=ss_item_sk and
cs_customer_sk=ss_customer_sk)
-where (coalesce(ws_qty,0)>0 or coalesce(cs_qty, 0)>0) and ss_sold_year=2000
-order by
- ss_sold_year, ss_item_sk, ss_customer_sk,
- ss_qty desc, ss_wc desc, ss_sp desc,
- other_chan_qty,
- other_chan_wholesale_cost,
- other_chan_sales_price,
- ratio
-limit 100;
-
-
+ round(ss_qty / (coalesce(ws_qty, 0) + coalesce(cs_qty, 0)), 2) ratio,
+ ss_qty store_qty,
+ ss_wc store_wholesale_cost,
+ ss_sp store_sales_price,
+ coalesce(ws_qty, 0) + coalesce(cs_qty, 0) other_chan_qty,
+ coalesce(ws_wc, 0) + coalesce(cs_wc, 0) other_chan_wholesale_cost,
+ coalesce(ws_sp, 0) + coalesce(cs_sp, 0) other_chan_sales_price
+FROM ss
+LEFT JOIN
+ ws
+ ON (
+ ws_sold_year = ss_sold_year
+ AND ws_item_sk = ss_item_sk
+ AND ws_customer_sk = ss_customer_sk
+ )
+LEFT JOIN
+ cs
+ ON (
+ cs_sold_year = ss_sold_year
+ AND cs_item_sk = ss_item_sk
+ AND cs_customer_sk = ss_customer_sk
+ )
+WHERE (coalesce(ws_qty, 0) > 0 OR coalesce(cs_qty, 0) > 0) AND ss_sold_year =
2000
+ORDER BY
+ ss_sold_year,
+ ss_item_sk,
+ ss_customer_sk,
+ ss_qty desc,
+ ss_wc desc,
+ ss_sp desc,
+ other_chan_qty,
+ other_chan_wholesale_cost,
+ other_chan_sales_price,
+ round(ss_qty / (coalesce(ws_qty + cs_qty, 1)), 2)
Review Comment:
Looks like the spec wants a slightly different formula:
`round(ss_qty/(coalesce(ws_qty,0)+coalesce(cs_qty,0)),2)`
--
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]
