#3 Advanced SQL Series: Time-Series Analytics with Window Functions

Summary

After detecting streaks and sequences, the next powerful SQL skill is analyzing trends over time using window functions like LAG, LEAD, RANK, and cumulative aggregates.

These patterns are extremely common in:

• product analytics

• growth analysis

• revenue monitoring

• SQL interviews

Let’s walk through a few real analytical problems.

---

Dataset

Products

Sales

Here Quantity → number of units sold and Price → selling price of each unit

---

Problem 1: Find Products With Continuous Year-Over-Year Growth

Business question

Which products have increasing sales every year?

First calculate yearly revenue.

SELECT
    product_id,
    year,
    quantity * price AS yearly_sales
FROM sales;

Now compare with the previous year using LAG.

SELECT
    product_id,
    year,
    yearly_sales,
    yearly_sales - LAG(yearly_sales)
        OVER(PARTITION BY product_id ORDER BY year) AS diff
FROM (
    SELECT product_id, year, quantity*price AS yearly_sales
    FROM sales
) t;

If the minimum difference is positive, the product shows continuous growth.

---

Problem 2: Find Products With No Sales

This is a classic LEFT JOIN anti-join pattern.

SELECT p.product_name
FROM products p
LEFT JOIN sales s
ON p.product_id = s.product_id
WHERE s.product_id IS NULL;

---

Problem 3: Detect Sales Decline Between Years

Business question

Which products had lower sales in 2012 compared to 2011?

SELECT p.product_name
FROM sales s2012
JOIN sales s2011
ON s2012.product_id = s2011.product_id
JOIN products p
ON p.product_id = s2012.product_id
WHERE s2012.year = 2012
AND s2011.year = 2011
AND s2012.quantity < s2011.quantity;

---

Problem 4: Top Selling Product Each Year

This problem introduces ranking window functions.

SELECT *
FROM (
    SELECT
        p.product_name,
        s.year,
        s.quantity,
        RANK() OVER(
            PARTITION BY year
            ORDER BY quantity DESC
        ) AS rank1
    FROM sales s
    JOIN products p
    ON s.product_id = p.product_id
) t
WHERE rank1 = 1;

---

Problem 5: Total Sales Per Product

SELECT
    p.product_name,
    COALESCE(SUM(s.quantity * s.price),0) AS total_sales
FROM products p
LEFT JOIN sales s
ON p.product_id = s.product_id
GROUP BY p.product_name;

---

🚀 Bonus SQL Challenge

Now let’s try a slightly trickier analytical question.

Problem

Find the products where the quantity sold in a given year is greater than the average quantity of that product across all years.

For example:

• If a product’s average quantity sold is 15,

• return the years where quantity > 15

You should return:

product_name
year
quantity

---

💡 Hint

You will likely need:

• AVG() as a window function

• PARTITION BY product_id

---

Your Turn

Instead of posting the solution here, I’d love to see how you approach it.

👇 Drop your SQL solution in the comments.

Bonus points if you solve it using:

• window functions

• subqueries

• or both!

---

If this post helped you learn something new about advanced SQL patterns, feel free to share it with someone preparing for data or analytics interviews.

📘 You can explore system design case studies, along with an interview-ready template and 12 real-world use cases, in this System Design for Data Engineers book.

• 👉 Subscribe now to get upcoming parts of the Advanced SQL Series, system design deep dives, and exclusive frameworks straight to your inbox.

  Think better. Design better. Query smarter.