Database Essentials - Writing Advanced SQL Queries - Analytic Functions - Filtering

In this article, we will walk through the solution for obtaining the top 5 daily products based on revenue. We will explore how to apply filtering on the derived values using analytic functions.

Explanation for the video

[Click here to watch the video](insert YouTube link here)

Key Concepts Explanation

Order of execution of SQL

Let’s understand the order of execution for SQL queries. The order typically involves:

  1. FROM
  2. JOIN or OUTER JOIN with ON
  3. WHERE
  4. GROUP BY and HAVING
  5. SELECT
  6. ORDER BY

Then, we explore how aliases defined in the SELECT clause cannot be used in other clauses, except for ORDER BY, due to the traditional database order of execution rules.

Sub Queries

We delve into Sub Queries, which are queries embedded within other queries. These subqueries are often present in the FROM clause and require an alias. Subqueries are commonly utilized for their benefits over queries using Analytics and Windowing Functions.

Filtering - Analytic Function Results

Understanding how to filter results derived from Analytic Functions is crucial. Since Analytic Functions can only be used in the SELECT clause, filtering based on these results requires the use of subqueries. The subqueries allow us to apply filters using aliases of the Analytic Functions.

Hands-On Tasks

  1. Understand the SQL order of execution.
  2. Practice creating subqueries to enhance your query filtering.
  3. Implement subqueries with Analytic Functions to filter results effectively.

Conclusion

In conclusion, we explored the nuances of executing SQL queries, incorporating subqueries for improved filtering, and filtering Analytic Function results using subqueries effectively. We encourage you to practice these concepts hands-on to strengthen your understanding and proficiency in SQL querying techniques. Happy querying!

[Click here to watch the video](insert YouTube link here)

Watch the video tutorial here