few_shot_examples.yaml

examples:
  - question: "What is the total revenue by product category?"
    sql: |
      SELECT dp.category_name, SUM(fo.order_total_usd) AS total_revenue
      FROM fact_orders fo
      JOIN dim_products dp ON fo.product_id = dp.product_id
      WHERE fo.order_status = 'delivered'
      GROUP BY dp.category_name
      ORDER BY total_revenue DESC

  - question: "How many orders were placed each month in 2018?"
    sql: |
      SELECT strftime('%Y-%m', created_at) AS month, COUNT(DISTINCT order_id) AS order_count
      FROM fact_orders
      WHERE strftime('%Y', created_at) = '2018'
      GROUP BY month
      ORDER BY month ASC

  - question: "Which states have the most active customers?"
    sql: |
      SELECT du.state, COUNT(DISTINCT du.user_id) AS active_customers
      FROM dim_users du
      WHERE du.is_active_member = 1
      GROUP BY du.state
      ORDER BY active_customers DESC
      LIMIT 10

  - question: "What is the average review score per product category?"
    sql: |
      SELECT dp.category_name,
             ROUND(AVG(dr.review_score), 2) AS avg_review_score,
             COUNT(dr.review_id) AS review_count
      FROM fact_orders fo
      JOIN dim_products dp ON fo.product_id = dp.product_id
      JOIN dim_reviews dr ON fo.order_id = dr.order_id
      GROUP BY dp.category_name
      ORDER BY avg_review_score DESC

  - question: "Who are the top 10 sellers by total revenue?"
    sql: |
      SELECT ds.seller_id, ds.seller_city, ds.seller_state,
             SUM(fo.order_total_usd) AS total_revenue,
             COUNT(DISTINCT fo.order_id) AS total_orders
      FROM fact_orders fo
      JOIN dim_sellers ds ON fo.seller_id = ds.seller_id
      WHERE fo.order_status = 'delivered'
      GROUP BY ds.seller_id, ds.seller_city, ds.seller_state
      ORDER BY total_revenue DESC
      LIMIT 10

  - question: "What percentage of orders were canceled in each state?"
    sql: |
      SELECT du.state,
             COUNT(DISTINCT fo.order_id) AS total_orders,
             SUM(CASE WHEN fo.order_status = 'canceled' THEN 1 ELSE 0 END) AS canceled_orders,
             ROUND(100.0 * SUM(CASE WHEN fo.order_status = 'canceled' THEN 1 ELSE 0 END)
                   / COUNT(DISTINCT fo.order_id), 2) AS cancel_rate_pct
      FROM fact_orders fo
      JOIN dim_users du ON fo.user_id = du.user_id
      GROUP BY du.state
      ORDER BY cancel_rate_pct DESC

  - question: "Which products have the highest freight cost relative to order value?"
    sql: |
      SELECT dp.category_name,
             dp.product_id,
             ROUND(AVG(fo.freight_value_usd), 2) AS avg_freight,
             ROUND(AVG(fo.order_total_usd), 2) AS avg_order_value,
             ROUND(AVG(fo.freight_value_usd) / NULLIF(AVG(fo.order_total_usd), 0) * 100, 2) AS freight_pct
      FROM fact_orders fo
      JOIN dim_products dp ON fo.product_id = dp.product_id
      GROUP BY dp.product_id, dp.category_name
      HAVING avg_order_value > 0
      ORDER BY freight_pct DESC
      LIMIT 20

  - question: "Find customers who placed more than 3 orders and their total spend"
    sql: |
      SELECT fo.user_id,
             du.city,
             du.state,
             COUNT(DISTINCT fo.order_id) AS order_count,
             ROUND(SUM(fo.order_total_usd), 2) AS total_spend
      FROM fact_orders fo
      JOIN dim_users du ON fo.user_id = du.user_id
      WHERE fo.order_status = 'delivered'
      GROUP BY fo.user_id, du.city, du.state
      HAVING order_count > 3
      ORDER BY total_spend DESC