Talk to Your Database
in Plain English

WITH customer_orders AS (
  SELECT customer_id,
    COUNT(*) as total_orders,
    SUM(total) as lifetime_value,
    MAX(created_at) as last_order,
    COUNT(*) FILTER (WHERE created_at >= NOW() - INTERVAL '6 months')
      as orders_last_6mo
  FROM orders GROUP BY customer_id
)
SELECT c.name, c.email,
  co.lifetime_value, co.last_order,
  co.total_orders, co.orders_last_6mo
FROM customers c
JOIN customer_orders co ON c.id = co.customer_id
WHERE co.last_order < NOW() - INTERVAL '90 days'
  AND co.orders_last_6mo >= 3
ORDER BY co.lifetime_value DESC;

WITH cohorts AS (
  SELECT id, DATE_TRUNC('month', created_at) as cohort_month
  FROM users WHERE created_at >= NOW() - INTERVAL '6 months'
),
activity AS (
  SELECT user_id, DATE_TRUNC('month', created_at) as activity_month
  FROM user_sessions GROUP BY 1, 2
)
SELECT c.cohort_month,
  COUNT(DISTINCT c.id) as cohort_size,
  COUNT(DISTINCT CASE WHEN a.activity_month = c.cohort_month
    + INTERVAL '1 month' THEN c.id END) * 100.0
    / COUNT(DISTINCT c.id) as month_1_retention,
  COUNT(DISTINCT CASE WHEN a.activity_month = c.cohort_month
    + INTERVAL '2 months' THEN c.id END) * 100.0
    / COUNT(DISTINCT c.id) as month_2_retention
FROM cohorts c LEFT JOIN activity a ON c.id = a.user_id
GROUP BY c.cohort_month ORDER BY c.cohort_month;

WITH daily_sales AS (
  SELECT oi.product_id,
    SUM(oi.quantity) / 30.0 as avg_daily_sales
  FROM order_items oi
  JOIN orders o ON o.id = oi.order_id
  WHERE o.created_at >= NOW() - INTERVAL '30 days'
  GROUP BY oi.product_id
)
SELECT p.sku, p.name, p.stock_quantity,
  ROUND(ds.avg_daily_sales, 1) as daily_velocity,
  ROUND(p.stock_quantity / NULLIF(ds.avg_daily_sales, 0)) as days_until_stockout
FROM products p
JOIN daily_sales ds ON p.id = ds.product_id
WHERE p.stock_quantity / NULLIF(ds.avg_daily_sales, 0) <= 14
ORDER BY days_until_stockout ASC;