TPC-DS Query 7

q07.malloy

import "tpcds.malloy"

query: all_sales -> {
  group_by:
    item.i_item_id
  
  aggregate:
    avg_quantity
    avg_list_price
    avg_coupon_amt
    avg_sales_price

  where:
    channel_category = 'store channel'
    and customer_demographics.cd_gender = 'M'
    and customer_demographics.cd_marital_status = 'S'
    and customer_demographics.cd_education_status = 'College'
    and (promotion.p_channel_email = 'N' or promotion.p_channel_event = 'N')
    and date_dim.d_year = 2000

  order_by:
    i_item_id

  limit: 100
}

q07.sql

SELECT i_item_id,
       avg(ss_quantity) agg1,
       avg(ss_list_price) agg2,
       avg(ss_coupon_amt) agg3,
       avg(ss_sales_price) agg4
FROM store_sales,
     customer_demographics,
     date_dim,
     item,
     promotion
WHERE ss_sold_date_sk = d_date_sk
  AND ss_item_sk = i_item_sk
  AND ss_cdemo_sk = cd_demo_sk
  AND ss_promo_sk = p_promo_sk
  AND cd_gender = 'M'
  AND cd_marital_status = 'S'
  AND cd_education_status = 'College'
  AND (p_channel_email = 'N'
       OR p_channel_event = 'N')
  AND d_year = 2000
GROUP BY i_item_id
ORDER BY i_item_id
LIMIT 100;