quick_start/04_code_generation.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "### 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",
      "### A query to list the names of the departments which employed more than 10 employees in the last 3 months\n",
      "\n",
      " query: \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",
      "        GROUP BY sp.employee_id\n",
      "        HAVING COUNT(sp.employee_id) > 10\n",
      "    )\n",
      ")\n"
     ]
    }
   ],
   "source": [
    "import openai\n",
    "import re\n",
    "import requests\n",
    "import sys\n",
    "import os\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from dotenv import load_dotenv\n",
    "load_dotenv()\n",
    "\n",
    "openai.api_type = \"azure\"\n",
    "openai.api_version = os.getenv(\"OPENAI_API_VERSION\")\n",
    "\n",
    "API_KEY = os.getenv(\"OPENAI_API_KEY\",\"\").strip()\n",
    "assert API_KEY, \"ERROR: Azure OpenAI Key is missing\"\n",
    "openai.api_key = API_KEY\n",
    "\n",
    "RESOURCE_ENDPOINT = os.getenv(\"OPENAI_API_BASE\",\"\").strip()\n",
    "assert RESOURCE_ENDPOINT, \"ERROR: Azure OpenAI Endpoint is missing\"\n",
    "assert \"openai.azure.com\" in RESOURCE_ENDPOINT.lower(), \"ERROR: Azure OpenAI Endpoint should be in the form: \\n\\n\\t<your unique endpoint identifier>.openai.azure.com\"\n",
    "openai.api_base = RESOURCE_ENDPOINT\n",
    "\n",
    "\n",
    "CHAT_COMPLETIONS_MODEL = os.getenv('DEPLOYMENT_NAME')\n",
    "\n",
    "\n",
    "prompt = \"### 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### A query to list the names of the departments which employed more than 10 employees in the last 3 months\\n\\n query: \"\n",
    "print(prompt)\n",
    "response = openai.ChatCompletion.create(\n",
    "  engine=CHAT_COMPLETIONS_MODEL,\n",
    "  messages = [{\"role\":\"system\", \"content\":\"You are a helpful assistant.\"},\n",
    "               {\"role\":\"user\",\"content\": prompt}],\n",
    "  temperature=0,\n",
    "  max_tokens=150,\n",
    "  top_p=1,\n",
    "  frequency_penalty=0,\n",
    "  presence_penalty=0,\n",
    "  stop=[\"#\",\";\"])\n",
    "print(response['choices'][0]['message']['content'])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "This code is a SQL query that retrieves the names of departments that have more than 10 employees who have received salary payments in the last 3 months. \n",
      "\n",
      "Here is a breakdown of the code:\n",
      "\n",
      "1. The query starts with the SELECT statement, which specifies that we want to retrieve the \"name\" column from the \"Department\" table.\n",
      "\n",
      "2. The FROM clause specifies that we are selecting from the \"Department\" table.\n",
      "\n",
      "3. The JOIN keyword is used to combine the \"Department\" table with the \"Employee\" table. The ON clause specifies the condition for the join, which is that the \"id\" column in the \"Department\" table must match the \"department_id\" column in the \"Employee\" table.\n",
      "\n",
      "4. The WHERE clause filters the results based on a condition. In this case, it checks if the \"id\" column in the \"Employee\" table is present in the subquery.\n",
      "\n",
      "5. The subquery selects the \"employee_id\" column from the \"Salary_Payments\" table where the \"date\" column is greater than the current date minus 3 months.\n",
      "\n",
      "6. The GROUP BY clause groups the results by the \"name\" column in the \"Department\" table.\n",
      "\n",
      "7. The HAVING clause filters the results based on a condition. In this case, it checks if the count of rows in each group is greater than 10.\n",
      "\n",
      "Overall, this code retrieves the names of departments that have more than 10 employees who have received salary payments in the last 3 months.\n"
     ]
    }
   ],
   "source": [
    "code = \"Can you explain what does this code do?\\n#\\n# ###\\n\\\n",
    "   Code:\\n\\\n",
    "   SELECT d.name FROM Department d JOIN Employee e ON d.id = e.department_id WHERE e.id IN (SELECT employee_id FROM Salary_Payments WHERE date > now() - interval '3 months') GROUP BY d.name HAVING COUNT(*) > 10\\n#\\n#\\\n",
    "   Answer:\\n# \"\n",
    "\n",
    "response = openai.ChatCompletion.create(\n",
    "  engine=CHAT_COMPLETIONS_MODEL,\n",
    "  messages = [{\"role\":\"system\", \"content\":\"You are a helpful assistant.\"},\n",
    "               {\"role\":\"user\",\"content\": code}],\n",
    "  temperature=0,\n",
    "  max_tokens=500,\n",
    "  top_p=1,\n",
    "  frequency_penalty=0,\n",
    "  presence_penalty=0,\n",
    "  stop=[\"#\",\";\"])\n",
    "print(response['choices'][0]['message']['content'])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.10.10"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "2139c70ac98f3202d028164a545621647e07f47fd6f5d8ac55cf952bf7c15ed1"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}