#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 functionPARTITION 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.





Love this series. Would love to see one that summarizes all the patterns, it's use in real life setting and frequency of pattern usage.