quick_start/04_Code_generation.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "from openai import AzureOpenAI\n",
    "from dotenv import load_dotenv\n",
    "load_dotenv()\n",
    "\n",
    "client = AzureOpenAI(\n",
    "    azure_endpoint = os.getenv(\"AZURE_OPENAI_ENDPOINT\",\"\"),\n",
    "    api_key        = os.getenv(\"AZURE_OPENAI_API_KEY\"),\n",
    "    api_version    = os.getenv(\"OPENAI_API_VERSION\")\n",
    ")\n",
    "\n",
    "deployment_name    = os.getenv('DEPLOYMENT_NAME')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "To list the names of the departments which employed more than 10 employees in the last 3 months, you can use the following SQL query:\n",
      "\n",
      "```sql\n",
      "SELECT d.name\n",
      "FROM Department d\n",
      "JOIN Employee e ON d.id = e.department_id\n",
      "JOIN Salary_Payments sp ON e.id = sp.employee_id\n",
      "WHERE sp.date >= NOW() - INTERVAL '3 months'\n",
      "GROUP BY d.name\n",
      "HAVING COUNT(DISTINCT e.id) > 10;\n",
      "```\n",
      "\n",
      "### Explanation:\n",
      "1. **JOIN Department and Employee**: This joins the `Department` and `Employee` tables on the `department_id` field.\n",
      "2. **JOIN Salary_Payments**: This further joins the `Salary_Payments` table on the `employee_id` field.\n",
      "3. **WHERE clause**: Filters the records to include only those salary payments made in the last 3 months.\n",
      "4. **GROUP BY**: Groups the results by the department name.\n",
      "5. **HAVING clause**: Filters the groups to include only those departments that have more than 10 distinct employees.\n",
      "\n",
      "This query ensures that you get the names of departments that have employed more than 10 employees in the last 3 months based on salary payments.\n"
     ]
    }
   ],
   "source": [
    "user_msg = \"\"\"### Postgres SQL tables, with their properties:\n",
    "#\n",
    "# Employee(id, name, department_id)\n",
    "# Department(id, name, address)\n",
    "# Salary_Payments(id, employee_id, amount, date)\n",
    "#\n",
    "### Make a query to list the names of the departments which employed more than 10 employees in the last 3 months.\n",
    "query:\n",
    "\"\"\"\n",
    "\n",
    "# user_msg = \"\"\"### Postgres SQL tables, with their properties:\n",
    "# #\n",
    "# # Employee(id, name, department_id)\n",
    "# # Department(id, name, address)\n",
    "# # Salary_Payments(id, employee_id, amount, date)\n",
    "# #\n",
    "# ### 최근 3 개월간 직원을 10명 이상 뽑은 부서의 이름을 수집하는 쿼리를 만들어줘.\n",
    "# query: \n",
    "# \"\"\"\n",
    "\n",
    "response = client.chat.completions.create(\n",
    "    model=deployment_name,\n",
    "    messages=[\n",
    "        {\"role\": \"system\", \"content\": \"You are a helpful assistant.\"},\n",
    "        {\"role\": \"user\", \"content\": user_msg},\n",
    "    ],\n",
    "  temperature=0,\n",
    "  max_tokens=500\n",
    ")\n",
    "\n",
    "print(response.choices[0].message.content)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Certainly! Let's break down what this SQL query does step by step.\n",
      "\n",
      "### Explanation\n",
      "\n",
      "1. **Outer Query:**\n",
      "   - The outer query selects the `name` of departments from the `Department` table (`d`).\n",
      "\n",
      "   ```sql\n",
      "   SELECT d.name\n",
      "   FROM Department d\n",
      "   ```\n",
      "\n",
      "2. **WHERE Clause in the Outer Query:**\n",
      "   - The outer query has a `WHERE` clause that checks if the `id` of the department is in a subquery.\n",
      "   \n",
      "   ```sql\n",
      "   WHERE d.id IN (\n",
      "   ```\n",
      "\n",
      "3. **First Subquery:**\n",
      "   - This subquery selects `department_id` from the `Employee` table (`e`). Hence, the outer query is interested in departments that satisfy the conditions inside this subquery.\n",
      "   \n",
      "   ```sql\n",
      "   SELECT e.department_id\n",
      "   FROM Employee e\n",
      "   ```\n",
      "\n",
      "4. **Second Subquery:**\n",
      "   - Inside the first subquery, there is another subquery that is part of a `WHERE` clause. This subquery selects `employee_id` from the `Salary_Payments` table (`sp`) where the `date` is within the last 3 months from the current date.\n",
      "   \n",
      "   ```\n"
     ]
    }
   ],
   "source": [
    "user_msg = \"\"\"###Can you explain what does this code do? \n",
    "# Code: \n",
    "SELECT d.name\n",
    "FROM Department d\n",
    "WHERE d.id IN (\n",
    "    SELECT e.department_id\n",
    "    FROM Employee e\n",
    "    WHERE e.id IN (\n",
    "        SELECT sp.employee_id\n",
    "        FROM Salary_Payments sp\n",
    "        WHERE sp.date >= current_date - interval '3 months'\n",
    "    )\n",
    "    GROUP BY e.department_id\n",
    "    HAVING COUNT(e.id) > 10\n",
    ")\n",
    "# Answer: \n",
    "\"\"\"\n",
    "\n",
    "# user_msg = \"\"\"###이 코드가 무엇을 수행하는지 설명해줘?\n",
    "# #Code:\n",
    "# SELECT d.name\n",
    "# FROM Department d\n",
    "# WHERE d.id IN (\n",
    "#     SELECT e.department_id\n",
    "#     FROM Employee e\n",
    "#     WHERE e.id IN (\n",
    "#         SELECT sp.employee_id\n",
    "#         FROM Salary_Payments sp\n",
    "#         WHERE sp.date >= current_date - interval '3 months'\n",
    "#     )\n",
    "#     GROUP BY e.department_id\n",
    "#     HAVING COUNT(e.id) > 10\n",
    "# )\n",
    "# Answer:\n",
    "# \"\"\"\n",
    "   \n",
    "response = client.chat.completions.create(\n",
    "    model=deployment_name,\n",
    "    messages=[\n",
    "        {\"role\": \"system\", \"content\": \"You are a helpful assistant.\"},\n",
    "        {\"role\": \"user\", \"content\": user_msg},\n",
    "    ],\n",
    "   max_tokens=250)\n",
    "\n",
    "print(response.choices[0].message.content)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "azureml_py310_sdkv2",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.4"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "2139c70ac98f3202d028164a545621647e07f47fd6f5d8ac55cf952bf7c15ed1"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}