Many resources are needed to download a project. Please understand that we have to compensate our server costs. Thank you in advance.
Project price only 1 $
You can buy this project and download/modify it how often you want.
sql-tests.testcases.tpcds.q66.sql Maven / Gradle / Ivy
-- database: presto_tpcds; groups: tpcds; requires: io.prestodb.tempto.fulfillment.table.hive.tpcds.ImmutableTpcdsTablesRequirements
SELECT
"w_warehouse_name"
, "w_warehouse_sq_ft"
, "w_city"
, "w_county"
, "w_state"
, "w_country"
, "ship_carriers"
, "year"
, "sum"("jan_sales") "jan_sales"
, "sum"("feb_sales") "feb_sales"
, "sum"("mar_sales") "mar_sales"
, "sum"("apr_sales") "apr_sales"
, "sum"("may_sales") "may_sales"
, "sum"("jun_sales") "jun_sales"
, "sum"("jul_sales") "jul_sales"
, "sum"("aug_sales") "aug_sales"
, "sum"("sep_sales") "sep_sales"
, "sum"("oct_sales") "oct_sales"
, "sum"("nov_sales") "nov_sales"
, "sum"("dec_sales") "dec_sales"
, "sum"(("jan_sales" / "w_warehouse_sq_ft")) "jan_sales_per_sq_foot"
, "sum"(("feb_sales" / "w_warehouse_sq_ft")) "feb_sales_per_sq_foot"
, "sum"(("mar_sales" / "w_warehouse_sq_ft")) "mar_sales_per_sq_foot"
, "sum"(("apr_sales" / "w_warehouse_sq_ft")) "apr_sales_per_sq_foot"
, "sum"(("may_sales" / "w_warehouse_sq_ft")) "may_sales_per_sq_foot"
, "sum"(("jun_sales" / "w_warehouse_sq_ft")) "jun_sales_per_sq_foot"
, "sum"(("jul_sales" / "w_warehouse_sq_ft")) "jul_sales_per_sq_foot"
, "sum"(("aug_sales" / "w_warehouse_sq_ft")) "aug_sales_per_sq_foot"
, "sum"(("sep_sales" / "w_warehouse_sq_ft")) "sep_sales_per_sq_foot"
, "sum"(("oct_sales" / "w_warehouse_sq_ft")) "oct_sales_per_sq_foot"
, "sum"(("nov_sales" / "w_warehouse_sq_ft")) "nov_sales_per_sq_foot"
, "sum"(("dec_sales" / "w_warehouse_sq_ft")) "dec_sales_per_sq_foot"
, "sum"("jan_net") "jan_net"
, "sum"("feb_net") "feb_net"
, "sum"("mar_net") "mar_net"
, "sum"("apr_net") "apr_net"
, "sum"("may_net") "may_net"
, "sum"("jun_net") "jun_net"
, "sum"("jul_net") "jul_net"
, "sum"("aug_net") "aug_net"
, "sum"("sep_net") "sep_net"
, "sum"("oct_net") "oct_net"
, "sum"("nov_net") "nov_net"
, "sum"("dec_net") "dec_net"
FROM
(
SELECT
"w_warehouse_name"
, "w_warehouse_sq_ft"
, "w_city"
, "w_county"
, "w_state"
, "w_country"
, "concat"("concat"('DHL', ','), 'BARIAN') "ship_carriers"
, "d_year" "YEAR"
, "sum"((CASE WHEN ("d_moy" = 1) THEN ("ws_ext_sales_price" * "ws_quantity") ELSE 0 END)) "jan_sales"
, "sum"((CASE WHEN ("d_moy" = 2) THEN ("ws_ext_sales_price" * "ws_quantity") ELSE 0 END)) "feb_sales"
, "sum"((CASE WHEN ("d_moy" = 3) THEN ("ws_ext_sales_price" * "ws_quantity") ELSE 0 END)) "mar_sales"
, "sum"((CASE WHEN ("d_moy" = 4) THEN ("ws_ext_sales_price" * "ws_quantity") ELSE 0 END)) "apr_sales"
, "sum"((CASE WHEN ("d_moy" = 5) THEN ("ws_ext_sales_price" * "ws_quantity") ELSE 0 END)) "may_sales"
, "sum"((CASE WHEN ("d_moy" = 6) THEN ("ws_ext_sales_price" * "ws_quantity") ELSE 0 END)) "jun_sales"
, "sum"((CASE WHEN ("d_moy" = 7) THEN ("ws_ext_sales_price" * "ws_quantity") ELSE 0 END)) "jul_sales"
, "sum"((CASE WHEN ("d_moy" = 8) THEN ("ws_ext_sales_price" * "ws_quantity") ELSE 0 END)) "aug_sales"
, "sum"((CASE WHEN ("d_moy" = 9) THEN ("ws_ext_sales_price" * "ws_quantity") ELSE 0 END)) "sep_sales"
, "sum"((CASE WHEN ("d_moy" = 10) THEN ("ws_ext_sales_price" * "ws_quantity") ELSE 0 END)) "oct_sales"
, "sum"((CASE WHEN ("d_moy" = 11) THEN ("ws_ext_sales_price" * "ws_quantity") ELSE 0 END)) "nov_sales"
, "sum"((CASE WHEN ("d_moy" = 12) THEN ("ws_ext_sales_price" * "ws_quantity") ELSE 0 END)) "dec_sales"
, "sum"((CASE WHEN ("d_moy" = 1) THEN ("ws_net_paid" * "ws_quantity") ELSE 0 END)) "jan_net"
, "sum"((CASE WHEN ("d_moy" = 2) THEN ("ws_net_paid" * "ws_quantity") ELSE 0 END)) "feb_net"
, "sum"((CASE WHEN ("d_moy" = 3) THEN ("ws_net_paid" * "ws_quantity") ELSE 0 END)) "mar_net"
, "sum"((CASE WHEN ("d_moy" = 4) THEN ("ws_net_paid" * "ws_quantity") ELSE 0 END)) "apr_net"
, "sum"((CASE WHEN ("d_moy" = 5) THEN ("ws_net_paid" * "ws_quantity") ELSE 0 END)) "may_net"
, "sum"((CASE WHEN ("d_moy" = 6) THEN ("ws_net_paid" * "ws_quantity") ELSE 0 END)) "jun_net"
, "sum"((CASE WHEN ("d_moy" = 7) THEN ("ws_net_paid" * "ws_quantity") ELSE 0 END)) "jul_net"
, "sum"((CASE WHEN ("d_moy" = 8) THEN ("ws_net_paid" * "ws_quantity") ELSE 0 END)) "aug_net"
, "sum"((CASE WHEN ("d_moy" = 9) THEN ("ws_net_paid" * "ws_quantity") ELSE 0 END)) "sep_net"
, "sum"((CASE WHEN ("d_moy" = 10) THEN ("ws_net_paid" * "ws_quantity") ELSE 0 END)) "oct_net"
, "sum"((CASE WHEN ("d_moy" = 11) THEN ("ws_net_paid" * "ws_quantity") ELSE 0 END)) "nov_net"
, "sum"((CASE WHEN ("d_moy" = 12) THEN ("ws_net_paid" * "ws_quantity") ELSE 0 END)) "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" = 2001)
AND ("t_time" BETWEEN 30838 AND (30838 + 28800))
AND ("sm_carrier" IN ('DHL' , 'BARIAN'))
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"
, "concat"("concat"('DHL', ','), 'BARIAN') "ship_carriers"
, "d_year" "YEAR"
, "sum"((CASE WHEN ("d_moy" = 1) THEN ("cs_sales_price" * "cs_quantity") ELSE 0 END)) "jan_sales"
, "sum"((CASE WHEN ("d_moy" = 2) THEN ("cs_sales_price" * "cs_quantity") ELSE 0 END)) "feb_sales"
, "sum"((CASE WHEN ("d_moy" = 3) THEN ("cs_sales_price" * "cs_quantity") ELSE 0 END)) "mar_sales"
, "sum"((CASE WHEN ("d_moy" = 4) THEN ("cs_sales_price" * "cs_quantity") ELSE 0 END)) "apr_sales"
, "sum"((CASE WHEN ("d_moy" = 5) THEN ("cs_sales_price" * "cs_quantity") ELSE 0 END)) "may_sales"
, "sum"((CASE WHEN ("d_moy" = 6) THEN ("cs_sales_price" * "cs_quantity") ELSE 0 END)) "jun_sales"
, "sum"((CASE WHEN ("d_moy" = 7) THEN ("cs_sales_price" * "cs_quantity") ELSE 0 END)) "jul_sales"
, "sum"((CASE WHEN ("d_moy" = 8) THEN ("cs_sales_price" * "cs_quantity") ELSE 0 END)) "aug_sales"
, "sum"((CASE WHEN ("d_moy" = 9) THEN ("cs_sales_price" * "cs_quantity") ELSE 0 END)) "sep_sales"
, "sum"((CASE WHEN ("d_moy" = 10) THEN ("cs_sales_price" * "cs_quantity") ELSE 0 END)) "oct_sales"
, "sum"((CASE WHEN ("d_moy" = 11) THEN ("cs_sales_price" * "cs_quantity") ELSE 0 END)) "nov_sales"
, "sum"((CASE WHEN ("d_moy" = 12) THEN ("cs_sales_price" * "cs_quantity") ELSE 0 END)) "dec_sales"
, "sum"((CASE WHEN ("d_moy" = 1) THEN ("cs_net_paid_inc_tax" * "cs_quantity") ELSE 0 END)) "jan_net"
, "sum"((CASE WHEN ("d_moy" = 2) THEN ("cs_net_paid_inc_tax" * "cs_quantity") ELSE 0 END)) "feb_net"
, "sum"((CASE WHEN ("d_moy" = 3) THEN ("cs_net_paid_inc_tax" * "cs_quantity") ELSE 0 END)) "mar_net"
, "sum"((CASE WHEN ("d_moy" = 4) THEN ("cs_net_paid_inc_tax" * "cs_quantity") ELSE 0 END)) "apr_net"
, "sum"((CASE WHEN ("d_moy" = 5) THEN ("cs_net_paid_inc_tax" * "cs_quantity") ELSE 0 END)) "may_net"
, "sum"((CASE WHEN ("d_moy" = 6) THEN ("cs_net_paid_inc_tax" * "cs_quantity") ELSE 0 END)) "jun_net"
, "sum"((CASE WHEN ("d_moy" = 7) THEN ("cs_net_paid_inc_tax" * "cs_quantity") ELSE 0 END)) "jul_net"
, "sum"((CASE WHEN ("d_moy" = 8) THEN ("cs_net_paid_inc_tax" * "cs_quantity") ELSE 0 END)) "aug_net"
, "sum"((CASE WHEN ("d_moy" = 9) THEN ("cs_net_paid_inc_tax" * "cs_quantity") ELSE 0 END)) "sep_net"
, "sum"((CASE WHEN ("d_moy" = 10) THEN ("cs_net_paid_inc_tax" * "cs_quantity") ELSE 0 END)) "oct_net"
, "sum"((CASE WHEN ("d_moy" = 11) THEN ("cs_net_paid_inc_tax" * "cs_quantity") ELSE 0 END)) "nov_net"
, "sum"((CASE WHEN ("d_moy" = 12) THEN ("cs_net_paid_inc_tax" * "cs_quantity") ELSE 0 END)) "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" = 2001)
AND ("t_time" BETWEEN 30838 AND (30838 + 28800))
AND ("sm_carrier" IN ('DHL' , 'BARIAN'))
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" ASC
LIMIT 100
