sql-tests.testcases.tpcds.q02.sql Maven / Gradle / Ivy

Go to download
Show more of this group Show more artifacts with this name
Show all versions of presto-product-tests Show documentation
There is a newer version: 0.289
-- database: presto_tpcds; groups: tpcds; requires: io.prestodb.tempto.fulfillment.table.hive.tpcds.ImmutableTpcdsTablesRequirements
WITH
  wscs AS (
   SELECT
     "sold_date_sk"
   , "sales_price"
   FROM
     (
      SELECT
        "ws_sold_date_sk" "sold_date_sk"
      , "ws_ext_sales_price" "sales_price"
      FROM
        web_sales
   )  
UNION ALL (
      SELECT
        "cs_sold_date_sk" "sold_date_sk"
      , "cs_ext_sales_price" "sales_price"
      FROM
        catalog_sales
   ) ) 
, wswscs AS (
   SELECT
     "d_week_seq"
   , "sum"((CASE WHEN ("d_day_name" = 'Sunday') THEN "sales_price" ELSE null END)) "sun_sales"
   , "sum"((CASE WHEN ("d_day_name" = 'Monday') THEN "sales_price" ELSE null END)) "mon_sales"
   , "sum"((CASE WHEN ("d_day_name" = 'Tuesday') THEN "sales_price" ELSE null END)) "tue_sales"
   , "sum"((CASE WHEN ("d_day_name" = 'Wednesday') THEN "sales_price" ELSE null END)) "wed_sales"
   , "sum"((CASE WHEN ("d_day_name" = 'Thursday') THEN "sales_price" ELSE null END)) "thu_sales"
   , "sum"((CASE WHEN ("d_day_name" = 'Friday') THEN "sales_price" ELSE null END)) "fri_sales"
   , "sum"((CASE WHEN ("d_day_name" = 'Saturday') THEN "sales_price" ELSE null END)) "sat_sales"
   FROM
     wscs
   , date_dim
   WHERE ("d_date_sk" = "sold_date_sk")
   GROUP BY "d_week_seq"
) 
SELECT
  "d_week_seq1"
, "round"(("sun_sales1" / "sun_sales2"), 2)
, "round"(("mon_sales1" / "mon_sales2"), 2)
, "round"(("tue_sales1" / "tue_sales2"), 2)
, "round"(("wed_sales1" / "wed_sales2"), 2)
, "round"(("thu_sales1" / "thu_sales2"), 2)
, "round"(("fri_sales1" / "fri_sales2"), 2)
, "round"(("sat_sales1" / "sat_sales2"), 2)
FROM
  (
   SELECT
     "wswscs"."d_week_seq" "d_week_seq1"
   , "sun_sales" "sun_sales1"
   , "mon_sales" "mon_sales1"
   , "tue_sales" "tue_sales1"
   , "wed_sales" "wed_sales1"
   , "thu_sales" "thu_sales1"
   , "fri_sales" "fri_sales1"
   , "sat_sales" "sat_sales1"
   FROM
     wswscs
   , date_dim
   WHERE ("date_dim"."d_week_seq" = "wswscs"."d_week_seq")
      AND ("d_year" = 2001)
)  y
, (
   SELECT
     "wswscs"."d_week_seq" "d_week_seq2"
   , "sun_sales" "sun_sales2"
   , "mon_sales" "mon_sales2"
   , "tue_sales" "tue_sales2"
   , "wed_sales" "wed_sales2"
   , "thu_sales" "thu_sales2"
   , "fri_sales" "fri_sales2"
   , "sat_sales" "sat_sales2"
   FROM
     wswscs
   , date_dim
   WHERE ("date_dim"."d_week_seq" = "wswscs"."d_week_seq")
      AND ("d_year" = (2001 + 1))
)  z
WHERE ("d_week_seq1" = ("d_week_seq2" - 53))
ORDER BY "d_week_seq1" ASC