carlineng

import "tpcds.malloy"

source: category_avg is from(
  item -> {
    group_by:
      i_category

    aggregate:
      avg_category_price is avg(i_current_price)
  }

carlineng

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"
}

carlineng

-- Option 1:
-- Multiple sub-queries joined together:
WITH yearly_sales AS (
  SELECT
    YEAR(d_date) AS d_year
    , ROUND(SUM(total_sales), 2) AS yearly_sales
  FROM sales_by_date
  GROUP BY 1
),

carlineng

import "tpcds.malloy"

query: all_sales -> {
  declare:
    catalog_total_sales_2001 is total_sales { where: date_dim.d_year = 2001 and sale_type = 'c'}
    catalog_total_sales_2002 is total_sales { where: date_dim.d_year = 2002 and sale_type = 'c'}
    web_total_sales_2001 is total_sales { where: date_dim.d_year = 2001 and sale_type = 'w'}
    web_total_sales_2002 is total_sales { where: date_dim.d_year = 2002 and sale_type = 'w'}
    store_total_sales_2001 is total_sales { where: date_dim.d_year = 2001 and sale_type = 's'}
    store_total_sales_2002 is total_sales { where: date_dim.d_year = 2002 and sale_type = 's'}

carlineng

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:

carlineng

query: yoy_weekly_sales is weekly_sales {
  join_many: date_dim on d_week_seq = date_dim.d_week_seq
  join_one: weekly_sales on d_week_seq = weekly_sales.d_week_seq - 53
} -> {
  group_by: 
    d_week_seq
    r_sun is sun_sales / weekly_sales.sun_sales
    r_mon is mon_sales / weekly_sales.mon_sales
    r_tue is tue_sales / weekly_sales.tue_sales
    r_wed is wed_sales / weekly_sales.wed_sales

carlineng

import "tpcds.malloy";

query: store_returns -> {
  group_by:
    sr_customer_sk
    sr_store_sk
    customer.c_customer_id
  aggregate: 
    customer_total_returns
    avg_store_return is all(customer_total_returns / count(distinct sr_customer_sk), sr_store_sk)

carlineng

Keybase proof

I hereby claim:

• I am carlineng on github.
• I am carlineng (https://keybase.io/carlineng) on keybase.
• I have a public key ASBnc3twEchumoCv2udoNgq015asXhcF5lI7mA-GNNSj-Ao

To claim this, I am signing this object: