Unsolved/1607. Sellers With No Sales (Easy).ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "f6ac9266-658c-4507-8a37-ecc81334b06d",
     "showTitle": false,
     "tableResultSettingsMap": {},
     "title": ""
    }
   },
   "source": [
    "## Importing Libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "d6194993-9312-4b46-8365-d6a73d8cb0b0",
     "showTitle": false,
     "tableResultSettingsMap": {},
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "from pyspark.sql.functions import *\n",
    "from pyspark.sql.types import *\n",
    "from pyspark.sql.window import Window"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "23f6a680-8f01-4976-83fa-88103e49c6b8",
     "showTitle": false,
     "tableResultSettingsMap": {},
     "title": ""
    }
   },
   "source": [
    "**1607. Sellers With No Sales (Easy)**\n",
    "\n",
    "**Table: Customer**\n",
    "\n",
    "| Column Name   | Type    |\n",
    "|---------------|---------|\n",
    "| customer_id   | int     |\n",
    "| customer_name | varchar |\n",
    "\n",
    "customer_id is the column with unique values for this table.\n",
    "Each row of this table contains the information of each customer in the WebStore.\n",
    " \n",
    "**Table: Orders**\n",
    "\n",
    "| Column Name   | Type    |\n",
    "|---------------|---------|\n",
    "| order_id      | int     |\n",
    "| sale_date     | date    |\n",
    "| order_cost    | int     |\n",
    "| customer_id   | int     |\n",
    "| seller_id     | int     |\n",
    "\n",
    "order_id is the column with unique values for this table.\n",
    "Each row of this table contains all orders made in the webstore.\n",
    "sale_date is the date when the transaction was made between the customer (customer_id) and the seller (seller_id).\n",
    " \n",
    "**Table: Seller**\n",
    "\n",
    "| Column Name   | Type    |\n",
    "|---------------|---------|\n",
    "| seller_id     | int     |\n",
    "| seller_name   | varchar |\n",
    "\n",
    "seller_id is the column with unique values for this table.\n",
    "Each row of this table contains the information of each seller.\n",
    " \n",
    "**Write a solution to report the names of all sellers who did not make any sales in 2020.**\n",
    "\n",
    "Return the result table ordered by seller_name in ascending order.\n",
    "\n",
    "The result format is in the following example.\n",
    "\n",
    "**Example 1:**\n",
    "\n",
    "**Input:** \n",
    "\n",
    "**Customer table:**\n",
    "\n",
    "| customer_id  | customer_name |\n",
    "|--------------|---------------|\n",
    "| 101          | Alice         |\n",
    "| 102          | Bob           |\n",
    "| 103          | Charlie       |\n",
    "\n",
    "**Orders table:**\n",
    "\n",
    "| order_id    | sale_date  | order_cost   | customer_id | seller_id   |\n",
    "|-------------|------------|--------------|-------------|-------------|\n",
    "| 1           | 2020-03-01 | 1500         | 101         | 1           |\n",
    "| 2           | 2020-05-25 | 2400         | 102         | 2           |\n",
    "| 3           | 2019-05-25 | 800          | 101         | 3           |\n",
    "| 4           | 2020-09-13 | 1000         | 103         | 2           |\n",
    "| 5           | 2019-02-11 | 700          | 101         | 2           |\n",
    "\n",
    "**Seller table:**\n",
    "\n",
    "| seller_id   | seller_name |\n",
    "|-------------|-------------|\n",
    "| 1           | Daniel      |\n",
    "| 2           | Elizabeth   |\n",
    "| 3           | Frank       |\n",
    "\n",
    "**Output:** \n",
    "| seller_name |\n",
    "|-------------|\n",
    "| Frank       |\n",
    "\n",
    "**Explanation:** \n",
    "- Daniel made 1 sale in March 2020.\n",
    "- Elizabeth made 2 sales in 2020 and 1 sale in 2019.\n",
    "- Frank made 1 sale in 2019 but no sales in 2020."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "a762f84d-f622-4403-b695-4066e5bdbaf0",
     "showTitle": false,
     "tableResultSettingsMap": {},
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "customer_data_1607 = [(101, \"Alice\"), (102, \"Bob\"), (103, \"Charlie\")]\n",
    "\n",
    "customer_columns__1607 = [\"customer_id\", \"customer_name\"]\n",
    "customer_df_1607 = spark.createDataFrame(customer_data_1607, customer_columns__1607)\n",
    "customer_df_1607.show()\n",
    "\n",
    "orders_data_1607 = [\n",
    "    (1, \"2020-03-01\", 1500, 101, 1),\n",
    "    (2, \"2020-05-25\", 2400, 102, 2),\n",
    "    (3, \"2019-05-25\", 800, 101, 3),\n",
    "    (4, \"2020-09-13\", 1000, 103, 2),\n",
    "    (5, \"2019-02-11\", 700, 101, 2)\n",
    "]\n",
    "\n",
    "orders_columns_1607 = [\"order_id\", \"sale_date\", \"order_cost\", \"customer_id\", \"seller_id\"]\n",
    "orders_df_1607 = spark.createDataFrame(orders_data_1607, orders_columns_1607)\n",
    "orders_df_1607.show()\n",
    "\n",
    "seller_data_1607 = [(1, \"Daniel\"), (2, \"Elizabeth\"), (3, \"Frank\")]\n",
    "\n",
    "seller_columns_1607 = [\"seller_id\", \"seller_name\"]\n",
    "seller_df_1607 = spark.createDataFrame(seller_data_1607, seller_columns_1607)\n",
    "seller_df_1607.show()"
   ]
  }
 ],
 "metadata": {
  "application/vnd.databricks.v1+notebook": {
   "computePreferences": null,
   "dashboards": [],
   "environmentMetadata": {
    "base_environment": "",
    "environment_version": "1"
   },
   "inputWidgetPreferences": null,
   "language": "python",
   "notebookMetadata": {
    "pythonIndentUnit": 4
   },
   "notebookName": "1607. Sellers With No Sales (Easy)",
   "widgets": {}
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}