• Write a query to retrieve the artist along with maxRank, title, and whether the artist is a remixer.

• Return all the artists for which there are other artists with fewer songs. Order the results by artist.

• Retrieve only the songs that are either House or Techno. Limit your response to 3.

• Write an SQL query that returns the title of a song followed by “->” followed by the subtitle of the song. Limit your response to 2 rows. The query should only return one column.

• Define an engagement score for each user based on the following:

  • +2 points per content they created

  • +1 point per like their content received

  • +3 points per comment activity they made

  Compute the total engagement score per user and rank all users by score in descending order. Include their rank.

  Keep only the top 5 users with the highest engagemnt.

• Return all the songs that have price greater than the average price of the songs of genre House. Limit your response to 5 songs ordered by title. The price should be rounded to 2 decimal places.

• Return all the songs of genre House that have price greater than the average price of the songs of genre House, along with the average price of genre House rounded to 2 decimal points. Limit your response to 5 songs ordered by title.

• Assume activity_type = ‘follow’ represents a user following another user. For each user, compute:

  • Number of users they follow

  • Number of users who follow them

  • Their follower-to-following ratio (round to 2 decimal places)

  Filter for only followers who have at least one follower and at least one following.

• Assume that we want to generate the raw data for creating a histogram of the number of songs per genre.

• Label songs that were released on or after 2016 as ‘recent’, songs that were released between 2015 and 2016 as ‘mid’, and songs that were released before 2015 as ‘old’. order by genre and limit your response to 5 rows. Return both the title of the song and whether the song is recent. Assume that some songs have missing values on their released day; you can ignore those.

• Find the median number of songs per artist.

• Find the number of unique users who performed any activity per month over the past 6 years. Return the month and the count of unique users, ordered by month.

  Tip: You can estimate the starting date of the past 6 years by using now() - INTERVAL 6 YEAR.

• For each user, return the content (content_id and content_text) that received the highest number of likes. If the user has multiple top-liked contents, return all of them. Order the results by user_id.

• Return all the songs in the charts that have durationInCharts greater than the average durationInCharts (rounded up to 1 decimal point) of songs that have entered the charts before them.

• We are looking to estimate the number of songs per genre. However, we would like to focus only on songs that have a genre listed (i.e., genre != -1), and we would also like to focus on popular genres, that have at least 8 songs. Return the result in decreasing order of the most popular genre, and limit your response to five rows.

• Calculate the number of songs that either did not make the charts or entered the charts and had a max rank greater than 90.

• Retrieve the trackId of the songs that ranked between \(1^{st}\) and \(50^{th}\) place in the charts. Limit your answer to 4.

• Return all the songs that have durationInCharts greater than the all the durationInCharts of songs of genre Breaks. Order the results by their durationInCharts in decreasing order.

• Find songs with titles that have only letters and no spaces (single-word titles). Order by title and return the first 5.

• For each user, calculate the number of days it took them to perform their first activity after account creation. You should return the user_id, and the number of days to first activity (days_to_first_activity).

  Filter out users who have no activities, and those for which our data might show that they had activities before creating an account. Order the results by user_id.