<jupyter_start><jupyter_text>Examples of business questions and queries to answer them  Setup and ConnectionLoad the `ipython-sql` library and connect to the `sparkifydb` local database with the `student` user and password.<jupyter_code>%load_ext sql
%sql postgresql://student:student@127.0.0.1/sparkifydb<jupyter_output><empty_output><jupyter_text>Examples  Fact table songplays 1. What are the artist and title of the top 10 most popular song of all time on the music streaming app?**Note:** There is only one row in the `songplays` table that has both `song_id` and `artist_id` fields that are not null so the result will only show one row.<jupyter_code>%%sql
WITH most_popular_song_ids AS (
    SELECT song_id FROM songplays 
    WHERE song_id IS NOT NULL 
    GROUP BY song_id 
    ORDER BY count(song_id) DESC LIMIT 10
)


SELECT title AS "Song title", name AS "Artist name"
FROM most_popular_song_ids
JOIN songs USING(song_id)
JOIN artists USING(artist_id);<jupyter_output>* postgresql://student:***@127.0.0.1/sparkifydb
1 rows affected.<jupyter_text>Dimension tables  songs1. In what year was there the most songs relased and how many?<jupyter_code>%%sql
SELECT year AS "Year", count(year) AS "Number of songs"
FROM songs
GROUP BY year
ORDER BY count(year) DESC
LIMIT 1;<jupyter_output>* postgresql://student:***@127.0.0.1/sparkifydb
1 rows affected.<jupyter_text>artists2. How many songs each artist has released on the music streaming app? Oder by the number of songs in descending oder.<jupyter_code>%%sql
SELECT name AS "Artist name", count AS "Number of songs"
FROM (SELECT artist_id, count(artist_id)
FROM songs
GROUP BY artist_id) AS artists_songs_count
JOIN artists USING(artist_id)
ORDER BY count DESC;<jupyter_output>* postgresql://student:***@127.0.0.1/sparkifydb
69 rows affected.<jupyter_text>3. What proportion of users have a paid membership level?<jupyter_code>%%sql
SELECT ROUND((count(level)*100.0 / (SELECT count(*) FROM users)),2) AS "Percentage of paid users"
FROM users
WHERE level = 'paid';<jupyter_output>* postgresql://student:***@127.0.0.1/sparkifydb
1 rows affected.
{'fraction_non_alphanumeric': 0.09328891004283675, 'fraction_numerical': 0.023322227510709188, 'mean_word_length': 5.610062893081761, 'pattern_counts': {'":': 0, '<': 17, '<?xml version=': 0, '>': 17, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 5, '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': '219153', 'license': 'MIT', 'n_tokens_mistral': 731, 'n_tokens_neox': 682, 'n_words': 246, 'path': 'example_queries.ipynb', 'repo_name': 'nasseredine/udacity-dend-p1', 'size': '2101'}