sql-tests.testcases.tpcds.q71.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
SELECT
  "i_brand_id" "brand_id"
, "i_brand" "brand"
, "t_hour"
, "t_minute"
, "sum"("ext_price") "ext_price"
FROM
  item
, (
   SELECT
     "ws_ext_sales_price" "ext_price"
   , "ws_sold_date_sk" "sold_date_sk"
   , "ws_item_sk" "sold_item_sk"
   , "ws_sold_time_sk" "time_sk"
   FROM
     web_sales
   , date_dim
   WHERE ("d_date_sk" = "ws_sold_date_sk")
      AND ("d_moy" = 11)
      AND ("d_year" = 1999)
UNION ALL    SELECT
     "cs_ext_sales_price" "ext_price"
   , "cs_sold_date_sk" "sold_date_sk"
   , "cs_item_sk" "sold_item_sk"
   , "cs_sold_time_sk" "time_sk"
   FROM
     catalog_sales
   , date_dim
   WHERE ("d_date_sk" = "cs_sold_date_sk")
      AND ("d_moy" = 11)
      AND ("d_year" = 1999)
UNION ALL    SELECT
     "ss_ext_sales_price" "ext_price"
   , "ss_sold_date_sk" "sold_date_sk"
   , "ss_item_sk" "sold_item_sk"
   , "ss_sold_time_sk" "time_sk"
   FROM
     store_sales
   , date_dim
   WHERE ("d_date_sk" = "ss_sold_date_sk")
      AND ("d_moy" = 11)
      AND ("d_year" = 1999)
)  tmp
, time_dim
WHERE ("sold_item_sk" = "i_item_sk")
   AND ("i_manager_id" = 1)
   AND ("time_sk" = "t_time_sk")
   AND (("t_meal_time" = 'breakfast')
      OR ("t_meal_time" = 'dinner'))
GROUP BY "i_brand", "i_brand_id", "t_hour", "t_minute"
ORDER BY "ext_price" DESC, "i_brand_id" ASC