Scribd
Upload
18 views1 page

SQL Window Functions Cheat Sheet Exercises

Uploaded by

juliomproyecto
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
18 views1 page

SQL Window Functions Cheat Sheet Exercises

Uploaded by

juliomproyecto
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 1

SQL Window Functions Cheat Sheet

With Worked Examples and Exercises

Summary
Window functions compute values across sets of rows related to the current row.

Common Patterns
-- Ranking
SELECT user_id, revenue,
RANK() OVER (ORDER BY revenue DESC) AS rnk
FROM sales;

-- Running totals
SELECT date, amount,
SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) AS running_total
FROM tx;

Exercises
1) Given (user_id, date, purchases), compute a 7-day rolling sum.
2) Tag the top 3 categories by revenue per month.
3) Find each user's time since previous login.

Solutions (Sketches)
-- 1) 7-day rolling sum (by day)
SELECT date, user_id, purchases,
SUM(purchases) OVER (PARTITION BY user_id ORDER BY date
RANGE BETWEEN INTERVAL '6' DAY PRECEDING AND
CURRENT ROW) AS roll_7d
FROM daily_user_purchases;

You might also like