2021: Week 8 - Karaoke Data

Challenge by: Jenny Martin

Recently I was helping a colleague prep some karaoke data and I thought it was too fun a subject to resist turning into a Preppin' Data challenge! I had a lot of fun creating the dataset and imagining the type of person who may sing one song and then not bother with the rest of the session. 

We will need to make some assumptions as part of our data prep:

  • Customers often don't sing the entire song
  • Sessions last 60 minutes
  • Customers arrive a maximum of 10 minutes before their sessions begin
I will warn you that this challenge may be a little on the trickier end of the spectrum!

Inputs

  1. Karaoke song choices and what time they began 

  2. Customer entry times 

Requirements

  • Input the data
  • Calculate the time between songs (help)
  • If the time between songs is greater than (or equal to) 59 minutes, flag this as being a new session (help)
  • Create a session number field (help)
  • Number the songs in order for each session (help)
  • Match the customers to the correct session, based on their entry time (help)
    • The Customer ID field should be null if there were no customers who arrived 10 minutes (or less) before the start of the session
  • Output the data

Output


  • 6 fields
    • Session #
    • Customer ID
    • Song Order
    • Date
    • Artist
    • Song
  • 988 rows (989 including headers)

The full output can be downloaded here.

After you finish the challenge make sure to fill in the participation tracker, then share your solution on Twitter using #PreppinData and tagging @Datajedininja@JennyMartinDS14 & @TomProwse1

You can also post your solution on the Tableau Forum where we have a Preppin' Data community page. Post your solutions and ask questions if you need any help! 



Comments

Popular posts from this blog

How to use... Custom SQL in Prep Builder

2021: Week 1

How to... Aggregate in Prep Builder