Aggregations

// COUNT - count rows
cook`main:users slay:cnt:*`
// → SELECT COUNT(*) FROM users

// SUM - sum values
cook`main:orders slay:sum:total`
// → SELECT SUM(total) FROM orders

// AVG - average value
cook`main:products slay:avg:price`
// → SELECT AVG(price) FROM products

// MIN / MAX
cook`main:products slay:min:price,max:price`
// → SELECT MIN(price), MAX(price) FROM products

cook`main:orders slay:cnt:*@total_orders,sum:total@revenue,avg:total@avg_order`
// → SELECT COUNT(*) AS total_orders,
//          SUM(total) AS revenue,
//          AVG(total) AS avg_order
//   FROM orders

// Group by single column
cook`main:orders slay:user_id,cnt:* squad:user_id`
// → SELECT user_id, COUNT(*)
//   FROM orders
//   GROUP BY user_id

// Group by multiple columns
cook`main:orders slay:user_id,status,cnt:* squad:user_id,status`
// → SELECT user_id, status, COUNT(*)
//   FROM orders
//   GROUP BY user_id, status

// Users with more than 5 orders
cook`main:orders slay:user_id,cnt:*@order_count squad:user_id tea:cnt:*>5`
// → SELECT user_id, COUNT(*) AS order_count
//   FROM orders
//   GROUP BY user_id
//   HAVING COUNT(*) > 5

// High-value customers (spill the tea on big spenders)
cook`main:orders slay:user_id,sum:total@total_spent squad:user_id tea:sum:total>1000`
// → SELECT user_id, SUM(total) AS total_spent
//   FROM orders
//   GROUP BY user_id
//   HAVING SUM(total) > 1000

// Count unique values
cook`main:orders slay:cnt:user_id/distinct@unique_customers`
// → SELECT COUNT(DISTINCT user_id) AS unique_customers FROM orders

// Per-group distinct count
cook`main:orders slay:status,cnt:user_id/distinct squad:status`
// → SELECT status, COUNT(DISTINCT user_id)
//   FROM orders
//   GROUP BY status

// Sales report by category
cook`main:products
    link:order_items match:products.id=order_items.product_id
    slay:products.category,cnt:*@items_sold,sum:order_items.quantity@total_qty,sum:order_items.price@revenue
    squad:products.category
    tea:sum:order_items.price>500
    vibe:revenue/desc`
// → SELECT products.category,
//          COUNT(*) AS items_sold,
//          SUM(order_items.quantity) AS total_qty,
//          SUM(order_items.price) AS revenue
//   FROM products
//   INNER JOIN order_items ON products.id = order_items.product_id
//   GROUP BY products.category
//   HAVING SUM(order_items.price) > 500
//   ORDER BY revenue DESC