{
"cells": [
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"deletable": false,
"editable": false
},
"outputs": [],
"source": [
"# Initialize Otter\n",
"import otter\n",
"grader = otter.Notebook(\"proj0.ipynb\")"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Project 0 - SQL Review -- Music Querying\n",
"## Due Date: Wednesday, September 9th, 5:00pm PST\n",
"\n",
"In this project, we will be working with SQL on a Billboard Music database."
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Collaboration Policy\n",
"Please read our [course collaboration policy](https://data101.org/fa26/syllabus/#collaboration-ai-and-integrity). Do not share code. If you discuss the assignments with others, please include their names below.\n",
"\n",
"**Collaborators**: _list collaborators here_\n"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Objectives\n",
"\n",
"- Review SQL syntax from prerequisite courses\n",
"\n",
"**Note:** If at any point during the project, the internal state of the database or its tables have been modified in an undesirable way (i.e. a modification not resulting from the instructions of a question), restart your kernel, clear output, and simply re-run the notebook as normal. This will shut down your current connection to the database, which will prevent the issue of multiple connections to the database at any given point. When re-running the notebook, you will create a fresh database based on the provided Postgres dump.\n",
"\n",
"If you face slow kernel times or are unable to open your notebook, restart your server by going to File -> Hub Control Panel -> Stop My Server and then clicking Start My Server.\n",
"\n",
"## Logistics & Scoring Breakdown\n",
"\n",
"Please read the submission instructions carefully and double check that your submission is not throwing any errors. Please ensure that public tests pass upon submission. It is your responsibility to wait until the autograder finishes running. We will not be accepting regrade requests for submission issues.\n",
"\n",
"Each coding question has **both public tests and hidden tests**. Roughly 50% of your coding grade will be made up of your score on the public tests released to you, while the remaining 50% will be made up of unreleased hidden tests. If there are free-response questions (marked 'm' in the table below), they are manually graded.\n",
"\n",
"This is an **individual project**. However, you’re welcome to collaborate with any other student in the class as long as it’s within the academic honesty guidelines.\n",
"\n",
"|Question|Points|\n",
"|---|---|\n",
"|0|3|\n",
"|1|3|\n",
"|2|3|\n",
"|3|3|\n",
"|4|3|\n",
"|5a|3|\n",
"|5b|3|\n",
"|6|0|\n",
"|**Total**|21|\n",
"\n",
"**Summary:** 21 points (autograded: 21, manual: 0) "
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"# Run this cell to set up imports\n",
"import numpy as np\n",
"import pandas as pd"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"\n",
""
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Before You Start\n",
"\n",
"## Assignment Tips\n",
"We recommend you read through Fall 2026 Assignment Tips—a handy resource that has many other tips that we **highly recommend** taking a moment to read through to save you time and improve your workflow.\n",
"\n",
"* PostgreSQL documentation\n",
"* Jupyter cell magic\n",
"* JupySQL and magic commands in Jupyter\n",
"* JupyterHub keyboard shortcuts\n",
"* psql and common meta-commands (not required for Project 0)\n",
"* Debugging:\n",
" * Where to create new cells to play nice with the autograder\n",
" * Opening/closing connections, deleting databases if all else fails\n",
"* Local installation (not supported by staff officially, but for your reference)\n",
"\n",
"## Table of Contents\n",
"Jump to different parts of this notebook:\n",
"* [Before You Start](#Before-You-Start): This section\n",
"* [Setup](#Setup): Database Setup, Grader Setup (make sure to run these cells)\n",
"* [The `billboard` Database](#The-billboard-Database): Information about this project's database schema, plus optional features for you to explore\n",
"* [Question 0](#q0): The first assignment question\n"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"\n",
"\n",
"\n",
"# Setup\n",
"\n",
"## Database Setup\n",
"We are going to be using the `JupySQL` library to connect our notebook to a PostgreSQL database server on your JupyterHub account. Running the next cell will do so; you should not see any error messages after it executes."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"# The first time you are running this cell, you may need to run the following line as: %load_ext sql\n",
"%reload_ext sql"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"In the next cell, we will unzip the data. This only needs to be done once."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"!unzip -uo data/billboard.zip -d data/"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
" \n",
"\n",
"**Create the `billboard` database**: \n",
"We will use PostgreSQL commands to create a database and import our data into it. Run the following cell to do this. It may take a few seconds.\n",
"\n",
"* If you run into the **role does not exist** error, feel free to ignore it as the data import is unaffected.\n",
"* If you run into the psql error **connection to server at \"localhost\" failed: FATAL: database \"billboard\" does not exist\"**, feel free to ignore it as well.\n",
"* You can alternatively run these cells in the command-line via `psql`."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"scrolled": true
},
"outputs": [],
"source": [
"!psql postgresql://localhost/billboard -c 'SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE datname = current_database() AND pid <> pg_backend_pid();'\n",
"!psql postgresql://localhost/postgres -c 'DROP DATABASE IF EXISTS billboard'\n",
"!psql postgresql://localhost/postgres -c 'CREATE DATABASE billboard'\n",
"!psql postgresql://localhost/billboard -f data/billboard.sql -q"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"**Connect to `billboard` database in the Notebook**: \n",
" \n",
"Now let's connect to the new database we just created! There should be no errors after running the following cell."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%sql postgresql://localhost/billboard"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Assuming the previous command doesn't show an error, this will use the current connection to show all tables in the `billboard` database."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%sqlcmd tables"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"---\n",
"\n",
"**Quick check**: To make sure things are working, let's fetch 10 rows from one of our tables `tiktok_top_50`. Just run the following cells. If there are no errors, no further action is needed."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%%sql\n",
"SELECT * FROM tiktok_top_50 LIMIT 10;"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Grader Setup"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"# Connecting the grader\n",
"# Just run the following cell, no further action is needed.\n",
"from data101_utils import GradingUtil\n",
"grading_util = GradingUtil(\"proj0\")\n",
"grading_util.prepare_autograder()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"\n",
"\n",
"\n",
"\n",
"# The `billboard` Database\n",
"\n",
"[Billboard](https://www.billboard.com) is a music industry publication which tracks the sales and distribution of music across many genres. Each week, they publish “charts” which aggregate the total sales, streams (e.g. via Spotify, Apple Music, or YouTube) as well as radio airplay in the US.\n",
" \n",
"The most (in)famous chart is the [Billboard Hot 100](https://www.billboard.com/charts/hot-100/), widely regarded as the list of most popular songs in the US. For this assignment, you’ll be looking at data from the Hot 100 list, primarily in recent years — though the database we’ve provided to you goes all the way back to 1958! \n",
"\n",
"Later questions will involve `JOIN`ing with data from the [Billboard TikTok Top 50](https://www.billboard.com/charts/tiktok-billboard-top-50/), a list of the songs that are the most popular on TikTok, factoring in views and user engagement.\n",
"\n",
"Below is a list of the relations (tables) in our database. \n",
"- **hot_100**: Metadata for songs in the Billboard Hot 100\n",
"- **tiktok_top_50**: Metadata for songs in the Billboard TikTok Top 50"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"
\n",
"\n",
"\n",
"\n",
"## Data Schema"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"When approaching a new database, one of the most important things to do is understand the database schema. Remember that a database is a set of tables, which each contain their own schema. For this assignment, both the `hot_100` and `tiktok_top_50` tables contain the following schema.\n",
"\n",
"```\n",
"CREATE TABLE hot_100 (\n",
" week_ending TEXT,\n",
" rank INTEGER,\n",
" title TEXT,\n",
" artist TEXT, \n",
" image_url TEXT,\n",
" peak_position INTEGER,\n",
" weeks_on_chart INTEGER,\n",
" UNIQUE (week_ending, rank)\n",
");\n",
"```\n",
"\n",
"- `week_ending`: The week that \"chart\" was published, as a `YYYY-MM-DD` string\n",
"\n",
"- `rank`: The track's current position on the chart. (A rank of 1 is better than a rank of 10.)\n",
"\n",
"- `title`: Track's title\n",
"\n",
"- `artist`: Track's artist\n",
"\n",
"- `image_url`: Image URL of the track's album cover\n",
"\n",
"- `peak_position`: The track's peak position on the chart as of the chart date\n",
"\n",
"- `weeks_on_chart`: The number of weeks the track has been or was on the chart up until that point\n",
"\n",
"**Note:** In the case of Billboard charts, a song (or album's) `peak_position` is based on the idea that being #1 is the 'best'. Therefore, a _smaller_ value for `peak_position` is better. \n",
"\n",
"_An aside_: ⏰ We've decided to use a `TEXT` data type for the `week_ending` date. Later we'll explore the `date` type. However, we can perform ordered comparisons on these strings, since `'2024-01-01'` comes before `'2024-01-02'` lexicographically. "
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"
\n",
"\n",
"\n",
"\n",
"## Optional Fun"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Call the `display_query` function passing in your SQL query result to view the results with a table that links to Apple Music and Spotify. An example usage of the `display_query` function can be found at the end of Question 0."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"# Some utilities to make exploration easier.\n",
"# NOTE: Make sure to write display_query(...);\n",
"# Use the ; to suppress the notebook's default output.\n",
"\n",
"# Common Configuration\n",
"DISPLAY_ALBUM_ART = False\n",
"IMAGE_SIZE = '50px' # Adjust to change the size of album art.\n",
"MAX_ROWS = 250\n",
"\n",
"import urllib.parse\n",
"from IPython.display import display, HTML\n",
"\n",
"# Adjust pandas display options to better handle large DataFrames\n",
"# Note: These settings do not apply when calling `df.to_html`\n",
"pd.set_option('display.max_rows', 500) # Adjust as needed\n",
"pd.set_option('display.max_seq_items', 250)\n",
"pd.set_option('display.max_columns', None) # Show all columns\n",
"pd.set_option('display.colheader_justify', 'left')\n",
"\n",
"spotify_url = 'https://play.spotify.com/search/'\n",
"apple_music_url = 'https://music.apple.com/us/search?term='\n",
"\n",
"# You probably don't need this function but you can use it to make \"fancy\"\n",
"# datatables which are sortable and searchable. (But then can be a little slow to load.)\n",
"def html_datatable(html):\n",
" return display(HTML(f\"\"\"\n",
" \n",
" \n",
" \n",
" \n",
"
\n",
" \"\"\"))\n",
"\n",
"def display_query(result, show_search=True):\n",
" \"\"\"\n",
" A fairly basic df to HTML-table display utility.\n",
" Includes the ability to give links to Spotify/Apple Music for easy exploring and maximal time wastage.\n",
" \"\"\"\n",
" song_query = lambda row: urllib.parse.quote(row.get(\"title\", '') + \" \" + row.get(\"artist\", ''))\n",
" format_link = lambda col_name: lambda cell: f'{col_name}'\n",
" formatters = {\n",
" 'image_url': lambda image_url: f'',\n",
" 'spotify': format_link('Spotify'),\n",
" 'apple': format_link('Apple Music'),\n",
" }\n",
"\n",
" # Display the DataFrame as a nicely formatted table with scroll bars\n",
" df = result.DataFrame()\n",
" if show_search and ('artist' in df.columns or 'title' in df.columns):\n",
" df['spotify'] = df.apply(lambda row: f'{spotify_url}{song_query(row)}', axis=1)\n",
" df['apple'] = df.apply(lambda row: f'{apple_music_url}{song_query(row)}', axis=1)\n",
"\n",
" if not DISPLAY_ALBUM_ART and 'image_url' in df.columns:\n",
" df = df.drop(columns='image_url')\n",
"\n",
" # Convert DataFrame to HTML\n",
" # We must manually set out own limits!\n",
" html_table = df.to_html(index=True, escape=False, formatters=formatters, show_dimensions=True, max_rows=MAX_ROWS)\n",
"\n",
" # Swap these two lines to see the fancy version of the output.\n",
" # html_datatable(html_table)\n",
" basic_table(html_table)\n",
" return df;"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"
\n",
"\n",
"\n",
"\n",
"\n",
"\n",
"\n",
"# Question 0 — Warmup\n"
]
},
{
"cell_type": "markdown",
"metadata": {
"deletable": false,
"editable": false
},
"source": [
"This question is given to you! Verify that your table loads correctly.\n",
"Run each of the following queries, and you should see the right output."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"tags": [
"otter_answer_cell"
]
},
"outputs": [],
"source": [
"%%sql --save query_0_0 result_0_0 <<\n",
"SELECT COUNT(*) FROM hot_100"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"tags": [
"otter_answer_cell"
]
},
"outputs": [],
"source": [
"# Do not delete/edit this cell!\n",
"# You must run this cell before running the autograder.\n",
"query_0_0 = %sqlcmd snippets query_0_0\n",
"grading_util.save_results(\"result_0_0\", query_0_0, result_0_0)\n",
"result_0_0"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"tags": [
"otter_answer_cell"
]
},
"outputs": [],
"source": [
"%%sql --save query_0_1 result_0_1 <<\n",
"SELECT * FROM hot_100 LIMIT 25"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"tags": [
"otter_answer_cell"
]
},
"outputs": [],
"source": [
"# Do not delete/edit this cell!\n",
"# You must run this cell before running the autograder.\n",
"query_0_1 = %sqlcmd snippets query_0_1\n",
"grading_util.save_results(\"result_0_1\", query_0_1, result_0_1)\n",
"result_0_1"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"tags": [
"otter_answer_cell"
]
},
"outputs": [],
"source": [
"%%sql --save query_0_2 result_0_2 <<\n",
"SELECT * FROM hot_100 ORDER BY week_ending DESC LIMIT 30"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"scrolled": true,
"tags": [
"otter_answer_cell"
]
},
"outputs": [],
"source": [
"# Do not delete/edit this cell!\n",
"# You must run this cell before running the autograder.\n",
"query_0_2 = %sqlcmd snippets query_0_2\n",
"grading_util.save_results(\"result_0_2\", query_0_2, result_0_2)\n",
"result_0_2"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"tags": [
"otter_answer_cell"
]
},
"outputs": [],
"source": [
"display_query(result_0_2);"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"deletable": false,
"editable": false
},
"outputs": [],
"source": [
"grader.check(\"q0\")"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"
\n",
"\n",
"\n",
"\n",
"\n",
"# Question 1 — Recent Hits\n"
]
},
{
"cell_type": "markdown",
"metadata": {
"deletable": false,
"editable": false
},
"source": [
"Select all song titles and artists on the Billboard Hot 100 chart in March 2024 which have ranking of 15 or better, sorted alphabetically by artist name.\n",
"\n",
"Notes:\n",
"- The column `week_ending` is a **string**. it is represented `YYYY-MM-DD`. Think back to string comparisons in Data 100. (Later in this class we will explore Postgres date types.)\n",
"\n",
"- Rank 1 is the best, so make sure you are using the right comparison operator!\n",
"\n",
"- Remember that a **unique** pair of song and artist can appear multiple times on the chart (for example, if the song charts for multiple weeks). Make sure your output only includes each song once."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"tags": [
"otter_answer_cell"
]
},
"outputs": [],
"source": [
"%%sql --save query_1 result_1 <<\n",
"..."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"scrolled": true,
"tags": [
"otter_answer_cell"
]
},
"outputs": [],
"source": [
"# Do not delete/edit this cell!\n",
"# You must run this cell before running the autograder.\n",
"query_1 = %sqlcmd snippets query_1\n",
"grading_util.save_results(\"result_1\", query_1, result_1)\n",
"result_1"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"tags": [
"otter_answer_cell"
]
},
"outputs": [],
"source": [
"display_query(result_1);"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"deletable": false,
"editable": false
},
"outputs": [],
"source": [
"grader.check(\"q1\")"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"
\n",
"\n",
"\n",
"\n",
"# Question 2 — Longest Time Spent on the Charts\n"
]
},
{
"cell_type": "markdown",
"metadata": {
"deletable": false,
"editable": false
},
"source": [
"Find all song titles, their artist, and the total number of weeks the song spent on the Billboard Hot 100 (call this column `total_weeks_on_chart`).\n",
"\n",
"Only include songs that have spent >8 weeks on the chart.\n",
"\n",
"Sort by the total number of weeks the song spent on the chart, descending order.\n",
"\n",
"Notes:\n",
"- Similar to the previous question, make sure each song appears once\n",
"- The `weeks_on_chart` column is the number of weeks on the chart **as of that week**. How can we get the total number of weeks?\n",
"- Do **NOT** count the total number of rows in which the song/artist pair appear on the hot_100 table"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"tags": [
"otter_answer_cell"
]
},
"outputs": [],
"source": [
"%%sql --save query_2 result_2 <<\n",
"..."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"scrolled": true,
"tags": [
"otter_answer_cell"
]
},
"outputs": [],
"source": [
"# Do not delete/edit this cell!\n",
"# You must run this cell before running the autograder.\n",
"query_2 = %sqlcmd snippets query_2\n",
"grading_util.save_results(\"result_2\", query_2, result_2)\n",
"result_2"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"tags": [
"otter_answer_cell"
]
},
"outputs": [],
"source": [
"display_query(result_2);"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"deletable": false,
"editable": false
},
"outputs": [],
"source": [
"grader.check(\"q2\")"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"
\n",
"\n",
"\n",
"\n",
"\n",
"# Question 3 — Longest Top 10 Hits of the 1970's\n"
]
},
{
"cell_type": "markdown",
"metadata": {
"deletable": false,
"editable": false
},
"source": [
"Find all song titles, their corresponding artist, and its *highest* peak position (call this column `highest_peak_position`). \n",
"\n",
"**Only include songs that charted from Jan. 1, 1970 - Dec. 31, 1979 (inclusive) AND achieved a highest peak position in the top 10 (position 1-10, inclusive) anytime before Dec. 31, 1979 (inclusive).**\n",
"\n",
"Sort by `highest_peak_position` in ascending order **AND** the song title in ascending order to break ties.\n",
"\n",
"Some reminders:\n",
"- Peak position refers to the historical highest rank of a song, while rank only indicates the chart position for that specific week.\n",
"- Remember that \"highest\" position refers to lower numbers (e.g. position of 5 is higher than position of 10)."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"tags": [
"otter_answer_cell"
]
},
"outputs": [],
"source": [
"%%sql --save query_3 result_3 <<\n",
"..."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"scrolled": true,
"tags": [
"otter_answer_cell"
]
},
"outputs": [],
"source": [
"# Do not delete/edit this cell!\n",
"# You must run this cell before running the autograder.\n",
"query_3 = %sqlcmd snippets query_3\n",
"grading_util.save_results(\"result_3\", query_3, result_3)\n",
"result_3"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"tags": [
"otter_answer_cell"
]
},
"outputs": [],
"source": [
"display_query(result_3);"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"deletable": false,
"editable": false
},
"outputs": [],
"source": [
"grader.check(\"q3\")"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"
\n",
"\n",
"\n",
"\n",
"\n",
"# Question 4 — Top Artists By Number 1 Hits\n"
]
},