The Challenge Index

Now we have lots of challenges, to make the site a little easier to navigate here is a link to all of the individual challenges and their solution posts.

Don't forget to fill out the Participation Tracker. You can check which challenges you have completed here.

Links

2024
Introductory Month
Week 1: Challenge  Solution  Video  Prep Air's Flow Card
Week 2: Challenge  Solution  Video  Average Price Analysis
Week 3: Challenge  Solution  Video  Performance Against Targets
Week 4: Challenge  Solution  Video  Unpopular Seats
Week 5: Challenge  Solution  Video  Getting The Right Data

Intermediate Month
Week 6: Challenge  Solution  Video  Staff Income Tax
Week 7: Challenge  Solution  Video  Valentine's Day
Week 8: Challenge  Solution  Video  Prep Air Loyalty
Week 9: Challenge  Solution  Video  Prep Air Capacity

Week 10: Challenge  Solution  Video  Preppin' for Pulse
Week 11: Challenge  Solution  Video  13 Months in a Year
Week 12: Challenge  Solution  Video  Graduate Student Loan Repayments
Week 13: Challenge  Solution  Video  Easter Sales
Week 14: Challenge  Solution  Video  Store Data
Week 15: Challenge  Solution  Video  Store Data Part 2


2023
Introductory Month
Week 1: Challenge  Solution  Video  The Data Source Bank
Week 2: Challenge  Solution  Video  International Bank Account Numbers
Week 3: Challenge  Solution  Video  Targets for DSB
Week 4: Challenge  Solution  Video  New Customers

Intermediate Month
Week 5: Challenge  Solution  Video  DSB Ranking
Week 6: Challenge  Solution  Video  DSB Customer Ratings
Week 7: Challenge  Solution  Video  Flagging Fraudulent Suspicions 
Week 8: Challenge  Solution  Video  Taking Stock

Advanced Month
Week 9:   Challenge  Solution  Video  Customer Bank Statements
Week 10: Challenge  Solution  Video  What's my Balance on this Day?
Week 11: Challenge  Solution  Video  Which Customers are Closest?
Week 12: Challenge  Solution  Video  Regulatory Reporting Alignment
Week 13: Challenge  Solution  Video  Rolling Stock Price Trends

Week 14: Challenge  Solution  Video  World Trade Data
Week 15: Challenge  Solution  Video  Easter Dates
Week 16: Challenge  Solution  Video  Easter and Full Moons
Week 17: Challenge  Solution  Video  Population Growth vs Country Size
Week 18: Challenge  Solution  Video  The Nut House Revenue Analysis
Week 19: Challenge  Solution  Video  Tableau Conference Special
Week 20: Challenge  Solution  Video  Dining Hall Debacle
Week 21: Challenge  Solution  Video  Prep School Grades
Week 22: Challenge  Solution  Video  Student Attendance vs Test Scores
Week 23: Challenge  Solution  Video  Is it the teacher or the student? Part 1
Week 24: Challenge  Solution  Video  Is it the teacher or the student? Part 2
Week 25: Challenge  Solution  Video  Prep School 2023 Admissions - Part 1
Week 26: Challenge  Solution  Video  Prep School 2023 Admissions - Part 2
Week 27: Challenge  Solution  Video  The Cost of Running the Prep School
Week 28: Challenge  Solution  Video  Prep School Track Team
Week 29: Challenge  Solution  Video  Moving Averages
Week 30: Challenge  Solution  Video  % Difference From

HR Month
Week 31: Challenge  Solution  Video  Filling in Missing IDs
Week 32: Challenge  Solution  Video  Reshaping Generations
Week 33: Challenge  Solution  Video  Combinations
Week 34: Challenge  Solution  Video  Turnover Rate
Week 35: Challenge  Solution  Video  Transfers

Week 36: Challenge  Solution  Video  C&BSCo 5-a-side
Week 37: Challenge  Solution  Video  Order Sequence Analysis
Week 38: Challenge  Solution  Video  Sustainable Development Goals
Week 39: Challenge  Solution  Video  Tennis 200 Club
Week 40: Challenge  Solution  Video  Prep School Subject Dropouts
Week 41: Challenge  Solution  Video  An International School 
Week 42: Challenge  Solution  Video  Prep School "Disco Fever"
Week 43: Challenge  Solution  Video  Best Attendance Awards
Week 44: Challenge  Solution  Video  Prep School Spring Calendar
Week 45: Challenge  Solution  Video  Fill Down
Week 46: Challenge  Solution  Video  Late for School
Week 47: Challenge  Solution  Video  Student GPA
Week 48: Challenge  Solution  Video  Reporting Week Calendars
Week 49: Challenge  Solution  Video  Regular Savings Accounts
Week 50: Challenge  Solution  Video  Regular Savings Accounts - Part 2
Week 51: Challenge  Solution  Video  Elf Toy Production
Week 52: Challenge  Solution  Video  Preppin' Classification


