chatGPT_notebook_submit.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import openai\n",
    "openai.api_key = \"設定open api key\"# set your API key -> https://platform.openai.com/account/api-keys"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- prepare"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "leetcode_all_database_2021=[\"175. Combine Two Tables\",\n",
    "                            \"176. Second Highest Salary\",\n",
    "                            \"177. Nth Highest Salary\",\n",
    "                            \"178. Rank Scores\",\n",
    "                            \"179. Consecutive Numbers\",\n",
    "                            \"180. Employees Earning More Than Their Managers\",\n",
    "                            \"181. Duplicate Emails\",\n",
    "                            \"182. Customers Who Never Order\",\n",
    "                            \"183. Delete Duplicate Emails\",\n",
    "                            \"184. Rising Temperature\",\n",
    "                            \"185. Trips and Users\",\n",
    "                            \"186. Game Play Analysis I\",\n",
    "                            \"187. Game Play Analysis II\",\n",
    "                            \"188. Game Play Analysis III\",\n",
    "                            \"189. Game Play Analysis IV\",\n",
    "                            \"190. Median Employee Salary\",\n",
    "                            \"191. Managers with at Least 5 Direct Reports\",\n",
    "                            \"192. Find Median Given Frequency of Numbers\",\n",
    "                            \"193. Winning Candidate\",\n",
    "                            \"194. Employee Bonus\",\n",
    "                            \"195. Get Highest Answer Rate Question\",\n",
    "                            \"196. Find Cumulative Salary of an Employee\",\n",
    "                            \"197. Count Student Number in Departments\",\n",
    "                            \"198. Find Customer Referee\",\n",
    "                            \"199. Investment in Equity\",\n",
    "                            \"200. Customer Placing the Largest Number of Orders\",\n",
    "                            \"201. Big Countries\",\n",
    "                            \"202. Classes More Than 5 Students\",\n",
    "                            \"203. Friend Requests I: Overall Acceptance Rate\",\n",
    "                            \"204. Human Traffic of Stadium\",\n",
    "                            \"205. Friend Requests II: Who Has the Most Friends\",\n",
    "                            \"206. Consecutive Available Seats\",\n",
    "                            \"207. Sales Person\",\n",
    "                            \"208. Tree Node\",\n",
    "                            \"209. Triangle Judgement\",\n",
    "                            \"210. Shortest Distance in a Plane\",\n",
    "                            \"211. Shortest Distance in a Line\",\n",
    "                            \"212. Second Degree Follower\",\n",
    "                            \"213. Average Salary: Departments VS Company\",\n",
    "                            \"214. Students Report By Geography\",\n",
    "                            \"215. Biggest Single Number\",\n",
    "                            \"216. Not Boring Movies\",\n",
    "                            \"217. Exchange Seats\",\n",
    "                            \"218. Swap Salary\",\n",
    "                            \"219. Customers Who Bought Products A and B but Not C\",\n",
    "                            \"220. Actors and Directors Who Cooperated At Least Three Times\",\n",
    "                            \"221. Height Checker\",\n",
    "                            \"222. Grumpy Bookstore Owner\",\n",
    "                            \"223. Product Sales Analysis I\",\n",
    "                            \"224. Product Sales Analysis II\",\n",
    "                            \"225. Product Sales Analysis III\",\n",
    "                            \"226. Project Employees I\",\n",
    "                            \"227. Project Employees II\",\n",
    "                            \"228. Sales Analysis I\",\n",
    "                            \"229. Sales Analysis II\",\n",
    "                            \"230. Game Play Analysis V\",\n",
    "                            \"231. New Users Daily Count\",\n",
    "                            \"232. Highest Grade For Each Student\",\n",
    "                            \"233. Reported Posts\",\n",
    "                            \"234. Active Businesses\",\n",
    "                            \"235. User Activity for the Past 30 Days I\",\n",
    "                            \"236. User Activity for the Past 30 Days II\",\n",
    "                            \"237. Article Views I\",\n",
    "                            \"238. Article Views II\",\n",
    "                            \"239. Market Analysis I\",\n",
    "                            \"240. Market Analysis II\",\n",
    "                            ]\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- function for leet code problem(Chinese)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "def getLeetCodeContent(each_question):\n",
    "    \"\"\"\n",
    "    each_question: The numb of target question:  \"175. Combine Two Tables\"\n",
    "    response: Leetcode problem content from OpenAI(text-davinci-003)\n",
    "    \"\"\"\n",
    "    # prompt = \"請給我LeetCode {question number}. {question title}的題目，包含：Table、Example、Output、Explanation的內容\"\n",
    "    prompt = f\"請給我LeetCode {each_question}的題目，包含：Input的所有Table、Output的Table、以及每個Example裡Input及Output的Table、Explanation的內容，總之100%還原leetcode題目內容\"\n",
    "    response = openai.Completion.create(\n",
    "        engine=\"text-davinci-003\",\n",
    "        prompt=prompt,\n",
    "        max_tokens=2000,\n",
    "        # n=1,\n",
    "        # stop=None,\n",
    "        top_p=1.0,\n",
    "        temperature=0.1,\n",
    "    )\n",
    "    return response\n",
    "\n",
    "\n",
    "def getLeetCodeSolution(each_question):\n",
    "    \"\"\"\n",
    "    each_question: The numb of target question:  \"175. Combine Two Tables\"\n",
    "    response: Leetcode solution from OpenAI(text-davinci-003)\n",
    "    \"\"\"\n",
    "    # prompt = \"請給我LeetCode {question number}. {question title}的題目，包含：Table、Example、Output、Explanation的內容\"\n",
    "    prompt = f\"請給我LeetCode {each_question}的解答，要能夠在LeetCode上精準通過的MySQL程式碼，我只要程式碼，其他不要\"\n",
    "    response = openai.Completion.create(\n",
    "        engine=\"text-davinci-003\",\n",
    "        prompt=prompt,\n",
    "        max_tokens=1000,\n",
    "        # n=1,\n",
    "        # stop=None,\n",
    "        top_p=1.0,\n",
    "        temperature=0.5,\n",
    "    )\n",
    "    return response\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- test function"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "## 175. Combine Two Tables\n",
      "\n",
      "### Problem\n",
      "Table: `Person`\n",
      "\n",
      "| Column Name | Type    |\n",
      "| ----------- | ------- |\n",
      "| PersonId    | int     |\n",
      "| FirstName   | varchar |\n",
      "| LastName    | varchar |\n",
      "\n",
      "Table: `Address`\n",
      "\n",
      "| Column Name | Type    |\n",
      "| ----------- | ------- |\n",
      "| AddressId   | int     |\n",
      "| PersonId    | int     |\n",
      "| City        | varchar |\n",
      "| State       | varchar |\n",
      "\n",
      "Write a SQL query for a report that provides the following information for each person in the Person table, regardless if there is an address for each of those people:\n",
      "\n",
      "- FirstName\n",
      "- LastName\n",
      "- City\n",
      "- State\n",
      "\n",
      "### Example\n",
      "\n",
      "Person table:\n",
      "\n",
      "| PersonId | FirstName | LastName |\n",
      "| -------- | --------- | -------- |\n",
      "| 1        | John      | Doe      |\n",
      "| 2        | Bob       | Smith    |\n",
      "\n",
      "Address table:\n",
      "\n",
      "| AddressId | PersonId | City  | State |\n",
      "| --------- | -------- | ----- | ----- |\n",
      "| 1         | 1        | New York City | NY |\n",
      "| 2         | 2        | Boston | MA    |\n",
      "\n",
      "Result table:\n",
      "\n",
      "| FirstName | LastName | City      | State |\n",
      "| --------- | -------- | --------- | ----- |\n",
      "| John      | Doe      | New York City | NY   |\n",
      "| Bob       | Smith    | Boston    | MA    |\n",
      "\n",
      "### Explanation\n",
      "We can use a `LEFT JOIN` to join the `Person` and `Address` tables. This will return all rows from the `Person` table, and any matching rows from the `Address` table. If there is no match, the columns from the `Address` table will be filled with `NULL` values.\n",
      "\n",
      "```\n",
      "SELECT p.FirstName, p.LastName, a.City, a.State\n",
      "FROM Person p\n",
      "LEFT JOIN Address a\n",
      "    ON p.PersonId = a.PersonId;\n",
      "```\n",
      "\n",
      "\n",
      "```sql\n",
      "SELECT \n",
      "    Person.FirstName, \n",
      "    Person.LastName, \n",
      "    Address.City, \n",
      "    Address.State \n",
      "FROM Person \n",
      "LEFT JOIN Address \n",
      "    ON Person.PersonId = Address.PersonId;\n",
      "```\n"
     ]
    }
   ],
   "source": [
    "# Problem content & Solution\n",
    "problem_content=getLeetCodeContent(leetcode_all_database_2021[0])\n",
    "print(problem_content.choices[0].text)\n",
    "\n",
    "\n",
    "problem_solution=getLeetCodeSolution(leetcode_all_database_2021[0])\n",
    "print(problem_solution.choices[0].text)\n",
    "\n",
    "\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Test.2 use loop to get each LeetCode Problem's content and solution."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# use three problems for testing.\n",
    "leetcode_all_database_2021=[\"175. Combine Two Tables\",\n",
    "                            \"176. Second Highest Salary\",\n",
    "                            \"177. Nth Highest Salary\",\n",
    "                            ]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "## 175. Combine Two Tables\n",
      "\n",
      "### Problem\n",
      "Table: `Person`\n",
      "\n",
      "| Column Name | Type    |\n",
      "| ----------- | ------- |\n",
      "| PersonId    | int     |\n",
      "| FirstName   | varchar |\n",
      "| LastName    | varchar |\n",
      "\n",
      "Table: `Address`\n",
      "\n",
      "| Column Name | Type    |\n",
      "| ----------- | ------- |\n",
      "| AddressId   | int     |\n",
      "| PersonId    | int     |\n",
      "| City        | varchar |\n",
      "| State       | varchar |\n",
      "\n",
      "Write a SQL query for a report that provides the following information for each person in the Person table, regardless if there is an address for each of those people:\n",
      "\n",
      "- FirstName\n",
      "- LastName\n",
      "- City\n",
      "- State\n",
      "\n",
      "### Example\n",
      "\n",
      "**Input**\n",
      "\n",
      "Person table:\n",
      "\n",
      "| PersonId | FirstName | LastName |\n",
      "| -------- | --------- | -------- |\n",
      "| 1        | John      | Doe      |\n",
      "| 2        | Bob       | Smith    |\n",
      "\n",
      "Address table:\n",
      "\n",
      "| AddressId | PersonId | City    | State |\n",
      "| --------- | -------- | ------- | ----- |\n",
      "| 1         | 1        | New York| NY    |\n",
      "| 2         | 2        | Boston  | MA    |\n",
      "\n",
      "**Output**\n",
      "\n",
      "| FirstName | LastName | City    | State |\n",
      "| --------- | -------- | ------- | ----- |\n",
      "| John      | Doe      | New York| NY    |\n",
      "| Bob       | Smith    | Boston  | MA    |\n",
      "\n",
      "### Explanation\n",
      "\n",
      "We can use an `INNER JOIN` to join the `Person` and `Address` tables on the `PersonId` column. This will give us all the people who have an address. We can then use a `LEFT JOIN` to join the `Person` and `Address` tables on the `PersonId` column. This will give us all the people, regardless if they have an address or not.\n",
      "\n",
      "SELECT \n",
      "    Person.FirstName, \n",
      "    Person.LastName, \n",
      "    Address.City, \n",
      "    Address.State\n",
      "FROM Person \n",
      "LEFT JOIN Address \n",
      "    ON Person.PersonId = Address.PersonId\n",
      "\n",
      "\n",
      "## 176. Second Highest Salary\n",
      "\n",
      "### Problem\n",
      "\n",
      "Write a SQL query to get the second highest salary from the `Employee` table.\n",
      "\n",
      "### Table: Employee\n",
      "\n",
      "| Id | Salary |\n",
      "|----|--------|\n",
      "| 1  | 100    |\n",
      "| 2  | 200    |\n",
      "| 3  | 300    |\n",
      "\n",
      "### Example\n",
      "\n",
      "#### Input\n",
      "\n",
      "| Id | Salary |\n",
      "|----|--------|\n",
      "| 1  | 100    |\n",
      "| 2  | 200    |\n",
      "| 3  | 300    |\n",
      "\n",
      "#### Output\n",
      "\n",
      "| SecondHighestSalary |\n",
      "|---------------------|\n",
      "| 200                 |\n",
      "\n",
      "### Explanation\n",
      "\n",
      "Given the above `Employee` table, the second highest salary is `200`.\n",
      "\n",
      "```sql\n",
      "SELECT MAX(Salary) AS SecondHighestSalary\n",
      "FROM Employee\n",
      "WHERE Salary < (SELECT MAX(Salary) FROM Employee)\n",
      "```\n",
      "\n",
      "\n",
      "## 177. Nth Highest Salary\n",
      "\n",
      "### Table: Employee\n",
      "\n",
      "| Id | Salary |\n",
      "|----|-------|\n",
      "| 1  | 100   |\n",
      "| 2  | 200   |\n",
      "| 3  | 300   |\n",
      "\n",
      "### Output Table:\n",
      "\n",
      "| NthHighestSalary |\n",
      "|------------------|\n",
      "\n",
      "### Example 1\n",
      "\n",
      "#### Input\n",
      "\n",
      "```\n",
      "Table: Employee\n",
      "\n",
      "Id\tSalary\n",
      "1\t100\n",
      "2\t200\n",
      "3\t300\n",
      "\n",
      "N = 2\n",
      "```\n",
      "\n",
      "#### Output\n",
      "\n",
      "```\n",
      "NthHighestSalary\n",
      "200\n",
      "```\n",
      "\n",
      "### Example 2\n",
      "\n",
      "#### Input\n",
      "\n",
      "```\n",
      "Table: Employee\n",
      "\n",
      "Id\tSalary\n",
      "1\t100\n",
      "2\t200\n",
      "3\t300\n",
      "\n",
      "N = 1\n",
      "```\n",
      "\n",
      "#### Output\n",
      "\n",
      "```\n",
      "NthHighestSalary\n",
      "300\n",
      "```\n",
      "\n",
      "### Explanation\n",
      "\n",
      "Write a SQL query to get the nth highest salary from the `Employee` table.\n",
      "\n",
      "```\n",
      "SELECT MAX(Salary) AS NthHighestSalary\n",
      "FROM Employee\n",
      "WHERE (SELECT COUNT(DISTINCT Salary) FROM Employee WHERE Salary >= e.Salary) >= N\n",
      "```\n",
      "\n",
      "```sql\n",
      "SELECT\n",
      "  (SELECT DISTINCT Salary\n",
      "   FROM Employee\n",
      "   ORDER BY Salary DESC\n",
      "   LIMIT N-1, 1) AS NthHighestSalary\n",
      "```\n"
     ]
    }
   ],
   "source": [
    "for each_question in leetcode_all_database_2021:\n",
    "    # Problem content & Solution\n",
    "    problem_content=getLeetCodeContent(each_question)\n",
    "    # print(problem_content.choices[0].text)\n",
    "\n",
    "\n",
    "    problem_solution=getLeetCodeSolution(each_question)\n",
    "    # print(problem_solution.choices[0].text)\n",
    "    \n",
    "    # content & solution\n",
    "    content=problem_content.choices[0].text+problem_solution.choices[0].text\n",
    "    print(content)\n",
    "    # save to txt\n",
    "    leetcode_database_problem=\"LeetCode_DataBase_\"+each_question+\".txt\"\n",
    "    with open(leetcode_database_problem, \"w\") as f:\n",
    "        f.write(content)\n",
    "    # print(each_question)\n",
    "\n",
    "    "
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Entire code"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# leetcode_all_database_2021=[\"175. Combine Two Tables\",\n",
    "#                             \"176. Second Highest Salary\",\n",
    "#                             \"177. Nth Highest Salary\",\n",
    "#                             \"178. Rank Scores\",\n",
    "#                             \"179. Consecutive Numbers\",\n",
    "#                             \"180. Employees Earning More Than Their Managers\",\n",
    "#                             \"181. Duplicate Emails\",\n",
    "#                             \"182. Customers Who Never Order\",\n",
    "#                             \"183. Delete Duplicate Emails\",\n",
    "#                             \"184. Rising Temperature\",\n",
    "#                             \"185. Trips and Users\",\n",
    "#                             \"186. Game Play Analysis I\",\n",
    "#                             \"187. Game Play Analysis II\",\n",
    "#                             \"188. Game Play Analysis III\",\n",
    "#                             \"189. Game Play Analysis IV\",\n",
    "#                             \"190. Median Employee Salary\",\n",
    "#                             \"191. Managers with at Least 5 Direct Reports\",\n",
    "#                             \"192. Find Median Given Frequency of Numbers\",\n",
    "#                             \"193. Winning Candidate\",\n",
    "#                             \"194. Employee Bonus\",\n",
    "#                             \"195. Get Highest Answer Rate Question\",\n",
    "#                             \"196. Find Cumulative Salary of an Employee\",\n",
    "#                             \"197. Count Student Number in Departments\",\n",
    "#                             \"198. Find Customer Referee\",\n",
    "#                             \"199. Investment in Equity\",\n",
    "#                             \"200. Customer Placing the Largest Number of Orders\",\n",
    "#                             \"201. Big Countries\",\n",
    "#                             \"202. Classes More Than 5 Students\",\n",
    "#                             \"203. Friend Requests I: Overall Acceptance Rate\",\n",
    "#                             \"204. Human Traffic of Stadium\",\n",
    "#                             \"205. Friend Requests II: Who Has the Most Friends\",\n",
    "#                             \"206. Consecutive Available Seats\",\n",
    "#                             \"207. Sales Person\",\n",
    "#                             \"208. Tree Node\",\n",
    "#                             \"209. Triangle Judgement\",\n",
    "#                             \"210. Shortest Distance in a Plane\",\n",
    "#                             \"211. Shortest Distance in a Line\",\n",
    "#                             \"212. Second Degree Follower\",\n",
    "#                             \"213. Average Salary: Departments VS Company\",\n",
    "#                             \"214. Students Report By Geography\",\n",
    "#                             \"215. Biggest Single Number\",\n",
    "#                             \"216. Not Boring Movies\",\n",
    "#                             \"217. Exchange Seats\",\n",
    "#                             \"218. Swap Salary\",\n",
    "#                             \"219. Customers Who Bought Products A and B but Not C\",\n",
    "#                             \"220. Actors and Directors Who Cooperated At Least Three Times\",\n",
    "#                             \"221. Height Checker\",\n",
    "#                             \"222. Grumpy Bookstore Owner\",\n",
    "#                             \"223. Product Sales Analysis I\",\n",
    "#                             \"224. Product Sales Analysis II\",\n",
    "#                             \"225. Product Sales Analysis III\",\n",
    "#                             \"226. Project Employees I\",\n",
    "#                             \"227. Project Employees II\",\n",
    "#                             \"228. Sales Analysis I\",\n",
    "#                             \"229. Sales Analysis II\",\n",
    "#                             \"230. Game Play Analysis V\",\n",
    "#                             \"231. New Users Daily Count\",\n",
    "#                             \"232. Highest Grade For Each Student\",\n",
    "#                             \"233. Reported Posts\",\n",
    "#                             \"234. Active Businesses\",\n",
    "#                             \"235. User Activity for the Past 30 Days I\",\n",
    "#                             \"236. User Activity for the Past 30 Days II\",\n",
    "#                             \"237. Article Views I\",\n",
    "#                             \"238. Article Views II\",\n",
    "#                             \"239. Market Analysis I\",\n",
    "#                             \"240. Market Analysis II\",\n",
    "#                             ]\n",
    "# for each_question in leetcode_all_database_2021:\n",
    "#     # Problem content & Solution\n",
    "#     problem_content=getLeetCodeContent(each_question)\n",
    "#     # print(problem_content.choices[0].text)\n",
    "\n",
    "\n",
    "#     problem_solution=getLeetCodeSolution(each_question)\n",
    "#     # print(problem_solution.choices[0].text)\n",
    "    \n",
    "#     # content & solution\n",
    "#     content=problem_content.choices[0].text+problem_solution.choices[0].text\n",
    "#     print(content)\n",
    "#     # save to txt\n",
    "#     leetcode_database_problem=\"LeetCode_DataBase_\"+each_question+\".txt\"\n",
    "#     with open(leetcode_database_problem, \"w\") as f:\n",
    "#         f.write(content)\n",
    "#     # print(each_question)\n",
    "\n",
    "    \n",
    "\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "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.9.12"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "4a6733c98ac2c8b1644282e50b376dd21b5e27b7b8db42bf2bc28073b5c321b7"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}