
{
"cell_type": "markdown",
"metadata": {
"deletable": false,
"editable": false
},
"source": [
"Find the top 50 _artists_ by total number of weeks any of their songs spent at rank 1 (call this column `total_weeks_at_number_one`).\n",
"\n",
"Sort by `total_weeks_at_number_one` in descending order **AND** the name of the artist in ascending order to break tie.\n",
"\n",
"Example: Suppose artist Jane Doe has 3 songs A, B, and C that have spent 10, 20, and 30 weeks at rank 1, respectively. Then the expected output would be: Jane Doe, 60 because 10 + 20 + 30 = 60 weeks total.\n",
"\n",
"Notes:\n",
"- Remember that an artist can have multiple songs that have reached rank 1 at some point\n",
"- Remember that every row in the `hot_100` table represents 1 week on the chart, so be careful about how you aggregate the rows. Which aggregation function would work best here?"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"tags": [
"otter_answer_cell"
]
},
"outputs": [],
"source": [
"%%sql --save query_4 result_4 <<\n",
"..."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"scrolled": true,
"tags": [
"otter_answer_cell"
]
},
"outputs": [],
"source": [
"# Do not delete/edit this cell!\n",
"# You must run this cell before running the autograder.\n",
"query_4 = %sqlcmd snippets query_4\n",
"grading_util.save_results(\"result_4\", query_4, result_4)\n",
"result_4"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"tags": [
"otter_answer_cell"
]
},
"outputs": [],
"source": [
"display_query(result_4);"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"deletable": false,
"editable": false
},
"outputs": [],
"source": [
"grader.check(\"q4\")"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"
\n",
"\n",
"\n",
"\n",
"\n",
"# Question 5 — What's Trending on TikTok?"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Question 5a — Popular Everywhere"
]
},
{
"cell_type": "markdown",
"metadata": {
"deletable": false,
"editable": false
},
"source": [
"Find the songs and their corresponding artists that were on both the Billboard Hot 100 and TikTok Top 50 charts in the same week.\n",
"\n",
"Sort in ascending order by song title.\n",
"\n",
"Notes:\n",
"- Remember that the same song can appear in multiple rows of the chart (for example, if the song was on the chart for multiple weeks)\n",
"- A song is considered the \"same\" as another song if the song title and artist match"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"tags": [
"otter_answer_cell"
]
},
"outputs": [],
"source": [
"%%sql --save query_5_0 result_5_0 <<\n",
"..."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"scrolled": true,
"tags": [
"otter_answer_cell"
]
},
"outputs": [],
"source": [
"# Do not delete/edit this cell!\n",
"# You must run this cell before running the autograder.\n",
"query_5_0 = %sqlcmd snippets query_5_0\n",
"grading_util.save_results(\"result_5_0\", query_5_0, result_5_0)\n",
"result_5_0"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"tags": [
"otter_answer_cell"
]
},
"outputs": [],
"source": [
"display_query(result_5_0);"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"deletable": false,
"editable": false
},
"outputs": [],
"source": [
"grader.check(\"q5a\")"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Question 5b — Brat Summer"
]
},
{
"cell_type": "markdown",
"metadata": {
"deletable": false,
"editable": false
},
"source": [
"Continuing to find only the songs and their corresponding artists that were on **both the Billboard Hot 100 and TikTok Top 50 charts in the same week**, write a query that outputs:\n",
"\n",
"- `week_ending`\n",
"- Billboard Hot 100 peak position for that week (call this column `hot_100_peak_pos`)\n",
"- TikTok Top 50 peak position for that week (call this column `tiktok_50_peak_pos`)\n",
"- Song title\n",
"\n",
"For all songs where the artist is `'Charli xcx'` (case sensitive). \n",
"\n",
"Sort by `week_ending`, ascending order.\n",
"\n",
"
![\"Brat](\"https://i.kym-cdn.com/photos/images/newsfeed/002/862/673/d07.jpg\")
\n",
"