<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

# 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 
spark = SparkSession.builder \
    .appName("Spark SQL Quiz") \
    .getOrCreate()

    
df = spark.read.json("data/sparkify_log_small.json")
df.createOrReplaceTempView('log_table')<jupyter_output><empty_output><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 log_table limit 5
''').show()
spark.sql('''
select distinct page from log_table
where page not in 
(select page from log_table
where userId = '' )

''').show()<jupyter_output>+----------------+
|            page|
+----------------+
|Submit Downgrade|
|       Downgrade|
|          Logout|
|   Save Settings|
|        Settings|
|        NextSong|
|         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?  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 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 count(artist) as total
from log_table
where artist is not null
group by artist
order by 1 desc
limit 1

''').show()<jupyter_output>+-----+
|total|
+-----+
|   83|
+-----+<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
is_home = spark.sql('''
select userId, page, ts, (case when (page = 'Home') then 1 else 0 end) as ishome
from log_table
where page = 'NextSong' or page = 'Home'
''')
is_home.createOrReplaceTempView('is_home')
cumsum = spark.sql('''
select userId, ishome, sum(ishome) over(partition by userId order by ts desc range between unbounded preceding and current row) as cumsum
from is_home
''')


cumsum.createOrReplaceTempView('cumsum')
spark.sql('''
select avg(total)
from
(select userId, cumsum, count(cumsum) as total from cumsum
where ishome = 0
group by 1,2) t
''').show()<jupyter_output>+-----------------+
|       avg(total)|
+-----------------+
|6.898347107438017|
+-----------------+
{'fraction_non_alphanumeric': 0.15603755260602137, 'fraction_numerical': 0.013920362576885724, 'mean_word_length': 3.297635605006954, 'pattern_counts': {'":': 0, '<': 17, '<?xml version=': 0, '>': 17, 'https://': 0, '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': '474263', 'license': 'MIT', 'n_tokens_mistral': 1005, 'n_tokens_neox': 940, 'n_words': 350, 'path': 'Data Lake with Spark/Data Lake with Spark Exercises/8_spark_sql_quiz.ipynb', 'repo_name': 'ivy-Ruxin-Tong/Udacity_DataEngineering', 'size': '3089'}