TPC-DS Query 21

tpcds21.malloy

// Malloy Implementation
// TPC-DS Query 21

import "tpcds.malloy"

query: inventory -> {
  declare: 
    inv_before is sum(inv_quantity_on_hand) { where: date_dim.d_date < '2000-03-11' }
    inv_after is sum(inv_quantity_on_hand) { where: date_dim.d_date >= '2000-03-11' }

  group_by:
    warehouse.w_warehouse_name
    item.i_item_id

  aggregate:
    inv_before
    inv_after

  where:
    date_dim.d_date >= '2000-02-10'
    and date_dim.d_date <= '2000-04-10'
    and item.i_current_price >= 0.99
    and item.i_current_price <= 1.49

  having:
    inv_before > 0
    , inv_after / inv_before >= 2.0 / 3.0
    , inv_after / inv_before <= 3.0 / 2.0

  order_by:
    w_warehouse_name
    i_item_id

  limit: 100
}

tpcds21.sql

-- SQL Implementation
-- TPC-DS Query 21

SELECT *
FROM
  (SELECT w_warehouse_name,
          i_item_id,
          sum(CASE
                  WHEN (cast(d_date AS date) < CAST ('2000-03-11' AS date)) THEN inv_quantity_on_hand
                  ELSE 0
              END) AS inv_before,
          sum(CASE
                  WHEN (cast(d_date AS date) >= CAST ('2000-03-11' AS date)) THEN inv_quantity_on_hand
                  ELSE 0
              END) AS inv_after
   FROM inventory,
        warehouse,
        item,
        date_dim
   WHERE i_current_price BETWEEN 0.99 AND 1.49
     AND i_item_sk = inv_item_sk
     AND inv_warehouse_sk = w_warehouse_sk
     AND inv_date_sk = d_date_sk
     AND d_date BETWEEN CAST ('2000-02-10' AS date) AND CAST ('2000-04-10' AS date)
   GROUP BY w_warehouse_name,
            i_item_id) x
WHERE (CASE
           WHEN inv_before > 0 THEN (inv_after*1.000) / inv_before
           ELSE NULL
       END) BETWEEN 2.000/3.000 AND 3.000/2.000
ORDER BY w_warehouse_name NULLS FIRST,
         i_item_id NULLS FIRST
LIMIT 100;