SQL and Business Insights
- Log-in to your Strata Scratch account.
- All tables used in this exercise are taken from the datasets schema. Make sure you select this schema on the SQL editor settings.
- Try to answer the following questions by writing the appropriate SQL query on the editor.
- For most of the questions, you will need to produce a chart that shows a list of dimensions (e.g., music artists) and counts
(e.g., the number of grammies they’ve won).
- For each topic, there are also one to two open-ended questions (e.g., who is the best athlete to be drafted into the NFL?).
You’ll need to analyze the dataset, create your own metrics, and provide a convincing argument.
- This is the teacher version of the SQL and business insights exercise. The questions below are followed with the correct solution.
Each topic includes one to two open-ended questions which could have numerous possible solutions, and one
of these solutions is shown here.
1. Billboard Top 100
- What were the top songs for the past 30 years?
- Which artists have had the most top 10 songs over the years? What were the songs?
- Which artists have been on the billboard top 100 the most in the past 10 years?
- Who is the best artist in the past 50 years? What metrics would you use to answer this question?
2. SF Crime
- What were the top crime categories in 2014? How many incidences per category?
- What day of the week was there the most crime? Does crime incidence vary depending on the month? Depending on the time?
- What districts had the most crime incidences?
- Where is the most dangerous place in SF? What metrics would you use to convince me?
3. Oscar Nominees
- Who has won the most oscars?
- Count the number of nominations which didn’t end with a award. Who was the least lucky?
- Who has the highest win to nomination ratio?
- Which movies had the most nominated actors/actresses?
- Who is the best actor/actress of all time? What metrics would you use to convince me?
4. Video Game Charts
- Which games have been in the top 100 for the longest? How many weeks were they in the top 100?
- Which games, by platform, has been in the top 10 for the longest?
- What genres yielded highest sales?
- Which are the best publishers? What metrics would you use to analyze?
5. NFL Combine
- Which colleges produce the most NFL players?
- How do the 40-yard dash, vertical, broad jump, and bench differ between athletes drafted into the NFL vs undrafted? How do the metrics vary by draft round?
- Which colleges produce the best quarterbacks? How about other positions? How did you define the best?
- Who is the best athlete? What metrics would you analyze to prove your case?
- How many people search for hosts on Airbnb?
- How many nights are most people searching for when trying to book a host?
- What day of the week are most people checking in? What day of the week are most people checking out?
- What type of rooms are most people searching for?
- What’s the acceptance rate of requests?
- What can Airbnb do to increase the number of bookings?