2022
Week 1: Challenge  Solution  Video  The Prep School - Parental Contact Details
Week 2: Challenge  Solution  Video  The Prep School - Birthday Cakes
Week 3: Challenge  Solution  Video  The Prep School - Passing Grades
Week 4: Challenge  Solution  Video  The Prep School - Travel Plans
Week 5: Challenge  Solution  Video  The Prep School - Setting Grades
Week 6: Challenge  Solution  Video  7 Letter Scrabble Words
Week 7: Challenge  Solution  Video  Call Centre Agent Metrics
Week 8: Challenge  Solution  Video  Pokemon Evolution Stats
Week 9: Challenge  Solution  Video  Customer Classifications
Week 10: Challenge  Solution  Video  The Bechdel test
Week 11: Challenge  Solution  Video  The Prep School - Filling the Blanks
Week 12: Challenge  Solution  Video  Gender Pay Gap
Week 13: Challenge  Solution  Video  Pareto Parameters
Week 14: Challenge  Solution  Video  House of Games Winners
Week 15: Challenge  Solution  Video  Property Management
Week 16: Challenge  Solution  Video  Restaurant Orders
Week 17: Challenge  Solution  Video  The Price of Streaming
Week 18: Challenge  Solution  Video  Un-Pivoting
Week 19: Challenge  Solution  Video  Good Sales, Wrong Sizes
Week 20: Challenge  Solution  Video  TC22 Session Attendance
Week 21: Challenge  Solution  Video  Multi-Sheets of Madness
Week 22: Challenge  Solution  Video  Dungeons & Dragons: Critical Role
Week 23: Challenge  Solution  Video  PD x WOW Salesforce Opportunities
Week 24: Challenge  Solution  Video  Longest Flights
Week 25: Challenge  Solution  Video  Housing Happy Hotel Guests
Week 26: Challenge  Solution  Video  Making Spotify Data Spotless
Week 27: Challenge  Solution  Video  C&BSCo Clean and Aggregate
Week 28: Challenge  Solution  Video  C&BSCo No Sales Today
Week 29: Challenge  Solution  Video  C&BSCo Meeting Targets?
Week 30: Challenge  Solution  Video  C&BSCo Actual Sales Values
Week 31: Challenge  Solution  Video  C&BSCo Preppin' Parameters 
Week 32: Challenge  Solution  Video  C&BSCo Mortgage Repayments
Week 33: Challenge  Solution  Video  C&BSCo Next Sale
Week 34: Challenge  Solution  Video  C&BSCo Parameters, Parameters, Parameters
Week 35: Challenge  Solution  Video  C&BSCo Summary Stats
Week 36: Challenge  Solution  Video  Calendar Conundrum
Week 37: Challenge  Solution  Video  Elden Ring Adventure
Week 38: Challenge  Solution  Video  Salesforce Standard Connections for Dreamforce 22
Week 39: Challenge  Solution  Video  Filling in for HR
Week 40: Challenge  Solution  Video  Times Tables
Week 41: Challenge  Solution  Video  Dynamic Times Tables
Week 42: Challenge  Solution  Video  Missing Training Data
Week 43: Challenge  Solution  Video  Missing Training Data 2.0
Week 44: Challenge  Solution  Video  Creating Order IDs
Week 45: Challenge  Solution  Video  Strange Table Structure
Week 46: Challenge  Solution  Video  Dynamically Fixing Table Structures
Week 47: Challenge  Solution  Video  Chelsea Managers per Prime Minister
Week 48: Challenge  Solution  Video  Tiddlywinks Tidy
Week 49: Challenge  Solution  Video  Cleaning Status History (HR month)
Week 50: Challenge  Solution  Video  Withdrawals (HR month)
Week 51: Challenge  Solution  Video  Parsing an Unfortunate Report Format (HR month)
Week 52: Challenge  Solution  Video  Find Sub-Departments from Employee Hierarchy (HR month)


