-- database: presto_tpcds; groups: tpcds; requires: com.teradata.tempto.fulfillment.table.hive.tpcds.ImmutableTpcdsTablesRequirements
WITH
  cross_items AS (
   SELECT "i_item_sk" "ss_item_sk"
   FROM
     item
   , (
      SELECT
        "iss"."i_brand_id" "brand_id"
      , "iss"."i_class_id" "class_id"
      , "iss"."i_category_id" "category_id"
      FROM
        store_sales
      , item iss
      , date_dim d1
      WHERE ("ss_item_sk" = "iss"."i_item_sk")
         AND ("ss_sold_date_sk" = "d1"."d_date_sk")
         AND ("d1"."d_year" BETWEEN 1999 AND (1999 + 2))
INTERSECT       SELECT
        "ics"."i_brand_id"
      , "ics"."i_class_id"
      , "ics"."i_category_id"
      FROM
        catalog_sales
      , item ics
      , date_dim d2
      WHERE ("cs_item_sk" = "ics"."i_item_sk")
         AND ("cs_sold_date_sk" = "d2"."d_date_sk")
         AND ("d2"."d_year" BETWEEN 1999 AND (1999 + 2))
INTERSECT       SELECT
        "iws"."i_brand_id"
      , "iws"."i_class_id"
      , "iws"."i_category_id"
      FROM
        web_sales
      , item iws
      , date_dim d3
      WHERE ("ws_item_sk" = "iws"."i_item_sk")
         AND ("ws_sold_date_sk" = "d3"."d_date_sk")
         AND ("d3"."d_year" BETWEEN 1999 AND (1999 + 2))
   ) 
   WHERE ("i_brand_id" = "brand_id")
      AND ("i_class_id" = "class_id")
      AND ("i_category_id" = "category_id")
) 
, avg_sales AS (
   SELECT "avg"(("quantity" * "list_price")) "average_sales"
   FROM
     (
      SELECT
        "ss_quantity" "quantity"
      , "ss_list_price" "list_price"
      FROM
        store_sales
      , date_dim
      WHERE ("ss_sold_date_sk" = "d_date_sk")
         AND ("d_year" BETWEEN 1999 AND (1999 + 2))
UNION ALL       SELECT
        "cs_quantity" "quantity"
      , "cs_list_price" "list_price"
      FROM
        catalog_sales
      , date_dim
      WHERE ("cs_sold_date_sk" = "d_date_sk")
         AND ("d_year" BETWEEN 1999 AND (1999 + 2))
UNION ALL       SELECT
        "ws_quantity" "quantity"
      , "ws_list_price" "list_price"
      FROM
        web_sales
      , date_dim
      WHERE ("ws_sold_date_sk" = "d_date_sk")
         AND ("d_year" BETWEEN 1999 AND (1999 + 2))
   )  x
) 
SELECT
  "channel"
, "i_brand_id"
, "i_class_id"
, "i_category_id"
, "sum"("sales")
, "sum"("number_sales")
FROM
  (
   SELECT
     'store' "channel"
   , "i_brand_id"
   , "i_class_id"
   , "i_category_id"
   , "sum"(("ss_quantity" * "ss_list_price")) "sales"
   , "count"(*) "number_sales"
   FROM
     store_sales
   , item
   , date_dim
   WHERE ("ss_item_sk" IN (
      SELECT "ss_item_sk"
      FROM
        cross_items
   ))
      AND ("ss_item_sk" = "i_item_sk")
      AND ("ss_sold_date_sk" = "d_date_sk")
      AND ("d_year" = (1999 + 2))
      AND ("d_moy" = 11)
   GROUP BY "i_brand_id", "i_class_id", "i_category_id"
   HAVING ("sum"(("ss_quantity" * "ss_list_price")) > (
         SELECT "average_sales"
         FROM
           avg_sales
      ))
UNION ALL    SELECT
     'catalog' "channel"
   , "i_brand_id"
   , "i_class_id"
   , "i_category_id"
   , "sum"(("cs_quantity" * "cs_list_price")) "sales"
   , "count"(*) "number_sales"
   FROM
     catalog_sales
   , item
   , date_dim
   WHERE ("cs_item_sk" IN (
      SELECT "ss_item_sk"
      FROM
        cross_items
   ))
      AND ("cs_item_sk" = "i_item_sk")
      AND ("cs_sold_date_sk" = "d_date_sk")
      AND ("d_year" = (1999 + 2))
      AND ("d_moy" = 11)
   GROUP BY "i_brand_id", "i_class_id", "i_category_id"
   HAVING ("sum"(("cs_quantity" * "cs_list_price")) > (
         SELECT "average_sales"
         FROM
           avg_sales
      ))
UNION ALL    SELECT
     'web' "channel"
   , "i_brand_id"
   , "i_class_id"
   , "i_category_id"
   , "sum"(("ws_quantity" * "ws_list_price")) "sales"
   , "count"(*) "number_sales"
   FROM
     web_sales
   , item
   , date_dim
   WHERE ("ws_item_sk" IN (
      SELECT "ss_item_sk"
      FROM
        cross_items
   ))
      AND ("ws_item_sk" = "i_item_sk")
      AND ("ws_sold_date_sk" = "d_date_sk")
      AND ("d_year" = (1999 + 2))
      AND ("d_moy" = 11)
   GROUP BY "i_brand_id", "i_class_id", "i_category_id"
   HAVING ("sum"(("ws_quantity" * "ws_list_price")) > (
         SELECT "average_sales"
         FROM
           avg_sales
      ))
)  y
GROUP BY ROLLUP (channel, i_brand_id, i_class_id, i_category_id)
ORDER BY "channel" ASC, "i_brand_id" ASC, "i_class_id" ASC, "i_category_id" ASC
LIMIT 100
