examples.yaml

- input: "I'm trying to figure out which agents are the busiest. Can you show me like the top few agents who have a lot on their plate?"
  reformulated_query: "Show me the top 5 agents with the highest workload, including their names, emails, and specializations. Use Tables: agents"
  sql_query: |
    SELECT name, email, specialization, current_workload
    FROM agents
    ORDER BY current_workload DESC
    LIMIT 5;

- input: "I need to get an overview of our classes. Could you pull up a list that shows what each class is about and which client it's for?"
  reformulated_query: "List all classes with their subjects, client names, and the number of learners in each class, sorted by the number of learners in descending order. Use Tables: clients, learners, classes"
  sql_query: |
    SELECT c.id AS class_id, c.subject, cl.name AS client_name, COUNT(l.id) AS learner_count
    FROM classes c
    JOIN clients cl ON c.client_id = cl.id
    LEFT JOIN learners l ON c.id = l.class_id
    GROUP BY c.id, c.subject, cl.name
    ORDER BY learner_count DESC;

- input: "Can you show me the list of agents who are available next week?"
  reformulated_query: "List all agents with their names and contact information who are available next week. Use Tables: agents, agent_availability"
  sql_query: |
    SELECT a.name, a.email, a.phone
    FROM agents a
    JOIN agent_availability aa ON a.id = aa.agent_id
    WHERE aa.available_date BETWEEN CURRENT_DATE + INTERVAL '7 days' AND CURRENT_DATE + INTERVAL '14 days'
      AND aa.availability_status = 'available';

- input: "Which clients have classes starting next month and how many learners are enrolled in each class?"
  reformulated_query: "List all clients with classes starting next month, including the client name, class subject, start date, and the number of learners enrolled in each class. Use Tables: clients, classes, learners"
  sql_query: |
    SELECT cl.name AS client_name, c.subject AS class_subject, c.start_date, COUNT(l.id) AS learner_count
    FROM clients cl
      JOIN classes c ON cl.id = c.client_id
      LEFT JOIN learners l ON c.id = l.class_id
    WHERE c.start_date >= date_trunc('month', CURRENT_DATE) + INTERVAL '1 month'
      AND c.start_date < date_trunc('month', CURRENT_DATE) + INTERVAL '2 months'
    GROUP BY cl.name, c.subject, c.start_date
    ORDER BY cl.name, c.start_date;

- input: "Show me all the tasks that are overdue and assigned to 'John Doe'."
  reformulated_query: "List all tasks assigned to 'John Doe' that have a due date before today and are not marked as 'completed', including the task description and due date. Use Tables: tasks"
  sql_query: |
    SELECT description, due_date
    FROM tasks
    WHERE assigned_to = 'John Doe'
      AND due_date < CURRENT_DATE
      AND status != 'completed';

- input: "I want to see the progression levels of learners in the history class."
  reformulated_query: "List all learners in the history class, including their names and progression levels. Use Tables: learners, progressions, classes"
  sql_query: |
    SELECT l.name AS learner_name, p.progression_level
    FROM classes c
    JOIN learners l ON c.id = l.class_id
    JOIN progressions p ON l.id = p.learner_id
    WHERE c.subject = 'Advanced Physics';

- input: "Can you show me a list of agents who have been reassigned more than once, along with the classes they were reassigned to and the reasons?"
  reformulated_query: "List all agents who have been reassigned more than once, including their names, the classes they were reassigned to, assignment dates, and the reasons for reassignment. Use Tables: agents, agent_assignments, classes"
  sql_query: |
    SELECT a.name AS agent_name, c.subject AS class_subject, aa.assignment_date, aa.reassignment_reason
    FROM agents a
    JOIN agent_assignments aa ON a.id = aa.agent_id
    JOIN classes c ON aa.class_id = c.id
    WHERE aa.reassigned_agent_id IS NOT NULL
        AND a.id IN (
            SELECT agent_id
            FROM agent_assignments
            WHERE reassigned_agent_id IS NOT NULL
            GROUP BY agent_id
            HAVING COUNT(*) > 1
        )
    ORDER BY a.name, aa.assignment_date;