sql-tests.testcases.tpcds.q77.sql Maven / Gradle / Ivy
-- database: presto_tpcds; groups: tpcds; requires: io.prestodb.tempto.fulfillment.table.hive.tpcds.ImmutableTpcdsTablesRequirements
WITH
ss AS (
SELECT
"s_store_sk"
, "sum"("ss_ext_sales_price") "sales"
, "sum"("ss_net_profit") "profit"
FROM
store_sales
, date_dim
, store
WHERE ("ss_sold_date_sk" = "d_date_sk")
AND ("d_date" BETWEEN CAST('2000-08-23' AS DATE) AND (CAST('2000-08-23' AS DATE) + INTERVAL '30' DAY))
AND ("ss_store_sk" = "s_store_sk")
GROUP BY "s_store_sk"
)
, sr AS (
SELECT
"s_store_sk"
, "sum"("sr_return_amt") "returns"
, "sum"("sr_net_loss") "profit_loss"
FROM
store_returns
, date_dim
, store
WHERE ("sr_returned_date_sk" = "d_date_sk")
AND ("d_date" BETWEEN CAST('2000-08-23' AS DATE) AND (CAST('2000-08-23' AS DATE) + INTERVAL '30' DAY))
AND ("sr_store_sk" = "s_store_sk")
GROUP BY "s_store_sk"
)
, cs AS (
SELECT
"cs_call_center_sk"
, "sum"("cs_ext_sales_price") "sales"
, "sum"("cs_net_profit") "profit"
FROM
catalog_sales
, date_dim
WHERE ("cs_sold_date_sk" = "d_date_sk")
AND ("d_date" BETWEEN CAST('2000-08-23' AS DATE) AND (CAST('2000-08-23' AS DATE) + INTERVAL '30' DAY))
GROUP BY "cs_call_center_sk"
)
, cr AS (
SELECT
"cr_call_center_sk"
, "sum"("cr_return_amount") "returns"
, "sum"("cr_net_loss") "profit_loss"
FROM
catalog_returns
, date_dim
WHERE ("cr_returned_date_sk" = "d_date_sk")
AND ("d_date" BETWEEN CAST('2000-08-23' AS DATE) AND (CAST('2000-08-23' AS DATE) + INTERVAL '30' DAY))
GROUP BY "cr_call_center_sk"
)
, ws AS (
SELECT
"wp_web_page_sk"
, "sum"("ws_ext_sales_price") "sales"
, "sum"("ws_net_profit") "profit"
FROM
web_sales
, date_dim
, web_page
WHERE ("ws_sold_date_sk" = "d_date_sk")
AND ("d_date" BETWEEN CAST('2000-08-23' AS DATE) AND (CAST('2000-08-23' AS DATE) + INTERVAL '30' DAY))
AND ("ws_web_page_sk" = "wp_web_page_sk")
GROUP BY "wp_web_page_sk"
)
, wr AS (
SELECT
"wp_web_page_sk"
, "sum"("wr_return_amt") "returns"
, "sum"("wr_net_loss") "profit_loss"
FROM
web_returns
, date_dim
, web_page
WHERE ("wr_returned_date_sk" = "d_date_sk")
AND ("d_date" BETWEEN CAST('2000-08-23' AS DATE) AND (CAST('2000-08-23' AS DATE) + INTERVAL '30' DAY))
AND ("wr_web_page_sk" = "wp_web_page_sk")
GROUP BY "wp_web_page_sk"
)
SELECT
"channel"
, "id"
, "sum"("sales") "sales"
, "sum"("returns") "returns"
, "sum"("profit") "profit"
FROM
(
SELECT
'store channel' "channel"
, "ss"."s_store_sk" "id"
, "sales"
, COALESCE("returns", 0) "returns"
, ("profit" - COALESCE("profit_loss", 0)) "profit"
FROM
(ss
LEFT JOIN sr ON ("ss"."s_store_sk" = "sr"."s_store_sk"))
UNION ALL SELECT
'catalog channel' "channel"
, "cs_call_center_sk" "id"
, "sales"
, "returns"
, ("profit" - "profit_loss") "profit"
FROM
cs
, cr
UNION ALL SELECT
'web channel' "channel"
, "ws"."wp_web_page_sk" "id"
, "sales"
, COALESCE("returns", 0) "returns"
, ("profit" - COALESCE("profit_loss", 0)) "profit"
FROM
(ws
LEFT JOIN wr ON ("ws"."wp_web_page_sk" = "wr"."wp_web_page_sk"))
) x
GROUP BY ROLLUP (channel, id)
ORDER BY "channel" ASC, "id" ASC, "sales" ASC
LIMIT 100