Solved/1142. User Activity for the Past 30 Days II (Easy)-(Solved).ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "c8535576-d746-40ee-8199-8ccd3e39577f",
     "showTitle": false,
     "tableResultSettingsMap": {},
     "title": ""
    }
   },
   "source": [
    "## Importing Libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "09da8fc1-d579-4949-bbc7-9132909993c8",
     "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": "4567f050-e6f4-4b8b-b899-db2f17021c1d",
     "showTitle": false,
     "tableResultSettingsMap": {},
     "title": ""
    }
   },
   "source": [
    "**1142. User Activity for the Past 30 Days II (Easy)**\n",
    "\n",
    "**Table: Activity**\n",
    "\n",
    "| Column Name   | Type    |\n",
    "|---------------|---------|\n",
    "| user_id       | int     |\n",
    "| session_id    | int     |\n",
    "| activity_date | date    |\n",
    "| activity_type | enum    |\n",
    "\n",
    "This table may have duplicate rows.\n",
    "The activity_type column is an ENUM (category) of type ('open_session', 'end_session', 'scroll_down', 'send_message').\n",
    "The table shows the user activities for a social media website. \n",
    "Note that each session belongs to exactly one user.\n",
    " \n",
    "**Write a solution to find the average number of sessions per user for a period of 30 days ending 2019-07-27 inclusively, rounded to 2 decimal places. The sessions we want to count for a user are those with at least one activity in that time period.**\n",
    "\n",
    "The result format is in the following example.\n",
    "\n",
    "**Example 1:**\n",
    "\n",
    "**Input:** \n",
    "\n",
    "**Activity table:**\n",
    "\n",
    "| user_id | session_id | activity_date | activity_type |\n",
    "|---------|------------|---------------|---------------|\n",
    "| 1       | 1          | 2019-07-20    | open_session  |\n",
    "| 1       | 1          | 2019-07-20    | scroll_down   |\n",
    "| 1       | 1          | 2019-07-20    | end_session   |\n",
    "| 2       | 4          | 2019-07-20    | open_session  |\n",
    "| 2       | 4          | 2019-07-21    | send_message  |\n",
    "| 2       | 4          | 2019-07-21    | end_session   |\n",
    "| 3       | 2          | 2019-07-21    | open_session  |\n",
    "| 3       | 2          | 2019-07-21    | send_message  |\n",
    "| 3       | 2          | 2019-07-21    | end_session   |\n",
    "| 3       | 5          | 2019-07-21    | open_session  |\n",
    "| 3       | 5          | 2019-07-21    | scroll_down   |\n",
    "| 3       | 5          | 2019-07-21    | end_session   |\n",
    "| 4       | 3          | 2019-06-25    | open_session  |\n",
    "| 4       | 3          | 2019-06-25    | end_session   |\n",
    "\n",
    "**Output:** \n",
    "| average_sessions_per_user |\n",
    "|---------------------------| \n",
    "| 1.33                      |\n",
    "\n",
    "**Explanation:** User 1 and 2 each had 1 session in the past 30 days while user 3 had 2 sessions so the average is (1 + 1 + 2) / 3 = 1.33."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "9b14d03f-22ca-4fa2-b371-32a135a82877",
     "showTitle": false,
     "tableResultSettingsMap": {},
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "activity_data_1142 = [\n",
    "    (1, 1, \"2019-07-20\", \"open_session\"),\n",
    "    (1, 1, \"2019-07-20\", \"scroll_down\"),\n",
    "    (1, 1, \"2019-07-20\", \"end_session\"),\n",
    "    (2, 4, \"2019-07-20\", \"open_session\"),\n",
    "    (2, 4, \"2019-07-21\", \"send_message\"),\n",
    "    (2, 4, \"2019-07-21\", \"end_session\"),\n",
    "    (3, 2, \"2019-07-21\", \"open_session\"),\n",
    "    (3, 2, \"2019-07-21\", \"send_message\"),\n",
    "    (3, 2, \"2019-07-21\", \"end_session\"),\n",
    "    (4, 3, \"2019-06-25\", \"open_session\"),\n",
    "    (4, 3, \"2019-06-25\", \"end_session\"),\n",
    "]\n",
    "activity_columns_1142 = [\"user_id\", \"session_id\", \"activity_date\", \"activity_type\"]\n",
    "activity_df_1142 = spark.createDataFrame(activity_data_1142, activity_columns_1142)\n",
    "activity_df_1142.show()"
   ]
  }
 ],
 "metadata": {
  "application/vnd.databricks.v1+notebook": {
   "computePreferences": null,
   "dashboards": [],
   "environmentMetadata": {
    "base_environment": "",
    "environment_version": "1"
   },
   "inputWidgetPreferences": null,
   "language": "python",
   "notebookMetadata": {
    "pythonIndentUnit": 4
   },
   "notebookName": "1142. User Activity for the Past 30 Days II (Easy)",
   "widgets": {}
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}