<jupyter_start><jupyter_text>Netflix user behaviour  Requirements[Jupyter Notebook](https://jupyter.org/install)  [Apache Toree](https://toree.incubator.apache.org/)  [sampleDataNetflix.tsv](https://guicaro.com/sampleDataNetflix.tsv) placed in local filesystem and path updated in 1) below Notes* I used a combination of Jupyter notebook and the Apache Toree project as it makes it easy and fast to explore a dataset. * I was part of the team that came up with [Apache Toree (aka The Spark Kernel)](https://twitter.com/guicaro/status/543541995247910917), and till now I think  it's still the only Jupyter kernel that ties to a Spark Session and is backed by Apache. It solved many issues for us back when we were developing applications in Spark. Future* I was hoping to use [Voila](https://github.com/voila-dashboards/voila) project to create an interactive dashboard for data scientists where they could move a slider widget to change the parameters in my SQL queries, thus, change the time window to search. So, for example, say a data scientist would want to search for users only between 8 and 9 in the morning.* I wanted to randomly generate a bigger dataset using rules so that we could at least have more data to play with  1. Let's read our data We will read in a TSV file and try to infer schema since it is not very complex data types we are using<jupyter_code>val sessions = spark.read.option("header", "true")
                         .option("sep", "\t")
                         .option("inferSchema","true")
                         .csv("/Users/memo/Desktop/netflixSpark/sampleDataNetflix.tsv")


sessions.printSchema
sessions.show(2)<jupyter_output>+-------+---------------+--------------------+----------+--------+----+----------+
|user_id|navigation_page|                 url|session_id|    date|hour| timestamp|
+-------+---------------+--------------------+----------+--------+----+----------+
|   1001|       HomePage|https://www.netfl...|      6001|20181125|  11|1543145019|
|   1001| OriginalsGenre|https://www.netfl...|      6001|20181125|  11|1543144483|
+-------+---------------+--------------------+----------+--------+----+----------+
only showing top 2 rows<jupyter_text>2. Let's create a temp SQL table to use of the SQL magic in Apache Toree to get our information<jupyter_code>sessions.registerTempTable("SESSIONS")<jupyter_output><empty_output><jupyter_text>a) Find all users who have visited OurPlanetTitle Page. Using DISTINCT to show unique users<jupyter_code>%%SQL select distinct user_id 
from SESSIONS 
where navigation_page = 'OurPlanetTitle'<jupyter_output><empty_output><jupyter_text>b) Find all users who have visited OurPlanetTitle Page only once. Showing the page visits just for validation, can be easily removed from the projection list in query<jupyter_code>%%SQL select user_id, count(user_id) as page_visits 
from SESSIONS 
where navigation_page = 'OurPlanetTitle' 
group by user_id
having page_visits == 1<jupyter_output><empty_output><jupyter_text>c) Find all users who have visited HomePage -> OriginalsGenre  -> OurPlanetTitle ->  HomePage Making sure we filter for the same path using the timestamps and making sure it's all within the same `session_id`<jupyter_code>%%SQL select distinct a.user_id
from sessions a,
sessions b,
sessions c,
sessions d
where a.user_id = b.user_id
and b.user_id = c.user_id
and c.user_id = d.user_id
and a.navigation_page = 'HomePage'
and b.navigation_page = 'OriginalsGenre'
and c.navigation_page = 'OurPlanetTitle'
and d.navigation_page = 'HomePage'
and a.timestamp < b.timestamp
and b.timestamp < c.timestamp
and c.timestamp < d.timestamp
and a.session_id = b.session_id
and b.session_id = c.session_id
and c.session_id = d.session_id<jupyter_output><empty_output><jupyter_text>d) Find all users who landed on LogIn Page from a Title Page The like operator is not the most performant but the SQL optimizer should be able to tell that my 2nd where clause can improve selectivity of this query. I am using the `timestamp` column to make sure that a before landing on a **Login** page, the user first comes from a **Title** page<jupyter_code>%%SQL select a.user_id
from sessions a,
sessions b
where a.user_id = b.user_id
and b.navigation_page = 'LogIn'
and a.navigation_page like '%Title'
and a.timestamp < b.timestamp<jupyter_output><empty_output><jupyter_text>e) Find all users who have visited only OurPlanetTitle Page We are using relation 'b' to get the total count of `url` the user has visited<jupyter_code>%%SQL select a.user_id
from sessions a,
(select user_id, count(url) as totalUrl from sessions group by user_id) b
where a.user_id = b.user_id
and a.navigation_page = 'OurPlanetTitle'
and b.totalurl = 1<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1257130783858018, 'fraction_numerical': 0.017747728713289668, 'mean_word_length': 3.664039408866995, 'pattern_counts': {'":': 0, '<': 32, '<?xml version=': 0, '>': 31, 'https://': 7, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '667835', 'license': 'MIT', 'n_tokens_mistral': 1467, 'n_tokens_neox': 1392, 'n_words': 529, 'path': 'Netflix Exploration.ipynb', 'repo_name': 'guicaro/guicaro.github.io', 'size': '4733'}