{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "# Support backlog: a reproducible portfolio example\n",
        "\n",
        "Synthetic ticket records, 4 May-28 June 2026. Fixed snapshot: 29 June 2026 at 00:00 UTC. Run the cells from the extracted example folder. Python code uses the standard library.\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "from pathlib import Path\n",
        "from analyze import analyze\n",
        "result = analyze(Path('support-tickets.csv'))\n",
        "result\n"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Interpret the finding\n",
        "\n",
        "There are 96 open tickets, including 58 in onboarding. Compare the age bands before deciding where to investigate. Elapsed age is measured at the fixed snapshot; duration summaries include their denominators in the output.\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "[(queue['queue'], queue['open'], queue['age_bands_days']) for queue in result['queues']]\n"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Reproduce the queue summary in SQLite\n",
        "\n",
        "The CSV contains 24 exact duplicate rows. Deduplicate before counting tickets. The Python loader also checks timestamps, status, and conflicting copies.\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "import csv\n",
        "import sqlite3\n",
        "with sqlite3.connect(':memory:') as database:\n",
        "    with Path('support-tickets.csv').open(newline='') as source:\n",
        "        reader = csv.DictReader(source)\n",
        "        columns = reader.fieldnames\n",
        "        database.execute('CREATE TABLE tickets_raw (' + ', '.join(column + ' TEXT' for column in columns) + ')')\n",
        "        database.executemany('INSERT INTO tickets_raw VALUES (?, ?, ?, ?, ?, ?, ?)', [tuple(row[column] for column in columns) for row in reader])\n",
        "    sql_result = database.execute(Path('analysis.sql').read_text()).fetchall()\n",
        "sql_result\n"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "by_queue = {queue['queue']: queue for queue in result['queues']}\n",
        "for name, count, *bands in sql_result:\n",
        "    assert count == by_queue[name]['open']\n",
        "    assert bands == list(by_queue[name]['age_bands_days'].values())\n",
        "print('SQL and Python counts agree.')\n"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Explain the next action\n",
        "\n",
        "Investigate the oldest onboarding tickets and compare incoming with resolved volume. The sample identifies a queue to inspect; it does not measure the effect of a staffing change.\n",
        "\n",
        "![Open tickets by age band](support-backlog.svg)\n"
      ]
    }
  ],
  "metadata": {
    "kernelspec": {
      "display_name": "Python 3",
      "language": "python",
      "name": "python3"
    },
    "language_info": {
      "name": "python",
      "version": "3.10"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 4
}
