<jupyter_start><jupyter_text>_Validations on the raw data to understand the data that was loaded in to the tables. Run this Notebook after etl.py has been executed_  Describing Songs Data set<jupyter_code>cur.execute('SELECT COUNT(1), COUNT(DISTINCT title) FROM songs')
output = cur.fetchone()
print(output)
cur.execute('SELECT MIN(duration), MAX(duration), AVG(duration) FROM songs')
output = cur.fetchone()
print(output)<jupyter_output>(Decimal('29.54404'), Decimal('599.24853'), Decimal('239.7296760563380282'))<jupyter_text>There are a total of 71 songs with songs playing on average 4 mins. Quickest song is a half minute and longest song is 10 mins<jupyter_code>cur.execute('SELECT COUNT(DISTINCT artist_id) FROM songs')
output = cur.fetchone()
print(output)<jupyter_output>(69,)<jupyter_text>Since each song is created by one and only one artist, the 71 songs in the data are almost all created by different artists<jupyter_code>cur.execute('SELECT COUNT(1) FROM songs WHERE year = 0')
output = cur.fetchone()
print(output)
cur.execute('SELECT year, COUNT(1) FROM songs WHERE year != 0 GROUP BY year ORDER BY year')
output = cur.fetchall()
print(output)<jupyter_output>(43,)
[(1961, 1), (1964, 1), (1969, 1), (1972, 1), (1982, 1), (1984, 1), (1985, 1), (1986, 1), (1987, 1), (1992, 1), (1993, 1), (1994, 2), (1997, 2), (1999, 1), (2000, 2), (2003, 2), (2004, 4), (2005, 2), (2007, 1), (2008, 1)]<jupyter_text>The data quality in the `year` column is quite bad. About 2/3rds of songs have no valid year. Of the remaining 1/3rd songs with a valid year, they range from early 60's to late 2000's<jupyter_code>cur.execute("SELECT COUNT(DISTINCT location) FROM artists WHERE location != ''")
output = cur.fetchone()
print(output)
cur.execute("SELECT location, COUNT(1) FROM artists WHERE location != '' GROUP BY location ORDER BY 2 desc LIMIT 1")
output = cur.fetchone()
print(output)<jupyter_output>(40,)
('London, England', 2)<jupyter_text>Of the artists that has valid location data, all are from different cities around world except the 2 from London. Nothing interesting here  Describing Log data<jupyter_code>cur.execute("SELECT COUNT(1), COUNT(DISTINCT song), COUNT(DISTINCT artist), COUNT(DISTINCT userId) FROM songplays_log")
output = cur.fetchone()
print(output)<jupyter_output>(6820, 5189, 3148, 96)<jupyter_text>The log data contains ~6800 plays by 100 users of 5200 different songs from 3150 distinct artists. Note that the dimension tables `songs` and `artists` contain only a small sample of the overall data<jupyter_code>cur.execute("SELECT song, COUNT(1) FROM songplays_log GROUP BY song ORDER BY 2 desc LIMIT 1;")
output = cur.fetchone()
print(output)
cur.execute("SELECT DISTINCT song, artist FROM songplays_log WHERE song = \
                (SELECT song FROM \
                    (SELECT song, COUNT(1) FROM songplays_log GROUP BY song ORDER BY 2 desc LIMIT 1) m)")
output = cur.fetchone()
print(output)<jupyter_output>("You're The One", 'Dwight Yoakam')<jupyter_text>Dwight Yoakam's You're The One is the most played song<jupyter_code>cur.execute("SELECT u.gender, COUNT(DISTINCT sp.user_id), COUNT(1) \
                FROM songplays sp JOIN users u on sp.user_id = u.user_id \
                GROUP BY u.gender;")
output = cur.fetchall()
print(output)<jupyter_output>[('F', 55, 4887), ('M', 41, 1933)]<jupyter_text>Female users make up 55% of the user base and constitute a whopping 72% of plays<jupyter_code>cur.execute("SELECT u.level, COUNT(DISTINCT sp.user_id), COUNT(1) \
                FROM songplays sp JOIN users u on sp.user_id = u.user_id \
                GROUP BY u.level;")
output = cur.fetchall()
print(output)<jupyter_output>[('free', 75, 1385), ('paid', 21, 5435)]<jupyter_text>Free users make up 75% of the user base but constitute only 20% of the plays. Paid users are engaged which could indicate a healthy content<jupyter_code>cur.execute("SELECT t.weekday, COUNT(DISTINCT sp.user_id), COUNT(1) \
                FROM songplays sp JOIN time t on sp.start_time = t.start_time \
                GROUP BY t.weekday;")
output = cur.fetchall()
print(output)<jupyter_output>[(0, 59, 1014), (1, 57, 1071), (2, 60, 1364), (3, 56, 1052), (4, 63, 1295), (5, 45, 628), (6, 39, 396)]
{'fraction_non_alphanumeric': 0.12514813936951885, 'fraction_numerical': 0.06755155250059255, 'mean_word_length': 4.8774373259052926, 'pattern_counts': {'":': 0, '<': 28, '<?xml version=': 0, '>': 28, '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': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '344364', 'license': 'MIT', 'n_tokens_mistral': 1659, 'n_tokens_neox': 1354, 'n_words': 491, 'path': 'DEND/DataModeling/project1/validations.ipynb', 'repo_name': 'bmareddy/Udacity', 'size': '4219'}