Hello readers, hope you are well. I’m back, as usual, with prepared insight and news for you in this week’s Data Analysis Journal.  

✨ In today’s newsletter:

• How to apply CASE in SQL to specify a result when there are multiple conditions.

• Testing and evaluating ML models.

• The best books to read on SQL.

  Subscribe now

  

  📚 Weekend Longread

  A technical deep dive from Jeremy Jordan, machine learning engineer, about effective testing for ML systems. He starts by covering various types of testing for traditional software development and then explains why testing ML is different. The author covers the difference between model evaluation and testing and clarifies why you need to do both.

  🔥 What’s new this week

  • Borrowing from another blogger, this is an awesome resource on best books, courses, and practices on data engineering  - Awesome Data Engineering. Big thanks to the author Snir David for putting it together. 

  • A horror story about what happens when you accidentally delete a database in production. Not a good thing to do. Read up on this if you weren’t scared enough over Halloween.

  • Check these graphs from Levels to estimate the approximate compensation you can expect at different companies in the Bay Area (California) broken down by levels. This also includes tips on how to work on your negotiation skills (then feel free to tell me how you do it, please!). 

  • Python creator joined Microsoft team Developer Division. Must be amazing to have a coworker who created Python!

  • 7 days left to register for The Great Indian Hiring Hackathon. MachineHack collaborated with 12 companies who all came together to launch the biggest hiring hackathon for data scientists. 

    🏆 Nailed It

    Be prepared for your next interview

    SQL practice for your enjoyment today. A famous CASE expression!

    CASE value 

       WHEN value1 THEN result1

       WHEN value2 THEN result2

       …

       [ELSE else_result]

    END

    CASE is a conditional expression similar to if-then-else statements. It is supported by all SQL databases (Oracle, MySQL, SQL Server, etc). Using CASE statements for SQL challenges during the technical interview demonstrates solid SQL understanding, so I highly encourage using it for filtering and specifying conditions and expressions.  

    SELECT product,

    CASE product

    WHEN 'Apple' THEN 'Fruit'

    WHEN 'Tomato' THEN 'Vegetable' 

    ELSE 'other'

    END 

    FROM items

    Here is a more complex CASE example using COUNT:

    SELECT COUNT(CASE WHEN country = 'FR' THEN 1 ELSE NULL END) AS fr_count,

           COUNT(CASE WHEN country = 'IN' THEN 1 ELSE NULL END) AS in_count,

           COUNT(CASE WHEN country = 'JP' THEN 1 ELSE NULL END) AS jp_count,

           COUNT(CASE WHEN country = 'IT' THEN 1 ELSE NULL END) AS it_count

      FROM users

    Things to remember about CASE:

    • There are 2 types of CASE expressions: simple (equals (=) comparisons) and extended (accepts comparison operators <>, BETWEEN, LIKE). 

    • If you use RedShift, it differentiates simple and searched (with Boolean expression) CASE statements.

    • CASE is a conditional expression that allows you to optimize your query and be flexible with your filters. There is always another way to get the same result without CASE with WHERE or additional filtering.

    • If you skip ELSE, and not a specified condition is found, it will return as NULL.

    • In most cases, you can also use DECODE instead of CASE. 

    • If you compare NULL to NULL using CASE, it will return as FALSE. DECODE will return as TRUE. This is an important difference.

    • Keep in mind, conditions in the WHEN clause are evaluated in the given order. Once a condition is found to be true, the CASE statement will return the result and not evaluate the conditions further.

    Here is more documentation on CASE usage: 

    CASE in MySQL

    CASE in PostgreSQL

    CASE in RedShift

    CASE in SQL Server

    🎓 Level Up

    Certifications, internships, schools, and courses.

    Interesting, helpful, and free books on data analysis and ML:

    1. Data Virtualization for Dummies - a guide about data pipelines, ETLs, and how to integrate together data sources and data applications. Learn methodologies on how to store, locate, access, and share massive data volumes. The book is leaning more towards enterprise adoption.  

    2. Mastering the Model Lifecycle Orchestration Journey: A practical guide for Data Scientists, IT/DevOps and Business Analysts - this book is a collection of best practices on ML modeling presented during the 2020 SAS Global Forum. 

    3. Best Practices for Optimizing Postgres Query Performance - “Learn how to get a 3x performance improvement on your Postgres database and 500x reduced data loaded from disk.”

    4. The why, what, & how of Digital Data Governance - how to ensure the data you are capturing is complete, accurate, and secure, and how to extract the most value from your available data.

    5. Machine Learning from Scratch - read about Linear Regression, Logistic Regression, Naive Bayes, Decision Trees, and Neural Networks.

    And the bonus:

    6. The best 16 books to learn SQL - a roundup of the best books on SQL (from beginner to advanced level) from the DataPine blog. 

    🍸 Drink and Mingle

    Upcoming free events, meetups, talk, and webinars.

    • Nov 18, Anaconda: Data Exploration, Visuals, and Dashboards Using Familiar APIs

    • Nov 19, WiMLDS: ML For Climate Change

    • Nov 19, Getting Started with GraphQL

    • Nov 19, Galvanize: Intro To Python. Part  2

    • Nov 19, Master Data Management:  Aligning Data, Process, & Governance

    • Nov 20, SWD: Accelerating ETL for Recommender Systems

    • Nov 21, WWC: Data Visualization through Tableau

    • Dec 8, DSS: Applying AI & ML to Finance & Tech

      

    🙏 Do Some Good.

    It pays back.

    I have a favor to ask. As my audience grows, I’d like to better understand how this journal can stay interesting and help you to become a better data analyst, data scientist, and progress in your career. I’d appreciate it if you could take a moment to fill out this brief survey. (And thank you to those who have already done it!) 

    

    Thanks for reading everyone. Until next Wednesday!

    Subscribe now