Build your first AI agent

1. Metrics Metric 1 Name: Total Revenue Definition: Total revenue generated from completed orders. Revenue already accounts for quantity and discounts. Select related tables or views: orders, order_items Calculation: SUM(order_items.line_revenue) WHERE orders.order_status = 'completed' Metric 2 Name: Top-Selling Product Definition: Product with the highest number of units sold from completed orders. Select related tables or views: products, order_items, orders Calculation: SUM(order_items.quantity) WHERE orders.order_status = 'completed' GROUP BY products.product_id ORDER BY SUM(order_items.quantity) DESC LIMIT 1 Metric 3 Name: Average Order Value Definition: Average revenue generated per completed order. Select related tables or views: orders, order_items Calculation: SUM(order_items.line_revenue) / COUNT(DISTINCT orders.order_id) WHERE orders.order_status = 'completed' 2. Business Rules Rule 1 Rule: Only completed orders should be included in revenue, units sold, and product/customer rankings. Affected tables: orders, order_items Rule 2 Rule: When the user says "top-selling product," interpret it as the product with the highest units sold. If they ask for "top product by revenue," rank by revenue instead. Affected tables: products, orders, order_items 3. Common Gotchas Issue 1 Issue: Revenue gets calculated using product price instead of the actual order revenue. Select tables or views affected: products, order_items Solution: Use order_items.line_revenue because it already includes quantity and discounts. Issue 2 Issue: Order count becomes inflated when joining orders with order items. Select tables or views affected: orders, order_items Solution: Use COUNT(DISTINCT orders.order_id) when counting orders. 4. Proven Queries Proven Query 1 Name: Top Customers by Revenue Description: Returns customers ranked by their total revenue from completed orders. SQL/query to use as a proven example: SELECT c.customer_id, c.first_name || ' ' || c.last_name AS customer_name, c.company_name, SUM(oi.line_revenue) AS total_revenue FROM customers c JOIN orders o ON o.customer_id = c.customer_id JOIN order_items oi ON oi.order_id = o.order_id WHERE o.order_status = 'completed' GROUP BY c.customer_id, customer_name, c.company_name ORDER BY total_revenue DESC LIMIT 5; Proven Query 2 Name: Top-Selling Products Description: Returns products ranked by units sold from completed orders. SQL/query to use as a proven example: SELECT p.product_id, p.product_name, p.category, SUM(oi.quantity) AS units_sold FROM products p JOIN order_items oi ON oi.product_id = p.product_id JOIN orders o ON o.order_id = oi.order_id WHERE o.order_status = 'completed' GROUP BY p.product_id, p.product_name, p.category ORDER BY units_sold DESC LIMIT 10; Proven Query 3 Name: Monthly Revenue Description: Shows total revenue by month from completed orders. SQL/query to use as a proven example: SELECT DATE_TRUNC('month', o.order_date) AS month, SUM(oi.line_revenue) AS revenue FROM orders o JOIN order_items oi ON oi.order_id = o.order_id WHERE o.order_status = 'completed' GROUP BY month ORDER BY month;