2021
Week 1: Challenge   Solution   Video  New Year, New Challenge
Week 2: Challenge   Solution   Video  Bike Store Sales 
Week 3: Challenge   Solution   Video  Bike Accessory Sales
Week 4: Challenge   Solution   Video  Bike Sales Targets
Week 5: Challenge   Solution   Video  Dealing With Duplication
Week 6: Challenge   Solution   Video  Comparing Prize Money for Professional Golfers
Week 7: Challenge   Solution   Video  Vegan Shopping List
Week 8: Challenge   Solution   Video  Karaoke Data
Week 9: Challenge   Solution   Video  Working with Strings
Week 10: Challenge   Solution   Video  Pokémon Hierarchies
Week 11: Challenge   Solution   Video  Cocktail Profit Margins
Week 12: Challenge   Solution   Video  Maldives Tourism
Week 13: Challenge   Solution   Video  Premier League Statistics
Week 14: Challenge   Solution   Video  Prep Air In-Flight Purchases
Week 15: Challenge   Solution   Video  Restaurant Menu & Orders
Week 16: Challenge   Solution   Video  The Super League
Week 17: Challenge   Solution   Video  Timesheet Checks
Week 18: Challenge   Solution   Video  Prep Air Project Overruns
Week 19: Challenge   Solution   Video  Prep Air Project Details
Week 20: Challenge   Solution   Video  Controlling Complaints
Week 21: Challenge   Solution   Video  Getting Trolleyed
Week 22: Challenge   Solution   Video  Answer Smash
Week 23: Challenge   Solution   Video  NPS for Airlines
Week 24: Challenge   Solution   Video  C&BS Co: Absence Monitoring
Week 25: Challenge   Solution   Video  The Worst Pokemon
Week 26: Challenge   Solution   Video  Rolling Weekly Revenue
Week 27: Challenge   Solution   Video  NBA Draft Lottery calculator
Week 28: Challenge   Solution   Video  It's Coming Rome
Week 29: Challenge   Solution   Video  PD x WOW - Tokyo 2020 Calendar
Week 30: Challenge   Solution   Video  Lift Your Spirits
Week 31: Challenge   Solution   Video  Excelling in Prep
Week 32: Challenge   Solution   Video  Excelling through aggregation
Week 33: Challenge   Solution   Video  Excelling at adding one more row
Week 34: Challenge   Solution   Video  Excelling with lookups
Week 35: Challenge   Solution   Video  Picture perfect
Week 36: Challenge   Solution   Video  What's trendy?
Week 37: Challenge   Solution   Video  Re-looking at phone contract revenue
Week 38: Challenge   Solution   Video  Trilogy
Week 39: Challenge   Solution   Video  Painting bikes
Week 40: Challenge   Solution   Video  Animal adoptions
Week 41: Challenge   Solution   Video  Southend Stats
Week 42: Challenge   Solution   Video  Charity Fundraising
Week 43: Challenge   Solution   Video  Is that the case?
Week 44: Challenge   Solution   Video  On yer bike!
Week 45: Challenge   Solution   Video  Spread the Knowledge for TC!
Week 46: Challenge   Solution   Video  Bookshop Data Modelling
Week 47: Challenge   Solution   Video  Games Night Viz Collab
Week 48: Challenge   Solution   Video  Departmental December - Finance
Week 49: Challenge   Solution   Video  Departmental December - Human Resources
Week 50: Challenge   Solution   Video  Departmental December - Sales
Week 51: Challenge   Solution   Video  Departmental December - IT
Week 52: Challenge   Solution   Video  Departmental December - Operations


