sql.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "\n",
    "\n",
    "def Execute_and_fetch(query=None):\n",
    "    if query is None:\n",
    "         query = input(\"Wrtite your query: \")\n",
    "         \n",
    "    try:\n",
    "        con = sqlite3.connect('./test.db')\n",
    "        cursor = con.cursor()\n",
    "        cursor.execute(query)\n",
    "        a = cursor.fetchall()\n",
    "        print(len(a))\n",
    "        return a\n",
    "    \n",
    "    except sqlite3.Error as error:\n",
    "\t    print(\"Failed to execute the above query\", error)\n",
    "            \n",
    "    finally:\n",
    "        if con:\n",
    "            con.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "5\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[('A', 153.35), ('B', 191.5), ('C', 307.77), ('D', 569.74), ('E', 405.24)]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# SQL test case 1\n",
    "q = \"\"\"SELECT SUBSTR(issue_key, 1, 1) AS group_key,\n",
    "       ROUND(AVG(minutes_in_status / 60), 2) AS average_hours_in_open\n",
    "FROM history\n",
    "WHERE status = 'Open'\n",
    "GROUP BY group_key;\"\"\"\n",
    "Execute_and_fetch(q)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "49\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[('C-1', 'In Progress', '2019-06-04 14:38:59'),\n",
       " ('D-8', 'Open', '2019-12-27 11:34:47'),\n",
       " ('A-31053', 'Open', '2020-10-21 15:08:13'),\n",
       " ('A-32133', 'Open', '2021-04-29 09:55:29'),\n",
       " ('C-20610', 'Reopened', '2021-11-23 14:44:58'),\n",
       " ('D-4951', 'Open', '2022-02-24 14:29:21'),\n",
       " ('D-5051', 'Open', '2022-03-14 09:30:51'),\n",
       " ('C-21435', 'Open', '2022-04-07 12:15:34'),\n",
       " ('D-5310', 'Open', '2022-04-18 07:48:07'),\n",
       " ('A-44107', 'Open', '2022-06-08 13:26:25'),\n",
       " ('E-51263', 'Open', '2022-06-21 16:18:56'),\n",
       " ('A-45450', 'Open', '2022-07-08 12:04:02'),\n",
       " ('D-5398', 'In Testing', '2022-07-29 16:01:15'),\n",
       " ('A-47686', 'Open', '2022-08-16 14:54:47'),\n",
       " ('A-42030', 'Open', '2022-09-01 15:04:15'),\n",
       " ('A-49140', 'Open', '2022-09-07 10:33:35'),\n",
       " ('C-31154', 'In Owl', '2022-09-14 17:02:17'),\n",
       " ('A-44916', 'Blocked', '2022-09-15 09:17:04'),\n",
       " ('A-44901', 'In Review', '2022-09-29 09:00:10'),\n",
       " ('C-36721', 'In Owl', '2022-10-17 09:19:54'),\n",
       " ('A-49829', 'In Review', '2022-10-20 10:13:04'),\n",
       " ('D-5518', 'In Progress', '2022-10-27 11:40:53'),\n",
       " ('A-47808', 'Reopened', '2022-10-31 08:54:08'),\n",
       " ('A-50422', 'In Testing', '2022-11-01 11:56:49'),\n",
       " ('D-5975', 'In Testing', '2022-11-01 14:39:04'),\n",
       " ('A-51617', 'Waiting for fix', '2022-11-02 18:40:48'),\n",
       " ('D-6832', 'In Progress', '2022-11-08 10:30:54'),\n",
       " ('A-49884', 'Blocked', '2022-11-09 08:55:39'),\n",
       " ('E-44266', 'In Review', '2022-11-09 14:44:45'),\n",
       " ('A-37761', 'Open', '2022-11-10 08:11:55'),\n",
       " ('A-49415', 'In Review', '2022-11-10 09:43:20'),\n",
       " ('C-39466', 'Open', '2022-11-10 12:01:39'),\n",
       " ('B-4645', 'In Review', '2022-11-10 13:33:56'),\n",
       " ('C-38787', 'In Review', '2022-11-11 14:02:54'),\n",
       " ('D-6828', 'In Progress', '2022-11-14 08:29:14'),\n",
       " ('C-37862', 'In Review', '2022-11-14 08:59:04'),\n",
       " ('C-35057', 'Reopened', '2022-11-14 11:49:52'),\n",
       " ('A-51466', 'Waiting for integration', '2022-11-14 12:42:43'),\n",
       " ('D-6809', 'In Progress', '2022-11-14 13:53:42'),\n",
       " ('A-51980', 'Waiting for integration', '2022-11-14 14:06:36'),\n",
       " ('C-37774', 'Reopened', '2022-11-14 15:34:03'),\n",
       " ('A-50359', 'In Owl', '2022-11-15 06:51:48'),\n",
       " ('C-38984', 'Reopened', '2022-11-15 10:15:23'),\n",
       " ('A-51082', 'In Progress', '2022-11-15 14:05:10'),\n",
       " ('A-51084', 'Blocked', '2022-11-15 15:40:23'),\n",
       " ('D-5886', 'In Owl', '2022-11-15 17:56:10'),\n",
       " ('A-47565', 'Build failed', '2022-11-15 20:38:11'),\n",
       " ('C-39687', 'In Progress', '2022-11-16 06:21:51'),\n",
       " ('B-7067', 'Test', '2022-11-16 07:25:04')]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# SQL test 2\n",
    "\n",
    "from datetime import datetime\n",
    "\n",
    "format='%Y-%m-%d %H:%M:%S%z'\n",
    "\n",
    "now = datetime.timestamp(datetime.now()) * 1000\n",
    "\n",
    "tm = '2022-11-16 07:25:05' + '+00:00'\n",
    "\n",
    "time = datetime.timestamp(datetime.strptime(tm, format)) * 1000\n",
    "# time = now\n",
    "\n",
    "q2 = f\"\"\"SELECT issue_key, status, datetime(started_at / 1000, 'unixepoch')\n",
    "FROM history\n",
    "WHERE status IS NOT 'Closed' AND status IS NOT 'Resolved'\n",
    "AND {time} >= started_at AND ({time} <= ended_at OR ended_at IS NULL)\n",
    "ORDER BY started_at\n",
    ";\"\"\"    \n",
    "a = Execute_and_fetch(q2)\n",
    "a\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "25\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[('Open', None, '2022-07-15 08:58:51', '2022-07-21 09:34:43'),\n",
       " ('In Progress', 'Open', '2022-07-21 09:34:43', '2022-08-01 11:51:43'),\n",
       " ('Resolved', 'In Progress', '2022-08-01 11:51:43', '2022-08-09 14:26:59'),\n",
       " ('Test', 'Resolved', '2022-08-09 14:26:59', '2022-08-09 14:47:27'),\n",
       " ('Reopened', 'Test', '2022-08-09 14:47:27', '2022-08-10 14:28:40'),\n",
       " ('Resolved', 'Reopened', '2022-08-10 14:28:40', '2022-08-19 09:03:45'),\n",
       " ('Test', 'Resolved', '2022-08-19 09:03:45', '2022-08-19 09:16:29'),\n",
       " ('Reopened', 'Test', '2022-08-19 09:16:29', '2022-08-22 09:37:21'),\n",
       " ('In Progress', 'Reopened', '2022-08-22 09:37:21', '2022-08-26 09:39:09'),\n",
       " ('In Review', 'In Progress', '2022-08-26 09:39:09', '2022-08-29 09:47:08'),\n",
       " ('In Testing', 'In Review', '2022-08-29 09:47:08', '2022-09-01 09:29:31'),\n",
       " ('Blocked', 'In Testing', '2022-09-01 09:29:31', '2022-09-01 09:29:34'),\n",
       " ('Open', 'Blocked', '2022-09-01 09:29:34', '2022-09-01 09:29:36'),\n",
       " ('In Progress', 'Open', '2022-09-01 09:29:36', '2022-09-01 09:29:41'),\n",
       " ('In Review', 'In Progress', '2022-09-01 09:29:41', '2022-09-02 09:35:57'),\n",
       " ('In Owl', 'In Review', '2022-09-02 09:35:57', '2022-10-28 07:56:22'),\n",
       " ('Resolved', 'In Owl', '2022-10-28 07:56:22', '2022-10-31 09:25:18'),\n",
       " ('Test', 'Resolved', '2022-10-31 09:25:18', '2022-10-31 09:44:30'),\n",
       " ('Closed', 'Test', '2022-10-31 09:44:30', '2022-11-08 14:11:00'),\n",
       " ('Resolved', 'Closed', '2022-11-08 14:11:00', '2022-11-10 08:54:07'),\n",
       " ('Test', 'Resolved', '2022-11-10 08:54:07', '2022-11-10 08:54:10'),\n",
       " ('Closed', 'Test', '2022-11-10 08:54:10', '2022-11-15 10:03:09'),\n",
       " ('Resolved', 'Closed', '2022-11-15 10:03:09', '2022-11-16 07:25:04'),\n",
       " ('Test', 'Resolved', '2022-11-16 07:25:04', '2022-11-16 07:27:13'),\n",
       " ('Closed', 'Test', '2022-11-16 07:27:13', None)]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Take a look on any issue_key\n",
    "# one can see that current status is between started_at and ended_at\n",
    "\n",
    "q = f\"\"\"SELECT \n",
    "       status,\n",
    "       previous_status,\n",
    "       strftime('%Y-%m-%d %H:%M:%S', datetime(started_at / 1000, 'unixepoch')),\n",
    "       strftime('%Y-%m-%d %H:%M:%S', datetime(ended_at / 1000, 'unixepoch'))\n",
    "FROM history\n",
    "WHERE issue_key = 'B-7067'\n",
    "ORDER BY started_at\n",
    ";\"\"\"\n",
    "Execute_and_fetch(q)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.1"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}