SQL to Malloy

SELECT
  TIMESTAMP_TRUNC(created_at, DAY) as order_date,
  SUM(sale_price) as total_sale_price,
  COUNT(DISTINCT order_id) as order_count
FROM `malloy-data.ecomm.order_items`
WHERE created_at>=TIMESTAMP('2021-01-01', 'UTC')
  AND created_at<TIMESTAMP('2021-04-01', 'UTC')
  AND status NOT IN ('Returned','Cancelled')
GROUP BY 1
ORDER BY 1 ASC

run: duckdb.table('data/order_items.parquet') -> {
 where: created_at = @2021-Q1, status != 'Cancelled' & 'Returned'
 group_by: order_date is created_at.day
 aggregate:
   total_sale_price is sale_price.sum()            -- names come before definitions
   order_count is count(distinct order_id)
 order_by: order_date asc
}

SELECT
 CASE
   WHEN (100.0*(ii.product_retail_price-ii.cost)) / (NULLIF(ii.product_retail_price,0)) >=55 THEN 'High (over 55%)'
   WHEN (100.0*(ii.product_retail_price-ii.cost)) / (NULLIF(ii.product_retail_price,0))>=45 THEN 'Medium (45% to 55%)'
   ELSE 'Low (up to 45%)' END AS gross_margin_pct_tier,
 SUM(ii.product_retail_price) AS total_retail_price,
 AVG((ii.product_retail_price-ii.cost)) AS avg_gross_margin
FROM `malloy-data.ecomm.inventory_items` AS ii
LEFT JOIN `malloy-data.ecomm.order_items` AS oi
 ON ii.id=oi.inventory_item_id
LEFT JOIN `malloy-data.ecomm.users` AS u
 ON oi.user_id = u.id
WHERE
 oi.status NOT IN ('Returned','Cancelled')
 AND (u.country='USA')
GROUP BY 1
ORDER BY 3 ASC

run: inventory_items is duckdb.table('data/inventory_items.parquet') extend {
  join_one: order_items is duckdb.table('data/order_items.parquet') on id = order_items.inventory_item_id
  join_one: users is duckdb.table('data/users.parquet') on order_items.user_id = users.id
  dimension: 
    gross_margin is (product_retail_price - cost)
    gross_margin_pct is gross_margin / nullif(product_retail_price, 0)
} -> {
  group_by:
    gross_margin_pct_tier is gross_margin_pct ?
      pick 'High (over 55%)' when >= 0.55
      pick 'Medium (45% to 55%)' when >= 0.45
      else 'Low (up to 45%)'
  aggregate:
    total_retail_price is product_retail_price.sum()
    avg_gross_margin is gross_margin.avg()
  where:
    order_items.status != 'Cancelled' & 'Returned',
    users.country = 'USA'
  order_by: avg_gross_margin asc
}

source: users is duckdb.table('data/users.parquet')

source: order_items is duckdb.table('data/order_items.parquet') extend {
  join_one: users on user_id = users.id
  dimension: valid_order is status != 'Cancelled' & 'Returned'
}

source: inventory_items is duckdb.table('inventory_items.parquet') extend {
  join_one: order_items on id = order_items.inventory_item_id
  dimension:
    gross_margin is (product_retail_price - cost)
    gross_margin_pct is gross_margin / nullif(product_retail_price, 0)
    gross_margin_pct_tier is gross_margin_pct ?
      pick 'High (over 55%)' when >= 0.55
      pick 'Medium (45% to 55%)' when >= 0.45
      else 'Low (up to 45%)'
  measure:
    total_retail_price is product_retail_price.sum()
    avg_gross_margin is gross_margin.avg()
}

run: inventory_items -> {
  group_by: gross_margin_pct_tier
  aggregate:
    total_retail_price
    avg_gross_margin
  where: order_items.valid_order, order_items.users.country = 'USA'
  order_by: avg_gross_margin asc
}

WITH user_facts AS (
 SELECT
   user_id as user_id,
   COUNT(1) as lifetime_orders
 FROM `malloy-data.ecomm.order_items`
 GROUP BY 1
 ORDER BY 2 desc
)
SELECT
  CASE WHEN user_facts.lifetime_orders=1 THEN 'One-Time' ELSE 'Repeat' END AS customer_category,
  SUM(order_items.sale_price) AS total_sale_price,
  COUNT(DISTINCT order_items.order_id) AS order_count
FROM `malloy-data.ecomm.order_items` AS order_items
LEFT JOIN user_facts
 ON order_items.user_id = user_facts.user_id
GROUP BY 1
ORDER BY 2 desc

source: user_facts is duckdb.table('data/order_items.parquet') -> {
  group_by: user_id
  aggregate: lifetime_orders is count()
}

source: order_items is duckdb.table('data/order_items.parquet') extend {
  join_one: user_facts on user_id = user_facts.user_id
}

run: order_items -> {
  group_by: customer_category is user_facts.lifetime_orders ?
    pick 'One-Time' when = 1
    else 'Repeat'
  aggregate:
    total_sale_price is sale_price.sum()
    order_count is count(distinct order_id)
}

source: order_facts is bigquery.sql("""
  SELECT
    order_id
    , user_id
    , ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY created_at) as user_order_sequence_number
  FROM malloy-data.ecomm.order_items
""")

source: order_items is bigquery.sql("""
  SELECT
    id
    , order_id
    , created_at
  FROM malloy-data.ecomm.order_items
  WHERE status NOT IN ('Cancelled', 'Returned')
""") extend {
  join_one: order_facts on order_id = order_facts.order_id
  measure: order_count is count(distinct order_id)
}

run: order_items -> {
  group_by: order_facts.user_order_sequence_number
  aggregate: order_count
}

SELECT
  order_facts_0.user_order_sequence_number as user_order_sequence_number,
  count(distinct order_items.order_id) as order_count
FROM (
  SELECT
    id
    , order_id
    , created_at
  FROM malloy-data.ecomm.order_items
  WHERE status NOT IN ('Cancelled', 'Returned')
) as order_items
LEFT JOIN (
  SELECT
    order_id
    , user_id
    , ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY created_at) as user_order_sequence_number
  FROM malloy-data.ecomm.order_items
) AS order_facts_0
  ON order_items.order_id=order_facts_0.order_id
GROUP BY 1
ORDER BY 2 desc