2020
Week 1: Challenge   Solution   Video  Accounting Formatting
Week 2: Challenge   Solution   Video  Cleaning Time
Week 3: Challenge   Solution   Video  NBA Standings
Week 4: Challenge   Solution   Video  C&BS Co: Store Survey deduplication
Week 5: Challenge   Solution   Video  Technique: Standard Competition Rank
Week 6: Challenge   Solution   Video  C&BS Co: Currency Conversion
Week 7: Challenge   Solution   Video  Counting People Past & Present
Week 8: Challenge   Solution   Video  Comparing Actuals vs Budget / Profitable levels
Week 9: Challenge   Solution   Video  C&BS Co: Political Monitoring
Week 10: Challenge   Solution   Video  C&BS Co: A Logic Grid Puzzle
Week 11: Challenge   Solution   Video  C&BS Co: Knapsack Problem
Week 12: Challenge   Solution   Video  C&BS Co: Messy Data Clean Up
Week 13: Challenge   Solution   Video  C&BS Co IT:  Analysing Support Desk SLA Compliance
Week 14: Challenge   Solution   Video  Preppin' Data to Build a Spider Chart
Week 15: Challenge   Solution   Video  Market Basket Analysis
Week 16: Challenge   Solution   Video  Preppin' the un-Visualisable
Week 17: Challenge   Solution   Video  Netflix Usage
Week 18: Challenge   Solution   Video  Liverpool Line-ups
Week 19: Challenge   Solution   Video  Liverpool Line-ups (Part 2)
Week 20: Challenge   Solution   Video  Cipher Solving
Week 21: Challenge   Solution   Video  Competitor Analysis
Week 22: Challenge   Solution   Video  Creating Data
Week 23: Challenge   Solution   Video  Let's Get Quizzical
Week 24: Challenge   Solution   Video  Historical Battles
Week 25: Challenge   Solution   Video  Roman Numerals
Week 26: Challenge   Solution   Video  Matching IDs
Week 27: Challenge   Solution   Video  Surf's Up!
Week 28: Challenge   Solution   Video  Olympic Dates
Week 29: Challenge   Solution   Video  Event Refunds
Week 30: Challenge   Solution   Video  What's the Weather?
Week 31: Challenge   Solution   Video  When PD met Workout Wednesday Again!
Week 32: Challenge   Solution   Video  Fill Down Challenge
Week 33: Challenge Solution Video Fill Down Challenge 2
Week 34: Challenge Solution Video C&BS Co: Profitability Analysis
Week 35: Challenge Solution Video C&BS Co: Restructuring Excel data
Week 36: Challenge Solution Video Back to School: Teacher Allocations
Week 37: Challenge Solution Video Working Days
Week 38: Challenge   Solution   Video  Anagrams or Snag a Ram?
Week 39: Challenge   Solution   Video  Prep a Manger
Week 40: Challenge   Solution   Video  What Were William's Words Worth? 
Week 41: Challenge   Solution   Video  Tableau Conference-ish!
Week 42: Challenge   Solution   Video  Progress to Date tracking
Week 43: Challenge   Solution   Video  Baby Names
Week 44: Challenge   Solution   Video  Spooky Sales
Week 45: Challenge   Solution   Video  Launching Prep Air
Week 46: Challenge   Solution   Video  Aviation Incidents 
Week 47: Challenge   Solution   Video  Prep Air Delays
Week 48: Challenge   Solution   Video  Gate Allocation Problem
Week 49: Challenge   Solution   Video  NBA Rankings
Week 50: Challenge   Solution   Video  Secret Santa
Week 51: Challenge   Solution   Video  NBA Travel Times
Week 52: Challenge   Solution   Video  US Women's Championship Golf
Week 53: Challenge   Solution   Video  Changing Star Signs


