{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "25d3dd2a",
   "metadata": {},
   "source": [
    "## ContextualCompressionRetriever"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "6f6de72c",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\p4pri\\AppData\\Local\\Temp\\ipykernel_4084\\2905898901.py:1: DeprecationWarning: `langchain-community` is being sunset and is no longer actively maintained. See https://github.com/langchain-ai/langchain-community/issues/674 for details and migration guidance toward standalone integration packages.\n",
      "  from langchain_community.vectorstores import FAISS\n"
     ]
    }
   ],
   "source": [
    "from langchain_community.vectorstores import FAISS\n",
    "from langchain_classic.retrievers import ContextualCompressionRetriever\n",
    "from langchain_classic.retrievers.document_compressors import LLMChainExtractor\n",
    "from langchain_core.documents import Document\n",
    "from langchain_ollama import OllamaEmbeddings\n",
    "from langchain_ollama import ChatOllama\n",
    "from langchain_core.prompts import PromptTemplate"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "461abc81",
   "metadata": {},
   "source": [
    "## Step 1a - Indexing (Document Ingestion and Text Splitting)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import re\n",
    "from langchain_core.documents import Document\n",
    "\n",
    "class SQLPracticeSplitter:\n",
    "    def split_documents(self, docs):\n",
    "        text = \"\\n\".join(doc.page_content for doc in docs)\n",
    "\n",
    "        # Remove page breaks\n",
    "        text = text.replace(\"\\f\", \"\\n\")\n",
    "\n",
    "        # Split at every numbered question\n",
    "        pattern = r'(?=^\\d+\\.)'\n",
    "\n",
    "        chunks = re.split(pattern, text, flags=re.MULTILINE)\n",
    "\n",
    "        documents = []\n",
    "\n",
    "        current_topic = \"Unknown\"\n",
    "\n",
    "        for chunk in chunks:\n",
    "            chunk = chunk.strip()\n",
    "\n",
    "            if not chunk:\n",
    "                continue\n",
    "\n",
    "            # Find section name\n",
    "            topic = re.search(r'Practice Problem\\s*:\\s*(.+)', chunk)\n",
    "\n",
    "            if topic:\n",
    "                current_topic = topic.group(1).strip()\n",
    "\n",
    "            # Question number\n",
    "            q = re.match(r'(\\d+)\\.', chunk)\n",
    "\n",
    "            question_no = int(q.group(1)) if q else None\n",
    "\n",
    "            documents.append(\n",
    "                Document(\n",
    "                    page_content=chunk,\n",
    "                    metadata={\n",
    "                        \"topic\": current_topic,\n",
    "                        \"question_no\": question_no,\n",
    "                    },\n",
    "                )\n",
    "            )\n",
    "\n",
    "        return documents"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2.From previous questions, we learnt that there are 830 orders in the databases. The COUNT function counts number of rows in a table. Hence, you will get the same results if you count order_id, customer_id or any other column of the same table. Try counting orders by counting customer_id column of the table and check if you are still getting 830 result only\n",
      "\n",
      "Expected Output: 830\n",
      "\n",
      "select \n",
      "\n",
      " count(customer_id) \n",
      "\n",
      " from orders\n"
     ]
    }
   ],
   "source": [
    "from langchain_community.document_loaders import Docx2txtLoader\n",
    "\n",
    "loader = Docx2txtLoader(\"SQL_s.docx\")\n",
    "docs = loader.load()\n",
    "\n",
    "splitter = SQLPracticeSplitter()\n",
    "\n",
    "documents = splitter.split_documents(docs)\n",
    "\n",
    "#print(len(documents))\n",
    "print(documents[12].page_content)\n",
    "#print(documents[1].metadata)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "12211d0c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Document(metadata={'topic': 'Select Statement', 'question_no': None}, page_content='Practice Material SQL \\n\\n    \\n\\nMilestone 8\\n\\n\\n\\nPractice Problem : Select Statement'),\n",
       " Document(metadata={'topic': 'Select Statement', 'question_no': 1}, page_content='1.Write a query to view all the columns of the orders table from the database\\n\\n\\n\\n select * from orders'),\n",
       " Document(metadata={'topic': 'Select Statement', 'question_no': 2}, page_content='2.Write a query to view order_id and customer_id of orders table from the database.\\n\\nExpected output: order_id and customer_id columns in the exact same sequence\\n\\nselect order_id, customer_id from orders'),\n",
       " Document(metadata={'topic': 'Select Statement', 'question_no': 3}, page_content='3. Extract customer_id and postal_code of the customer from customers table\\n\\nExpected output: customer_id and postal_code columns in the exact same sequence\\n\\nselect customer_id,postal_code from customers'),\n",
       " Document(metadata={'topic': 'Select Statement', 'question_no': 4}, page_content='4.Write a code to view all the columns of employees table\\n\\n\\n\\nselect * from employees'),\n",
       " Document(metadata={'topic': 'Select Statement', 'question_no': 5}, page_content='5.Get the details(all columns) of the suppliers from the suppliers table\\n\\n\\n\\nselect * from suppliers'),\n",
       " Document(metadata={'topic': 'Select Statement', 'question_no': 6}, page_content='6.Check the territories table and see what columns are available in the table\\n\\n\\n\\nselect * from territories'),\n",
       " Document(metadata={'topic': 'Select Statement', 'question_no': 7}, page_content='7.Check order_details table from the database and observe what information is available in the table\\n\\n\\n\\nselect * from order_details'),\n",
       " Document(metadata={'topic': 'Select Statement', 'question_no': 8}, page_content='8. Extract employee_id, first_name, last_name and birth_date from employees table in the exact same sequence\\n\\nExpected output: employee_id, first_name, last_name and birth_date columns in the exact same sequence\\n\\n select \\n\\n employee_id, first_name, last_name, birth_date \\n\\n  from employees'),\n",
       " Document(metadata={'topic': 'Select Statement', 'question_no': 9}, page_content='9.There is an error in the code while extracting order_id, customer_id, shipped_date and order_date from orders table\\n\\nSELECT \\norder_id,\\ncustomer_id,\\nshipped_date,\\norder_date,\\n From orders\\n\\nIdentify the error and rerun the correct query\\n\\n select \\n\\n order_id,\\n\\n customer_id,\\n\\n shipped_date,\\n\\n order_date\\n\\n from orders'),\n",
       " Document(metadata={'topic': 'Distinct and Count Statement', 'question_no': 10}, page_content='10. There is an error in the code while extracting customer_id, company_name, city and country from customers table\\n\\nSELECT \\ncustomer_id,\\ncompany_name,\\ncity,\\ncountry,\\nFROM customers\\n\\nIdentify the error and rerun the correct query\\n\\nselect \\n\\n customer_id,\\n\\n company_name,\\n\\n city,\\n\\n country \\n\\n from customers\\n\\n\\n\\n\\n\\nPractice Problem : Distinct and Count Statement'),\n",
       " Document(metadata={'topic': 'Distinct and Count Statement', 'question_no': 1}, page_content='1.Identify count of orders by counting ORDER_ID from ORDERS table. To count order_Id from table, you can write the following statement.\\n\\n\\n\\nselect \\n\\n count(order_id) \\n\\n from orders'),\n",
       " Document(metadata={'topic': 'Distinct and Count Statement', 'question_no': 2}, page_content='2.From previous questions, we learnt that there are 830 orders in the databases. The COUNT function counts number of rows in a table. Hence, you will get the same results if you count order_id, customer_id or any other column of the same table. Try counting orders by counting customer_id column of the table and check if you are still getting 830 result only\\n\\nExpected Output: 830\\n\\nselect \\n\\n count(customer_id) \\n\\n from orders'),\n",
       " Document(metadata={'topic': 'Distinct and Count Statement', 'question_no': 3}, page_content='3.You can see that counting customer_id is going to give you the same result. It means that even if you use count(*), you are going to get the same number of rows. Try using COUNT(*) this time to count the number of rows in the table\\n\\n\\n\\n select \\n\\n count(*) \\n\\n from orders'),\n",
       " Document(metadata={'topic': 'Distinct and Count Statement', 'question_no': 4}, page_content=\"4. only counts the number of rows of the table, it doesn't take care of duplicate values. Hence, in some particular case, if you need to count distinct values of a column, then you need to use DISTINCT within COUNT statement. Imagine you are going to run a Weekend offer for all the customers who have ever transacted on the Northwind platform. Write a query to get an exhaustive list of customers who have ever transacted on the website.\\n\\nExpected Output: List of distinct customer_id \\n\\nselect \\n\\n distinct customer_id\\n\\n from orders\"),\n",
       " Document(metadata={'topic': 'Distinct and Count Statement', 'question_no': 5}, page_content='5.Count the number of customer_ids who transacted on Northwind Platform.\\n\\nExpected Output: Single number showing the count of distinct customer_id in orders table\\n\\n select \\n\\ncount(distinct customer_id)\\n\\n from orders'),\n",
       " Document(metadata={'topic': 'Where Statement', 'question_no': 6}, page_content='6.From the orders table, in how many countries Northwind delivered any order?\\n\\nExpected Output: Single number showing countries where any order is delivered\\n\\n select \\n\\ncount(distinct ship_country)\\n\\n from orders\\n\\n\\n\\n\\n\\n\\n\\n\\n\\nPractice Problem : Where Statement'),\n",
       " Document(metadata={'topic': 'Where Statement', 'question_no': 1}, page_content=\"1.Identify order_ids and customer_ids for the orders delivered in USA (use ship_country column).\\n\\nExpected Output: order_id, customer_id in exact same sequence\\n\\nselect \\n\\norder_id,\\n\\ncustomer_id \\n\\nfrom orders \\n\\nwhere ship_country = 'USA'\"),\n",
       " Document(metadata={'topic': 'Where Statement', 'question_no': 2}, page_content=\"2.Identify order_ids and customer_ids for the orders delivered in B-6000 ship_postal_code.\\n\\nExpected Output: order_id, customer_id columns in exact same sequence\\n\\nselect \\n\\norder_id,\\n\\ncustomer_id \\n\\nfrom orders \\n\\nwhere ship_postal_code = 'B-6000'\"),\n",
       " Document(metadata={'topic': 'Where Statement', 'question_no': 3}, page_content=\"3.List of the customer_id who placed orders which are shipped to Brazil.\\n\\nExpected Output: Customer_id from orders table.\\n\\nselect \\n\\ncustomer_id \\n\\nfrom orders \\n\\nwhere ship_country = 'Brazil'\"),\n",
       " Document(metadata={'topic': 'Where Statement', 'question_no': 4}, page_content=\"4.Find the list of the orders_ids which were placed on 26th Feb, 1998?\\n\\nExpected Output: order_id from orders table.\\n\\nselect \\n\\norder_id\\n\\nfrom orders \\n\\nwhere order_date = '1998-02-26'\"),\n",
       " Document(metadata={'topic': 'Where Statement', 'question_no': 5}, page_content=\"5.Find the list of the order_id, customer_id which were placed after 26th Feb, 1998(excluding)?\\n\\nExpected Output: order_id, customer_id columns from orders table.\\n\\nselect \\n\\norder_id,\\n\\ncustomer_id\\n\\nfrom orders \\n\\nwhere order_date > '1998-02-26'\"),\n",
       " Document(metadata={'topic': 'Where Statement', 'question_no': 6}, page_content=\"6.Get a list of customers in Berlin. \\n\\nExpected Output : All the columns from the customers table.\\n\\nselect \\n\\n*\\n\\nfrom customers \\n\\nwhere city = 'Berlin'\"),\n",
       " Document(metadata={'topic': 'Where Statement', 'question_no': 7}, page_content=\"7.Get the contact names and phone numbers of customers in Berlin. \\n\\nExpected Output: contact_name and phone from Berlin city.\\n\\nselect \\n\\ncontact_name,\\n\\nphone\\n\\nfrom customers \\n\\nwhere city = 'Berlin'\"),\n",
       " Document(metadata={'topic': 'Where Statement', 'question_no': 8}, page_content=\"8.Get a list of the cities in Japan where you have suppliers. \\n\\nExpected Output: City list from suppliers the table in country Japan\\n\\nselect \\n\\ncity\\n\\nfrom suppliers \\n\\nwhere country = 'Japan'\"),\n",
       " Document(metadata={'topic': 'Where Statement', 'question_no': 9}, page_content=\"9.List the records of all employees hired from 1993 (including) onwards. \\n\\nExpected Output: Employee_id, last_name, first_name and hire_date columns from employees in exact same sequence\\n\\nselect \\n\\nemployee_id,\\n\\nlast_name,\\n\\nfirst_name,\\n\\nhire_date\\n\\nfrom employees\\n\\nwhere hire_date >= '1993-01-01'\"),\n",
       " Document(metadata={'topic': 'AND-OR Operators in Where Statement', 'question_no': 10}, page_content=\"10.  Get a list of customers outside the USA.\\n\\nExpected Output: customer_id, company_name, contact_name, city\\n\\n\\n\\nselect \\n\\ncustomer_id,\\n\\ncompany_name,\\n\\ncontact_name,\\n\\ncity \\n\\nfrom customers \\n\\nwhere country <> 'USA'\\n\\n\\n\\n\\n\\nPractice Problem : AND-OR Operators in Where Statement\"),\n",
       " Document(metadata={'topic': 'AND-OR Operators in Where Statement', 'question_no': 1}, page_content='1.Using products table, identify product_id and product_name having category_id = 1 and supplier_id = 1\\n\\nExpected Output: product_id and product_name columns in exact same sequence\\n\\nselect\\n\\nproduct_id,\\n\\nproduct_name\\n\\nfrom products \\n\\nwhere category_id =1 and supplier_id =1'),\n",
       " Document(metadata={'topic': 'AND-OR Operators in Where Statement', 'question_no': 2}, page_content='2.Using the products table, identify product_id and product_name which are falling in category_id = 2 and are priced above ten (excluding).\\n\\nExpected Output: product_id and product_name columns in exact same sequence\\n\\nselect\\n\\nproduct_id,\\n\\nproduct_name\\n\\nfrom products \\n\\nwhere category_id =2 and unit_price >10'),\n",
       " Document(metadata={'topic': 'AND-OR Operators in Where Statement', 'question_no': 3}, page_content='3.Using the products table, identify product_name, units_in_stock and reorder_level which are falling in category_id = 2, priced above ten (excluding) and are discontinued (discontinued = 1).\\n\\nExpected Output: product_name, units_in_stock and reorder_level columns in exact same sequence\\n\\nselect\\n\\nproduct_name,\\n\\nunits_in_stock,\\n\\nreorder_level\\n\\nfrom products \\n\\nwhere category_id =2 and unit_price >10 and discontinued = 1'),\n",
       " Document(metadata={'topic': 'AND-OR Operators in Where Statement', 'question_no': 4}, page_content='4.Using products table, identify the product_name, units_in_stock and reorder_level of the products which are either from category_id = 1 or supplied by supplier_id = 2 \\n\\nExpected Output = product_name, units_in_stock and reorder_level in exact same sequence\\n\\n\\n\\nselect\\n\\nproduct_name,\\n\\nunits_in_stock,\\n\\nreorder_level\\n\\nfrom products \\n\\nwhere category_id =1 or supplier_id = 2'),\n",
       " Document(metadata={'topic': 'AND-OR Operators in Where Statement', 'question_no': 5}, page_content=\"5.Northwind's inventory team wants to identify the products which need to be stocked as soon as possible. For that, the manager of the team is asking you to give the list of product_name, units_in_stock and units_on_order having either units_in_stock is below 10 (excluding) OR units_on_order above 80 (excluding)\\n\\nExpected Output: product_name, units_in_stock and units_on_order in exact order\\n\\nselect\\n\\nproduct_name,\\n\\nunits_in_stock,\\n\\nunits_on_order\\n\\nfrom products \\n\\nwhere units_in_stock <10 or units_on_order >80\"),\n",
       " Document(metadata={'topic': 'AND-OR Operators in Where Statement', 'question_no': 6}, page_content=\"6.Using orders table, identify order_id which are ordered after 4th July 1996(excluding) and shipped to Spain\\n\\nExpected Output: list of order_id\\n\\n\\n\\nselect\\n\\norder_id\\n\\nfrom orders \\n\\nwhere order_date > '1996-07-04' and ship_country = 'Spain'\"),\n",
       " Document(metadata={'topic': 'AND-OR Operators in Where Statement', 'question_no': 7}, page_content=\"7.The UK sales team are visiting the Seattle office: list the records of employees who are either from UK country or Seattle city .Expected Output : All the columns from Employees table having country as UK or city as Seattle.\\n\\nselect\\n\\n* \\n\\nfrom employees \\n\\nwhere country = 'UK' or city = 'Seattle'\"),\n",
       " Document(metadata={'topic': 'AND-OR Operators in Where Statement', 'question_no': 8}, page_content=\"8.Get the records for orders shipped to Brazil from 1997 onwards.\\n\\nExpected Output: Extract all the columns from orders table with the given condition\\n\\n\\n\\nselect\\n\\n* \\n\\nfrom orders \\n\\nwhere ship_country = 'Brazil' and order_date >= '1997-01-01'\"),\n",
       " Document(metadata={'topic': 'Between Operator in Where Statement', 'question_no': 9}, page_content=\"9.Get a list of company_name, city from customers table who are not from USA and having 'Owner' as contact_title.\\n\\nExpected Output : company_name , city columns in exact sequence\\n\\nselect\\n\\ncompany_name,\\n\\ncity \\n\\nfrom customers \\n\\nwhere country <> 'USA' and contact_title = 'Owner'\\n\\n\\n\\nPractice Problem : Between Operator in Where Statement\"),\n",
       " Document(metadata={'topic': 'Between Operator in Where Statement', 'question_no': 1}, page_content='1.Using the products table, find out the product_id for products having unit_price between ten and twenty dollars. Expected Output: List of product_ids\\n\\nselect\\n\\nproduct_id\\n\\nfrom products\\n\\nwhere unit_price between 10 and 20'),\n",
       " Document(metadata={'topic': 'Between Operator in Where Statement', 'question_no': 2}, page_content=\"2.Using orders table, find out orders which are placed between 1st July 1997 and 1st June 1998\\n\\nExpected Output: order_id, customer_id and order_date in exact same sequence\\n\\nselect\\n\\norder_id,\\n\\ncustomer_id,\\n\\norder_date\\n\\nfrom orders\\n\\nwhere order_date between '1997-07-01' and '1998-06-01'\"),\n",
       " Document(metadata={'topic': 'Between Operator in Where Statement', 'question_no': 3}, page_content=\"3.Using employees table, identify the list of employees who are born in 1950, 1951 and 1952.\\n\\nExpected Output: employee_id, first_name, title and birth_date in exact same sequence\\n\\nselect\\n\\nemployee_id,\\n\\nfirst_name,\\n\\ntitle,\\n\\nbirth_date \\n\\nfrom employees \\n\\nwhere birth_date between '1950-01-01' and '1952-12-31'\"),\n",
       " Document(metadata={'topic': 'In Operator in Where Statement', 'question_no': 4}, page_content='4.How many product_ids are there in the products table having units_on_order between 10 and 20?Expected Output: Single number showing the count of product_ids\\n\\nselect\\n\\ncount(product_id)\\n\\nfrom products \\n\\nwhere units_on_order between 10 and 20\\n\\n\\n\\nPractice Problem : In Operator in Where Statement'),\n",
       " Document(metadata={'topic': 'In Operator in Where Statement', 'question_no': 1}, page_content=\"1.Using orders table, identify order_id, customer_id and ship_country for orders which are delivered in 'France', 'Germany', 'Brazil', 'Belgium'.\\n\\nExpected Output: order_id, customer_id and ship_country in the exact same sequence\\n\\n select\\n\\n order_id,\\n\\n customer_id,\\n\\n ship_country\\n\\n from orders\\n\\n where ship_country in ( 'France', 'Germany', 'Brazil', 'Belgium')\"),\n",
       " Document(metadata={'topic': 'In Operator in Where Statement', 'question_no': 2}, page_content=\"2.Using the customers table, count the number of customers with Owner, Sales Representative and Accounting Manager as contact_title.\\n\\nExpected Output: 44\\n\\n select\\n\\ncount(customer_id)\\n\\nfrom customers \\n\\nwhere contact_title in ('Owner', 'Sales Representative', 'Accounting Manager')\"),\n",
       " Document(metadata={'topic': 'In Operator in Where Statement', 'question_no': 3}, page_content=\"3.To get the order details from orders table for the orders placed by SAVEA, ERNSH, QUICK, HUNGO, FOLKO, BERGS customer_id, the following code is written. However, there are some errors in the code. Correct and rerun the following code.\\n\\n\\n\\n select \\n\\n * from orders\\n\\nwhere  customer_id in ('SAVEA', 'ERNSH', 'QUICK', 'HUNGO', 'FOLKO', 'BERGS')\"),\n",
       " Document(metadata={'topic': 'Like Operator in Where Statement', 'question_no': 4}, page_content='4.Using EMPLOYEE_TERRITORIES, identify the territory_id for employee_id 1,4,5 and 2\\n\\nExpected Output: employee_id and territory_id in exact same sequence                                                                                                                                                                         \\n\\nselect\\n\\nemployee_id,\\n\\nterritory_id \\n\\nfrom \\n\\nemployee_territories \\n\\nwhere employee_id in (1,2,4,5)\\n\\n\\n\\n\\n\\nPractice Problem : Like Operator in Where Statement'),\n",
       " Document(metadata={'topic': 'Like Operator in Where Statement', 'question_no': 1}, page_content=\"1. Get a list of customers where your contact has a title beginning with “Sales”. \\n\\nExpected output : Contact_name,contact_title,city from customer table \\n\\n\\n\\n select \\n\\n contact_name,\\n\\n contact_title,\\n\\n city \\n\\n from customers \\n\\n where contact_title like 'Sales%'\"),\n",
       " Document(metadata={'topic': 'Like Operator in Where Statement', 'question_no': 2}, page_content=\"2.Get a list of customers where your contact has a title beginning with “Sales” or “Marketing”. \\n\\nExpected Output : company_name,contact_name,contact_title\\n\\n\\n\\n select\\n\\n company_name, \\n\\n contact_name,\\n\\n contact_title\\n\\n from customers \\n\\n where contact_title like 'Sales%' or contact_title like 'Marketing%'\"),\n",
       " Document(metadata={'topic': 'Like Operator in Where Statement', 'question_no': 3}, page_content=\"3.Get a list of the names and phone numbers of your non USA contacts with those “Sales” and “Marketing” titles \\n\\nExpected Output: Contact_Name, Phone, Contact_Title, Country from customers table .\\n\\n\\n\\n\\n\\n select\\n\\n contact_name, \\n\\n Phone,\\n\\n contact_title,\\n\\n country\\n\\n from customers \\n\\n where country <> 'USA' and (contact_title like 'Sales%' or contact_title like 'Marketing%')\"),\n",
       " Document(metadata={'topic': 'Like Operator in Where Statement', 'question_no': 4}, page_content=\"4.Get a list of the names and numbers of contacts outside of the US and Mexico. Also the contact title shouldn't begin with “Sales” or “Marketing”. \\n\\nExpected output : Contact_Name, Phone, Contact_Title, Country from customers table \\n\\n select\\n\\n contact_name, \\n\\n Phone,\\n\\n contact_title,\\n\\n country\\n\\n from customers \\n\\n where country <> 'USA'\\n\\n  and  country <> 'Mexico'\\n\\n  and contact_title not like 'Sales%' \\n\\n  and contact_title not like 'Marketing%'\"),\n",
       " Document(metadata={'topic': 'Like Operator in Where Statement', 'question_no': 5}, page_content=\"5. Get the details of the orders which are shipped to UK but Shipping city doesn't NOT start from 'V' .\\n\\nExpected Output : customer_id, employee_id,order_date,ship_city columns from orders table.\\n\\n select\\n\\n customer_id,\\n\\n employee_id,\\n\\n order_date,\\n\\n ship_city\\n\\n from orders \\n\\n where ship_country ='UK' and ship_city not like 'V%'\"),\n",
       " Document(metadata={'topic': 'Level of Data', 'question_no': 6}, page_content=\"6.Using customers table, find the details of the customers having country starting from 'F' and city is not starting from 'S'.\\n\\nExpected output : Customer_id,contact_name,city and country from customers table in exact same order\\n\\n select\\n\\n customer_id,\\n\\n contact_name,\\n\\n city,\\n\\n country\\n\\n from customers \\n\\n where country like 'F%' and city not like 'S%'\\n\\n\\n\\nMilestone 9\\n\\n\\nPractice Problem : Level of Data\"),\n",
       " Document(metadata={'topic': 'Level of Data', 'question_no': 1}, page_content='1.In the orders table, identify whether customer_id is the level of data or not. For this count customer id with and without DISTINCT functions and see if both the numbers are the same or not. If both the counts are the same, it means that the used column is the level of data else different columns are level of data.\\n\\nExpected Output: COUNT(customer_id)  and COUNT(DISTINCT customer_id). The simple COUNT is going to give you 830 while the COUNT DISTINCT is going to give you 89. It simply means that there are duplicate customer_id in the table. It is logically true as well, as a customer can place multiple orders and hence the id of the customer is going to be appeared multiple time in orders table\\n\\nselect \\n\\n count(customer_id),\\n\\n count(distinct customer_id)\\n\\n from \\n\\n orders'),\n",
       " Document(metadata={'topic': 'Level of Data', 'question_no': 2}, page_content='2.In the orders table, identify whether employee_id is the level of data or not. For this count employee_id with and without DISTINCT functions and see if both the numbers are the same or not. If both the counts are the same, it means that the used column is the level of data else different columns are the level of data.\\n\\nExpected Output: COUNT(employee_id)  and COUNT(DISTINCT employee_id). The simple COUNT is going to give you 830 while the COUNT DISTINCT is going to give you 9. It simply means that there are duplicate employee_id in the table.\\n\\n \\n\\nselect \\n\\n count(employee_id),\\n\\n count(distinct employee_id)\\n\\n from \\n\\n orders'),\n",
       " Document(metadata={'topic': 'Level of Data', 'question_no': 3}, page_content='3.In the orders table, identify whether order_id is the level of data or not. For this count order_id with and without DISTINCT functions and see if both the numbers are the same or not. If both the counts are the same, it means that the used column is the level of data else different columns are the level of data.\\n\\nExpected Output: COUNT(order_id)  and COUNT(DISTINCT order_id). The simple COUNT is going to give you 830 while the COUNT DISTINCT is going to give you 830 as well. Hence, order_id is the level of data as every row in this table is representing one order. \\n\\n select \\n\\n count(order_id),\\n\\n count(distinct order_id)\\n\\n from \\n\\n orders'),\n",
       " Document(metadata={'topic': 'Level of Data', 'question_no': 4}, page_content='4.In the product table, identify whether category_id is the level of data or not. For this count category_id with and without the DISTINCT function and see if both the numbers are the same or not. If both the counts are the same, it means that the used column is the level of data else different columns are the level of data.\\n\\nExpected Output: COUNT(category_id)  and COUNT(DISTINCT category_id). The simple COUNT is going to give you 77 while the COUNT DISTINCT is going to give you 8. It simply means that there are duplicate category_id in the table. And logically this is true as well. We are looking at the products table, which has a list of all the products. Every product table is categorized into some category hence, there would be duplicate category_ids in the table as there might be more than one products in a single category\\n\\n select \\n\\n count(category_id),\\n\\n count(distinct category_id)\\n\\n from \\n\\n products'),\n",
       " Document(metadata={'topic': 'Group By Statement', 'question_no': 5}, page_content='5.In the product table, identify whether product_id is the level of data or not. For this, count product_id with and without the DISTINCT function and see if both the numbers are the same or not. If both the counts are the same, it means that the used column is the level of data else different columns are the level of data.\\n\\nExpected Output: COUNT(product_id)  and COUNT(DISTINCT product_id). The simple COUNT is going to give you 77 while the COUNT DISTINCT is 77 as well. It simply means product_id is the level of data in the table. And logically this is true as well. This table is representing one product_id in one row, hence product_id itself is level of data in the table\\n\\n select \\n\\n count(product_id),\\n\\n count(distinct product_id)\\n\\n from \\n\\n products\\n\\n\\n\\n\\n\\nPractice Problem : Group By Statement'),\n",
       " Document(metadata={'topic': 'Group By Statement', 'question_no': 1}, page_content='1.Get the number of units on order from each supplier.\\n\\nExpected Output : List of the Supplier_id with sum of the Units_on_order from products tables. The output should be sorted by supplier_id in ascending order\\n\\n select \\n\\n supplier_id,\\n\\n sum(units_on_order) \\n\\n from products \\n\\n group by 1 \\n\\n order by 1 asc'),\n",
       " Document(metadata={'topic': 'Group By Statement', 'question_no': 2}, page_content='2.Get the price of the most expensive item in each category. \\n\\nExpected Output : List of the Category_ID with maximum unit_price from products table. The output should be sorted by category_id in descending order.\\n\\n select \\n\\n category_id,\\n\\n max(unit_price)\\n\\n from products \\n\\n group by 1 \\n\\n order by 1 desc'),\n",
       " Document(metadata={'topic': 'Group By Statement', 'question_no': 3}, page_content='3.Get the price of the cheapest item from each supplier. \\n\\nExpected Output : List of the Supplier_id with minmum unit_price from products table. The output should be sorted by the newly created minimum price column in ascending order. In case the output has more than one supplier_ids having same minimum price, then sort the output in descending order of supplier_id. In SQL, while using the order by statement, instead of writing full name, we can also write the sequence number of the columns in the output. Refer following code \\n\\nSELECT Supplier_ID, MIN(Unit_Price)\\nFROM Products\\nGROUP BY Supplier_ID\\nORDER BY 2 ASC,\\n1 DESC\\n\\nHere 2 means newly created min(unit_price) column and 1 means supplier_id column\\n\\nSELECT Supplier_ID, MIN(Unit_Price)\\n\\nFROM Products\\n\\nGROUP BY Supplier_ID\\n\\nORDER BY 2 ASC,\\n\\n1 DESC'),\n",
       " Document(metadata={'topic': 'Group By Statement', 'question_no': 4}, page_content='4.Get a list of order_ids, cash values and the number of units in order_details. \\n\\nCash Value = Quantity x Unit_price \\n\\nTotal Units = Sum of Quantity\\n\\nExpected Output : List of the  Order_ID, cash value and units columns from order_details table. The final output should be sorted by order_id ascending, then CashValue in descending order and then Total Units in ascending order \\n\\nselect \\n\\n order_id,\\n\\n sum(quantity* unit_price) as cash_value ,\\n\\n sum(quantity) as total_units \\n\\n from \\n\\n order_details \\n\\n group by 1\\n\\n order by 1 asc, 2 desc, 3 asc\\n\\n 5.Using Products, get the total units on order from each supplier\\n\\nExpected Output: List of the Supplier_id with the sum of the Unit_on_orders column from the products table. The output should be sorted by Supplier_id in ascending order\\n\\n select \\n\\n supplier_id,\\n\\n sum(units_on_order) \\n\\n from \\n\\n products \\n\\n group by 1\\n\\n order by 1 asc'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 6}, page_content='6.Using the products table, get the price of the cheapest item from each supplier. \\n\\nExpected Output : List of the Supplier_id and Unit_price columns from products table. The table should be sorted by supplier_id in descending order.\\n\\n select \\n\\n supplier_id,\\n\\n min (unit_price) \\n\\n from \\n\\n products \\n\\n group by 1\\n\\n order by 1 desc\\n\\n\\n\\nPractice Problem : Having Statement'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 1}, page_content='1.Using the products table, get a list of categories that contain less than ten product.\\n\\nExpected Output : Category_id and count of products columns in exact same sequence. The output should be sorted by category_id in ascending order\\n\\n select \\n\\n category_id,\\n\\n count(product_id)\\n\\n from products\\n\\n group by 1 \\n\\n having count(product_id) < 10\\n\\n order by 1 asc'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 2}, page_content='2.Using products table, get the list of the category_ids with more than 100 units in stock in total. \\n\\nExpected output : Category_ID, Sum(Units_In_Stock) in exact same sequence. The output should be sorted by total units in stock in ascending order. In case, if there is more then one category having the same total units in stock then the output should be sorted by category id in descending order\\n\\n \\n\\nTo sort tables with more than one column, we can also use give the position of the columns in the ORDER BY statement. For example:\\n\\nSELECT Category_ID, SUM(Units_In_Stock)\\nFROM Products\\nGROUP BY Category_ID\\nHAVING SUM(Units_In_Stock) > 100\\nORDER BY 2 ASC, \\n1 DESC\\n\\nIn the above query in ORDER BY, 2 means SUM(Units_In_Stock) column and 1 means Category_ID\\n\\nSELECT Category_ID, SUM(Units_In_Stock)\\n\\nFROM Products\\n\\nGROUP BY Category_ID\\n\\nHAVING SUM(Units_In_Stock) > 100\\n\\nORDER BY 2 ASC, \\n\\n1 DESC'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 3}, page_content='3. Using the products table, get the suppliers_id having only one product.\\n\\nExpected output : Supplier_id, count of product_id column in exact same sequence. The output should be sorted by supplier_id in descending order\\n\\nSELECT supplier_ID, \\n\\ncount(product_id) as count_of_product_id\\n\\nFROM Products\\n\\nGROUP BY 1\\n\\nHAVING count(product_id) = 1 \\n\\nORDER BY 1 DESC'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 4}, page_content='4.Using the products table, list the categories which don’t have units on order.\\n\\nExpected Output : Category_id column\\n\\nSELECT category_id\\n\\nFROM Products\\n\\nGROUP BY 1\\n\\nHAVING sum (units_on_order) = 0'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 5}, page_content='5.Using order_details table, list the orders_ids having less than fifty total quantity. \\n\\nExpected output : Order_id and total Quantity column in exact same sequence. The output should be sorted by order_id in descending order.\\n\\nSelect\\n\\norder_id,\\n\\nsum(quantity) as total_quantity\\n\\nfrom \\n\\norder_details \\n\\ngroup by 1\\n\\nhaving sum(quantity) < 50 \\n\\norder by 1 desc'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 6}, page_content='6.Using order_details table, get a list of order_id in which any of the products have been discounted by more than 20%. \\n\\nExpected Output : Order_ID, maximum ofDiscount in the exact same sequence. The output should be sorted by order_id in descending order\\n\\nSelect\\n\\norder_id,\\n\\nmax(discount) \\n\\nfrom \\n\\norder_details \\n\\ngroup by 1\\n\\nhaving max(discount) > 0.2\\n\\norder by 1 desc\\n\\n\\n\\nPractice Problems: Aggregation functions I'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 1}, page_content='1.Find customers who appear in the orders table more than two times.\\n\\nExpected output : cust_id from orders table. The output should be sorted by cust_id in ascending order\\n\\nselect\\n\\ncust_id\\n\\nfrom orders\\n\\ngroup by 1\\n\\nhaving count(*)>2\\n\\norder by 1 asc'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 2}, page_content='2.Find customers whose total cost ( sum of total_order_cost)  is more than 250.\\n\\nExpected output: cust_id, total_cost , the output should be sorted by cust_id in ascending order\\n\\nselect\\n\\ncust_id,\\n\\nsum(total_order_cost) as total_cost\\n\\nfrom orders\\n\\ngroup by 1\\n\\nhaving sum(total_order_cost)>250\\n\\norder by 1 asc'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 3}, page_content='3. Find the total_customers from each city.\\n\\nExpected output : city,total_customers , the output should be sorted by city in ascending order\\n\\nselect\\n\\ncity,\\n\\ncount(*) as total_customerrs\\n\\nfrom customers\\n\\ngroup by 1\\n\\norder by 1 asc'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 4}, page_content=\"4. Calculate the total revenue (sum of total_order_cost) from each customer in March 2019. Include only customers who were active in March 2019.\\n\\nExpected output: cust_id, total_revenue , the output should be sorted by cust_id in ascending order\\n\\nselect\\n\\ncust_id,\\n\\nsum(total_order_cost) as total_revenue\\n\\nfrom \\n\\norders \\n\\nwhere  order_date between '2019-03-01' and '2019-03-31'\\n\\ngroup by 1\\n\\norder by 1 asc\"),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 5}, page_content='5. Find customers who have never made an order. Output the first name of the customer.\\n\\nExpected output: first_name , the output should be sorted by first_name in descending order.\\n\\nselect\\n\\nfirst_name\\n\\nfrom customers\\n\\nwhere id not in (select cust_id from orders)\\n\\norder by 1 desc\\n\\n\\n\\nPractice Problems: Aggregation functions II'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 1}, page_content='1.Print the first three characters of the first name.\\n\\nExpected output: name , the output should be sorted by name in ascending order\\n\\nselect\\n\\nleft (first_name,3) as name \\n\\nfrom \\n\\nworker\\n\\norder by 1 asc'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 2}, page_content='2. Find the duplicate records in the title table.\\n\\nOutput the worker title, affected_from date, and the number of times the records appear in the dataset.\\n\\nExpected output: worker_title, affected_from, n_affected , the output should be sorted by worker_title in ascending order\\n\\nselect\\n\\nworker_title,\\n\\naffected_from,\\n\\ncount(*) as n_affected\\n\\nfrom title \\n\\ngroup by 1,2\\n\\nhaving count(*)>1\\n\\norder by 1 asc'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 3}, page_content='3. Calculate the total salary for each department. Provide the salary as well as the corresponding department.\\n\\nExpected output: department, total_salary , the output should be sorted by department in ascending order\\n\\nselect\\n\\ndepartment,\\n\\nsum(salary) as total_salary\\n\\nfrom worker\\n\\ngroup by 1\\n\\norder by 1 asc'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 4}, page_content=\"4. Find the total workers (count of worker ids) who have salary greater than 80000 in each department and joined after feb 2014.\\n\\nExpected output: department, workers , the output should be sorted by department in ascending order.\\n\\nselect\\n\\ndepartment,\\n\\ncount(worker_id) as workers\\n\\nfrom worker\\n\\nwhere salary > 80000 and joining_date > '2014-02-28'\\n\\ngroup by 1\\n\\norder by 1 asc\"),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 5}, page_content='5. Find the details of worker with the least salary without using MIN function.\\n\\nExpected output: first_name, last_name, joining_date, department,salary \\n\\nselect\\n\\nfirst_name,\\n\\nlast_name,\\n\\njoining_date,\\n\\ndepartment,\\n\\nsalary\\n\\nfrom\\n\\nworker\\n\\norder by 5 asc\\n\\nlimit 1\\n\\n\\n\\nPractice Problems: Aggregation functions III'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 1}, page_content='1.Find the minimum age of employees in each department.\\n\\nExpected output: departement, age , the output should be sorted by department in ascending order\\n\\n\\n\\nselect\\n\\ndepartment,\\n\\nMin(age)\\n\\nfrom \\n\\nemployee\\n\\ngroup by 1\\n\\norder by 1 asc'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 2}, page_content=\"2. Find the departments where the total salary (sum of salary) of female employees is greater than 10000\\n\\nExpected output: department, total_salary, the output should be sorted by department in descending order.\\n\\n \\n\\n\\n\\nselect\\n\\ndepartment,\\n\\nSum(salary)\\n\\nfrom \\n\\nemployee\\n\\ngroup by 1,sex\\n\\nhaving sum(salary) > 10000 and  sex ='F'  \\n\\norder by 1 desc\"),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 3}, page_content=\"3. Find employees whose first name end with 'm' or 'n'.\\n\\nExpected output: first_name, the output should be sorted by first_name in ascending order\\n\\nselect\\n\\nfirst_name\\n\\nfrom employee\\n\\nwhere first_name Like '%m' or first_name Like '%n'\\n\\norder by 1 asc\"),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 4}, page_content='4. Find the city wise total salary,minimum salary,maximum salary,average salary of employees.\\n\\nExpected output: city,total_salary,min_salary,max_salary,avg_salary , the output should be sorted by city in ascending order, round off the avg_salary upto 2 decimal points\\n\\nselect\\n\\ncity,\\n\\nsum(salary) as total_salary,\\n\\nmin(salary) as min_salary,\\n\\nmax(salary)as max_salary,\\n\\nround (avg(salary),2) as avg_salary\\n\\nfrom \\n\\nemployee\\n\\ngroup by 1\\n\\norder by 1 asc'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 5}, page_content=\"5. Find the employees whose are between 30 to 50 age and have bonus more than target.\\n\\nExpected output: first_name, last_name, the output should be sorted by first_name in descending order\\n\\n\\n\\nselect\\n\\nfirst_name,\\n\\nlast_name \\n\\nfrom \\n\\nemployee\\n\\nwhere age between '30' and '50'\\n\\n and bonus > target\\n\\norder by 1 desc\\n\\n\\n\\nPractice Problems: Aggregation functions IV\"),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 1}, page_content='1.Find the products having the total quantity (sum of orderquantity) greater than 2000.\\n\\nExpected output: product_id, total_quantity , the output should be sorted by total_quantity by descending order.\\n\\n\\n\\nselect\\n\\nproduct_id,\\n\\nsum(orderquantity) as total_quantity\\n\\nfrom sales\\n\\ngroup by 1\\n\\nhaving sum(orderquantity)> 2000\\n\\norder by 2 desc'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 2}, page_content=\"2. Find the total number of male married customers who have annualincome greater than 100000 and is a homeowner.\\n\\nExpected output: 429 \\n\\nselect\\n\\n distinct (count(customer_id))\\n\\nfrom customers\\n\\nwhere maritalstatus ='M' and gender = 'M' and annualincome > 100000 and homeowner = 'Y'\"),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 3}, page_content='3.  Find the total products(count of product ids) in each subcategory and color.\\n\\nExpected output: subcategory_id, productcolor, total_products , the output should be sorted by subcategory_id, productcolor in ascending order\\n\\nselect\\n\\nsubcategory_id,\\n\\nproductcolor,\\n\\ncount(product_id) as total_products\\n\\nfrom products\\n\\ngroup by 1,2 \\n\\norder by 1,2 asc\\n\\n\\n\\nMilestone 10\\n\\n\\nWriting queries in CTE format'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 1}, page_content='1.Identify the category_id having more than 10 products. Write query in CTE format\\n\\n with cate_prod as (\\n\\n     select \\n\\n     category_id,\\n\\n     count(product_name) as product_count\\n\\n     from products \\n\\n     group by 1 \\n\\n )\\n\\n select * from cate_prod\\n\\n where product_count>10\\n\\n\\n\\nPractice Problems: INNER JOIN'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 1}, page_content=\"1. Orders table has order-level information but it doesn't have the customer name. Customer details are available in the customer table. To get the customer name, you need to join the table. Write a query to get order_id, customer_id and contact name (from customer_details)\\n\\nExpected Output: order_id, customer_id, and contact name in exact same sequence. Sort the output in ascending order of order-id\\n\\n\\n\\nSELECT\\n\\nO.order_id,\\n\\nC.customer_id,\\n\\nC.contact_name\\n\\nFROM Orders as O\\n\\nINNER JOIN customers C\\n\\nON O.customer_id=C.customer_id\\n\\nORDER BY order_id ASC\"),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 2}, page_content=\"2. Orders table has order-level information but it doesn't have the employee first_name. Employee details are available in the employees table. To get the customer name, you need to join the table. Write a query to get order_id and first_name (from employees table)\\n\\nExpected Output: order_id, employee_id and first_name in exact same sequence. Sort the output in ascending order of order-id.\\n\\n\\n\\nSELECT\\n\\nO.order_id,\\n\\nE.employee_id,\\n\\nE.first_name\\n\\nFROM Orders as O\\n\\nINNER JOIN employees E\\n\\nON O.employee_id=E.employee_id\\n\\nORDER BY order_id ASC\"),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 3}, page_content=\"3. Now you have learned how to use JOIN. Let's write a query joining more then 2 tables. Write a query to get order_id, contact_name(from customers) and last_name(from employees)\\n\\nExpected Output: order_id, contact_name and last_name in exact same sequence. Sort the output in ascending order of order-id.\\n\\nSELECT\\n\\nO.order_id,\\n\\nC.contact_name,\\n\\nE.last_name\\n\\nFROM Orders as O\\n\\nINNER JOIN employees E\\n\\nON O.employee_id = E.employee_id\\n\\nINNER JOIN  customers C\\n\\nON O.customer_id=C.customer_id\\n\\nORDER BY order_id ASC\"),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 4}, page_content='4. Check if all the previous queries have same number of rows or not. Lets first check how many rows we have in orders table\\n\\nExpected Output: 830\\n\\nAns -  SELECT  COUNT (*) FROM orders'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 5}, page_content='5. Check if all the previous queries have the same number of rows or not. Lets check how many rows for the output of the following query\\n\\nSELECT\\n\\no.order_id,\\n\\nc.customer_id,\\n\\nc.contact_name\\n\\nFROM orders as o \\n\\nJOIN customers as c \\n\\nON c.customer_id = o.customer_id\\n\\nORDER BY 1 ASC\\n\\nExpected Output: 830\\n\\n\\n\\nWITH table1 as (SELECT\\n\\nO.order_id,\\n\\nC.customer_id,\\n\\nC.contact_name\\n\\nFROM orders O\\n\\nINNER JOIN customers C\\n\\nON O.customer_id=C.customer_id\\n\\nORDER BY 1 ASC)\\n\\nSELECT COUNT(*) FROM table1'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 6}, page_content='6.Check if all the previous queries have the same number of rows or not. Lets check how many rows for the output of the following query\\n\\nSELECT\\no.order_id,\\no.employee_id,\\ne.first_name\\nFROM orders as o \\nJOIN employees as e \\nON e.employee_id = o.employee_id\\nORDER BY 1 ASC\\n\\nExpected Output: 830\\n\\nWITH table1 as (SELECT\\n\\no.order_id,\\n\\no.employee_id,\\n\\ne.first_name\\n\\nFROM orders as o \\n\\nJOIN employees as e \\n\\nON e.employee_id = o.employee_id\\n\\nORDER BY 1 ASC\\n\\n)\\n\\nSELECT COUNT(*) FROM table1'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 7}, page_content=\"7.If you check the number of rows in the following query, you will again find 830 rows. This is happening because although customer_id and employee_id are getting repeated in orders table, they are active as the foreign key. Customers and employees tables don't have duplicate value of customer_id and employee_id respectively. Hence, due to many to one mapping, we are getting 830 result everytime\\n\\nSELECT\\no.order_id,\\nc.contact_name,\\ne.last_name\\nFROM orders as o \\nJOIN employees as e \\nON e.employee_id = o.employee_id\\nJOIN customers as c \\nON C.customer_id = o.customer_id\\nORDER BY 1 ASC\\n\\nExpected Output: 830\\n\\nWITH table1 as (SELECT\\n\\no.order_id,\\n\\nc.contact_name,\\n\\ne.last_name\\n\\nFROM orders as o \\n\\nJOIN employees as e \\n\\nON e.employee_id = o.employee_id\\n\\nJOIN customers as c \\n\\nON C.customer_id = o.customer_id\\n\\nORDER BY 1 ASC\\n\\n)\\n\\nSELECT COUNT(*) FROM table1\\n\\n\\n\\nPractice Problems: INNER JOINS\"),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 1}, page_content='1.Return a list of products and their category names. Include product_name and unit price columns from the Products table and only the category name column from the Categories table. \\n\\nExpected Output : category_name,product_name and Unit_price. The output should be sorted by Unit price first in ascending order, then product_name in descending order and then category_name in ascending order.\\n\\n\\n\\nselect \\n\\nc.category_name,\\n\\np.product_name,\\n\\np.unit_price \\n\\nfrom categories c\\n\\njoin products p \\n\\non c.category_id = p.category_id\\n\\norder by 3 asc,2 desc, 1 asc'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 2}, page_content='2.Using orders and customer table, get the contact name of the customer against every order_id in the orders table\\n\\nExpected Output : order_id and contact_name in exact same sequence. The output should be sorted by order_id in ascending order\\n\\n\\n\\nselect \\n\\no.order_id,\\n\\nc.contact_name\\n\\nfrom orders o \\n\\njoin customers c \\n\\non o.customer_id = c.customer_id\\n\\norder by 1 asc'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 3}, page_content='3.Calculate company_name wise total units_on_order.\\n\\nExpected output : company_name and sum of Units_On_Order in exact same order. The output should be sorted by sum of units_on_order ascending first then category_name in descending\\n\\n\\n\\nSELECT\\n\\nS.company_name,\\n\\nSUM(P.units_on_Order)\\n\\nFROM suppliers S\\n\\nINNER JOIN Products P\\n\\nON S.supplier_id = P.supplier_id\\n\\nGROUP BY S.company_name\\n\\nORDER BY 2 ASC,\\n\\n1 DESC'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 4}, page_content='4.Using products and categories table, get the price of the most expensive item in each category_name. \\n\\nExpected Output: category_name and maximum of unit_price. The output should be sorted by maximum price first in descending order then category_name in ascending order\\n\\n\\n\\nSELECT\\n\\nC.category_name,\\n\\nMAX(P.unit_price)\\n\\nFROM categories C\\n\\nINNER JOIN Products P\\n\\nON C.category_id = P.category_id\\n\\nGROUP BY C.category_name\\n\\nORDER BY 2 DESC,\\n\\n1 ASC\\n\\n\\n\\n\\n\\nPractice Problems: Multiple INNER JOINs'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 1}, page_content='1.Using order_details and products table, map category_id against every order_id and product_id.\\n\\nExpected Output: Order_id, product_id and category_id sorted by order_id in ascending order and product id in ascending order\\n\\nselect \\n\\n o.order_id,\\n\\n p.product_id,\\n\\n p.category_id\\n\\n from order_details o \\n\\n join products p \\n\\n on o.product_id = p.product_id\\n\\n order by 1 asc,2 asc\\n\\nOR\\n\\n select \\n\\n o.order_id,\\n\\n p.product_id,\\n\\n c.category_id\\n\\n from order_details o \\n\\n join products p \\n\\n on o.product_id = p.product_id\\n\\n join categories c \\n\\n on c.category_id = p.category_id\\n\\n order by 1 asc,2 asc'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 2}, page_content=\"2.In previous questions, we cannot get category_name as the products table doesn't have a category names. To get the category name, you need to write another join with the categories table. Using order_details, categories and products table, map category_name against every order_id and product_id.\\n\\n\\n\\nExpected Output: Order_id, product_id and category_name sorted by order_id in ascending order and product id in ascending order\\n\\n\\n\\nSELECT\\n\\nO.order_id,\\n\\nP.product_id,\\n\\nC.category_name\\n\\nFROM Products P\\n\\nINNER JOIN order_details O\\n\\nON O.product_id=P.product_id\\n\\nINNER JOIN categories C  \\n\\nON C.category_id=P.category_id\\n\\nORDER BY 1 ASC,\\n\\n2 ASC\"),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 3}, page_content=\"3.In previous questions, we cannot get category_name as the products table doesn't have a category names. To get the category name, you need to write another join with the categories table. Using order_details, categories, and products table, identify quantity(from order_details) sold for every category name. Name the sum of quantity as quantity_sold\\n\\nExpected Output: Category_name and quantity_sold sorted by quantity_sold in ascending order. Don't forget to rename the newly created column\\n\\nSELECT\\n\\nC.category_name,\\n\\nSUM(O.quantity) As quantity_sold\\n\\nFROM Products P\\n\\nINNER JOIN order_details O\\n\\nON O.product_id=P.product_id\\n\\nINNER JOIN categories C  \\n\\nON C.category_id=P.category_id\\n\\nGROUP BY C.category_name\\n\\nORDER BY quantity_sold ASC\\n\\n\\n\\nPractice Problems: LEFT JOIN\"),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 1}, page_content='1.Using the customers and orders table, find the contact_name and customer_id of customers who have not purchased anything from Northwind\\n\\nExpected Output: Contact_name and customer_id in exact same sequence. The output should be sorted by customer_id in ascending order\\n\\n\\n\\nselect\\n\\nc.contact_name,\\n\\nc.customer_id\\n\\nfrom customers c\\n\\nleft join orders o \\n\\non c.customer_id= o.customer_id\\n\\nwhere o.order_id is null\\n\\norder by 1 asc'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 2}, page_content='2. Write a query that returns some of the territories do not have any assigned employees.\\n\\nExpected output : Territories_ID with null values. Output should be sorted by Territories_ID in ascending order.\\n\\n\\n\\nselect\\n\\nt.territory_id,\\n\\ne.employee_id\\n\\nfrom territories t\\n\\nleft join employee_territories e \\n\\non t.territory_id=e.territory_id\\n\\nwhere e.employee_id is null\\n\\norder by 1 asc\\n\\n\\n\\nPractice Problems: LEFT JOIN II'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 1}, page_content='1.Get list of all the customer_id who purchased at least once.\\n\\nExpected Output: List of customer_id in ascending order. Name this list as transacting_customer\\n\\n\\n\\nSELECT\\n\\ncustomer_id as transacting_customer\\n\\nFROM\\n\\nOrders\\n\\nGROUP BY 1\\n\\nORDER BY 1'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 2}, page_content='2. Put the list of customer_ids from the customers table in the LEFT and join it with the transacting_customer id.\\n\\nExpected Output: transacting_customer and all customer_ids. The output should be sorted by transacting_customer ids in ascending order\\n\\n\\n\\nWITH transacting_customers AS (SELECT\\n\\ncustomer_id as transacting_customer\\n\\nFROM\\n\\nOrders\\n\\nGROUP BY 1)\\n\\nSELECT\\n\\ncustomer_id,\\n\\ntransacting_customer\\n\\nFROM customers C\\n\\nLEFT JOIN transacting_customers as Tc\\n\\nON Tc.transacting_customer= C.customer_id\\n\\nORDER BY  transacting_customer ASC\\n\\n\\n\\n\\n2nd answer. \\nSELECT \\n\\nc.customer_id,\\n\\no.customer_id as transacting_customer\\n\\nfrom customers c \\n\\nleft join orders o \\n\\non c.customer_id = o.customer_id\\n\\ngroup by 1,2\\n\\norder by transacting_customer asc'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 3}, page_content='3. Find out the customer_ids who have not purchased anything from store\\n\\n\\n\\nExpected Output: List of customer_ids who did not purchase anything. The output should be sorted by ascending order\\n\\n\\n\\nWITH transacting_customers AS (SELECT\\n\\ncustomer_id as transacting_customer\\n\\nFROM\\n\\nOrders\\n\\nGROUP BY 1)\\n\\nSELECT\\n\\ncustomer_id\\n\\nFROM customers C\\n\\nLEFT JOIN transacting_customers as Tc\\n\\nON Tc.transacting_customer= C.customer_id\\n\\nWHERE transacting_customer IS NULL\\n\\nORDER BY 1 ASC'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 4}, page_content='4. What is the total number of rows you will get if you use INNER JOIN between customers and orderes table\\n\\nExpected Output: 830\\n\\n\\n\\nSELECT\\n\\nCOUNT (*)\\n\\nFROM customers C\\n\\nINNER JOIN orders O\\n\\nON C.customer_id = O.customer_id'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 5}, page_content='5. What is the total number of rows you will get if you use LEFT JOIN between customers(right side) and orders table (left side)\\n\\nExpected Output: 830\\n\\n\\n\\nSELECT\\n\\nCOUNT (*)\\n\\nFROM  orders O\\n\\nLEFT JOIN customers C\\n\\nON C.customer_id = O.customer_id'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 6}, page_content='6. What is the total number of rows you will get if you use LEFT JOIN between customers(right side) and orders table (left side)\\n\\nExpected Output: 832. Also try to find out the reason why this number is higher then previous 2 questions\\n\\n\\n\\nSELECT\\n\\nCOUNT (*)\\n\\nFROM customers C\\n\\nLEFT JOIN orders O\\n\\nON C.customer_id = O.customer_id\\n\\n\\n\\nPractice Problems: LEFT JOIN III'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 1}, page_content=\"1.Find the details of each customer regardless of whether the customer made an order. Output the customer's first name, last name, and the city along with the order details.\\n\\nExpected output: first_name,last_name,city,order_date,order_details,total_order_cost\\n\\nYou may have duplicate rows in your results due to a customer ordering several of the same items. Sort records based on the customer's first name in ascending order and the order details,order_date,total_order_cost in descending order.\\n\\n\\n\\nselect\\n\\nc.first_name,\\n\\nc.last_name,\\n\\nc.city,\\n\\no.order_date,\\n\\no.order_details,\\n\\no.total_order_cost\\n\\nfrom customers c \\n\\nleft join orders o \\n\\non c.id = o.cust_id\\n\\norder by 1 asc,\\n\\norder_date desc,\\n\\norder_details desc,\\n\\ntotal_order_cost desc\"),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 2}, page_content='2. Find the number of orders, the number of customers, and the total cost of orders for each city. Only include cities that have made at least 5 orders and count all customers in each city even if they did not place an order.\\n\\nOutput each calculation along with the corresponding city name.\\n\\nExpected output: city, orders_per_city, customers_per_city, orders_cost_per_city , the output should be sorted by city in ascending order\\n\\n\\n\\nselect\\n\\nc.city,\\n\\ncount(distinct o.id) as orders_per_city,\\n\\ncount(distinct c.id) as customers_per_city,\\n\\nSum(o.total_order_cost) as orders_cost_per_city\\n\\nfrom customers c\\n\\nLEFT JOIN Orders o\\n\\non c.id = o.cust_id\\n\\ngroup by 1\\n\\nhaving count(o.id) >= 5\\n\\norder by 1 asc'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 3}, page_content=\"3. Find the customer with the highest daily total order cost between 2019-02-01 to 2019-05-01. If customer had more than one order on a certain day, sum the order costs on daily basis. Output customer's first name, total cost of their items, and the date.\\n\\nFor simplicity, you can assume that every first name in the dataset is unique.\\n\\nExpected output: first_name, total_order_cost, order_date\\n\\n\\n\\nWITH cte  AS\\n\\n(SELECT\\n\\nfirst_name,\\n\\ncust_id,\\n\\nsum(total_order_cost) as total_order_cost,\\n\\norder_date\\n\\nFROM orders as o\\n\\nLEFT JOIN customers as c\\n\\nON o.cust_id = c.id\\n\\nWHERE order_date BETWEEN '2019-02-01' AND '2019-05-01'\\n\\nGROUP BY 1,2,4)\\n\\nSelect\\n\\nfirst_name,\\n\\ntotal_order_cost,\\n\\norder_date\\n\\nfrom cte\\n\\norder by 2 desc\\n\\nlimit 1\"),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 4}, page_content=\"4. Canculate the total cost(sum of total_order_cost) for each customer. In case there is a customer who hasn't placed any order, include it in your output with a value of 0.\\n\\nExpected output: id,  first_name, last_name, total_cost , the output should be sorted by id in ascending order\\n\\n\\n\\nSELECT c.id, c.first_name, c.last_name, \\n\\nCOALESCE(SUM(total_order_cost), 0) AS total_cost \\n\\nFROM customers c \\n\\nLEFT JOIN Orders o \\n\\nON c.id = o.cust_id \\n\\nGROUP BY 1,2,3\\n\\nORDER BY 1 ASC\\n\\n\\n\\nPractice Problems: LEFT JOIN IV\"),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 1}, page_content='1.Calculate the total revenue (sum of quantity*unit_price) made per book. Output the book ID and total sales per book. In case there is a book that has never been sold, include it in your output with a value of 0.\\n\\nExpected output: book_id, total_sales , the output should be sorted by book_id in descending order.\\n\\n\\n\\nCorrect query in DA \\n\\n\\nselect\\n\\na.book_id,\\n\\nsum(b.quantity*a.unit_price) as total_sales\\n\\nfrom amazon_books a \\n\\nleft join amazon_books_order_details b \\n\\non a.book_id = b.book_id\\n\\ngroup by 1\\n\\norder by 1 desc'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 2}, page_content=\"2. Find the details of each book regardless of whether the book was sold. \\n\\n\\n\\nExpected output: book_id, book_title, order_details_id, order_id, quantity, unit_price, you may have duplicate rows in your results due to a customer ordering several of the same items. Sort records based on the book's name and details in ascending order.\\n\\n\\n\\nSELECT\\n\\nab.book_id,\\n\\nab.book_title,\\n\\nod.order_details_id,\\n\\nod.order_id,\\n\\nod.quantity,\\n\\nab.unit_price\\n\\nFROM AMAZON_BOOKS ab\\n\\nLEFT JOIN AMAZON_BOOKS_ORDER_DETAILS od \\n\\nON od.book_id = ab.book_id \\n\\nORDER BY 2 ASC,3 ASC\\n\\n\\n\\nPractice Problems: LEFT JOIN V\"),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 1}, page_content=\"1.Find all the male customers who haven't placed any order.\\n\\nExpected output: customer_id, first_name, the output should be sorted by customer_id in ascending order \\n\\n\\n\\nselect\\n\\nc.customer_id,\\n\\nc.firstname\\n\\nfrom customers c \\n\\nleft join sales s \\n\\non cast(c.customer_id as int) =cast(s.customer_id as int)\\n\\nwhere c.gender = 'M' and s.orderdate is null\"),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 2}, page_content=\"2. Find all the male married customers who haven't bought the product 'Front Brakes'.\\n\\nExpected output: customer_id, firstname, lastname, the output should be sorted by customer_id in descending order.\\n\\n\\n\\nwith table1 as\\n\\n(\\n\\n    Select CAST(customer_id as INT) as customer_id,firstname,lastname\\n\\n    from customers\\n\\n    where maritalstatus='M' and gender='M'\\n\\n),\\n\\ntable2 as\\n\\n(\\n\\n    Select CAST(customer_id as INT) as c2,ordernumber\\n\\n    from sales s\\n\\n    join products p\\n\\n    on s.product_id=p.product_id\\n\\n    where productname<>'Front Brakes'\\n\\n)\\n\\nSelect distinct t1.customer_id, t1.firstname,t1.lastname\\n\\nfrom table1 t1\\n\\nleft join table2 t2\\n\\non t1.customer_id=t2.c2\\n\\norder by 1 desc\\n\\n\\n\\nPractice Problems: UNION and UNION ALL\"),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 1}, page_content=\"1.Create a list of order_is where either the shipping country is Germany or the total quantity of orders is more than 200.\\n\\n\\n\\nExpected Output: Distinct list of orders_ids fulfilling both conditions. Sort the output in descending order of order_ids. Use CTE statements to write the query\\n\\n\\n\\nWITH order_germany as (\\n\\n    SELECT order_id\\n\\n    FROM orders\\n\\n    WHERE ship_country ='Germany'),\\n\\n    Order_200 as(\\n\\n    SELECT\\n\\n    order_id\\n\\n    FROM order_details\\n\\n    GROUP BY 1\\n\\n    HAVING SUM(quantity) > 200),\\n\\n    UNION_Data as (\\n\\nSELECT * FROM order_germany\\n\\nUNION\\n\\nSELECT * FROM Order_200)\\n\\nSELECT * FROM UNION_Data\\n\\nORDER BY 1 DESC\\n\\n\\n\\n\\n\\n 2.Company wants to send special gifts to some customers who are falling in either of the following buckets.\\n\\nCustomers who have placed more than 20(excluding) times based on the data\\n\\nCustomers having 'Owner' as the contact title\\n\\nExpect Output: Customer id list sorted in descending order. \\n\\n\\n\\nWITH customer_20 AS (\\n\\n    SELECT customer_id\\n\\n    FROM orders\\n\\n    GROUP BY 1\\n\\n    HAVING count(*)>20),\\n\\n    contact_title_Owner as (\\n\\n    SELECT\\n\\n    customer_id\\n\\n    FROM customers\\n\\n    WHERE contact_title='Owner'),\\n\\n    Union_Data AS  (\\n\\n    SELECT * FROM customer_20\\n\\n    UNION\\n\\n    SELECT * FROM contact_title_Owner\\n\\n    )\\n\\n    SELECT * FROM Union_Data\\n\\n    ORDER BY 1 DESC\"),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 3}, page_content=\"3.Identify the list of employee_ids that are either working in 02116, 03049, 06897, 02903 territory_ids or having designation as 'Sales Manager'\\n\\nExpected Output: list of distinct employee_ids sorted by descending order\\n\\n\\n\\nWITH employee_working AS (\\n\\n    SELECT employee_id\\n\\n    FROM Employee_territories\\n\\n    WHERE territory_id IN ('02116','03049','06897','02903')),\\n\\n    employee_destination as (\\n\\n    SELECT\\n\\n    employee_id\\n\\n    FROM employees\\n\\n    WHERE title='Sales Manager'\\n\\n    GROUP BY 1),\\n\\n    Union_Data AS  (\\n\\n    SELECT * FROM employee_working\\n\\n    UNION\\n\\n    SELECT * FROM employee_destination\\n\\n    )\\n\\n    SELECT * FROM Union_Data\\n\\n    ORDER BY 1 DESC\"),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 4}, page_content=\"4.In the previous question (Identify the list of employee_ids that are either working in 02116, 03049, 06897, 02903 territory_ids or having designation as 'Sales Manager') use UNION instead of UNION ALL and check how the result is different from previous\\n\\n\\n\\nExpected Output: list of distinct employee_ids sorted by descending order\\n\\n\\n\\nWITH employee_working AS (\\n\\n    SELECT employee_id\\n\\n    FROM Employee_territories\\n\\n    WHERE territory_id IN ('02116','03049','06897','02903')),\\n\\n    employee_destination as (\\n\\n    SELECT\\n\\n    employee_id\\n\\n    FROM employees\\n\\n    WHERE title='Sales Manager'\\n\\n    GROUP BY 1),\\n\\n    Union_Data AS  (\\n\\n    SELECT * FROM employee_working\\n\\n    UNION ALL \\n\\n    SELECT * FROM employee_destination\\n\\n    )\\n\\n    SELECT * FROM Union_Data\\n\\n    ORDER BY 1 DESC\\n\\n\\n\\n\\n\\nPractice Problems: JOIN with aggregation\"),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 1}, page_content='1.Identify revenue generated for every order_id. Revenue = unit_price x quantity x (1-discount).\\n\\nExpected Output: Order_id and Revenue. The output should be sorted by revenue in descending order. Make sure the column names are proper else the query will not be accepted\\n\\nSELECT\\n\\norder_id ,\\n\\nSUM(unit_price * quantity * (1-discount)) AS Revenue\\n\\nFROM\\n\\nOrder_details\\n\\nGROUP BY order_id\\n\\nORDER BY Revenue DESC'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 2}, page_content='2.Identify revenue generated for every product_name. Revenue = unit_price x quantity x (1-discount).\\n\\nExpect Output: product_name and Revenue. The output should be sorted by revenue in descending order. Make sure the column names are proper else the query will not be accepted\\n\\n\\n\\nSELECT\\n\\nP.product_name,\\n\\nSUM(O.unit_price *quantity *(1-discount)) AS Revenue\\n\\nFROM\\n\\nOrder_details O\\n\\nINNER JOIN Products P\\n\\nON P.product_id = O.product_id\\n\\nGROUP BY P.product_name\\n\\nORDER BY Revenue DESC'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 3}, page_content='3. Identify revenue generated for every category_id. Revenue = unit_price x quantity x (1-discount).\\n\\nExpect Output: category_id and revenue. The output should be sorted by revenue in descending order. Make sure the column names are proper else the query will not be accepted\\n\\n \\n\\nSELECT\\n\\nP.category_id,\\n\\nSUM(O.unit_price *quantity *(1-discount)) AS Revenue\\n\\nFROM\\n\\nOrder_details O\\n\\nINNER JOIN Products P\\n\\nON P.product_id = O.product_id\\n\\nGROUP BY P.category_id\\n\\nORDER BY Revenue DESC'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 4}, page_content='4.Identify revenue generated for every category_name. Revenue = unit_price x quantity x (1-discount).\\n\\nExpect Output: product_name and category_name. The output should be sorted by revenue in descending order. Make sure the column names are proper else the query will not be accepted\\n\\n\\n\\nSELECT\\n\\nP.product_name,\\n\\nC.category_name,\\n\\nSUM(O.unit_price *quantity *(1-discount)) AS Revenue\\n\\nFROM\\n\\nOrder_details O\\n\\nINNER JOIN Products P\\n\\nON P.product_id = O.product_id    \\n\\nINNER JOIN categories C\\n\\nON C.category_id = P.category_id\\n\\nGROUP BY C.category_name, P.product_name\\n\\nORDER BY Revenue DESC'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 5}, page_content='5.Calculate total units_in_stock for every category_name.\\n\\nExpected Output: category_name and total units_in_orders. Sort the output in decreasing order of total units_in_order.\\n\\n\\n\\nSELECT\\n\\nC.category_name,\\n\\nSUM(units_in_stock)\\n\\nFROM\\n\\ncategories C\\n\\nINNER JOIN Products P\\n\\nON C.category_id = P.category_id\\n\\nGROUP BY C.category_name\\n\\nORDER BY 2 DESC'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 6}, page_content='6.Count discounted product_ids for every category_name. Name the newly aggregated column as \"discounted_items\"\\n\\nExpected Output: Category_name and discounted_items(count products having discontinued = 1). The output should be sorted by discounted_items in descending order.\\n\\n\\n\\nSELECT\\n\\nC.category_name,\\n\\nCOUNT(P.product_id) AS discounted_items\\n\\nFROM\\n\\ncategories C\\n\\nINNER JOIN Products P\\n\\nON C.category_id = P.category_id\\n\\nWHERE P.discontinued = 1\\n\\nGROUP BY C.category_name\\n\\nORDER BY 2 DESC'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 7}, page_content=\"7. Identify the count of products supplied by every supplier name. Use the products and supplier table to get the info.\\n\\nExpected Output: supplier's company_name and product_count. The output should be in descending order of product_count\\n\\n\\n\\nSELECT\\n\\nS.company_name,\\n\\nCOUNT(P.product_id)\\n\\nFROM\\n\\nsuppliers S\\n\\nINNER JOIN Products P\\n\\nON S.supplier_id = P.supplier_id\\n\\nGROUP BY S.company_name\\n\\nORDER BY 2 DESC\\n\\n\\n\\nPractice Problems: JOIN with aggregation II\"),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 1}, page_content='1.Calculate order_id wise total payment paid by every customer_id till date.  Total payment = unit_price x quantity x (1-discount). Name the newly created column as total_payment else the query is not going to be accepted\\n\\nExpected Output: Customer_id, order_id, and total_payment. The output should be in decreasing order of order_id \\n\\n\\n\\nSELECT\\n\\nO.customer_id,\\n\\nO.order_id,\\n\\nSUM(Od.unit_price * quantity * (1-discount)) AS total_payment\\n\\nFROM order_details Od\\n\\nINNER JOIN orders O\\n\\nON O.order_id = Od.order_id\\n\\nGROUP BY O.customer_id,\\n\\nO.order_id\\n\\nORDER BY 2 DESC'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 2}, page_content='2. Calculate  total payment paid by every customer_id till date.  Total payment = unit_price x quantity x (1-discount). Name the newly created column as total_payment else the query is not going to be accepted\\n\\nExpected Output: Customer_id and total_payment. The output should be in decreasing order of total_payment \\n\\n\\n\\nSELECT\\n\\nO.customer_id,\\n\\nSUM(Od.unit_price * quantity * (1-discount)) AS total_payment\\n\\nFROM orders O\\n\\nINNER JOIN order_details Od\\n\\nON O.order_id = Od.order_id\\n\\nGROUP BY 1\\n\\nORDER BY 2 DESC'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 3}, page_content=\"3. In the previous table, we had customer_id wise total payment. But its very difficult to identify the customer from customer_id. Add the customer's contact name in the previous table as well. \\n\\nExpected Out: customer_id, contact_name and total_payment. The final output should be sorted by total_payment column in descending order.\\n\\n\\n\\nSELECT\\n\\nO.customer_id,\\n\\nC.contact_name,\\n\\nSUM(Od.unit_price * quantity * (1-discount)) AS total_payment\\n\\nFROM customers C\\n\\nINNER JOIN orders O\\n\\nON O.customer_id = C.customer_id\\n\\nINNER JOIN order_details Od\\n\\nON O.order_id = Od.order_id\\n\\nGROUP BY O.customer_id,C.contact_name\\n\\nORDER BY 3 DESC\"),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 4}, page_content=\"4.Calculate total quantity ordered against every customer's contact_name.\\n\\nExpected Output: contact_name and total quantity ordered. Sort the output by quantity in descending order\\n\\n\\n\\nSELECT\\n\\nC.contact_name,\\n\\nSUM(quantity)\\n\\nFROM customers C\\n\\nINNER JOIN orders O\\n\\nON O.customer_id = C.customer_id\\n\\nINNER JOIN order_details Od\\n\\nON O.order_id = Od.order_id\\n\\nGROUP BY O.customer_id,C.contact_name\\n\\nORDER BY 2 DESC\\n\\n\\n\\nPractice Problems: JOINS I\"),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 1}, page_content=\"1.Retrieve a list of all employees whose job title is 'manager'.\\nOutput the first name along with the corresponding title.\\n\\nExpected output: first_name, worker_title, the output should be sorted by first_name in ascending order.\\n\\nselect\\n\\nw.first_name,\\n\\nt.worker_title\\n\\nfrom \\n\\ntitle t \\n\\njoin worker w \\n\\non t.worker_ref_id = w.worker_id \\n\\nwhere worker_title = 'Manager'\\n\\norder by 1 asc\"),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 2}, page_content='2. Find employees who earn the same salary.\\n\\nOutput the worker id along with the first name and the salary in descending order.\\n\\nExpected output: worker_id, first_name, salary, the output should be sorted by worker_id in ascending order\\n\\n\\n\\nselect\\n\\nw.worker_id,\\n\\nw.first_name,\\n\\nw.salary\\n\\nfrom \\n\\nworker w\\n\\njoin worker w1 \\n\\non w.worker_id != w1.worker_id and w.salary = w1.salary\\n\\norder by 1 asc'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 3}, page_content=\"3. You have been asked to find employees in the HR department, and then output the result with one duplicate.\\n\\nThe employee's first name and department should be included in your output.\\n\\nNote: This dataset does not contain any duplicates.\\n\\nExpected output: first_name, department, the output should be sorted by first_name in ascending order\\n\\n\\n\\nselect\\n\\nw.first_name,\\n\\nw.department \\n\\nfrom worker w \\n\\njoin worker w1 \\n\\non w.worker_id = w1.worker_id\\n\\nwhere w.department = 'HR'\\n\\nUnion all \\n\\nselect\\n\\nw.first_name,\\n\\nw.department \\n\\nfrom worker w \\n\\njoin worker w1 \\n\\non w.worker_id = w1.worker_id\\n\\nwhere w.department = 'HR'\\n\\norder by 1 asc\"),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 4}, page_content='4. You have been asked to find the employee with the highest salary in each department.\\n\\nExpected Output: department , first_name, salary, the output should be sorted by department and first_name in ascending order\\n\\n\\n\\nSELECT w1.Department, w1.first_name, w1.Salary\\n\\nFROM worker w1\\n\\nJOIN (\\n\\n  SELECT Department, MAX(Salary) AS MaxSalary\\n\\n  FROM worker\\n\\n  GROUP BY Department\\n\\n) w2 ON w1.Department = w2.Department AND w1.Salary = w2.MaxSalary\\n\\nORDER BY w1.Department ASC, w1.Salary ASC\\n\\n\\n\\n\\nwith cte as (SELECT Department, first_name, Salary\\n\\nFROM worker),\\n\\ncte2 as (SELECT Department, MAX(Salary) AS MaxSalary\\n\\n  FROM worker \\n\\n  GROUP BY Department)\\n\\n  select w1.Department, w1.first_name, w1.Salary\\n\\n  from cte  w1 \\n\\n  join cte2 w2 \\n\\n  ON w1.Department = w2.Department AND w1.Salary = w2.MaxSalary\\n\\nORDER BY w1.Department ASC, w1.Salary ASC\\n\\n\\n\\nPractice Problems: JOINS II'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 1}, page_content=\"1.Find the number of posts which were reacted to with a like.\\n\\n\\n\\nselect \\n\\n count(distinct f.post_id)\\n\\nfrom \\n\\nfacebook_posts f\\n\\njoin facebook_reactions fr \\n\\non f.post_id = fr.post_id\\n\\nwhere fr.reaction = 'like'\"),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 2}, page_content=\"2. Find the number of people who posted regarding basketball.\\n\\n\\n\\nselect \\n\\ncount(distinct f.post_id)\\n\\nfrom \\n\\nfacebook_posts f\\n\\njoin facebook_reactions fr \\n\\non f.post_id = fr.post_id\\n\\nwhere f.post_keywords like '%basketball%'\"),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 3}, page_content='3. Find total reactions(count of reactions) on posts datewise.\\n\\nExpected output: post_date, reaction,total_reactions, The output should be sorted by post_date and reaction in ascending order\\n\\n\\n\\nselect\\n\\nf.post_date,\\n\\nfr.reaction,\\n\\ncount(fr.reaction) as total_reactions\\n\\nfrom \\n\\nfacebook_posts f \\n\\njoin facebook_reactions fr\\n\\non f.post_id = fr.post_id\\n\\ngroup by 1,2\\n\\norder by 1,2 asc\\n\\n\\n\\n\\n\\nPractice Problems: JOINS III'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 1}, page_content='1.Find all the pizza ids for every month where the order count is more than 100.\\n\\nExpected output: month, pizza_id, total_orders, The output should be sorted by month and pizza_id in ascending order.\\n\\n\\n\\nselect\\n\\nextract(Month from date ) as Month,\\n\\nod.pizza_id,\\n\\ncount(od.order_id) as total_orders\\n\\nfrom\\n\\norders  o \\n\\njoin order_details od \\n\\non o.order_id = od.order_id\\n\\ngroup by 1,2\\n\\nhaving count(o.order_id)>100\\n\\nOrder by 1,2 asc'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 2}, page_content='2. Retrieve the total number of pizzas for each category.\\n\\nExpected output: category, total_pizzas, The output should be sorted by category in ascending order.\\n\\n\\n\\nselect\\n\\npt.category,\\n\\ncount(o.order_id)\\n\\nfrom \\n\\npizza_types pt\\n\\njoin pizzas p \\n\\non pt.pizza_type_id = p.pizza_type_id\\n\\njoin order_details o \\n\\non o.pizza_id = p.pizza_id\\n\\ngroup by 1\\n\\norder by 1 asc\\n\\n2nd \\n\\n\\nselect\\n\\npt.category,\\n\\nSum(quantity)\\n\\nfrom \\n\\npizza_types pt\\n\\njoin pizzas p \\n\\non pt.pizza_type_id = p.pizza_type_id\\n\\njoin order_details o \\n\\non o.pizza_id = p.pizza_id\\n\\ngroup by 1\\n\\norder by 1 asc'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 3}, page_content='3. Find the order id with the highest order amount.\\n\\nExpected output: order_id, order_amount\\n\\n\\n\\nselect\\n\\no.order_id,\\n\\nmax(o.quantity*p.price) as order_amount\\n\\nFrom \\n\\norder_details o \\n\\njoin pizzas p \\n\\non o.pizza_id = p.pizza_id\\n\\ngroup by 1\\n\\norder by 2 desc\\n\\nlimit 1'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 4}, page_content='4. Find all the pizza types that are ordered more than 2000 times.\\n\\nExpected output: pizza_type_id\\n\\n\\n\\nselect\\n\\np.pizza_type_id\\n\\nfrom pizzas as p\\n\\njoin order_details as od\\n\\non p.pizza_id = od.pizza_id\\n\\ngroup by 1\\n\\nhaving count (od.order_id) > 2000\\n\\norder by 1'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 5}, page_content='5.Find total number of orders placed in every month and pizza name.\\n\\nExpected output: month, name, total_orders, The output should be sorted by month and pizza_type_id in escending order.   \\n\\n\\n\\nselect\\n\\nextract(month from date) as month,\\n\\npt.name,\\n\\ncount(o.order_id)\\n\\nfrom orders o\\n\\njoin order_details od\\n\\non o.order_id = od.order_id\\n\\njoin pizzas p\\n\\non od.pizza_id = p.pizza_id\\n\\njoin pizza_types pt\\n\\non p.pizza_type_id = pt.pizza_type_id\\n\\ngroup by 1,2\\n\\n\\n\\nPractice Problems: JOINS IV'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 1}, page_content='1.Find the number of hosts that have accommodations in countries of which they are not citizens.\\n\\n\\n\\nselect \\n\\ncount (distinct h.host_id)\\n\\nfrom airbnb_hosts h\\n\\njoin airbnb_units u \\n\\non h.host_id = u.host_id\\n\\nwhere country <> nationality'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 2}, page_content=\"2. Find the average age of guests reviewed by each host.\\n\\nOutput the user along with the average age.\\n\\nExpected output: from_user, average_age, The output should be sorted by from_user in ascending order\\n\\n\\n\\nselect\\n\\nr.from_user,\\n\\navg(g.age) as average_age\\n\\nfrom airbnb_reviews r \\n\\njoin airbnb_guests g \\n\\non r.to_user = g.guest_id \\n\\nwhere r.from_type = 'host'\\n\\ngroup by 1 \\n\\norder by 1\"),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 3}, page_content='3. Find matching hosts and guests pairs in a way that they are both of the same gender and nationality. Output the host id and the guest id of matched pair.\\n\\nExpected output: host_id, guest_id, The output should be sorted by host_id in ascending order\\n\\n\\n\\nselect\\n\\ndistinct h.host_id,\\n\\ng.guest_id \\n\\nfrom airbnb_hosts h \\n\\njoin airbnb_guests g \\n\\non g.nationality = h.nationality and  g.gender = h.gender \\n\\norder by 1'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 4}, page_content=\"4. Find the number of apartments per nationality that are owned by people under 30 years old.\\n\\nExpected output: nationality, apartment_count, The order should be sorted by the apartments count in descending order.\\n\\n\\n\\nselect\\n\\nh.nationality,\\n\\ncount(distinct unit_id) as apartment_count \\n\\nfrom airbnb_hosts h \\n\\njoin airbnb_units a \\n\\non h.host_id = a.host_id \\n\\nwhere h.age < 30 and unit_type = 'Apartment'\\n\\ngroup by 1 \\n\\norder by 2 desc\"),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 5}, page_content=\"5. Find the total number of available beds per hosts' nationality.\\n\\nExpected output: nationality, total_beds_available, The output should be sorted by total_beds_available in descending order\\n\\n\\n\\nselect\\n\\nh.nationality,\\n\\nsum(u.n_beds)\\n\\nfrom airbnb_hosts h \\n\\njoin airbnb_units u \\n\\non h.host_id = u.host_id \\n\\ngroup by 1\\n\\norder by 2 desc\\n\\n\\n\\nMilestone 11\\n\\n\\n\\n\\nPractice Problems: EXTRACT\"),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 1}, page_content='1.In the flights table, the scheduled departure column is in the time_stamp. Create another column where the year corresponding to the scheduled column will be mentioned. \\n\\nExpected Output: Flight_id, scheduled_departure, and year_of_scheduled_departure. The output should be sorted by flight_id in ascending order\\n\\n\\n\\nSELECT\\n\\nFlight_id,\\n\\nscheduled_departure,\\n\\nEXTRACT (year from scheduled_departure) AS year_of_scheduled_departure\\n\\nFROM Flights\\n\\nORDER BY 1 ASC'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 2}, page_content='2.Identify the time taken from departure to arrival using scheduled departure and scheduled arrival.\\n\\n\\n\\nExpected Output: flight_id and departure to arrival time duration. The output should be sorted in ascending order of flight_id\\n\\n\\n\\nSELECT\\n\\nFlight_id,\\n\\nscheduled_arrival-scheduled_departure\\n\\nFROM Flights\\n\\nORDER BY 1 ASC'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 3}, page_content='3.In the flights table, the scheduled departure column is in the time_stamp. Create 3 columns next to the flight_id and scheduled departure\\n\\nscheduled_departure_year : Get year from scheduled_departure column\\n\\nscheduled_departure_month : Get month number from the scheduled_departure column\\n\\nscheduled_departure_day: Get day of the month number from the scheduled_departure column\\n\\nExpected Output: Flight_id, scheduled_departure, scheduled_departure_year, scheduled_departure_month and scheduled_departure_day. The output should be sorted by flight_id in ascending order.\\n\\n\\n\\nSELECT\\n\\nFlight_id,\\n\\nscheduled_departure,\\n\\nEXTRACT(year from scheduled_departure) AS scheduled_departure_year,\\n\\nEXTRACT(month from scheduled_departure) AS scheduled_departure_month,\\n\\nEXTRACT(day from scheduled_departure) AS scheduled_departure_day\\n\\nFROM Flights\\n\\nORDER BY 1 ASC'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 4}, page_content='4.In the flights table, the scheduled departure column is in the time_stamp. Count the number of flights against every scheduled_departure_year and scheduled_departure_month. Both month and Year should be in different columns\\n\\n\\n\\nExpected Output: scheduled_departure_year, scheduled_departure_month and flight_count. The output should be sorted by year first in ascending order then month in ascending order. Make sure that you use the column names as suggested in the question\\n\\n\\n\\nSELECT\\n\\nEXTRACT(year from scheduled_departure) AS scheduled_departure_year,\\n\\nEXTRACT(month from scheduled_departure) AS scheduled_departure_month,\\n\\nCount(flight_id) as flight_count\\n\\nFROM Flights\\n\\nGROUP BY 1,2\\n\\norder by 1 asc, 2 asc'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 5}, page_content='5.Identify Year and month-wise bookings using the bookings table. \\n\\nExpected Output: Year, month, and count of bookings. The table should be sorted by year first in ascending order and then the month in descending order\\n\\n\\n\\nSELECT\\n\\nEXTRACT(year from book_date) AS Year,\\n\\nEXTRACT(month from book_date) AS Month,\\n\\nCount(book_ref)\\n\\nFROM Bookings\\n\\nGROUP BY 1,2\\n\\nORDER BY 1 ASC,\\n\\n2 DESC\\n\\n\\n\\nPractice Problems: TO_CHAR()'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 1}, page_content=\"1.In the flights table, the scheduled departure column is in the time_stamp. Using TO_CHAR, create another column where the year corresponding to the scheduled column will be mentioned. \\n\\nExpected Output: Flight_id, scheduled_departure, and year_of_scheduled_departure. The output should be sorted by flight_id in ascending order\\n\\n\\n\\nSELECT\\n\\nFlight_id,\\n\\nscheduled_departure,\\n\\nTO_CHAR(scheduled_departure,'yyyy') as year_of_scheduled_departure\\n\\nFROM flights\\n\\nORDER BY 1\"),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 2}, page_content=\"2.In the flights table, the scheduled departure column is in the time_stamp. Using TO_CHAR(), create 3 columns next to the flight_id and scheduled departure\\n\\nscheduled_departure_year : Get year from scheduled_departure column\\n\\nscheduled_departure_month : Get month number from the scheduled_departure column\\n\\nscheduled_departure_day: Get day of the month number from the scheduled_departure column\\n\\nExpected Output: Flight_id, scheduled_departure, scheduled_departure_year, scheduled_departure_month and scheduled_departure_day. The output should be sorted by flight_id in ascending order. Make sure that you use the column names as suggested in the question\\n\\n\\n\\nSELECT\\n\\nFlight_id,\\n\\nscheduled_departure,\\n\\nTO_CHAR(scheduled_departure,'yyyy') as scheduled_departure_year,\\n\\nTO_CHAR(scheduled_departure,'mm') as scheduled_departure_month,\\n\\nTO_CHAR(scheduled_departure,'dd') as scheduled_departure_day\\n\\nFROM flights \\n\\nORDER BY 1\"),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 3}, page_content=\"3.In the flights table, the scheduled departure column is in the time_stamp. Count the number of flights against every scheduled_departure_year and scheduled_departure_month. Both month and Year should be in different columns created using TO_CHAR()\\n\\n\\n\\nExpected Output: scheduled_departure_year, scheduled_departure_month and flight_count. The output should be sorted by year first in ascending order then month in ascending order. Make sure that you use the column names as suggested in the question\\n\\n\\n\\nSELECT\\n\\nTO_CHAR(scheduled_departure,'yyyy') as scheduled_departure_year,\\n\\nTO_CHAR(scheduled_departure,'mm') as scheduled_departure_month,\\n\\ncount(flight_id)\\n\\nFROM flights\\n\\nGROUP BY 1,2\\n\\nORDER BY 1 ASC,\\n\\n2 ASC\"),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 4}, page_content=\"4. In the flights table, the scheduled departure column is in the time_stamp. Count the number of flights against every month and year combination. Both month and Year should be in same column in 'YYYY-MM' format as year_month using TO_CHAR()\\n\\nExpected Output: year_month and flight_count. The output should be sorted by year_month in ascending order\\n\\n\\n\\nSELECT\\n\\nTO_CHAR(scheduled_departure,'YYYY-MM') as year_month,\\n\\ncount(flight_id)\\n\\nFROM flights\\n\\nGROUP BY 1\\n\\nORDER BY 1 ASC\"),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 5}, page_content=\"5.Identify Year and month-wise bookings using the bookings table. \\n\\nExpected Output: Year_month in YYYY-MM format and count of bookings. The table should be sorted by Year_month in ascending order\\n\\n\\n\\nSELECT\\n\\nTO_CHAR(book_date,'YYYY-MM') as Year_month,\\n\\ncount(book_ref)\\n\\nFROM bookings\\n\\nGROUP BY 1\\n\\nORDER BY 1 ASC\"),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 6}, page_content=\"6.In bookings table, create another column where book date should look like 'Thursday , 10-Aug-2017'.\\n\\nExpected Output: book_ref, book_date and revised book_date in new format. The table should be sorted by asending order of book_ref\\n\\n\\n\\nSELECT\\n\\nbook_ref,\\n\\nbook_date,\\n\\nTO_CHAR(book_date,'Day, DD-Mon-YYYY') as book_date\\n\\nFROM bookings\\n\\nORDER BY 1 ASC\\n\\n\\n\\n\\n\\nPractice Problems: String Functions\"),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 1}, page_content=\"1.In the employees table, first name and last name are mentioned. Write a query to concate both and use space as a seperation between first name and last name. \\n\\nExpected Output: Exployee_id, first_name, last_name and full_name. Sort the output by employee_id in ascending order. Make sure the column names are correct\\n\\n\\n\\nSELECT\\n\\nemployee_id,\\n\\nfirst_name,\\n\\nlast_name,\\n\\nfirst_name ||' ' || last_name as full_name\\n\\nFROM employees\\n\\nORDER BY 1\"),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 2}, page_content='2.In the employees table, first name and last name are mentioned. Write a query to create email id in \"First_name.Last_name@skillovilla.com\"\\n\\nExpected Output: Exployee_id, first_name, last_name and email_id. Sort the output by employee_id in ascending order. Make sure the column names are correct. Make sure that all the letters in the email id should be in small case.\\n\\n\\n\\nSELECT\\n\\nemployee_id,\\n\\nfirst_name,\\n\\nlast_name,\\n\\nlower(first_name ||\\'.\\' || last_name ||\\'@skillovilla.com\\')as email_id\\n\\nFROM employees\\n\\nORDER BY 1'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 3}, page_content='3.Company wants to shorten the category description in the data. Identify the length(number of characters) of the description of every category.\\n\\nExpected Output: category_id, description, and description_length. The output should be sorted by category_id\\n\\n\\n\\nSELECT\\n\\ncategory_id,\\n\\ndescription,\\n\\nlength(description) as description_length\\n\\nFROM categories\\n\\nORDER BY 1'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 4}, page_content=\"4. Write a query to break contact_name into 2 different columns, first_name and second_name. Make sure that there should not be space in around first_name and last name. \\n\\nExpected Output: contact_name, first_name and last name. The output should be sorted in ascending order of contact_name\\n\\n\\n\\nSELECT\\n\\ncontact_name,\\n\\nleft(contact_name,POSITION(' 'IN contact_name)-1) as first_name,\\n\\nright(contact_name,length(contact_name)-POSITION(' 'IN contact_name)) as last_name\\n\\nFROM customers\\n\\nORDER BY 1 DESC\\n\\n\\n\\nPractice Problems: Windows functions\"),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 1}, page_content='1.Create order_rank for all the orders based on the order data. Also, observe if more than one order_Id have gotten same rank or not.\\n\\nExpected Output: Order_id, order_date and order_rank. \\n\\n\\n\\nSELECT\\n\\norder_id,\\n\\norder_date,\\n\\nRank() over(ORDER BY order_date) AS order_rank\\n\\nFROM orders'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 2}, page_content='2.Create shipping_rank for all the orders based on the orders data. The first shipped order should get the first rank. Also, observe if more than one order_Id have gotten the same rank or not.\\n\\nExpected Output: Order_id, shipped_date and shipping_rank. \\n\\n\\n\\nSELECT\\n\\norder_id,\\n\\nshipped_date,\\n\\nRank() over(ORDER BY shipped_date) AS shipping_rank\\n\\nFROM orders'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 3}, page_content='3. Create order_rank for all the orders based on the order data. The latest order should get the first rank Also, observe if more than one order_Id have gotten the same rank or not.\\n\\nExpected Output: Order_id, order_date and order_rank. \\n\\nSELECT\\n\\norder_id,\\n\\norder_date,\\n\\nRank() over(ORDER BY order_date DESC) AS order_rank\\n\\nFROM orders'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 4}, page_content='4.Company wants to give $5 cashback to every customer on the first order. Identify the order_ids and customer_ids which are eligible for getting the cashback. \\n\\n\\n\\nExpected Output: Customer_id and first_order_id. The output should be sorted by customer_id first then order_id\\n\\n\\n\\nWITH CTE AS ( SELECT\\n\\n    CUSTOMER_ID,\\n\\n    ORDER_ID\\n\\n    FROM ORDERS\\n\\n),\\n\\nCASHBACK_DATA AS (\\n\\n    SELECT\\n\\n    CUSTOMER_ID,\\n\\n    ORDER_ID,\\n\\n    RANK() OVER (PARTITION BY CUSTOMER_ID ORDER BY ORDER_ID ASC) AS ORDER_RANK\\n\\n    FROM CTE\\n\\n)\\n\\nSELECT\\n\\nCUSTOMER_ID,\\n\\nORDER_ID AS FIRST_ORDER_ID\\n\\nFROM CASHBACK_DATA\\n\\nWHERE ORDER_RANK = 1\\n\\nORDER BY 1,2'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 5}, page_content='5.To increase the sale of a new employee, bonus is given on the first sale of the employee. Identify the first order_id for every employee id using order_date in the orders table. \\n\\n\\n\\nWith cte as (\\n\\n     select employee_id, order_id\\n\\n     from orders ),\\n\\n     BONAS_DATA AS (\\n\\n    SELECT\\n\\n    employee_id,\\n\\n    ORDER_ID,\\n\\n    RANK() OVER (PARTITION BY employee_ID ORDER BY ORDER_ID ASC) AS ORDER_RANK\\n\\n    FROM CTE\\n\\n     )\\n\\n     SELECT\\n\\nemployee_id,\\n\\nORDER_ID AS FIRST_ORDER_ID\\n\\nFROM BONAS_DATA\\n\\nWHERE ORDER_RANK = 1'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 6}, page_content=\"6.Identify first order_id (using order_date) for every month. \\n\\n\\n\\nExpected Output: Year_month in 'YYYY-MM' format and first order_id. Final output should be sorted in ascending order of Year_month\\n\\n\\n\\n\\nwith year_month_orders as (\\n\\n    select\\n\\n    to_char(order_date, 'YYYY-MM') as year_month,\\n\\n    order_id,\\n\\n    order_date\\n\\n    from orders\\n\\n),\\n\\nRank_application as (\\n\\nselect\\n\\n*,\\n\\nrank() over(partition by year_month order by order_date) as order_rank\\n\\nfrom year_month_orders\\n\\n)\\n\\nselect\\n\\nyear_month,\\n\\norder_id\\n\\nfrom \\n\\nRank_application\\n\\nwhere order_rank =1\"),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 7}, page_content='7.Identify highest selling (quantity sold) product_id in every category_id\\n\\n\\n\\nExpected Output: Category_id, Highest_selling_product_id and quantity_sold. The output should be sorted by category_id in ascending order\\n\\n\\n\\nWITH product_cid AS (\\n\\nSELECT\\n\\nP.category_id,\\n\\nOd.product_id AS highest_selling_product_id,\\n\\nSUM(Od.quantity) AS quantity_sold\\n\\nFROM Products P\\n\\nINNER JOIN order_details Od\\n\\nON P.product_id=Od.product_id\\n\\nGROUP BY 1,2\\n\\n),\\n\\nrank_data AS(\\n\\n    SELECT *,\\n\\n    RANK() Over(PARTITION BY category_id ORDER BY quantity_sold DESC)AS rank_product\\n\\n    FROM product_cid\\n\\n)\\n\\nSELECT\\n\\ncategory_id,\\n\\nhighest_selling_product_id,\\n\\nquantity_sold\\n\\nFROM rank_data\\n\\nWHERE rank_product = 1\\n\\nORDER BY 1 ASC'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 8}, page_content='8.Identify least selling (quantity sold) product_id in every category_id\\n\\nExpected Output: Category_id, Highest_selling_product_id, and quantity_sold. The output should be sorted by category_id in ascending order\\n\\nWITH product_cid AS (\\n\\nSELECT\\n\\nP.category_id,\\n\\nOd.product_id AS highest_selling_product_id,\\n\\nSUM(Od.quantity) AS quantity_sold\\n\\nFROM Products P\\n\\nINNER JOIN order_details Od\\n\\nON P.product_id=Od.product_id\\n\\nGROUP BY 1,2\\n\\n),\\n\\nrank_data AS(\\n\\n    SELECT *,\\n\\n    RANK() Over(PARTITION BY category_id ORDER BY quantity_sold ASC)AS rank_product\\n\\n    FROM product_cid\\n\\n)\\n\\nSELECT\\n\\ncategory_id,\\n\\nhighest_selling_product_id,\\n\\nquantity_sold\\n\\nFROM rank_data\\n\\nWHERE rank_product = 1\\n\\nORDER BY 1 ASC'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 9}, page_content='9.Identify highest selling (quantity sold) product_id in every category_name\\n\\nExpected Output: Category_name, Highest_selling_product_id and quantity_sold. The output should be sorted by category_id in ascending order\\n\\nWITH product_cid AS (\\n\\nSELECT\\n\\nC.category_name,\\n\\nOd.product_id AS highest_selling_product_id,\\n\\nSUM(Od.quantity) AS quantity_sold\\n\\nFROM Products P\\n\\nINNER JOIN order_details Od\\n\\nON P.product_id=Od.product_id\\n\\nINNER JOIN categories C\\n\\nON C.category_id=P.category_id\\n\\nGROUP BY 1,2\\n\\n),\\n\\nrank_data AS(\\n\\n    SELECT *,\\n\\n    RANK() Over(PARTITION BY category_name ORDER BY quantity_sold DESC)AS rank_product\\n\\n    FROM product_cid\\n\\n)\\n\\nSELECT\\n\\ncategory_name,\\n\\nhighest_selling_product_id,\\n\\nquantity_sold\\n\\nFROM rank_data\\n\\nWHERE rank_product = 1\\n\\nORDER BY 1 ASC\\n\\n\\n\\nPractice Problems: Windows functions I'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 1}, page_content='1.Find the 2nd order id for every month\\n\\nExpected output: month, order_id, The output should be sorted by month in ascending order\\n\\n with t1 as (\\n\\n     select \\n\\n     extract (month from date) as months,\\n\\n     order_id,\\n\\n     date as dates\\n\\n     from \\n\\n     orders \\n\\n ),\\n\\n t2 as(\\n\\n    select\\n\\n    months,\\n\\n    order_id,\\n\\n    dates,\\n\\n    row_number() over(partition by months order by dates) as rnk\\n\\n    from t1\\n\\n     )\\n\\n     select\\n\\n     months,\\n\\n     order_id\\n\\n     from t2\\n\\n     where rnk = 2\\n\\n     order by 1'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 2}, page_content='2.Find the pizza_id that is least sold in each category.\\n\\nExpected output: category, pizza_id , The output should be sorted by category in ascending order. \\n\\n with t1 as(\\n\\n     select\\n\\n     pt.category,\\n\\n     od.pizza_id,\\n\\n     row_number () over(partition by category order by count(od.order_id) asc) as rnk \\n\\n     from pizzas p \\n\\n     join pizza_types pt \\n\\n     on p.pizza_type_id = pt.pizza_type_id\\n\\n     join order_details od \\n\\n     on od.pizza_id = p.pizza_id\\n\\n     group by 1,2\\n\\n )\\n\\n select \\n\\n category,pizza_id\\n\\n from t1 \\n\\n where rnk =1 \\n\\norder by 1'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 3}, page_content='3.Find the pizza id that is the most sold in each category.\\n\\nExpected output: category, pizza_id, The output should be sorted by category in ascending order\\n\\n with t1 as(\\n\\n     select\\n\\n     pt.category,\\n\\n     od.pizza_id,\\n\\n     row_number () over(partition by category order by count(od.order_id) desc) as rnk \\n\\n     from pizzas p \\n\\n     join pizza_types pt \\n\\n     on p.pizza_type_id = pt.pizza_type_id\\n\\n     join order_details od \\n\\n     on od.pizza_id = p.pizza_id\\n\\n     group by 1,2\\n\\n )\\n\\n select \\n\\n category,pizza_id\\n\\n from t1 \\n\\n where rnk =1 \\n\\n order by 1\\n\\n\\n\\nPractice Problems: Windows functions II'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 1}, page_content='1.Find the top 10 highest paid employees.\\nNote: If two people earn the same salary, they are counted separately.\\n\\nExpected output: worker_id, first_name, last_name, salary, joining_date, department, Sort records based on the salary in descending order.\\n\\n select\\n\\n worker_id,\\n\\n first_name,\\n\\n last_name,\\n\\n salary,\\n\\n joining_date,\\n\\n department\\n\\n from \\n\\n (select *,\\n\\n rank () over(order by  salary desc) as rnk \\n\\n from worker\\n\\n ) sb\\n\\n where rnk <= 11'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 2}, page_content='2.Find the fifth highest salary from a database without using TOP or LIMIT functions ?\\n\\nNote: Duplicate salaries should not be removed.\\n\\n\\n\\n select\\n\\n salary\\n\\n from \\n\\n (select *,\\n\\n row_number () over(order by  salary desc) as rnk \\n\\n from worker\\n\\n ) sb\\n\\n where rnk = 5'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 3}, page_content='3.Find the first 50% records of the dataset.\\n\\nExpected output: worker_id, first_name, last_name, salary, joining_date, department, The output should be sorted by worker_id in ascending order.\\n\\n with t1 as \\n\\n (select *,\\n\\n row_number () over() as row_num\\n\\n from worker\\n\\n ) \\n\\n select\\n\\n worker_id,\\n\\n first_name,\\n\\n last_name,\\n\\n salary,\\n\\n joining_date,\\n\\n department\\n\\n from t1\\n\\nwhere row_num <= (select count(*)/2 from worker)\\n\\n order by 1 asc'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 4}, page_content='4.Identify the employee with the highest salary in a given dataset or database? \\n\\nExpected output: first_name,salary, first_name .Output should be sorted by first_name in ascending order\\n\\n with t1 as \\n\\n (select \\n\\n first_name,\\n\\n salary,\\n\\n dense_rank () over(order by salary desc) as d_r\\n\\n from worker\\n\\n ) \\n\\n select\\n\\n first_name,\\n\\n salary\\n\\n from t1\\n\\nwhere d_r = 1\\n\\n\\n\\nPractice Problems: Advance SQL Statements I'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 1}, page_content='1.Each Employee is assigned one territory and is responsible for the Customers from that territory. There may be multiple employees assigned to the same territory. Write a query to get the Employees who are responsible for the maximum number of Customers. \\n\\nExpected output: empl_id, total_customers, The output should be sorted by empl_id in ascending order.\\n\\n select \\n\\n empl_id,\\n\\n total_customers\\n\\n from \\n\\n (select\\n\\n empl_id,\\n\\n count(cust_id) as total_customers,\\n\\n rank() over(order by count(cust_id)desc)rnk \\n\\n from map_employee_territory e \\n\\n join map_customer_territory c \\n\\n on e.territory_id = c.territory_id\\n\\n group by empl_id) sb\\n\\n where rnk = 1 \\n\\n order by 1 asc'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 2}, page_content='2.Write a query to find the Market Share at the Product Brand level for each Territory, for Time Period Q4-2021. Market Share is the number of Products of a certain Product Brand brand sold in a territory, divided by the total number of Products sold in this Territory. Only include these Product Brands that had at least one sale in a given territory.\\n\\nExpected output: territory_id, prod_brand, market_share, The output should be sorted by territory_id, prod_brand in ascending order\\n\\nwith sales_per_brand_territory as \\n\\n(select\\n\\nprod_brand,\\n\\nterritory_id,\\n\\ncount(*) as n_sales\\n\\nfrom fct_customer_sales s\\n\\njoin map_customer_territory t \\n\\non s.cust_id = t.cust_id\\n\\njoin dim_product p \\n\\non s.prod_sku_id = p.prod_sku_id\\n\\nwhere extract(Year from order_date) = 2021\\n\\nand extract(Quarter from order_date) = 4\\n\\ngroup by prod_brand, territory_id)\\n\\nselect \\n\\nterritory_id,\\n\\nprod_brand,\\n\\nn_sales/sum(n_sales)\\n\\nover (partition by territory_id)*100 as market_share\\n\\nfrom sales_per_brand_territory\\n\\norder by 1,2'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 3}, page_content='3.Write a query to return Territory and corresponding Sales Growth. Compare growth between periods Q4-2021 vs Q3-2021. If Territory (say T123) has Sales worth $100 in Q3-2021 and Sales worth $110 in Q4-2021, then the Sales Growth will be 10% [ i.e. = ((110 - 100)/100) * 100 ] Output the ID of the Territory and the Sales Growth. Only output these territories that had any sales in both quarters.\\n\\nExpected output: territory_id, sales_growth, The output should be sorted by territory_id in ascending order. Round off the sales_growth to 2 decimal points.\\n\\nWITH sales_by_quarter_territory AS\\n\\n  (SELECT territory_id,\\n\\n          EXTRACT (QUARTER\\n\\n                   FROM order_date) AS QUARTER,\\n\\n                  SUM(order_value) AS total_order_value\\n\\n   FROM fct_customer_sales s\\n\\n   JOIN map_customer_territory t ON s.cust_id = t.cust_id\\n\\n   WHERE EXTRACT (YEAR\\n\\n                  FROM order_date) = 2021\\n\\n     AND EXTRACT (QUARTER\\n\\n                  FROM order_date) IN (3, 4)\\n\\n   GROUP BY 1, 2)\\n\\nSELECT a.territory_id,\\n\\n      Round(cast((b.total_order_value - a.total_order_value) as decimal)  /\\n\\n      cast((a.total_order_value) as decimal)  * 100,2) AS sales_growth\\n\\nFROM sales_by_quarter_territory a\\n\\nJOIN sales_by_quarter_territory b ON a.territory_id = b.territory_id\\n\\nAND a.quarter < b.quarter'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 4}, page_content='4.Find the top selling brand in each territory.\\n\\nExpected output: territory_id, prod_brand, The output should be sorted by territory_id in ascending order\\n\\n\\n\\nSELECT\\n\\n    territory_id,\\n\\n      prod_brand from(\\n\\n    SELECT\\n\\n        m.territory_id AS territory_id,\\n\\n         p.prod_brand AS prod_brand,\\n\\n        ROW_NUMBER() OVER (PARTITION BY m.territory_id ORDER BY count(order_id) DESC) AS rn\\n\\n    FROM\\n\\n    DIM_PRODUCT p\\n\\n    JOIN\\n\\n        FCT_CUSTOMER_SALES s ON p.prod_sku_id= s.prod_sku_id\\n\\n    JOIN\\n\\n        MAP_CUSTOMER_TERRITORY m ON m.cust_id=s.cust_id\\n\\n    GROUP BY 1,2) t1 WHERE rn = 1 order by 1'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 5}, page_content='5.Retrieve the month and total number of brands for each month where the total number of brands is greater than 10.\\n\\nExpected output: month, total_brands, The output should be sorted by month in ascending order.\\n\\n\\nSelect\\n\\nextract(month from order_date) as month , count(prod_brand) as total_brands\\n\\nfrom fct_customer_sales f \\n\\njoin dim_product d \\n\\non f.prod_sku_id=d.prod_sku_id\\n\\ngroup by 1\\n\\nhaving count(prod_brand)>10\\n\\norder by 1\\n\\n\\n\\nPractice Problems: Advance SQL Statements II'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 1}, page_content=\"1.Rank the pizza categories on basis of total number of pizza types available who have the ingredient red pepper.\\n\\nExpected output: category, cat_rank, the output should be sorted by cat_rank in ascending order.\\n\\n\\n\\nSelect category, rank() over(order by count(pizza_type_id) desc) as cat_rank\\n\\nfrom pizza_types\\n\\nwhere lower(ingredients) like '%red pepper%'\\n\\ngroup by 1\\n\\norder by 2\"),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 2}, page_content='2.Find the first order id for each size of pizza.\\n\\nExpected output: size, order_id , Each size should have 1 record only, and the output should be sorted by size in ascending order.\\n\\n\\n\\nwith t1 as (Select size,od.order_id,date,time, row_number() over(partition by size order by date,time) as rnk\\n\\nfrom order_details od \\n\\njoin pizzas p \\n\\non od.pizza_id=p.pizza_id\\n\\njoin orders o \\n\\non o.order_id=od.order_id)\\n\\nSelect size, order_id \\n\\nfrom t1\\n\\nwhere rnk=1\\n\\norder by 1'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 3}, page_content=\"3.Find all the pizzas which have the ingredient 'Red Peppers' and replace it with 'Capsicum'.\\n\\nExpected output: category, name, ingredients, The output should be sorted by category and name in ascending order.\\n\\n\\n\\nSelect category,name,replace(ingredients, 'Red Peppers', 'Capsicum') as ingredients\\n\\nfrom (Select *\\n\\nfrom pizza_types\\n\\nwhere lower(ingredients) like '%red peppers%') t1\\n\\norder by 1,2\"),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 4}, page_content='4.Find the total order value for the order id with 5th highest total order value (price * quantity).\\n\\nExpected output: order_id, total_price\\n\\nwith t1 as (Select order_id, sum(price*quantity) as total_price, dense_rank() over(order by sum(price*quantity) desc) as rnk\\n\\nfrom order_details od \\n\\njoin pizzas p \\n\\non od.pizza_id=p.pizza_id\\n\\ngroup by 1\\n\\n)\\n\\nSELECT order_id, total_price\\n\\nfrom t1 \\n\\nwhere rnk=5\\n\\n\\n\\nMilestone 12\\n\\n\\nPractice Problems: Conditional Functions'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 1}, page_content='1.Write a query to classify the product_ids into \"Expensive\" and \"Affordable\" using the following logic.\\n\\nExpensive: Unit_price above 100\\n\\nAffordable: Unit_price Below 100\\n\\nExpected Output: Product_id,unit_price and price_category. The output should be sorted in ascending order of product_id\\n\\nSELECT \\n\\nproduct_id,\\n\\nunit_price,\\n\\nCASE \\n\\n WHEN unit_price >100 THEN \\'Expensive\\'\\n\\n ELSE \\'Affordable\\' END AS price_category\\n\\nFROM products\\n\\nORDER BY 1 ASC'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 2}, page_content=\"2.Classify employees into 'Old' and 'Very old' category using following logic\\n\\nBirth Year > 1950: Old\\n\\nBirth Year < 1950: Very old\\n\\nExpected Output: Employee_id, Birth_year and Age_category. Sort the table on employee_id in ascending order\\n\\n\\n\\nSELECT\\n\\nemployee_id,\\n\\n EXTRACT(year from birth_date),\\n\\n CASE\\n\\n WHEN EXTRACT(year from birth_date) > 1950 THEN 'Old'\\n\\n ELSE 'Very old' END AS Age_category\\n\\n FROM employees\\n\\n ORDER BY 1 asc\"),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 3}, page_content='3.Create a is_ship_region_present column that through true or false. True when ship_region is not null and False when ship_region is null\\n\\nExpected Output: Order_id, Ship_region, and is_ship_region_present. The output should be sorted by order_id in ascending order\\n\\n\\n\\nSELECT\\n\\norder_id,\\n\\nship_region,\\n\\nCASE\\n\\n    WHEN ship_region IS NULL THEN false\\n\\n    ELSE true END AS is_ship_region_present\\n\\nFROM orders \\n\\nOrder by 1 asc'),\n",
       " Document(metadata={'topic': 'Having Statement', 'question_no': 4}, page_content='4.From the data, create a column called is_shipped. It is going to be TRUE if shipping date is available. If shipping date is not available then its going to be FALSE\\n\\nExpected Output: order_id, order_date, shipped_date and is_shipped. The output should be sorted by order_id in ascending order\\n\\n\\n\\nSELECT\\n\\norder_id,\\n\\norder_date,\\n\\nshipped_date,\\n\\nCASE\\n\\n WHEN shipped_date IS NULL then FALSE\\n\\n ELSE TRUE END AS is_shipped\\n\\nFROM orders\\n\\nORDER BY 1 ASC\\n\\n\\n\\n=================================END======================================')]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "documents"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "97895ddb",
   "metadata": {},
   "source": [
    "## Step 1b & 1c - Indexing (Embedding Generation and Storing in Vector Store)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "7b00bd57",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a FAISS vector store from the documents\n",
    "embedding = OllamaEmbeddings(model=\"nomic-embed-text\")\n",
    "vector_store = FAISS.from_documents(documents, embedding)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "9e586048",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{0: '6a09643f-a645-4345-bd6e-964c816dee57',\n",
       " 1: 'dc8c992d-12a9-4bdc-aa85-6a5e2e8aba04',\n",
       " 2: 'eebac2f3-9279-4343-a9f1-63ef707d22f9',\n",
       " 3: '8781bd0d-70fc-456a-ba75-15c7ae5467f2',\n",
       " 4: '8d1fa931-0056-4c43-b30d-05d2a5bf4544',\n",
       " 5: 'bbe8ee45-00b0-45fe-a72c-4ee9fe8edfcf',\n",
       " 6: '5fb5204b-fc61-4fce-b327-f8e20c1c7636',\n",
       " 7: '92bf09d8-a855-42a8-bafa-e88a762c2517',\n",
       " 8: '524e7103-20e8-486e-8988-0aeb52106447',\n",
       " 9: '88069b88-a674-439b-9cee-328232fe70a1',\n",
       " 10: '2def0e94-13b2-4895-b056-45ba195f2ad7',\n",
       " 11: 'd19b5983-6c58-4baa-9878-d1edcc1330f0',\n",
       " 12: 'd5d3f433-8e2d-4d72-ae06-53c704825938',\n",
       " 13: '579858b1-f5c5-475c-8958-b16098c1a20b',\n",
       " 14: '05290ef9-c396-4e6a-876b-cfc33f15a35c',\n",
       " 15: 'ed477b0c-4975-4fb2-99d1-453155ec71e0',\n",
       " 16: '85a5b1b1-15e2-430d-a2c6-6e9a28b965e1',\n",
       " 17: 'a3508f85-03d9-4789-94b5-3bd1fb14102f',\n",
       " 18: '18c24c94-63dc-44a8-8ed5-c18bbe606528',\n",
       " 19: '65bdf2ce-a68e-4694-ad24-de868209a1ed',\n",
       " 20: '717eda48-d174-4698-a9b3-deeff61322a4',\n",
       " 21: '6594937a-1ae1-4391-8d9c-d72ab675fe53',\n",
       " 22: '72425307-102d-4daf-bbf9-c41f7bfe3262',\n",
       " 23: '41869304-96a6-41ec-ab7f-ac40a4228c2b',\n",
       " 24: '2e4610b5-4804-42b7-a90d-55fc65c6c1cd',\n",
       " 25: 'bc73e601-61b5-4587-8658-26b9fa3e5230',\n",
       " 26: '92acfa0e-05d0-4d72-84c6-81edffbe0360',\n",
       " 27: 'b3de98f0-f30e-4f3a-9ea6-4ea0e3accb07',\n",
       " 28: '599fdf26-9f00-4d47-b270-8a523edace79',\n",
       " 29: 'e20f0930-44c8-4e7d-a715-b78bd5d643c9',\n",
       " 30: '11f8da64-dd10-4f1d-8b4f-9b3914c218ae',\n",
       " 31: '3a36c949-d77c-424b-978d-3e2b523d29ec',\n",
       " 32: '016eb0a4-2ddc-47c9-9bd3-8cc571a97601',\n",
       " 33: '39be5849-744a-46bf-b9ba-fd1d6c94b8b0',\n",
       " 34: '5772203b-f050-4bf2-8874-82e358e9fd35',\n",
       " 35: '5ef51abd-646a-4204-8302-e11472a98209',\n",
       " 36: 'fba32a31-7779-4651-92bf-16524dfb296d',\n",
       " 37: '46c4382a-2069-4a27-bf70-4729f10bc659',\n",
       " 38: '8d80ca59-4de6-4def-9b7b-6a4f1c43d0b2',\n",
       " 39: '9e388cbf-2075-4131-9c77-768c5a799afa',\n",
       " 40: 'f207a91d-72f4-4646-9a01-1fb5d668b57f',\n",
       " 41: '1e4b54bb-91af-4676-b9dd-2a5ac592e603',\n",
       " 42: 'ecfa3fc3-d5d8-4434-b139-09abaa370dd3',\n",
       " 43: 'f35309a1-ea3f-4c1c-9a54-783955c49c6d',\n",
       " 44: 'c05296fd-b201-4623-a7a8-b75a72083d22',\n",
       " 45: 'd3a99297-cf92-4c6e-840e-21686aed55b8',\n",
       " 46: 'fd04a966-5d5a-4aa9-a913-810c118a12c3',\n",
       " 47: '81c00102-aedb-413c-8610-ebe3789b618c',\n",
       " 48: '8bbc3a2c-6dbb-40b9-a7fe-eb35dc3f89ee',\n",
       " 49: '768db589-b646-4fa1-8f01-efb5ccbdb09a',\n",
       " 50: '01660d40-8bda-410d-b53f-0e33d2374ec0',\n",
       " 51: '4105ad47-deea-400b-8f48-0b9b5eff845c',\n",
       " 52: '564ba341-6b26-4b4f-862a-b946501c6c47',\n",
       " 53: '16c46633-14b4-4ff1-84b3-6a810d4e8bf7',\n",
       " 54: '48608ff4-5b9d-48d1-b5c8-e7a96e62ecd7',\n",
       " 55: '243332cc-115c-4010-8580-578aaab24abd',\n",
       " 56: '4ed2659d-cadc-4075-af58-20d702ddafe3',\n",
       " 57: 'ea3e5761-efa3-4e2a-b368-11cc8ae5eeab',\n",
       " 58: '1e61c4d4-8d90-48fa-af1b-26dfd3e889f6',\n",
       " 59: 'c9e326f4-0a76-44db-bafd-cc9eda57a388',\n",
       " 60: 'f81fd4cd-40d8-437a-82c3-e5e726bedba9',\n",
       " 61: 'bf9626fb-4a15-4f19-9ee6-434d19994ce7',\n",
       " 62: '8737223f-ac5c-4468-a1e5-30fdfd7f3e34',\n",
       " 63: '0940f412-706c-452e-8067-61a0d121cdb3',\n",
       " 64: '6df4d89e-5a85-4d00-96c2-665ad2c0b179',\n",
       " 65: 'b562fd82-28ee-4399-b266-732d20986c62',\n",
       " 66: '3a0730a7-422f-4da0-80f6-37d8285b727f',\n",
       " 67: 'c06f6b35-41b4-48b7-8976-07cc198db2a0',\n",
       " 68: 'b09f2663-fc65-4c7e-a07c-e9603d56589d',\n",
       " 69: '400beea5-3ae4-4e74-8fcc-e8f63394bcc5',\n",
       " 70: '528f0b73-d0b6-4c70-86b0-ccb99d31f875',\n",
       " 71: 'e52aef6d-fc85-466a-8707-b239f541b92e',\n",
       " 72: 'bce466f8-b46e-44bd-aef2-16d39032e8b4',\n",
       " 73: 'eec9f207-5c48-4774-b38f-67aceb46d994',\n",
       " 74: '35d9d1cc-2282-4a08-9b37-09687245a371',\n",
       " 75: 'b36ced15-5594-444c-b907-31280f86d92d',\n",
       " 76: '912f80c0-4538-47ad-893c-dbcd81dc60fc',\n",
       " 77: 'ac6259bf-ee69-4fad-959a-96dcdf56a766',\n",
       " 78: '6e964f61-c2c8-4929-87de-5012fb2b3df6',\n",
       " 79: '9bdf3110-be17-4596-ac97-9c60373f0709',\n",
       " 80: 'e2b06da5-a6e5-4ea3-abe8-4d93f057c486',\n",
       " 81: '2e9cd6ea-43f5-49f5-8436-abb27c03bd0b',\n",
       " 82: '84f9be32-d871-4b5c-8531-ad00ee830ce3',\n",
       " 83: '42fcdaec-877c-45f8-8c86-d2babac6f3ee',\n",
       " 84: '377e7116-0751-4c2f-aba0-77fde5543ab9',\n",
       " 85: '6e2aa2f4-e046-403b-8851-45b141f4c2d6',\n",
       " 86: '7c72831e-7ac6-4a60-88fe-94e48f63cf8d',\n",
       " 87: '6cc44461-b28c-4d05-a5ba-17bb02363cc3',\n",
       " 88: 'b3cde6ff-0afc-4ac0-849e-32077caa36fd',\n",
       " 89: '90a984f3-b4c8-49ec-a314-b117d1f04aa8',\n",
       " 90: 'baf4b112-5e82-4790-8365-eaed3e42c7e3',\n",
       " 91: '0fbb1578-6c45-410d-ac49-11ae76c397eb',\n",
       " 92: 'd6ecea5a-d69e-4c94-8b58-49971696a9cf',\n",
       " 93: '5c4100b0-0cf7-403d-b7e3-249d3eff8290',\n",
       " 94: '45fabab2-ac22-4032-a3a0-2396bf551ccb',\n",
       " 95: '9de24919-ec26-4ca5-9ec1-9bcc626703ed',\n",
       " 96: '3d0a14bc-4384-4cb1-a676-ae4411ebb07d',\n",
       " 97: 'e6db6120-503c-4942-ab97-730c719dad68',\n",
       " 98: 'd328dbd1-62b6-4770-af2b-04e054123891',\n",
       " 99: 'a8acc7ee-e2e8-426e-bec3-687d675de6e9',\n",
       " 100: '68d04f42-7ce8-4827-b90e-b98458f58ea5',\n",
       " 101: 'c3d90cbd-0623-4550-a922-c0d662c03301',\n",
       " 102: '2cca48c7-d411-47fe-80ea-58a27b6f5007',\n",
       " 103: 'fb43f97d-5c08-4cce-906d-e20707534858',\n",
       " 104: '418fcfce-5ff2-41be-9111-b41ebf3bd9e2',\n",
       " 105: 'ca5b8d04-a457-48b4-98a6-95ab250d29d9',\n",
       " 106: 'cc94bb56-2739-48fc-bd10-bb0e295ad1d1',\n",
       " 107: '7e358d39-4e9c-4390-ac02-ca6873bbc7c8',\n",
       " 108: 'e1554347-33f4-462c-85c0-80d10b5568a6',\n",
       " 109: '8f01ef08-82bf-4175-8f3d-a97a6d54de93',\n",
       " 110: 'a29794c9-1289-4f37-8ea8-e14c5b36505d',\n",
       " 111: '40855c0e-ddab-4856-b8e7-0b4666bfe9b3',\n",
       " 112: '67e26d4a-ca02-4b29-b271-cb61841795f9',\n",
       " 113: '0c53e2aa-824c-44e4-9322-cd5b71432361',\n",
       " 114: 'e44e9256-06ca-4ec5-a473-6509a7db9f4c',\n",
       " 115: '08491969-5656-4bf8-b5d0-0bf13a730e4f',\n",
       " 116: '4eccd774-96a9-46ad-b24a-5418faffb204',\n",
       " 117: 'ef7cb10b-c9a9-4b4e-a1d0-919dd2ebe9ef',\n",
       " 118: 'b328431b-db9c-4ef9-afd7-587c2c96230e',\n",
       " 119: '22bd59e2-fd75-4327-83f2-5315159b4cc5',\n",
       " 120: '0469de1f-7ba3-4c9d-b4cd-bfd6b13d4e50',\n",
       " 121: '6b69d4a6-1861-493b-9368-668eec063afe',\n",
       " 122: 'aaaf1c9c-2036-415d-a199-f00ab8954fba',\n",
       " 123: 'dfdf50fd-2541-44e3-8a6a-42dd67ac7d6f',\n",
       " 124: '07814695-c6ff-49e4-930d-ad3563840e81',\n",
       " 125: '31c382f3-1c77-4f07-82a6-c0fbdd382ed9',\n",
       " 126: '5bf3df25-082e-4fa3-95c2-1e180f342c72',\n",
       " 127: '32de780c-1aeb-44fa-95e0-ba397c72e4f9',\n",
       " 128: '3ec8aff2-f072-4e53-95dd-a81b60b005a6',\n",
       " 129: 'f8526308-af28-461f-8f2d-a1caed79e34b',\n",
       " 130: '15b37cc1-0ccb-4ee9-b868-56d338ec24cc',\n",
       " 131: 'a072d43e-4196-4967-9d8a-5bbcd58b655b',\n",
       " 132: '126ead41-e0fe-4139-96ef-760714bc160f',\n",
       " 133: '09841481-6b89-4743-bec0-70830eb4e083',\n",
       " 134: '210912e6-f79a-4506-91a0-8216dc6f2fa9',\n",
       " 135: '1a1af617-0c83-4b1d-8863-6c725f5d2912',\n",
       " 136: '4239ad0a-1fa6-4229-b3ff-054c4f59f759',\n",
       " 137: 'a7e762cc-e564-4aad-b37a-b180216f6bed',\n",
       " 138: '720e9280-5e5f-4b22-977f-6fa3aefad9ef',\n",
       " 139: '080cdc11-f022-4f4d-818a-daf4b397f382',\n",
       " 140: 'c8abb82e-81e1-451d-b558-f3eefc824439',\n",
       " 141: '709f6775-f6ee-4d81-800c-54dcd2ce8f95',\n",
       " 142: '2f467b15-021c-40ed-b0c8-96349d6b1f52',\n",
       " 143: 'ba84f177-0593-4bbe-ba41-e5c43a8b4834',\n",
       " 144: 'c3c32fcf-bd2e-4d95-83c2-34baef014b67',\n",
       " 145: '57f41ee0-4467-4087-b8a5-eaa726748298',\n",
       " 146: '4205d6a7-377e-43f3-816c-2ecb55a7b1cc',\n",
       " 147: '1e1f5ed6-a707-4ae4-9ce3-46093cc84447',\n",
       " 148: '0034b421-6c65-4b29-8eda-7cdbf83f9cc6',\n",
       " 149: '0dd58019-ec6f-412f-873c-51b73196bd35',\n",
       " 150: '57929d34-86a8-4ef5-a442-4fddba2bbb41',\n",
       " 151: '3d70aaca-0a52-4b43-8308-3ac3958d6f87',\n",
       " 152: '56be6e97-2ee4-4354-8434-12de8ced209c',\n",
       " 153: '3458a2db-09a1-487c-b336-fafe06bbbe43',\n",
       " 154: 'f336983b-8b22-47f4-b5a3-5d2c6dd49bce',\n",
       " 155: '796155a2-8957-470b-9aaa-48e9c9a6ba59',\n",
       " 156: '5a4e1334-90c6-4659-97b5-8d4b85e92d25',\n",
       " 157: '27089cf5-5ff7-4969-9fd6-2dad7bf10a1d',\n",
       " 158: '35a55298-1c0a-43a9-8816-cc71324ad01d',\n",
       " 159: '2b86cac8-0c28-401d-8784-5848880d1e24',\n",
       " 160: '54571f2c-115b-477d-ba4e-938a056e1005',\n",
       " 161: '8e7abbad-bd6d-4065-a7e3-5d7aee616022',\n",
       " 162: '4e81f4bd-2373-44a5-8a40-a738b9a29072',\n",
       " 163: 'c8d95890-e1cd-4cb4-bcb8-373368814ec7',\n",
       " 164: 'ca34332d-5c14-4dd5-9d12-9e5e83310f26',\n",
       " 165: 'dd675449-45cf-4d3b-822f-264b916ef968',\n",
       " 166: 'd02b271a-6356-4cb0-b1a4-d3a79f8243a6',\n",
       " 167: '5db3245b-7ca8-4f30-82a0-0b2ebfe8f6bb',\n",
       " 168: 'e69c57a3-71c8-4bdf-a0fd-6048b9fe1caf',\n",
       " 169: '83ec563d-27bb-43e4-9039-feffe5c121b4',\n",
       " 170: '38f93920-8504-4cf0-9627-4b11a35c9740',\n",
       " 171: 'a50d673f-8d85-4794-9a75-a816caac1c96',\n",
       " 172: 'ec58526b-d7dd-48b2-a340-545b6ba40605',\n",
       " 173: '16241e02-4e1e-41be-8fb4-45336ddafd8a',\n",
       " 174: 'adf59db1-2c24-46eb-9618-bf540416feea',\n",
       " 175: 'd5b30702-18ac-48c4-b235-d9374ca18637',\n",
       " 176: 'd90297ac-f626-4a14-970f-bd7fc57a79e6',\n",
       " 177: 'b26d50cd-cd55-401d-8425-e03e9d37d1d1',\n",
       " 178: '7eb31784-674e-4a90-9408-f2eac177db86',\n",
       " 179: '95595174-d855-4b31-8fab-7d643c11e33b',\n",
       " 180: 'fe9f312a-2edc-49b5-b53c-6013212a0a71',\n",
       " 181: '93b2cde8-2e1e-45dc-a100-1caf25566cab',\n",
       " 182: 'f51ea11c-1052-42d7-8403-b9499836c6c9',\n",
       " 183: '96db28a5-4603-40ab-9762-16622b4b7279',\n",
       " 184: 'ad671a3a-be20-45ac-ab04-9ad121cb87e0',\n",
       " 185: '546d3d69-d38c-4023-a9fb-02cc48bb2b64',\n",
       " 186: 'c4868ed5-44b9-43f6-8f88-163dfb1c2138',\n",
       " 187: '17e47bb7-0514-43d3-84c5-4edf5fdbb273',\n",
       " 188: '2464ec00-41ef-4958-9fd3-4fdc06dd3c4d',\n",
       " 189: '22557859-7a9e-4bc0-bec9-3ef3828204fe'}"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "vector_store.index_to_docstore_id"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "1d3209ab",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Document(id='22557859-7a9e-4bc0-bec9-3ef3828204fe', metadata={'topic': 'Having Statement', 'question_no': 4}, page_content='4.From the data, create a column called is_shipped. It is going to be TRUE if shipping date is available. If shipping date is not available then its going to be FALSE\\n\\nExpected Output: order_id, order_date, shipped_date and is_shipped. The output should be sorted by order_id in ascending order\\n\\n\\n\\nSELECT\\n\\norder_id,\\n\\norder_date,\\n\\nshipped_date,\\n\\nCASE\\n\\n WHEN shipped_date IS NULL then FALSE\\n\\n ELSE TRUE END AS is_shipped\\n\\nFROM orders\\n\\nORDER BY 1 ASC\\n\\n\\n\\n=================================END======================================')]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "vector_store.get_by_ids(['22557859-7a9e-4bc0-bec9-3ef3828204fe'])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a2e344cd",
   "metadata": {},
   "source": [
    "# Save vector_store"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "27c96fa7",
   "metadata": {},
   "outputs": [],
   "source": [
    "vector_store.save_local(\"faiss_index\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8eee3b67",
   "metadata": {},
   "source": [
    "## Step 2 - Retrieval"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "0d841272",
   "metadata": {},
   "outputs": [],
   "source": [
    "from langchain_community.vectorstores import FAISS\n",
    "from langchain_openai import OpenAIEmbeddings\n",
    "\n",
    "embeddings = OpenAIEmbeddings()\n",
    "\n",
    "vectorstore = FAISS.load_local(\n",
    "    \"faiss_index\",\n",
    "    embeddings,\n",
    "    allow_dangerous_deserialization=True\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "874dfa55",
   "metadata": {},
   "outputs": [],
   "source": [
    "retriever = vector_store.as_retriever(search_type=\"similarity\", search_kwargs={\"k\": 1})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "ab839ebc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "VectorStoreRetriever(tags=['FAISS', 'OllamaEmbeddings'], vectorstore=<langchain_community.vectorstores.faiss.FAISS object at 0x000002AB67BB5CD0>, search_kwargs={'k': 1})"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "retriever"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "f45f0274",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Document(id='d19b5983-6c58-4baa-9878-d1edcc1330f0', metadata={'topic': 'Distinct and Count Statement', 'question_no': 1}, page_content='1.Identify count of orders by counting ORDER_ID from ORDERS table. To count order_Id from table, you can write the following statement.\\n\\n\\n\\nselect \\n\\n count(order_id) \\n\\n from orders')]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "retriever.invoke('Identify count of orders by counting ORDER_ID from ORDERS table. To count order_Id from table, you can write the following statement')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "41584a5c",
   "metadata": {},
   "source": [
    "# Step 3 - Augmentation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "abdaed65",
   "metadata": {},
   "outputs": [],
   "source": [
    "llm = ChatOllama(\n",
    "    model=\"llama3.2\",\n",
    "    temperature=0\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "d688536c",
   "metadata": {},
   "outputs": [],
   "source": [
    "prompt = PromptTemplate(\n",
    "    template=\"\"\"\n",
    "      You are a helpful assistant.\n",
    "      Answer ONLY from the provided transcript context.\n",
    "      If the context is insufficient, just say you don't know.\n",
    "\n",
    "      {context}\n",
    "      Question: {question}\n",
    "    \"\"\",\n",
    "    input_variables = ['context', 'question']\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "c93a78ca",
   "metadata": {},
   "outputs": [],
   "source": [
    "question = \"You can see that counting customer_id is going to give you the same result. It means that even if you use count(*), you are going to get the same number of rows. Try using COUNT(*) this time to count the number of rows in the table \"\n",
    "retrieved_docs    = retriever.invoke(question)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "1ce06681",
   "metadata": {},
   "outputs": [],
   "source": [
    "question          = \"is the topic of nuclear fusion discussed in this video? if yes then what was discussed\"\n",
    "retrieved_docs    = retriever.invoke(question)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "3b24a312",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Document(id='579858b1-f5c5-475c-8958-b16098c1a20b', metadata={'topic': 'Distinct and Count Statement', 'question_no': 3}, page_content='3.You can see that counting customer_id is going to give you the same result. It means that even if you use count(*), you are going to get the same number of rows. Try using COUNT(*) this time to count the number of rows in the table\\n\\n\\n\\n select \\n\\n count(*) \\n\\n from orders')]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "retrieved_docs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "be28109d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'3.You can see that counting customer_id is going to give you the same result. It means that even if you use count(*), you are going to get the same number of rows. Try using COUNT(*) this time to count the number of rows in the table\\n\\n\\n\\n select \\n\\n count(*) \\n\\n from orders'"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "context_text = \"\\n\\n\".join(doc.page_content for doc in retrieved_docs)\n",
    "context_text"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "745443a6",
   "metadata": {},
   "outputs": [],
   "source": [
    "final_prompt = prompt.invoke({\"context\": context_text, \"question\": question})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "466eb4fc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "StringPromptValue(text=\"\\n      You are a helpful assistant.\\n      Answer ONLY from the provided transcript context.\\n      If the context is insufficient, just say you don't know.\\n\\n      3.You can see that counting customer_id is going to give you the same result. It means that even if you use count(*), you are going to get the same number of rows. Try using COUNT(*) this time to count the number of rows in the table\\n\\n\\n\\n select \\n\\n count(*) \\n\\n from orders\\n      Question: You can see that counting customer_id is going to give you the same result. It means that even if you use count(*), you are going to get the same number of rows. Try using COUNT(*) this time to count the number of rows in the table \\n    \")"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_prompt "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ba1bbe35",
   "metadata": {},
   "source": [
    "# Step 4 - Generation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "f4f7ed2c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "COUNT(*) will give you the total number of rows in the orders table, as it counts all non-null values in the table, regardless of the specific column (in this case, customer_id).\n"
     ]
    }
   ],
   "source": [
    "answer = llm.invoke(final_prompt)\n",
    "print(answer.content)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "70245f40",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The image features a close-up of a fluffy cat with striking blue eyes and a mix of brown, black, and white fur. The cat has long whiskers and is lying down on what appears to be a wooden surface. Its expression is calm and slightly curious as it looks directly at the camera. The background is softly blurred, drawing attention to the cat's face and fur details.\n"
     ]
    }
   ],
   "source": [
    "from ollama import chat\n",
    "\n",
    "response = chat(\n",
    "    model=\"qwen2.5vl:7b\",\n",
    "    messages=[\n",
    "        {\n",
    "            \"role\": \"user\",\n",
    "            \"content\": \"Describe this image.\",\n",
    "            \"images\": [\"cat.jpg\"]\n",
    "        }\n",
    "    ]\n",
    ")\n",
    "\n",
    "print(response[\"message\"][\"content\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "6c9dce20",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "To get the second highest salary from the Employee table, you can use the following SQL query:\n",
      "\n",
      "```sql\n",
      "SELECT MAX(Salary) AS SecondHighestSalary \n",
      "FROM (\n",
      "    SELECT Salary \n",
      "    FROM Employee \n",
      "    ORDER BY Salary DESC \n",
      "    LIMIT 1 OFFSET 1\n",
      ");\n",
      "```\n",
      "\n",
      "This query works as follows:\n",
      "- The inner query selects all salaries from the `Employee` table and orders them in descending order.\n",
      "- The outer query then takes the maximum salary from this ordered list, which will be the second highest salary because it skips the highest salary (which is at the top of the ordered list).\n",
      "\n",
      "Here's how you can run this query with the provided data:\n",
      "\n",
      "```sql\n",
      "SELECT MAX(Salary) AS SecondHighestSalary \n",
      "FROM (\n",
      "    SELECT Salary \n",
      "    FROM Employee \n",
      "    ORDER BY Salary DESC \n",
      "    LIMIT 1 OFFSET 1\n",
      ");\n",
      "```\n",
      "\n",
      "Given the sample data:\n",
      "- Id: 1, Salary: 100\n",
      "- Id: 2, Salary: 200\n",
      "- Id: 3, Salary: 300\n",
      "\n",
      "The query will return `SecondHighestSalary` as `200`, which is the second highest salary.\n"
     ]
    }
   ],
   "source": [
    "from ollama import chat\n",
    "\n",
    "response = chat(\n",
    "    model=\"qwen2.5vl:7b\",\n",
    "    messages=[\n",
    "        {\n",
    "            \"role\": \"user\",\n",
    "            \"content\": \"provide the solution\"\n",
    "            \"\",\n",
    "            \"images\": [\"sql_query.jpg\"]\n",
    "        }\n",
    "    ]\n",
    ")\n",
    "\n",
    "print(response[\"message\"][\"content\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "695a4241",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b0463bd2",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8194d01b",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "edfde14c",
   "metadata": {},
   "source": [
    "# Building a Chain"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "1c4fd588",
   "metadata": {},
   "outputs": [],
   "source": [
    "from langchain_core.runnables import RunnableParallel, RunnablePassthrough, RunnableLambda\n",
    "from langchain_core.output_parsers import StrOutputParser"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "54b52855",
   "metadata": {},
   "outputs": [],
   "source": [
    "def format_docs(retrieved_docs):\n",
    "  context_text = \"\\n\\n\".join(doc.page_content for doc in retrieved_docs)\n",
    "  return context_text"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "0c3f3290",
   "metadata": {},
   "outputs": [],
   "source": [
    "parallel_chain = RunnableParallel({\n",
    "    'context': retriever | RunnableLambda(format_docs),\n",
    "    'question': RunnablePassthrough()\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "506e942b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'context': '6.Check the territories table and see what columns are available in the table\\n\\n\\n\\nselect * from territories',\n",
       " 'question': 'who is Demis'}"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "parallel_chain.invoke('who is Demis')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "9f550e85",
   "metadata": {},
   "outputs": [],
   "source": [
    "parser = StrOutputParser()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "2a5141eb",
   "metadata": {},
   "outputs": [],
   "source": [
    "main_chain = parallel_chain | prompt | llm | parser"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "634bd7d9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\"I don't know. There is no transcript context provided for a video to summarize. Please provide more information or clarify which video you would like me to summarize, and I'll do my best to assist you.\""
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "main_chain.invoke('Can you summarize the video')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3b194fea",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "langchain311",
   "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.15"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}