SQL Aggregation Queries

-- Basic aggregation with window functions
SELECT 
    category,
    date,
    value,
    SUM(value) OVER (PARTITION BY category ORDER BY date) as running_total,
    AVG(value) OVER (PARTITION BY category) as category_avg,
    RANK() OVER (PARTITION BY category ORDER BY value DESC) as rank
FROM sales
WHERE date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);