Welcome to The Summer of SQL! This series is designed to help participants gain skills and confidence in SQL through a variety of hands-on challenges.
I ran this series interally with The Information Lab which:
- Doubled our active SQL users monthly
- Empowered participants to start their own SQL projects
- Gave them the confidence to run their own SQL training sessions
If you are new to SQL, we recommend starting with the interactive tutorials on SQLBolt. These tutorials will help you understand the basics of SQL and prepare you for the weekly challenges ahead.
Each week, a new SQL challenge will be posted to socials. Participants can attempt the challenge, and then review the walkthrough and solutions to enhance their SQL skills.
Of note, there are sometimes:
- multiple ways to solve a question,
- different was of interpreting a question,
- and variations in SQL dialects (e.g. mySQL vs PostgreSQL)
Your answer may be different and that's ok!
If you can explain why you have written the code to answer the question that is the most important thing.
Share your progress on social media using the hashtags #SummerofSQL and #datafam.
| Week | Challenge | Estimated Time | Skills Tested | Walkthrough | Solutions |
|---|---|---|---|---|---|
| 1. | SQL Murder Mystery | 1/2 a day | - Filtering with WHERE - JOINing tables - Aggregations and GROUP BY - String Functions and Pattern Matching (LIKE) - Subqueries and CTEs (optional) |
YouTube Walkthrough | Solution |
| 2. | Data with Danny: Danny's Diner |
1 day | - Ranking - Common Table Expressions (CTEs) - Case Statements - Dates and Scalar functions |
YouTube Walkthrough | Solution |
| 3. | Preppin' Data: - 2023 Week 1 - 2023 Week 2 - 2023 Week 3 - 2023 Week 4 |
1 day (in total) | - String manipulation with SPLIT_PART and REPLACE - UNION ALL vs UNION -Using ROW_NUMBER for deduplication - Pivoting data with PIVOT and UNPIVOT - Common Table Expressions (CTEs) |
Week 1 Week 2 Week 3 Week 4 |
Week 1 Week 2 Week 3 Week 4 |
| 4. | Data with Danny: Pizza Runner |
2 days | - Searching Text - Case Statements - NULLs - Datetime and Scalar functions - Comma Separated Strings - Aggregation, Unions - CTEs - Type Conversions |
Section A Section B Section C |
Section A Section B Section C |
| 5. | Data with Danny: Foodie Fi Sections A & B |
1 day | - CTEs - Row Number - Date Functions |
YouTube Walkthrough | Solutions |
| 6. | Preppin' Data: - 2023 Week 5 - 2023 Week 6 - 2023 Week 7 - 2023 Week 8 |
1-2 days (in total) | - Multi-condition RANKing - Using CASE statements for conditional logic and categorization - Complex JOIN operations - Handling null values and splitting strings into rows with SPLIT_TO_TABLE - Applying ROUND function for rounding numeric values |
Week 5 Week 6 Week 7 Week 8 |
Week 5 Week 6 Week 7 Week 8 |
| 7. | Portfolio Project: Lego Creator Lego Analysis Challenge |
1-2 days | - Setup a local database - Load data to database - Create a user/role - Connect to dBeaver, Tableau, Python - Create a schema diagram (ERD) - Analyse data and create a view - Visualise the data in Tableau |
Setup PostgreSQL Prep, Analyse & Visualise Data |
Import Data Data Prep Data-Analysis Visualisation |
| 8. | Data with Danny: Data Bank Section A |
1 day | - Calculating stats - Median - Percentile |
YouTube Walkthrough | Solutions |
| 9. | Preppin' Data: - 2023 Week 9 - 2023 Week 10 - 2023 Week 11 - 2023 Week 12 |
2 days (in total) | - Running SUMs - Recursive CTEs - Setting Variables - Math functions - Filling in blank data points |
Week 9 Week 10 Week 11 Week 12 |
Week 9 Week 10 Week 11 Week 12 |
| 10. | Data with Danny: Data Bank Section B |
1 day | - End of month calculations - Running sums - conditional aggregations |
YouTube Walkthrough | Solutions |
| Bonus! | Portfolio Project: The SQL Olympics | 2 days | - Setup a mySQL database - Load data to database - Connect to dBeaver & Tableau - Create a data model and a schema diagram (ERD) - Analyse data and create a view - Visualise the data in Tableau |
Create db Data model Data analysis |