SQL_Subqueries_&_Aggregation.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "SQL - Subqueries & Aggregation",
      "provenance": [],
      "collapsed_sections": [],
      "authorship_tag": "ABX9TyPv11tp6zhmUBGt8IIBy2Nv"
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    }
  },
  "cells": [
    {
      "cell_type": "code",
      "metadata": {
        "id": "49HaW1jpgym0",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "# A real example of subqueries and aggregation in my data work at the Office of Finance\n",
        "# Results informed recipient pool of regular monthly email marketing campaign\n",
        "\n",
        "SELECT \n",
        "  CREATED_BY, COUNT(*)\n",
        "FROM\n",
        "\t(SELECT *\n",
        "\tFROM latax.h_web_filing_hdr\n",
        "\tWHERE\n",
        "\t\tTAX_PERIOD IS NOT NULL AND\n",
        "\t\tHISTORY_FLAG = 'A' AND\n",
        "\t\t(CREATED_DT >= DATE '2019-12-01' AND CREATED_DT <= DATE '2019-12-31') AND\n",
        "\t\t(EMAIL_SUBSCRIBE_IND = 'Y' OR EMAIL_SUBSCRIBE_IND IS NULL))\n",
        "GROUP BY CREATED_BY\n",
        "ORDER BY 2;"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "YiImpCfgiTT5",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "# JOIN Example\n",
        "SELECT \n",
        "  a.ACCOUNT_NUMBER, a.EXEMPTION_ID, a.EFFECTIVE_DT, a.END_DT, a.STATUS, a.APPLICATION_DT, a.CREATED_DT, b.EXEMPTION_NAME, b.EXEMPTION_SUBNAME\n",
        "FROM \n",
        "  LATAX.EXEMPT_AND_INCENT a\n",
        "LEFT JOIN \n",
        "  LATAX.EXEMPTION_DEFN b\n",
        "ON \n",
        "  a.EXEMPTION_ID = b.EXEMPTION_ID\n",
        "WHERE \n",
        "  a.STATUS='APR' AND (b.EXEMPTION_SUBNAME='Vendor' OR b.EXEMPTION_NAME='Non-Profit')\n",
        "ORDER BY a.EFFECTIVE_DT DESC;"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "HnzpV4cLhqTi",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "# UNION Example\n",
        "SELECT \n",
        "  DISTINCT FILED_BY_EMAIL_ADDR, LOCATION_ACCT_NBR, EMAIL_SUBSCRIBE_IND, PRIMARY_NAICS, CREATED_DT, ''\n",
        "FROM \n",
        "  latax.web_filing_hdr\n",
        "WHERE\n",
        "\tTAX_PERIOD IS NOT NULL AND\n",
        "\tCREATED_BY = 'trc_app' AND\n",
        "\t(CREATED_DT >= DATE '2019-12-01' AND CREATED_DT <= DATE '2019-12-31') AND\n",
        "\t(EMAIL_SUBSCRIBE_IND = 'Y' OR EMAIL_SUBSCRIBE_IND IS NULL)\n",
        "UNION\n",
        "SELECT \n",
        "  DISTINCT FILED_BY_EMAIL_ADDR, LOCATION_ACCT_NBR, EMAIL_SUBSCRIBE_IND, PRIMARY_NAICS, CREATED_DT, HISTORY_FLAG\n",
        "FROM \n",
        "  latax.h_web_filing_hdr\n",
        "WHERE\n",
        "\tTAX_PERIOD IS NOT NULL AND\n",
        "\tHISTORY_FLAG = 'A' AND\n",
        "\t(CREATED_DT >= DATE '2019-12-01' AND CREATED_DT <= DATE '2019-12-31') AND\n",
        "\tCREATED_BY = 'trc_app' AND\n",
        "\t(EMAIL_SUBSCRIBE_IND = 'Y' OR EMAIL_SUBSCRIBE_IND IS NULL)\n",
        ";"
      ],
      "execution_count": 0,
      "outputs": []
    }
  ]
}