q.txt

1: Which 3 genres are most represented in terms of number of songs in that genre?
The result must have two columns, named genre and number_of_songs.

SELECT genre, count(*) AS 'number_of_songs'
    FROM song_genre
    GROUP BY genre
    ORDER BY count(*) DESC limit 3;
    
2. Find names of artists who have songs that are in albums as well as outside of albums (singles).
The result must have one column, named artist_name

SELECT artist AS artist_name 
    FROM song
    WHERE EXISTS (SELECT * FROM album WHERE song.artist = album.artist)
    GROUP BY (SELECT distinct artist);
    
3. What were the top 10 most highly rated albums (highest average user rating) in the period 1990-1999? Break ties using alphabetical order of album names. (Period refers to the rating date, NOT the date of release).

The result must have two columns, named album_name and average_user_rating.

SELECT Album.album_name AS album_name, AVG(album_rating.rating_score) AS average_user_rating
    FROM Album, album_rating
    where (year(album_rating.Rating_Date) between 1990 and 1999) AND (Album.Album_ID = album_rating.Album_ID)
    GROUP BY Album.Album_ID
    ORDER BY average_user_rating DESC, album_name ASC
    LIMIT 10;
    
4. Which were the top 3 most rated genres (this is the number of ratings of songs in genres, not the actual rating scores) in the years 1991-1995? (Years refers to the rating date, NOT the date of release).
    
SELECT song_genre.genre, count(*) AS 'number_of_song_ratings'
     FROM song_genre
     INNER JOIN song_rating ON song_genre.song_id=song_rating.song_id
     WHERE year(rating_date) between 1991 and 1995
     GROUP BY genre 
     ORDER BY number_of_song_ratings DESC
     LIMIT 3;
     
5. Which users have a playlist that has an average song rating of 4.0 or more? (This is the average of the average song rating for each song in the playlist.) A user may appear multiple times in the result if more than one of their playlists make the cut

The result must 3 columns named username, playlist_title, average_song_rating

SELECT playlist.username, playlist.playlist_title, AVG(avg_song_rating) 'average_song_rating'
FROM playlist_song, playlist, (SELECT song.song_id, AVG(song_rating.rating_score) AS 'avg_song_rating'
FROM song, song_rating
WHERE song.song_id = song_rating.song_id 
GROUP BY song_id) songs_avg
WHERE playlist_song.song_id = songs_avg.song_id AND playlist.playlist_id = playlist_song.playlist_id
GROUP BY playlist_song.playlist_id
HAVING average_song_rating >=4;


6. Who are the top 5 most engaged users in terms of number of ratings that they have given to songs or albums? (In other words, they have given the most number of ratings to songs or albums combined.)
The result must have 2 columns, named username and number_of_ratings.

SELECT username, sum(tbl.number_of_ratings) AS 'number_of_ratings'
    FROM
    (
    SELECT song_rating.username, count(*) AS number_of_ratings FROM song_rating GROUP BY song_rating.username
    UNION ALL
    SELECT album_rating.username, count(*) AS number_of_ratings FROM album_rating GROUP BY album_rating.username
    )tbl
    GROUP BY username
    ORDER BY number_of_ratings DESC
    LIMIT 5;


7. Find the top 10 most prolific artists (most number of songs) in the years 1990-2010? Count each song in an album individually.

SELECT artist AS 'artist_name', count(*) AS 'number_of_songs'
    FROM song
    WHERE year(release_date) between 1990 and 2010
    GROUP BY artist_name
    ORDER BY number_of_songs DESC
    LIMIT 10;
    
8. Find the top 10 songs that are in most number of playlists. Break ties in alphabetical order of song titles.

The result must have a 2 columns, named song_title and number_of_playlists.

SELECT song.title AS 'song_title', count(*) AS 'number_of_playlists'
    FROM song
    INNER JOIN playlist_song ON song.song_id = playlist_song.song_id
    GROUP BY song.title
    ORDER BY number_of_playlists DESC, title ASC
    LIMIT 10; 

9. Find the top 20 most rated singles (songs that are not part of an album). Most rated meaning number of ratings, not actual rating scores. The result must have 3 columns, named song_title, artist_name, number_of_ratings.
    
SELECT song.title AS 'song_title', 
    song.artist AS 'artist_name', 
    count(song_rating.song_id) AS 'number_of_ratings'
FROM song, song_rating
WHERE song.song_id NOT IN (select album_song.song_id FROM album_song) AND song_rating.song_id = song.song_id
GROUP BY song_rating.song_id
ORDER BY number_of_ratings DESC
limit 20;
    
10. Find all artists who discontinued making music after 1993.
The result should be a single column named artist_title

SELECT Artist AS artist_title
FROM song 
WHERE song.release_date <= 1993;