O'Reilly logo
live online training icon Live Online training

Advanced SQL Series: Proximal and Linear Interpolations

Ami Levin

Structured Query Language—more commonly known as SQL—is an amazing declarative programming language. While easy enough to get started, a basic command of the syntax and familiarity with simple use cases will only get you so far. The trickery that you can apply to writing highly efficient and refined logic is mind-blowingly complex. As a result, producing elegant, efficient, and scalable set-based solutions in SQL can be very challenging.

Join expert Ami Levin to explore advanced topics in SQL. Through a series of hands-on exercises, you'll explore and learn how to use proximal and linear interpolations to improve performance, readability, and scalability of your SQL queries. Along the way, Ami demonstrates methodologies and techniques that you can later use to solve any other SQL challenge. These newly acquired tools will not only make your everyday programming tasks easier and more fun but also impress your colleagues and managers.

What you'll learn-and how you can apply it

By the end of this live, online course, you’ll understand:

  • How to systematically approach solving tough challenges in SQL
  • How query processing works for windowed functions
  • Linear and proximal interpolations basic concepts and how and when to use them

And you’ll be able to:

  • Solve linear interpolation and similar challenges using SQL
  • Approach other challenging SQL tasks with a proven, easy methodology
  • Improve performance, readability, and scalability of your SQL queries

This training course is for you because...

  • You're an experienced SQL developer who wants to take your skills to a new level.
  • You're often stumped with challenges that seem too hard to solve in SQL.

Prerequisites

  • At least two years of SQL programming experience
  • A basic understanding of query processing

Recommended preparation:

SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, Fourth edition (book)

About your instructor

  • Ami Levin is a senior instructor, data tier architect, data modeler, database designer and SQL developer with over 20 years of experience.

Schedule

The timeframes are only estimates and may vary according to how the class is progressing

Introduction, challenge description, and background (20 minutes)

  • Lecture: What are curve fittings and interpolations and why we should use them; the various types of interpolations; the schema and data used in the training
  • Hands-on exercise: Create an interpolations schema and data

Solution methodology (20 minutes)

  • Lecture: How to approach tough SQL challenges; using auxiliary data tables; writing down expected result sets; constructing the initial row set; figuring out the missing data columns; using views and CTEs to simplify solutions

Break (10 minutes)

Proximal interpolations (55 minutes)

  • Hands-on exercise: Solve the proximal interpolation challenge
  • Lecture: Solution walkthrough—using window functions efficiently; finding the last or next non-null value; realizing your grouping expressions; putting it all together; testing and performance considerations

Break (10 minutes)

Linear interpolations (55 minutes)

  • Hands-on exercise: Solve the linear interpolation challenge
  • Lecture: Solution walkthrough—an alternative approach for finding the last or next non-null value; realizing your grouping expressions; generating counts and sequence positions; putting it all together; testing and performance considerations

Wrap-up and Q&A (10 minutes)