2019
Week 1: Challenge   Solution   What colour cars are selling?
Week 2: Challenge   Solution   Windy, wet or both?
Week 3: Challenge   Solution   Mobile phone contract revenue
Week 4: Challenge   Solution   NBA player profile (pane) analysis
Week 5: Challenge   Solution   Balances, statements, and complaints
Week 6: Challenge   Solution   C&BS Co: purveyors of soap
Week 7: Challenge   Solution   C&BS Co: bubbling distribution problems
Week 8: Challenge   Solution   C&BS Co: accounting for slippery thieves
Week 9: Challenge   Solution   C&BS Co: scouring Twitter feedback
Week 10: Challenge   Solution   C&BS Co: freshening up a mailing list
Week 11: Challenge   Solution   Parsing JSON data
Week 12: Challenge   Solution   C&BS Co: sanitizing system downtime
Week 13: Challenge   Solution   C&BS Co: sniffing out balance issues
Week 14: Challenge   Solution   Meal deals at Whyte's Cafe
Week 15: Challenge   Solution   Finding weird client behaviour
Week 16: Challenge   Solution   C&BS Co: a 15 perScent discount!
Week 17: Challenge   Solution   Arrow's Impossibility Theorem
Week 18: Challenge   Solution   C&BS Co: Endless Legend of the Soap Heroes
Week 19: Challenge   Solution   C&BS Co: mud, sweat, & bicycle gears
Week 20: Challenge   Solution   C&BS Co: paying for hospital scrubs - Part 1
Week 21: Challenge   Solution   C&BS Co: paying for hospital scrubs - Part 2
Week 22: Challenge   Solution   Calculating a moving average
Week 23: Challenge   Solution   Creating TitleCase Text
Week 24: Challenge   Solution   What's up with WhatsApp?
Week 25: Challenge   Solution   When PD met Workout Wednesday
Week 26: Challenge   Solution   No typed calculations allowed!
Week 27: Challenge   Solution   C&BS Co: Valentine's analysis
Week 28: Challenge   Solution   C&BS Co: People time recording
Week 29: Challenge   Solution   C&BS Co: Subscription package financial model
Week 30: Challenge   Solution   Serpentine Swimming Club
Week 31: Challenge   Solution   C&BS Co: Orders and Operational Stats
Week 32: Challenge   Solution   C&BS Co: Multiple products, multiple columns
Week 33: Challenge   Solution   C&BS Co: HR data from multiple sources
Week 34: Challenge   Solution   C&BS Co: Supplier Timings part I
Week 35: Challenge   Solution   C&BS Co: Supplier Timings part II
Week 36: Challenge   Solution   Reusable Steps - Metric Conversion
Week 37: Challenge   Solution   Aggregations, aggregations and more aggregations
Week 38: Challenge   Solution   Health in Harmony challenge
Week 39: Challenge   Solution   C&BS Co: Web Analytics
Week 40: Challenge   Solution   Sudzilla Conference Survey results
Week 41: Challenge   Solution   C&BS Co: Complaints and Returns
Week 42: Challenge   Solution   C&BS Co: International Expansion
Week 43: Challenge   Solution   C&BS Co: End of Year Store Targets
Week 44: Challenge   Solution   C&BS Co: End of Year Team Member Performance
Week 45: Challenge   Solution   C&BS Co: Duplication and So, So Many Files
Week 46: Challenge   Solution   Naughty & Nice List

Popular posts from this blog

2023: Week 1 The Data Source Bank

Image
 Created by: Carl Allchin Welcome to a New Year of Preppin' Data. These are weekly exercises to help you learn and develop data preparation skills. We publish the challenges on a Wednesday and share a solution the following Tuesday. You can take the challenges whenever you want and we love to see your solutions. With data preparation, there is never just one way to complete the tasks so sharing your solutions will help others learn too. Share on Twitter, LinkedIn, the Tableau Forums or wherever you want to too. Tag Jenny Martin, Tom Prowse or myself or just use the #PreppinData to share your solutions.  The challenges are designed for learning Tableau Prep but we have a broad community who complete the challenges in R, Python, SQL, DBT, EasyMorph and many other tools. We love seeing people learn new tools so feel free to use whatever tools you want to complete the challenges.  A New Year means we start afresh so January's challenges will be focused on beginners. We will use dif
Read more

2023: Week 2 - International Bank Account Numbers

Image
Challenge By: Jenny Martin For week 2 of our beginner month, Data Source Bank has a requirement to construct International Bank Account Numbers (IBANs), even for Transactions taking place in the UK. We have all the information in separate fields, we just need to put it altogether in the following order: Inputs A list of the transactions, with information about the receiving bank account  A lookup table for the SWIFT Bank Codes  Requirements Input the data In the Transactions table, there is a Sort Code field which contains dashes. We need to remove these so just have a 6 digit string ( hint ) Use the SWIFT Bank Code lookup table to bring in additional information about the SWIFT code and Check Digits of the receiving bank account ( hint ) Add a field for the Country Code ( hint ) Hint: all these transactions take place in the UK so the Country Code should be GB Create the IBAN as above ( hint ) Hint: watch out for trying to combine sting fields with numeric fields - check data types Re
Read more

How to...Handle Free Text

Image
Free isn't always a good thing. In data, Free text is the example to state when proving that statements correct. However, lots of benefit can be gained from understanding data that has been entered in Free Text fields. What do we mean by Free Text? Free Text is the string based data that comes from allowing people to type answers in to systems and forms. The resulting data is normally stored within one column, with one answer per cell. As Free Text means the answer could be anything, this is what you get - absolutely anything. From expletives to slang, the words you will find in the data may be a challenge to interpret but the text is the closest way to collect the voice of your customer / employee. The Free Text field is likely to contain long, rambling sentences that can simply be analysed. If you count these fields, you are likely to have one of each entry each. Therefore, simply counting the entries will not provide anything meaningful to your analysis. The value is in
Read more