Solved/1917. Leetcodify Friends Recommendations (Hard)-(Solved).ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "e405a61e-83e0-4a83-8234-4a55e28c9c87",
     "showTitle": false,
     "tableResultSettingsMap": {},
     "title": ""
    }
   },
   "source": [
    "## Importing Libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "bb35cd35-f82a-443f-90d7-d161591cdb61",
     "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": "29ed3054-4879-4c88-8f36-f775ffecdfe0",
     "showTitle": false,
     "tableResultSettingsMap": {},
     "title": ""
    }
   },
   "source": [
    "**1917. Leetcodify Friends Recommendations (Hard)**\n",
    "\n",
    "**Table: Listens**\n",
    "\n",
    "| Column Name | Type    |\n",
    "|-------------|---------|\n",
    "| user_id     | int     |\n",
    "| song_id     | int     |\n",
    "| day         | date    |\n",
    "\n",
    "This table may contain duplicates (In other words, there is no primary key for this table in SQL).\n",
    "Each row of this table indicates that the user user_id listened to the song song_id on the day day.\n",
    " \n",
    "**Table: Friendship**\n",
    "\n",
    "| Column Name   | Type    |\n",
    "|---------------|---------|\n",
    "| user1_id      | int     |\n",
    "| user2_id      | int     |\n",
    "\n",
    "In SQL,(user1_id, user2_id) is the primary key for this table.\n",
    "Each row of this table indicates that the users user1_id and user2_id are friends.\n",
    "Note that user1_id < user2_id.\n",
    " \n",
    "Recommend friends to Leetcodify users. We recommend user x to user y if:\n",
    "- Users x and y are not friends, and\n",
    "- Users x and y listened to the same three or more different songs on the same day.\n",
    "\n",
    "**Note that friend recommendations are unidirectional, meaning if user x and user y should be recommended to each other, the result table should have both user x recommended to user y and user y recommended to user x. Also, note that the result table should not contain duplicates (i.e., user y should not be recommended to user x multiple times.).**\n",
    "\n",
    "Return the result table in any order.\n",
    "\n",
    "The result format is in the following example.\n",
    "\n",
    "**Example 1:**\n",
    "\n",
    "**Input:** \n",
    "\n",
    "**Listens table:**\n",
    "\n",
    "| user_id | song_id | day        |\n",
    "|---------|---------|------------|\n",
    "| 1       | 10      | 2021-03-15 |\n",
    "| 1       | 11      | 2021-03-15 |\n",
    "| 1       | 12      | 2021-03-15 |\n",
    "| 2       | 10      | 2021-03-15 |\n",
    "| 2       | 11      | 2021-03-15 |\n",
    "| 2       | 12      | 2021-03-15 |\n",
    "| 3       | 10      | 2021-03-15 |\n",
    "| 3       | 11      | 2021-03-15 |\n",
    "| 3       | 12      | 2021-03-15 |\n",
    "| 4       | 10      | 2021-03-15 |\n",
    "| 4       | 11      | 2021-03-15 |\n",
    "| 4       | 13      | 2021-03-15 |\n",
    "| 5       | 10      | 2021-03-16 |\n",
    "| 5       | 11      | 2021-03-16 |\n",
    "| 5       | 12      | 2021-03-16 |\n",
    "\n",
    "**Friendship table:**\n",
    "| user1_id | user2_id |\n",
    "|----------|----------|\n",
    "| 1        | 2        |\n",
    "\n",
    "**Output:** \n",
    "| user_id | recommended_id |\n",
    "|---------|----------------|\n",
    "| 1       | 3              |\n",
    "| 2       | 3              |\n",
    "| 3       | 1              |\n",
    "| 3       | 2              |\n",
    "\n",
    "**Explanation:** \n",
    "- Users 1 and 2 listened to songs 10, 11, and 12 on the same day, but they are already friends.\n",
    "- Users 1 and 3 listened to songs 10, 11, and 12 on the same day. Since they are not friends, we recommend them to each other.\n",
    "- Users 1 and 4 did not listen to the same three songs.\n",
    "- Users 1 and 5 listened to songs 10, 11, and 12, but on different days.\n",
    "\n",
    "Similarly, we can see that users 2 and 3 listened to songs 10, 11, and 12 on the same day and are not friends, so we recommend them to each other."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "64a3f754-3c0a-4ca1-af02-4d6f736ea2e9",
     "showTitle": false,
     "tableResultSettingsMap": {},
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "listens_data_1917 = [\n",
    "    (1, 10, \"2021-03-15\"), (1, 11, \"2021-03-15\"), (1, 12, \"2021-03-15\"),\n",
    "    (2, 10, \"2021-03-15\"), (2, 11, \"2021-03-15\"), (2, 12, \"2021-03-15\"),\n",
    "    (3, 10, \"2021-03-15\"), (3, 11, \"2021-03-15\"), (3, 12, \"2021-03-15\"),\n",
    "    (4, 10, \"2021-03-15\"), (4, 11, \"2021-03-15\"), (4, 13, \"2021-03-15\"),\n",
    "    (5, 10, \"2021-03-16\"), (5, 11, \"2021-03-16\"), (5, 12, \"2021-03-16\")\n",
    "]\n",
    "\n",
    "listens_columns_1917 = [\"user_id\", \"song_id\", \"day\"]\n",
    "listens_df_1917 = spark.createDataFrame(listens_data_1917, listens_columns_1917)\n",
    "listens_df_1917.show()\n",
    "\n",
    "friendship_data_1917 = [(1, 2)]\n",
    "\n",
    "friendship_columns_1917 = [\"user1_id\", \"user2_id\"]\n",
    "friendship_df_1917 = spark.createDataFrame(friendship_data_1917, friendship_columns_1917)\n",
    "friendship_df_1917.show()"
   ]
  }
 ],
 "metadata": {
  "application/vnd.databricks.v1+notebook": {
   "computePreferences": null,
   "dashboards": [],
   "environmentMetadata": {
    "base_environment": "",
    "environment_version": "1"
   },
   "inputWidgetPreferences": null,
   "language": "python",
   "notebookMetadata": {
    "pythonIndentUnit": 4
   },
   "notebookName": "1917. Leetcodify Friends Recommendations (Hard)",
   "widgets": {}
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}