carlineng

-- Prompt:

-- I want help writing a SQL query. I have a database with the two tables:
-- CREATE TABLE order_items (
--   created_at timestamp,
--   sale_price decimal,
--   inventory_item_id integer
-- );
--
-- CREATE TABLE inventory_items (

carlineng

-- Prompt:
-- 
-- I want help writing a SQL query. I have a database with the two tables:
--
-- CREATE TABLE order_items (
--   created_at timestamp,
--   sale_price decimal,
--   inventory_item_id integer
-- );
--

carlineng

-- Using DuckDB SQL dialect
-- Data for these queries can be found here: https://github.com/malloydata/malloy-samples/tree/main/duckdb/ecommerce

-- Question: I want to calculate year-over-year sales for this e-commerce dataset.
-- Then, for each product_department, I want to calculate year-over-year sales in that department,
-- and the contribution of that department to the overall year-over-year sales growth.
-- Then, for each product_category, I want to calculate year-over-year sales for that category,
-- and the contribution of that category to the departmental year-over-year sales growth.
-- How do I do that in SQL?
-- Two approaches. One with window functions, one with GROUP BY ROLLUP.

carlineng

import "model.malloy"

/*

The file `model.malloy` (imported above) is defined as follows:

source: lineitem is table('duckdb:../data/lineitem.parquet') { 
  join_one: 
    orders is table('duckdb:../data/orders.parquet') on orders.o_orderkey = l_orderkey
    customer is table('duckdb:../data/customer.parquet') on customer.c_custkey = orders.o_custkey

carlineng

// 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' }

carlineng

query: all_sales + {
  join_one: manufacturers is from(
    item -> {
      group_by: i_manufact_id
      where: i_category = 'Electronics'
    }
  ) on item.i_manufact_id = manufacturers.i_manufact_id
} -> {
  group_by: item.i_manufact_id
  aggregate: total_ext_sales_price

carlineng

// Malloy Implementation
// TPC-DS Query 4

import "tpcds.malloy"

query: all_sales -> {
  declare:
    catalog_total_sales_2001 is total_sales { where: date_dim.d_year = 2001 and channel_category = 'catalog channel'}
    catalog_total_sales_2002 is total_sales { where: date_dim.d_year = 2002 and channel_category = 'catalog channel'}
    web_total_sales_2001 is total_sales { where: date_dim.d_year = 2001 and channel_category = 'web channel'}

carlineng

import "tpcds.malloy"

query: store_sales + {
  query: margin_agg is {
    aggregate:
      gross_margin is total_profit / total_ext_sales
  }
} -> {
  nest: margin_agg
  nest: by_category is {

carlineng

SELECT 
  YEAR(d_date) AS d_year
  , MONTH(d_date) AS d_month
  , ROUND(SUM(total_sales), 2) AS total_sales
FROM sales_by_date 
GROUP BY ROLLUP (d_year, d_month)
ORDER BY d_year NULLS FIRST, d_month NULLS FIRST

carlineng

import "tpcds.malloy"

query: all_sales -> {
  group_by:
    item.i_item_id
  
  aggregate:
    avg_quantity
    avg_list_price
    avg_coupon_amt