bigwide_vs_tree.malloynb

>>>markdown
# Big Wide Table vs Tree
SQL (and LookML) present joined data as a _Big Wide Table_.  Each alias joined tables is prestented as an alias.
>>>sql
\>>>sql
SELECT
  *
FROM 'data/ecommerce/orders.parquet' as orders
LEFT JOIN 'data/ecommerce/items.parquet' as items 
  ON orders.order_id=items.order_id
LEFT JOIN 'data/ecommerce/products.parquet' as products 
  ON items.product_id = products.product_id
LEFT JOIN 'data/ecommerce/users.parquet' as users
  ON orders.user_id = users.user_id
GROUP BY 1
order by 2 desc
>>>sql
\>>>sql
SELECT
  products.brand,
  count(distinct orders.order_id) as order_count,
  count(distinct items.item_id) as item_count,
  sum(items.sale_price) as total_sales,
  count(distinct users.user_id) as user_count
FROM 'data/ecommerce/orders.parquet' as orders
LEFT JOIN 'data/ecommerce/items.parquet' as items 
  ON orders.order_id=items.order_id
LEFT JOIN 'data/ecommerce/products.parquet' as products 
  ON items.product_id = products.product_id
LEFT JOIN 'data/ecommerce/users.parquet' as users
  ON orders.user_id = users.user_id
GROUP BY 1
order by 2 desc
limit 10
>>>malloy
source: products is duckdb.table('data/ecommerce/products.parquet')
source: users is duckdb.table('data/ecommerce/users.parquet')
source: inventory_items is duckdb.table('data/ecommerce/inventory_items.parquet')
source: items is duckdb.table('data/ecommerce/items.parquet') {
  join_one: products on product_id = products.product_id
  join_one: inventory_items on inventory_item_id = inventory_items.inventory_item_id
}
source: orders is duckdb.table('data/ecommerce/orders.parquet') {
  join_many: items on order_id = items.order_id
  join_one: users on user_id = users.user_id
}
>>>malloy

run: orders -> {
  group_by: 
    items.products.brand
  aggregate:
    order_count is count()
    item_count is items.count()
    total_sales is items.sale_price.sum()
    user_count is users.count()
  limit: 10
}
>>>malloy
run: orders -> {
  group_by: 
    items.products.brand
    items.products.category
  aggregate:
    order_count is count()
    item_count is items.count()
    total_sales is items.sale_price.sum()
    user_count is users.count()
    average_age is users.age.avg()
    average_cost is items.inventory_items.cost.avg()
  limit: 10
}
>>>malloy
run: orders -> {
  group_by: order_month is created_at.month
  aggregate:
    order_count is count()
    item_count is items.count()
    total_sales is items.sale_price.sum()
    user_count is users.count()
}