q1.malloy

import "tpcds.malloy";

query: store_returns -> {
  group_by:
    sr_customer_sk
    sr_store_sk
    customer.c_customer_id
  aggregate: 
    customer_total_returns
    avg_store_return is all(customer_total_returns / count(distinct sr_customer_sk), sr_store_sk)
  where:
    date_dim.d_year = 2000
    and store.s_state = 'TN'
    and sr_return_amt != null
} -> {
  project:
    c_customer_id
  where: 
    customer_total_returns > avg_store_return * 1.2
  order_by:
    c_customer_id
  limit: 100
}

tpcds.malloy

// Dimensions

source: customer is table('duckdb:../data/customer.parquet') {
  primary_key: c_customer_sk
}

source: date_dim is table('duckdb:../data/date_dim.parquet') {
  primary_key: d_date_sk
}

source: store is table('duckdb:../data/store.parquet') {
  primary_key: s_store_sk
}

// Facts

source: store_returns is table('duckdb:../data/store_returns.parquet') {
  join_one: date_dim on sr_returned_date_sk = date_dim.d_date_sk
  join_one: store on sr_store_sk = store.s_store_sk
  join_one: customer on sr_customer_sk = customer.c_customer_sk

  measure: customer_total_returns is sum(sr_return_amt)
  measure: avg_returns is avg(sr_return_amt)
}

tpcds_q01.sql

WITH customer_total_return AS
  (SELECT sr_customer_sk AS ctr_customer_sk,
          sr_store_sk AS ctr_store_sk,
          sum(sr_return_amt) AS ctr_total_return
   FROM store_returns,
        date_dim
   WHERE sr_returned_date_sk = d_date_sk
     AND d_year = 2000
   GROUP BY sr_customer_sk,
            sr_store_sk)
SELECT c_customer_id
FROM customer_total_return ctr1,
     store,
     customer
WHERE ctr1.ctr_total_return >
    (SELECT avg(ctr_total_return)*1.2
     FROM customer_total_return ctr2
     WHERE ctr1.ctr_store_sk = ctr2.ctr_store_sk)
  AND s_store_sk = ctr1.ctr_store_sk
  AND s_state = 'TN'
  AND ctr1.ctr_customer_sk = c_customer_sk
ORDER BY c_customer_id
LIMIT 100;