{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "283ccb11-78f6-466f-b330-1580a9de2394",
          "showTitle": false,
          "tableResultSettingsMap": {},
          "title": ""
        }
      },
      "source": [
        "# First query on real data\n",
        "\n",
        "Every Databricks workspace arrives with data already in it. The `samples` catalog is shared into\n",
        "your workspace by Databricks, it is read-only, and it is the same in every workspace, so it is the\n",
        "one place where a query you write here gives the same answer as a query somebody else writes on the\n",
        "other side of the world.\n",
        "\n",
        "This lab is four questions about one table. It exists to get you from signed in to your first\n",
        "passed run without a download, a cluster, or a file.\n",
        "\n",
        "Full instructions: https://brickster.io/labs/first-query-on-real-data\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "6b64c612-09d0-4ba8-ae86-69b6a4696208",
          "showTitle": false,
          "tableResultSettingsMap": {},
          "title": ""
        }
      },
      "source": [
        "## 1. Your salt\n",
        "\n",
        "Copy the eight characters under **Your salt** on the lab page and paste them below. One of the four\n",
        "answers depends on it.\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {
            "byteLimit": 2048000,
            "rowLimit": 10000
          },
          "inputWidgets": {},
          "nuid": "27a8908a-d578-4437-8eed-70ee303fda30",
          "showTitle": false,
          "tableResultSettingsMap": {},
          "title": ""
        }
      },
      "outputs": [],
      "source": [
        "SALT = \"00000000\"  # <- paste yours here\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "5fbbe23d-899a-4196-b890-f63538337e02",
          "showTitle": false,
          "tableResultSettingsMap": {},
          "title": ""
        }
      },
      "source": [
        "## 2. What is already here\n",
        "\n",
        "`samples` is a catalog like any other, so the same three statements you would use on your own data\n",
        "work on it. A catalog holds schemas, a schema holds tables, and a table is what you query. That is\n",
        "the whole namespace.\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {
            "byteLimit": 2048000,
            "rowLimit": 10000
          },
          "inputWidgets": {},
          "nuid": "275b2702-5edf-4520-8ade-353750d55ea6",
          "showTitle": false,
          "tableResultSettingsMap": {},
          "title": ""
        }
      },
      "outputs": [],
      "source": [
        "display(spark.sql(\"show schemas in samples\"))\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {
            "byteLimit": 2048000,
            "rowLimit": 10000
          },
          "inputWidgets": {},
          "nuid": "536d279c-0c93-49a8-b9a4-4cba7a6613a5",
          "showTitle": false,
          "tableResultSettingsMap": {},
          "title": ""
        }
      },
      "outputs": [],
      "source": [
        "display(spark.sql(\"show tables in samples.tpch\"))\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "91dde7cc-df44-488f-a26b-06ceb045e574",
          "showTitle": false,
          "tableResultSettingsMap": {},
          "title": ""
        }
      },
      "source": [
        "`samples.tpch` is the TPC-H benchmark set: a wholesale supplier's orders, customers,\n",
        "parts and suppliers. It is generated to a published specification, which is why the numbers below\n",
        "are the same for everybody.\n",
        "\n",
        "`orders` is the table this lab asks about. Before counting anything, look at what the columns are\n",
        "called, because every question after this one is about one of them.\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {
            "byteLimit": 2048000,
            "rowLimit": 10000
          },
          "inputWidgets": {},
          "nuid": "057d66c0-0ed9-4d03-ac3a-05f802daccc5",
          "showTitle": false,
          "tableResultSettingsMap": {},
          "title": ""
        }
      },
      "outputs": [],
      "source": [
        "display(spark.sql(\"describe table samples.tpch.orders\"))\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "48a2e1e3-ebe7-41c7-9eae-6302afa51088",
          "showTitle": false,
          "tableResultSettingsMap": {},
          "title": ""
        }
      },
      "source": [
        "## 3. How many rows\n",
        "\n",
        "The first thing anybody asks of a table they have not seen. `count(*)` reads the table's own\n",
        "statistics rather than the rows, so it answers in about the same time on a million rows as on a\n",
        "thousand.\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {
            "byteLimit": 2048000,
            "rowLimit": 10000
          },
          "inputWidgets": {},
          "nuid": "6dcc9d52-7c42-4743-8ba2-5fbf0185edcf",
          "showTitle": false,
          "tableResultSettingsMap": {},
          "title": ""
        }
      },
      "outputs": [],
      "source": [
        "# TODO: count the rows in samples.tpch.orders.\n",
        "# .collect() brings the result back as a list of Row, and a Row is subscriptable: [0][\"n\"].\n",
        "order_rows = None\n",
        "\n",
        "print(f\"BRICKSTER:order_rows={order_rows}\")\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "7ccb756b-336c-4c60-b799-779f25a41a35",
          "showTitle": false,
          "tableResultSettingsMap": {},
          "title": ""
        }
      },
      "source": [
        "## 4. What is in a column\n",
        "\n",
        "`o_orderstatus` is a single letter per order. Two questions about it: how many different letters\n",
        "are there, and which one turns up most often.\n",
        "\n",
        "`group by` answers both at once, and it is the statement the rest of your Databricks life is built\n",
        "on. Group the rows by the column, count each group, and order the counts.\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {
            "byteLimit": 2048000,
            "rowLimit": 10000
          },
          "inputWidgets": {},
          "nuid": "8d9b2736-9647-40f4-ba62-613942c8fc43",
          "showTitle": false,
          "tableResultSettingsMap": {},
          "title": ""
        }
      },
      "outputs": [],
      "source": [
        "display(spark.sql(\"\"\"\n",
        "  select o_orderstatus, count(*) as orders\n",
        "  from samples.tpch.orders\n",
        "  group by o_orderstatus\n",
        "  order by orders desc\n",
        "\"\"\"))\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {
            "byteLimit": 2048000,
            "rowLimit": 10000
          },
          "inputWidgets": {},
          "nuid": "a6222ff3-1946-409f-a6cf-a647df746f55",
          "showTitle": false,
          "tableResultSettingsMap": {},
          "title": ""
        }
      },
      "outputs": [],
      "source": [
        "# TODO: read the two answers out of the query above.\n",
        "# status_kinds is how many different statuses there are, top_status is the commonest one,\n",
        "# as the single letter itself and not as a count.\n",
        "status_kinds = None\n",
        "top_status = None\n",
        "\n",
        "print(f\"BRICKSTER:status_kinds={status_kinds}\")\n",
        "print(f\"BRICKSTER:top_status={top_status}\")\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "5b93ee3b-8f5c-4c67-90cd-d4c1157541de",
          "showTitle": false,
          "tableResultSettingsMap": {},
          "title": ""
        }
      },
      "source": [
        "## 5. Your slice of the table\n",
        "\n",
        "The last question is yours alone. Your salt picks one bucket out of 64, `o_custkey` is the\n",
        "customer who placed the order, and the bucket is `o_custkey % 64`. Count the orders in it.\n",
        "\n",
        "A notebook borrowed from somebody else answers this with their bucket and fails here, which is the\n",
        "only thing standing between this lab and a copied run.\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {
            "byteLimit": 2048000,
            "rowLimit": 10000
          },
          "inputWidgets": {},
          "nuid": "06eb9a9c-2af1-482e-9d9d-e7179a5030ce",
          "showTitle": false,
          "tableResultSettingsMap": {},
          "title": ""
        }
      },
      "outputs": [],
      "source": [
        "bucket = int(SALT, 16) % 64\n",
        "print(\"your bucket:\", bucket)\n",
        "\n",
        "# TODO: count the orders whose customer falls in your bucket, that is o_custkey % 64 = bucket.\n",
        "bucket_orders = None\n",
        "\n",
        "print(f\"BRICKSTER:bucket_orders={bucket_orders}\")\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "4782cf04-2787-44e5-bb1e-33a6391a9b51",
          "showTitle": false,
          "tableResultSettingsMap": {},
          "title": ""
        }
      },
      "source": [
        "## 6. Check your run\n",
        "\n",
        "Two ways, same grader.\n",
        "\n",
        "**Export and upload.** *File > Export > IPYNB*, then drop it on the lab page.\n",
        "\n",
        "**Or straight from here.** Make a token on https://brickster.io/settings, put it in the `brickster`\n",
        "secret scope once per workspace, and run the last cell. Your Databricks credentials never leave\n",
        "this workspace.\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {
            "byteLimit": 2048000,
            "rowLimit": 10000
          },
          "inputWidgets": {},
          "nuid": "88379edb-eb7b-4a55-bd34-5f14b7a1f919",
          "showTitle": false,
          "tableResultSettingsMap": {},
          "title": ""
        }
      },
      "outputs": [],
      "source": [
        "%pip install brickster\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {
            "byteLimit": 2048000,
            "rowLimit": 10000
          },
          "inputWidgets": {},
          "nuid": "002bdcea-7e33-45eb-85fb-aab461e43b95",
          "showTitle": false,
          "tableResultSettingsMap": {},
          "title": ""
        }
      },
      "outputs": [],
      "source": [
        "import brickster\n",
        "\n",
        "brickster.submit(\"first-query-on-real-data\")\n"
      ]
    }
  ],
  "metadata": {
    "application/vnd.databricks.v1+notebook": {
      "computePreferences": null,
      "dashboards": [],
      "environmentMetadata": {
        "base_environment": "",
        "environment_version": "5"
      },
      "inputWidgetPreferences": null,
      "language": "python",
      "notebookMetadata": {
        "pythonIndentUnit": 4
      },
      "notebookName": "starter",
      "widgets": {}
    },
    "kernelspec": {
      "display_name": "Python 3",
      "language": "python",
      "name": "python3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}