resources/tpcds-v2.7.0/q78.sql

0001 WITH ws AS
0002 (SELECT
0003     d_year AS ws_sold_year,
0004     ws_item_sk,
0005     ws_bill_customer_sk ws_customer_sk,
0006     sum(ws_quantity) ws_qty,
0007     sum(ws_wholesale_cost) ws_wc,
0008     sum(ws_sales_price) ws_sp
0009   FROM web_sales
0010     LEFT JOIN web_returns ON wr_order_number = ws_order_number AND ws_item_sk = wr_item_sk
0011     JOIN date_dim ON ws_sold_date_sk = d_date_sk
0012   WHERE wr_order_number IS NULL
0013   GROUP BY d_year, ws_item_sk, ws_bill_customer_sk
0014 ),
0015     cs AS
0016   (SELECT
0017     d_year AS cs_sold_year,
0018     cs_item_sk,
0019     cs_bill_customer_sk cs_customer_sk,
0020     sum(cs_quantity) cs_qty,
0021     sum(cs_wholesale_cost) cs_wc,
0022     sum(cs_sales_price) cs_sp
0023   FROM catalog_sales
0024     LEFT JOIN catalog_returns ON cr_order_number = cs_order_number AND cs_item_sk = cr_item_sk
0025     JOIN date_dim ON cs_sold_date_sk = d_date_sk
0026   WHERE cr_order_number IS NULL
0027   GROUP BY d_year, cs_item_sk, cs_bill_customer_sk
0028   ),
0029     ss AS
0030   (SELECT
0031     d_year AS ss_sold_year,
0032     ss_item_sk,
0033     ss_customer_sk,
0034     sum(ss_quantity) ss_qty,
0035     sum(ss_wholesale_cost) ss_wc,
0036     sum(ss_sales_price) ss_sp
0037   FROM store_sales
0038     LEFT JOIN store_returns ON sr_ticket_number = ss_ticket_number AND ss_item_sk = sr_item_sk
0039     JOIN date_dim ON ss_sold_date_sk = d_date_sk
0040   WHERE sr_ticket_number IS NULL
0041   GROUP BY d_year, ss_item_sk, ss_customer_sk
0042   )
0043 SELECT
0044   round(ss_qty / (coalesce(ws_qty + cs_qty, 1)), 2) ratio,
0045   ss_qty store_qty,
0046   ss_wc store_wholesale_cost,
0047   ss_sp store_sales_price,
0048   coalesce(ws_qty, 0) + coalesce(cs_qty, 0) other_chan_qty,
0049   coalesce(ws_wc, 0) + coalesce(cs_wc, 0) other_chan_wholesale_cost,
0050   coalesce(ws_sp, 0) + coalesce(cs_sp, 0) other_chan_sales_price
0051 FROM ss
0052   LEFT JOIN ws
0053     ON (ws_sold_year = ss_sold_year AND ws_item_sk = ss_item_sk AND ws_customer_sk = ss_customer_sk)
0054   LEFT JOIN cs
0055     ON (cs_sold_year = ss_sold_year AND cs_item_sk = ss_item_sk AND cs_customer_sk = ss_customer_sk)
0056 WHERE coalesce(ws_qty, 0) > 0 AND coalesce(cs_qty, 0) > 0 AND ss_sold_year = 2000
0057 ORDER BY
0058   -- order-by list of q78 in TPCDS v1.4 is below:
0059   -- ratio,
0060   -- ss_qty DESC, ss_wc DESC, ss_sp DESC,
0061   -- other_chan_qty,
0062   -- other_chan_wholesale_cost,
0063   -- other_chan_sales_price,
0064   -- round(ss_qty / (coalesce(ws_qty + cs_qty, 1)), 2)
0065   ss_sold_year,
0066   ss_item_sk,
0067   ss_customer_sk,
0068   ss_qty desc,
0069   ss_wc desc,
0070   ss_sp desc,
0071   other_chan_qty,
0072   other_chan_wholesale_cost,
0073   other_chan_sales_price,
0074   ratio
0075 LIMIT 100