hive-testbench/spark-queries-tpcds/q71.sql

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 AS ext_price,
     ws_sold_date_sk AS sold_date_sk,
     ws_item_sk AS sold_item_sk,
     ws_sold_time_sk AS 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 AS ext_price,
     cs_sold_date_sk AS sold_date_sk,
     cs_item_sk AS sold_item_sk,
     cs_sold_time_sk AS 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 AS ext_price,
     ss_sold_date_sk AS sold_date_sk,
     ss_item_sk AS sold_item_sk,
     ss_sold_time_sk AS time_sk
   FROM store_sales, date_dim
   WHERE d_date_sk = ss_sold_date_sk
     AND d_moy = 11
     AND d_year = 1999
  ) AS 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, brand_id