TPC-DS Query 5

q05.malloy

import "tpcds.malloy"

sql: daily_profit_loss_sql is {
  select: """
  SELECT * FROM (%{ all_sales -> daily_profit_loss }%)
  UNION ALL
  SELECT * FROM (%{ all_returns -> daily_profit_loss }%)
  """
  connection: "duckdb"
}

source: daily_profit_loss is from_sql(daily_profit_loss_sql) {
  measure: total_sales is sum(sales)
  measure: total_returns is sum(return_amt)
  measure: net_profit is sum(total_profit) - sum(net_loss)

  query: by_category is {
    group_by: channel_category
    aggregate:
      total_sales
      total_returns
      net_profit
    order_by: channel_category
  }

  query: by_channel_id is {
    group_by: channel_id
    aggregate:
      total_sales
      total_returns
      net_profit
    order_by: channel_id
    limit: 100
  }
}

query: daily_profit_loss -> {

  aggregate:
    total_sales
    total_returns
    net_profit

  nest: by_category + {
    nest: by_channel_id
    order_by: channel_category
  }

  where:
    d_date >= '2000-08-23' and d_date <= '2000-09-06'
}

q05.sql

WITH ssr AS
  (SELECT s_store_id,
          sum(sales_price) AS sales,
          sum(profit) AS profit,
          sum(return_amt) AS returns_,
          sum(net_loss) AS profit_loss
   FROM
     (SELECT ss_store_sk AS store_sk,
             ss_sold_date_sk AS date_sk,
             ss_ext_sales_price AS sales_price,
             ss_net_profit AS profit,
             cast(0 AS decimal(7,2)) AS return_amt,
             cast(0 AS decimal(7,2)) AS net_loss
      FROM store_sales
      UNION ALL SELECT sr_store_sk AS store_sk,
                       sr_returned_date_sk AS date_sk,
                       cast(0 AS decimal(7,2)) AS sales_price,
                       cast(0 AS decimal(7,2)) AS profit,
                       sr_return_amt AS return_amt,
                       sr_net_loss AS net_loss
      FROM store_returns ) salesreturns,
        date_dim,
        store
   WHERE date_sk = d_date_sk
     AND d_date BETWEEN cast('2000-08-23' AS date) AND cast('2000-09-06' AS date)
     AND store_sk = s_store_sk
   GROUP BY s_store_id) ,
     csr AS
  (SELECT cp_catalog_page_id,
          sum(sales_price) AS sales,
          sum(profit) AS profit,
          sum(return_amt) AS returns_,
          sum(net_loss) AS profit_loss
   FROM
     (SELECT cs_catalog_page_sk AS page_sk,
             cs_sold_date_sk AS date_sk,
             cs_ext_sales_price AS sales_price,
             cs_net_profit AS profit,
             cast(0 AS decimal(7,2)) AS return_amt,
             cast(0 AS decimal(7,2)) AS net_loss
      FROM catalog_sales
      UNION ALL SELECT cr_catalog_page_sk AS page_sk,
                       cr_returned_date_sk AS date_sk,
                       cast(0 AS decimal(7,2)) AS sales_price,
                       cast(0 AS decimal(7,2)) AS profit,
                       cr_return_amount AS return_amt,
                       cr_net_loss AS net_loss
      FROM catalog_returns ) salesreturns,
        date_dim,
        catalog_page
   WHERE date_sk = d_date_sk
     AND d_date BETWEEN cast('2000-08-23' AS date) AND cast('2000-09-06' AS date)
     AND page_sk = cp_catalog_page_sk
   GROUP BY cp_catalog_page_id) ,
     wsr AS
  (SELECT web_site_id,
          sum(sales_price) AS sales,
          sum(profit) AS profit,
          sum(return_amt) AS returns_,
          sum(net_loss) AS profit_loss
   FROM
     (SELECT ws_web_site_sk AS wsr_web_site_sk,
             ws_sold_date_sk AS date_sk,
             ws_ext_sales_price AS sales_price,
             ws_net_profit AS profit,
             cast(0 AS decimal(7,2)) AS return_amt,
             cast(0 AS decimal(7,2)) AS net_loss
      FROM web_sales
      UNION ALL SELECT ws_web_site_sk AS wsr_web_site_sk,
                       wr_returned_date_sk AS date_sk,
                       cast(0 AS decimal(7,2)) AS sales_price,
                       cast(0 AS decimal(7,2)) AS profit,
                       wr_return_amt AS return_amt,
                       wr_net_loss AS net_loss
      FROM web_returns
      LEFT OUTER JOIN web_sales ON (wr_item_sk = ws_item_sk
                                    AND wr_order_number = ws_order_number) ) salesreturns,
        date_dim,
        web_site
   WHERE date_sk = d_date_sk
     AND d_date BETWEEN cast('2000-08-23' AS date) AND cast('2000-09-06' AS date)
     AND wsr_web_site_sk = web_site_sk
   GROUP BY web_site_id)
SELECT channel ,
       id ,
       sum(sales) AS sales ,
       sum(returns_) AS returns_ ,
       sum(profit) AS profit
FROM
  (SELECT 'store channel' AS channel ,
          concat('store', s_store_id) AS id ,
          sales ,
          returns_ ,
          (profit - profit_loss) AS profit
   FROM ssr
   UNION ALL SELECT 'catalog channel' AS channel ,
                    concat('catalog_page', cp_catalog_page_id) AS id ,
                    sales ,
                    returns_ ,
                    (profit - profit_loss) AS profit
   FROM csr
   UNION ALL SELECT 'web channel' AS channel ,
                    concat('web_site', web_site_id) AS id ,
                    sales ,
                    returns_ ,
                    (profit - profit_loss) AS profit
   FROM wsr ) x
GROUP BY ROLLUP (channel,
                 id)
ORDER BY channel NULLS FIRST,
         id NULLS FIRST
LIMIT 100;

sales_and_returns.malloy

source: web_sales is table('duckdb:../data/web_sales.parquet') {
  join_one: date_dim with ws_sold_date_sk
  join_one: customer with ws_bill_customer_sk
  join_one: web_site with ws_web_site_sk

  dimension: channel_category is 'web channel'
  dimension: channel_id is concat('web_site', web_site.web_site_id)

  measure: total_ext_sales is sum(ws_ext_sales_price)
  measure: total_profit is sum(ws_net_profit)

  query: projection is {
    project:
      channel_category
      channel_id
      sold_date_sk is ws_sold_date_sk
      sold_time_sk is ws_sold_time_sk
      item_sk is ws_item_sk
      customer_sk is ws_bill_customer_sk 
      cdemo_sk is ws_bill_cdemo_sk
      promo_sk is ws_promo_sk
      quantity is ws_quantity
      ext_sales_price is ws_ext_sales_price
      wholesale_cost is ws_wholesale_cost
      list_price is ws_list_price
      sales_price is ws_sales_price
      ext_discount_amt is ws_ext_discount_amt
      ext_wholesale_cost is ws_ext_wholesale_cost
      ext_list_price is ws_ext_list_price
      ext_tax is ws_ext_tax
      coupon_amt is ws_coupon_amt
      net_paid is ws_net_paid
      net_paid_inc_tax is ws_net_paid_inc_tax
      net_profit is ws_net_profit

    where: web_site.web_site_id != null
  }
}

source: web_returns is table('duckdb:../data/web_returns.parquet') {
  join_one: date_dim with wr_returned_date_sk
  join_one: customer with wr_refunded_customer_sk
  join_one: web_sales on wr_item_sk = web_sales.ws_item_sk and wr_order_number = web_sales.ws_order_number

  measure: total_returns is sum(wr_return_amt)
  measure: total_loss is sum(wr_net_loss)
  measure: avg_returns is avg(wr_return_amt)

  dimension: channel_category is 'web channel'
  dimension: channel_id is concat('web_site', web_sales.web_site.web_site_id)

  query: projection is {
    project:
      channel_category
      channel_id
      customer_sk is wr_refunded_customer_sk
      returned_date_sk is wr_returned_date_sk
      return_amt is wr_return_amt
      net_loss is wr_net_loss

    where: web_sales.web_site.web_site_id != null
  }
}

union_all_model.malloy

sql: all_sales_sql is {
  select: """
  SELECT * FROM (%{ web_sales -> projection }%)
  UNION ALL
  SELECT * FROM (%{ catalog_sales -> projection }%)
  UNION ALL
  SELECT * FROM (%{ store_sales -> projection }%)
  """
  connection: "duckdb"
}

sql: all_returns_sql is {
  select: """
  SELECT * FROM (%{ web_returns -> projection }%)
  UNION ALL
  SELECT * FROM (%{ catalog_returns -> projection }%)
  UNION ALL
  SELECT * FROM (%{ store_returns -> projection }%)
  """
  connection: "duckdb"
}

source: all_sales is from_sql(all_sales_sql) {
  join_one: date_dim with sold_date_sk
  join_one: customer_demographics with cdemo_sk
  join_one: customer with customer_sk
  join_one: item with item_sk
  join_one: promotion with promo_sk

  measure: total_sales is sum((ext_list_price - ext_wholesale_cost - ext_discount_amt + ext_sales_price) / 2)
  measure: total_ext_sales_price is sum(ext_sales_price)
  measure: total_profit is sum(net_profit)
  measure: avg_quantity is avg(quantity)
  measure: avg_list_price is avg(list_price)
  measure: avg_coupon_amt is avg(coupon_amt)
  measure: avg_sales_price is avg(sales_price)

  query: daily_profit_loss is {
    group_by:
      channel_category
      channel_id
      date_dim.d_date

    aggregate:
      sales is total_ext_sales_price
      total_profit
      return_amt is 0
      net_loss is 0
  }
}

source: all_returns is from_sql(all_returns_sql) {
  join_one: date_dim with returned_date_sk
  join_one: customer with customer_sk

  measure: total_returns is sum(return_amt)
  measure: total_loss is sum(net_loss)

  query: daily_profit_loss is {
    group_by:
      channel_category
      channel_id
      date_dim.d_date

    aggregate:
      sales is 0
      total_profit is 0
      return_amt is total_returns
      net_loss is total_loss
  }
}