<jupyter_start><jupyter_text>Data Wrangling with Spark SQL QuizThis quiz uses the same dataset and most of the same questions from the earlier "Quiz - Data Wrangling with Data Frames Jupyter Notebook." For this quiz, however, use Spark SQL instead of Spark Data Frames.<jupyter_code>from pyspark.sql import SparkSession
from pyspark.sql.functions import udf
from pyspark.sql.types import StringType
from pyspark.sql.types import IntegerType
from pyspark.sql.functions import desc
from pyspark.sql.functions import asc
from pyspark.sql.functions import sum as Fsum

import pandas as pd

# TODOS: 
# 1) import any other libraries you might need
# 2) instantiate a Spark session 
# 3) read in the data set located at the path "data/sparkify_log_small.json"
# 4) create a view to use with your SQL queries
# 5) write code to answer the quiz questions 
# 2
spark = SparkSession.builder.appName('Data Wrangling with Spark Quizbook').getOrCreate()
# 3
user_log = spark.read.json("data/sparkify_log_small.json")
# 4
user_log.createOrReplaceTempView("user_log_table")
spark.sql("""
    SELECT * FROM user_log_table
    """).show(1)<jupyter_output>+-------------+---------+---------+------+-------------+--------+---------+-----+--------------------+------+--------+-------------+---------+--------------------+------+-------------+--------------------+------+
|       artist|     auth|firstName|gender|itemInSession|lastName|   length|level|            location|method|    page| registration|sessionId|                song|status|           ts|           userAgent|userId|
+-------------+---------+---------+------+-------------+--------+---------+-----+--------------------+------+--------+-------------+---------+--------------------+------+-------------+--------------------+------+
|Showaddywaddy|Logged In|  Kenneth|     M|          112|Matthews|232.93342| paid|Charlotte-Concord...|   PUT|NextSong|1509380319284|     5132|Christmas Tears W...|   200|1513720872284|"Mozilla/5.0 (Win...|  1046|
+-------------+---------+---------+------+-------------+--------+---------+-----+--------------------+------+--------+-------------+---------+------[...]<jupyter_text>Question 1Which page did user id ""(empty string) NOT visit?<jupyter_code># TODO: write your code to answer question 1
spark.sql("""
        Select * FROM
            (Select DISTINCT page
            FROM user_log_table
            where userId ="") as user_pages
        right join (Select DISTINCT page
                    from user_log_table) as all_pages
        ON user_pages.page = all_pages.page
        WHERE user_pages.page IS NULL
         """).show()

spark.sql('''
Select distinct page
from user_log_table
''').show()<jupyter_output>+----------------+
|            page|
+----------------+
|Submit Downgrade|
|            Home|
|       Downgrade|
|          Logout|
|   Save Settings|
|           About|
|        Settings|
|           Login|
|        NextSong|
|            Help|
|         Upgrade|
|           Error|
|  Submit Upgrade|
+----------------+<jupyter_text>Question 2 - ReflectWhy might you prefer to use SQL over data frames? Why might you prefer data frames over SQL? -- because BOTH spark SQL and spark dataframes are part of the Spark SQL library. Hence they both use the Spark SQL catalyst optimizer to optimize queries.You might prefer SQL over dataframes because the syntax is clearer espeically for teams versed in SQL.Spark data frames gives you more control. You can break down your queries into smaller steps, which can make debugging easier. You can also [cache] (https://unraveldata.com/to-cache-or-not-to-cache/) intermediate results of [repartition]https://medium.com/@mrpowers/managing-spark-partitions-with-coalesce-and-repartition-4050c57ad5c4) intermediate results.   Question 3How many female users do we have in the data set?<jupyter_code># TODO: write your code to answer question 3
spark.sql("""
    SELECT count(DISTINCT userId)
    FROM user_log_table
    where gender == 'F'
""").show()<jupyter_output>+----------------------+
|count(DISTINCT userId)|
+----------------------+
|                   462|
+----------------------+<jupyter_text>Question 4How many songs were played from the most played artist?<jupyter_code># TODO: write your code to answer question 4

spark.sql("""
    SELECT Artist, COUNT(Artist) as plays
    FROM user_log_table
    GROUP BY artist
    order by plays DESC
""").show()<jupyter_output>+--------------------+-----+
|              Artist|plays|
+--------------------+-----+
|            Coldplay|   83|
|       <NAME>|   69|
|Florence + The Ma...|   52|
|            BjÃÂ¶rk|   46|
|       <NAME>|   45|
|       <NAME>|   43|
|      The Black Keys|   40|
|         OneRepublic|   37|
|                Muse|   36|
|        <NAME>|   36|
|           Radiohead|   31|
|        <NAME>|   29|
|<NAME>/Ac...|   28|
|          <NAME>|   28|
|               Train|   28|
|           <NAME>|   27|
|           Metallica|   27|
|          Nickelback|   27|
|          <NAME>|   26|
|Red Hot Chili Pep...|   24|
+--------------------+-----+
only showing top 20 rows<jupyter_text>Question 5 (challenge)How many songs do users listen to on average between visiting our home page? Please round your answer to the closest integer.<jupyter_code># TODO: write your code to answer question 5


# SELECT CASE WHEN 1 > 0 THEN 1 WHEN 2 > 0 THEN 2.0 ELSE 1.2 END;
is_home = spark.sql("SELECT userID, page, ts, CASE WHEN page = 'Home' THEN 1 ELSE 0 END AS is_home FROM user_log_table \
            WHERE (page = 'NextSong') or (page = 'Home') \
            ")

# keep the results in a new view
is_home.createOrReplaceTempView("is_home_table")

# find the cumulative sum over the is_home column
cumulative_sum = spark.sql("SELECT *, SUM(is_home) OVER \
    (PARTITION BY userID ORDER BY ts DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS period \
    FROM is_home_table")

# keep the results in a view
cumulative_sum.createOrReplaceTempView("period_table")

# find the average count for NextSong
spark.sql("SELECT AVG(count_results) FROM \
          (SELECT COUNT(*) AS count_results FROM period_table \
GROUP BY userID, period, page HAVING page = 'NextSong') AS counts").show()<jupyter_output>+------------------+
|avg(count_results)|
+------------------+
| 6.898347107438017|
+------------------+
{'fraction_non_alphanumeric': 0.21433090794052514, 'fraction_numerical': 0.02341031319202784, 'mean_word_length': 1.9546728971962617, 'pattern_counts': {'":': 0, '<': 25, '<?xml version=': 0, '>': 27, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, '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': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '471334', 'license': 'MIT', 'n_tokens_mistral': 1979, 'n_tokens_neox': 1805, 'n_words': 582, 'path': '3-Spark_DataLake/8_spark_sql_quiz.ipynb', 'repo_name': 'jmalinao19/Data-Engineer-NanoDegree', 'size': '6374'}