TPC-DS Query 6

q06.malloy

import "tpcds.malloy"

source: category_avg is from(
  item -> {
    group_by:
      i_category

    aggregate:
      avg_category_price is avg(i_current_price)
  }
) {}

query: all_sales_with_category_avg is all_sales {
  join_one: category_avg on item.i_category = category_avg.i_category
} -> {
  group_by:
    customer.customer_address.ca_state

  where:
    date_dim.d_year = 2001
    and date_dim.d_moy = 1
    and item.i_current_price > 1.2 * category_avg.avg_category_price
    and channel_category = 'store channel'
    and customer.c_customer_sk != null

  aggregate:
    cnt is count(*)

  having:
    count(*) >= 10

  order_by:
    cnt
    ca_state
}

q06.refactor.malloy

import "tpcds.malloy"

query: all_sales -> {
  group_by: 
    item.i_category
    item.i_current_price
    item.i_item_id
    customer.customer_address.ca_state

  aggregate:
    avg_price is all(item.i_current_price.avg(), i_category)
    cnt is count(*)

  where:
    date_dim.d_year = 2001
    and date_dim.d_moy = 1
    and channel_category = 'store channel'
    and customer.c_customer_sk != null
} -> {
  group_by: ca_state
  aggregate: cnt is cnt.sum()
  where: i_current_price > avg_price * 1.2 
  having: cnt.sum() > 10
  order_by: 
    cnt
    ca_state
}

q06.sql

SELECT a.ca_state state,
       count(*) cnt
FROM customer_address a ,
     customer c ,
     store_sales s ,
     date_dim d ,
     item i
WHERE a.ca_address_sk = c.c_current_addr_sk
  AND c.c_customer_sk = s.ss_customer_sk
  AND s.ss_sold_date_sk = d.d_date_sk
  AND s.ss_item_sk = i.i_item_sk
  AND d.d_month_seq =
    (SELECT DISTINCT (d_month_seq)
     FROM date_dim
     WHERE d_year = 2001
       AND d_moy = 1 )
  AND i.i_current_price > 1.2 *
    (SELECT avg(j.i_current_price)
     FROM item j
     WHERE j.i_category = i.i_category)
GROUP BY a.ca_state
HAVING count(*) >= 10
ORDER BY cnt NULLS FIRST,
         a.ca_state NULLS FIRST
LIMIT 100;