{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d18c0fa9-7b5e-4a07-b851-e8bb3396a25d",
   "metadata": {},
   "outputs": [],
   "source": [
    "from DATA225utils import make_connection, dataframe_query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f0ea4227-43f3-44ec-8365-5618eea7eca3",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = make_connection('ZAGI-Sales.ini')\n",
    "cursor = conn.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "216952f5-0893-4f93-87b8-34b98c8f5e3e",
   "metadata": {},
   "source": [
    "# Which products sell below the average price?\n",
    "### What is the average price?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4b2b844a-b9ec-41b5-b382-d45771e6e70f",
   "metadata": {},
   "outputs": [],
   "source": [
    "_, df = dataframe_query(conn, \n",
    "    \"\"\"\n",
    "    SELECT AVG(product_price)\n",
    "    FROM   product\n",
    "    \"\"\"\n",
    ")\n",
    "\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "37b7b1dc-6010-4bbd-b0dd-81d4785b54b9",
   "metadata": {},
   "source": [
    "### Products selling below the average price."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3f92e21f-52e7-40fa-aa9d-35c3d907a84f",
   "metadata": {},
   "outputs": [],
   "source": [
    "_, df = dataframe_query(conn, \n",
    "    \"\"\"\n",
    "    SELECT product_id, product_name, product_price\n",
    "    FROM   product\n",
    "    WHERE  product_price < (\n",
    "        SELECT AVG(product_price)\n",
    "        FROM   product\n",
    "    )\n",
    "    \"\"\"\n",
    ")\n",
    "\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "698cc138-930c-4563-9cb9-99ab8fa275c1",
   "metadata": {},
   "source": [
    "# What is the name, id, and price of products that have more than 3 items sold in all sales transactions?\n",
    "### Number of product items sold."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "29f98ba5-af59-4195-90c0-4afac690f411",
   "metadata": {},
   "outputs": [],
   "source": [
    "_, df = dataframe_query(conn, \n",
    "    \"\"\"\n",
    "    SELECT product_id, no_of_items\n",
    "    FROM sold_via\n",
    "    \"\"\"\n",
    ")\n",
    "\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b0cdecd4-7e42-4f7b-8970-e2ebe13d49ec",
   "metadata": {},
   "source": [
    "### Products that sold more than 3 items."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ebe953a1-4e34-413f-a622-4c43f52088da",
   "metadata": {},
   "outputs": [],
   "source": [
    "_, df = dataframe_query(conn, \"\"\"\n",
    "    SELECT product_id, SUM(no_of_items)\n",
    "    FROM sold_via\n",
    "    GROUP BY product_id\n",
    "    HAVING SUM(no_of_items) > 3\n",
    "    \"\"\"\n",
    ")\n",
    "\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f59ffd76-e067-4f6c-b08f-a7a6db407bdd",
   "metadata": {},
   "source": [
    "### Product id, name, and price."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "20900a8d-7115-4ef2-84c6-825fa0aead2e",
   "metadata": {},
   "outputs": [],
   "source": [
    "_, df = dataframe_query(conn, \n",
    "    \"\"\"\n",
    "    SELECT product_id, product_name, product_price\n",
    "    FROM product\n",
    "    WHERE product_id IN (\n",
    "        SELECT product_id\n",
    "        FROM sold_via\n",
    "        GROUP BY product_id\n",
    "        HAVING SUM(no_of_items) > 3\n",
    "    );\n",
    "    \"\"\"\n",
    ")\n",
    "\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e088f742-21e3-44cb-9bd3-7cac7080cb12",
   "metadata": {},
   "source": [
    "# What is the name, id, and price of products that were each sold in more than one sales transaction?\n",
    "### Sales transactions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d5b60ce5-1b3b-4ad6-a680-8908227366e6",
   "metadata": {},
   "outputs": [],
   "source": [
    "_, df = dataframe_query(conn, \n",
    "    \"\"\"\n",
    "    SELECT product_id, tid\n",
    "    FROM sold_via\n",
    "    \"\"\"\n",
    ")\n",
    "\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "229e84bb-d983-4162-9024-7cc2bb694727",
   "metadata": {},
   "source": [
    "### More than one transaction."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "788c8ab6-71b8-44bf-9bdb-4639c1baa7fc",
   "metadata": {},
   "outputs": [],
   "source": [
    "_, df = dataframe_query(conn, \n",
    "    \"\"\"\n",
    "    SELECT product_id\n",
    "    FROM sold_via\n",
    "    GROUP BY product_id\n",
    "    HAVING COUNT(*) > 1\n",
    "    \"\"\"\n",
    ")\n",
    "\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5c480e77-b837-49bf-b9c9-de9d2f4662bf",
   "metadata": {},
   "source": [
    "### Product id, name, and price."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4c0b0c15-4da4-49ba-a532-7da5c5f345b4",
   "metadata": {},
   "outputs": [],
   "source": [
    "_, df = dataframe_query(conn, \n",
    "    \"\"\"\n",
    "    SELECT product_id, product_name, product_price\n",
    "    FROM product\n",
    "    WHERE product_id IN (\n",
    "         SELECT product_id\n",
    "         FROM sold_via\n",
    "         GROUP BY product_id\n",
    "         HAVING COUNT(*) > 1\n",
    "    );\n",
    "    \"\"\"\n",
    ")\n",
    "\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9e0b3032-a8ba-451a-b91d-af659376ef35",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.close()\n",
    "conn.close()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}