TPC-DS Query 3

q03.malloy

query: store_sales -> {
  group_by: 
    d_year is date_dim.d_year
    brand_id is item.i_brand_id
    brand is item.i_brand

  aggregate:
    total_sales_price

  where:
    item.i_manufact_id = 128
    and date_dim.d_moy = 11

  order_by: d_year, total_sales_price DESC, brand_id
  limit: 100
}

q03.sql

SELECT dt.d_year,
       item.i_brand_id brand_id,
       item.i_brand brand,
       sum(ss_ext_sales_price) sum_agg
FROM date_dim dt,
     store_sales,
     item
WHERE dt.d_date_sk = store_sales.ss_sold_date_sk
  AND store_sales.ss_item_sk = item.i_item_sk
  AND item.i_manufact_id = 128
  AND dt.d_moy=11
GROUP BY dt.d_year,
         item.i_brand,
         item.i_brand_id
ORDER BY dt.d_year,
         sum_agg DESC,
         brand_id
LIMIT 100;

tpcds.malloy

source: store_sales is table('duckdb:../data/store_sales.parquet') {
  join_one: date_dim on ss_sold_date_sk = date_dim.d_date_sk
  join_one: item on ss_item_sk = item.i_item_sk

  measure: total_sales_price is sum(ss_ext_sales_price)
}