LeetCode & HackerRank Adventures: Conquering Advanced SQL and Windows Functions for Database Mastery

Subtitle: A 10-Day Journey to SQL Excellence

THE 10-DAY CHALLENGE

This article chronicles my journey of mastering Advanced SQL and Windows Functions in just 10 days. The key to success lies in setting a target date and diligently following it. Establish specific target hours for daily practice, as the more you delve into the concepts, the better your understanding will become. Throughout this article, I share examples that serve as a stepping stone. By starting with simpler problems, you can gradually tackle more intricate challenges, gaining a deeper understanding of the concepts along the way.

You can build tables, insert values and compile your problems effectively on https://www.mycompiler.io/new/mysql . An example table and insert columns are provided in the new MySQL page.

MyCompiler for solving your MySQL problems

THE MOTIVATION

As a database enthusiast, I recognized the increasing importance of not just SQL basics but the advanced features that can unlock powerful insights from databases. The decision to embark on this 10-day challenge was fueled by a desire to deepen my SQL expertise, particularly in the realm of Windows Functions, and share the insights gained along the way.

Day 1: Introduction to SQL Essentials

You can go through readily available databases to understand WHY you’re querying the specific result? This allows comprehending how to work on particular use-case or debug if the values are missing or incorrect.

-- Retrieving all columns from the "employees" table
SELECT * FROM employees;

-- Retrieving employees with salaries greater than 50000
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > 50000;

Day 2–3: Diving Deeper into SQL

-- Retrieving employees and their departments using INNER JOIN
SELECT employees.employee_id, employees.first_name, employees.last_name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;

Learn about INNER, I, LEFT, RIGHT, FULL OUTER joins and their usage. I have re-shared a short post on these joins in my LinkedIn page.

You might also come across some functions like COALESCE. The COALESCE function in MySQL picks the first non-null value from a list of values and returns it. If all values are null, it gives back a null result.

Day 3–5: Group By and Aggregate Functions

Learning that aggregate functions clauses are specifically mentioned in HAVING clause is the most important part here. Solving specific examples this observation can be made stronger.

-- List the Products Ordered in a Period
select product_name, 
       sum(o.unit) as unit
from Products p join Orders o on p.product_id = o.product_id
where 
    order_date like '2020-02%'
group by o.product_id
having unit >= 100

This was a great example as understanding the simple difference between WHERE and HAVING clause.

Day 6–8: Advanced SQL Techniques

Explore the power of Window Functions, a feature in SQL that allows for more complex and insightful queries. Dive into key functions like ROW_NUMBER(), RANK(), DENSE_RANK(), and NTILE(), gaining a deeper understanding of how they provide valuable insights into your data.

The PARTITION BY and ORDER BY clauses are the essential components in leveraging Window Functions effectively. Understanding how these clauses help you organize and analyze your data, allows room for solving more nuanced and targeted results.

with RankedEmployees as (
    select 
        d.name as Department, 
        e.name as Employee,
        e.salary as Salary, 
        RANK() OVER ( PARTITION BY e.departmentid 
        ORDER BY e.salary DESC ) as regular_rank,
        DENSE_RANK() OVER ( PARTITION BY e.departmentid 
        ORDER BY e.salary DESC ) as dense_ranking,
        ROW_NUMBER() OVER (PARTITION BY departmentid ORDER BY salary DESC) AS RowNumber,
        NTILE(4) OVER (PARTITION BY e.departmentId ORDER BY e.salary DESC) AS SalaryQuartile
    from employee e join department d
    on e.departmentid = d.id
)
SELECT
    Department,
    Employee,
    Salary,
    regular_rank,
    dense_ranking,
    rownumber,
    SalaryQuartile
FROM RankedEmployees
WHERE dense_ranking <= 3;

This SQL query selects the department, employee, salary, and various rankings (regular_rank, dense_ranking, and rownumber) from the RankedEmployees temporary result set, filtering only the top 3 earners in each department based on dense ranking.

This was a great example to understand difference between ROW_NUMBER(), RANK(), DENSE_RANK(), and NTILE()

Day 9–11: Mastering Windows Functions

This is the simplest example I used to comprehend LEAD() and LAG().

Suppose, a table StockPrices with columns stock_date and price. To find the percentage change in stock prices from the current day to the next day using the LEAD() and LAG() function.

Lead Percentage Calculation in mycompiler.io

Lag Percentage Calculation in mycompiler.io

The key difference between the LEAD() and LAG() window functions is:

1. LEAD(): Value of column accessed from the next row within partition
2. LAG(): Value of column is accessed from previous row within partition

Now, you see the reason why Lead has NULL in last and LAG has NULL first. The former one calculates percentage in ascending order and former calculates percentage in descending order.

First and Last Value calculation on Stock Price Table

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWINGdefines the window frame. UNBOUNDED PRECEDING means the window frame starts from the first row of the partition, and UNBOUNDED FOLLOWING means it ends at the last row of the partition.

This ensures that the window frame includes all rows in the partition.

And that’s how we get the last and the first price for a stock price table.

Hopefully, these examples were useful.

Thank you for reading my article, if you have any feedbacks please reach me out on LinkedIn: https://www.linkedin.com/in/varsha-hindupur-4aaa83175/

And visit my portfolio to know more about me: https://varshahindupur09.github.io/