Github user davies commented on a diff in the pull request:
https://github.com/apache/spark/pull/13188#discussion_r63970987
--- Diff:
sql/core/src/test/scala/org/apache/spark/sql/execution/datasources/parquet/tpcds/queries/q78.sql
---
@@ -0,0 +1,64 @@
+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
+ round(ss_qty / (coalesce(ws_qty + cs_qty, 1)), 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 = cs_item_sk AND
cs_customer_sk = ss_customer_sk)
--- End diff --
cs_item_sk => ss_item_sk
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastruct...@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscr...@spark.apache.org
For additional commands, e-mail: reviews-h...@spark.apache.org
