quick_start/v1/03_code_generation.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# OpenAI Code Generation"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Overview  \n",
    "OpenAI Code Generation allows you to generate code snippets based on given instructions or prompts. \\\n",
    "This feature enables you to create code assistants, automated coding tools, and other developer-centric AI applications by providing a series of instructions as input and receiving a model-generated code snippet as output.\\\n",
    "\\\n",
    "For more detailed examples, refer to the official Training on [Generate code with Azure OpenAI Service](https://learn.microsoft.com/en-us/training/modules/generate-code-azure-openai/)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1. Import helper libraries and instantiate credentials and model"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import re\n",
    "import requests\n",
    "import sys\n",
    "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_KEY\"),  \n",
    "  api_version=\"2024-02-15-preview\"\n",
    ")\n",
    "CHAT_COMPLETIONS_MODEL = os.getenv('AZURE_OPENAI_DEPLOYMENT_NAME') # model = \"deployment_name\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Generating SQL Queries\n",
    "In this example, we will use OpenAI to generate a SQL query that lists the names of departments which employed more than 10 employees in the last 3 months.\n",
    "\n",
    "#### Model Parameters:\n",
    " _temperature_: \\\n",
    "  Controls the randomness of the output. A value of 0 makes the output more deterministic.\n",
    "\n",
    " _max_tokens_:\\\n",
    "  The maximum number of tokens in the generated output.\n",
    "  \n",
    "   _top_p_: \\\n",
    "  Controls diversity via nucleus sampling.\n",
    "\n",
    "_frequency_penalty and presence_penalty_: \\\n",
    "  These parameters are set to 0 to avoid penalizing the generation of similar words.\n",
    "\n",
    " _stop_: \\\n",
    "  Specifies stopping sequences to end the generated text appropriately."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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",
      "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. This query assumes that the \"last 3 months\" is relative to the current date.\n",
      "\n",
      "```sql\n",
      "SELECT d.name\n",
      "FROM Department d\n",
      "JOIN Employee e ON d.id = e.department_id\n",
      "WHERE e.id IN (\n",
      "    SELECT DISTINCT e.id\n",
      "    FROM Employee e\n",
      "    JOIN Salary_Payments sp ON e.id = sp.employee_id\n",
      "    WHERE sp.date >= CURRENT_DATE - INTERVAL '3 months'\n",
      ")\n",
      "GROUP BY d.id, d.name\n",
      "HAVING COUNT(DISTINCT e.id) > 10\n"
     ]
    }
   ],
   "source": [
    "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 = client.chat.completions.create(\n",
    "  model = 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": "markdown",
   "metadata": {},
   "source": [
    "#### Use cases to explore\n",
    "1. **Automated Query Generation** \\\n",
    "   Quickly generate complex SQL queries based on natural language descriptions.\n",
    "\n",
    "2. **Code Assistance** \\\n",
    "   Provide developers with code suggestions and examples to speed up development.\n",
    "\n",
    "3. **Educational Tools for Developers** \\\n",
    "   Help developers learn SQL by providing examples and explanations of different queries."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Explaining SQL Code\n",
    "OpenAI can help you understand and explain code snippets by providing natural language descriptions of what the code does.  \\\n",
    "This feature is useful for creating educational tools, code documentation, and automated code review systems."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Certainly! Let's break down the SQL query step by step to understand what it does:\n",
      "\n",
      "1. **FROM and JOIN Clause**:\n",
      "   - `FROM Department d JOIN Employee e ON d.id = e.department_id`: This part of the query is joining two tables: `Department` and `Employee`. It uses the `department_id` from the `Employee` table to match with the `id` in the `Department` table. This allows us to associate each employee with their respective department.\n",
      "\n",
      "2. **WHERE Clause**:\n",
      "   - `WHERE e.id IN (SELECT employee_id FROM Salary_Payments WHERE date > now() - interval '3 months')`: This subquery selects `employee_id` from the `Salary_Payments` table where the `date` of the payment is within the last 3 months. The `WHERE` clause then filters the employees to only include those who have received a salary payment in the last 3 months.\n",
      "\n",
      "3. **GROUP BY and HAVING Clause**:\n",
      "   - `GROUP BY d.name HAVING COUNT(*) > 10`: After filtering, the query groups the results by department name (`d.name`). The `HAVING` clause is used to filter these groups to only include departments that have more than 10 employees who have received a salary payment in the last 3 months.\n",
      "\n",
      "4. **SELECT Clause**:\n",
      "   - `SELECT d.name`: Finally, the query selects the names of the departments that meet the criteria specified.\n",
      "\n",
      "**Summary**:\n",
      "The query retrieves the names of departments that have more than 10 employees who have received salary payments in the last 3 months. It does this by joining the `Department` and `Employee` tables, filtering employees based on recent salary payments, grouping by department, and then applying a count filter to ensure only departments with more than 10 such employees are included in the results.\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 = client.chat.completions.create(\n",
    "  model=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": "markdown",
   "metadata": {},
   "source": [
    "#### Use cases to explore\n",
    "1. **Educational Tools** \\\n",
    "Help your developers understand SQL queries even better by providing clear explanations.\n",
    "\n",
    "2. **Code Documentation** \\\n",
    "Automatically generate documentation for SQL queries to improve code readability and maintainability.\n",
    "\n",
    "\n",
    "3. **Automated Code Review** \\\n",
    "Enhance code review processes by providing automated explanations and insights into the code."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": ".venv",
   "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.12.5"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}