SQL Patterns — Animate | AI Wisdom

← Back to topic
🎞 ANIMATE · Mode 3 of 5 · +100 XP— Advanced SQL Patterns
📋Window
⚙️CTE
🔄CASE
✅UPSERT
🎯 Next: Test →
Step 1 / 4
Speed
Window Functions
SUM OVER ORDER BY = running total; AVG OVER ROWS BETWEEN = moving average💡 Analogy
Like a sports scoreboard that shows both the current score and the running total without collapsing all games.
Window functions compute across rows related to the current row without collapsing them (unlike GROUP BY). Running total: sum of all rows up to current. Moving average: average over last 7 rows.
Console Output
> row: 2024-01-07, revenue=1200
> running_total: 8450 (sum so far)
> moving_avg_7d: 942 (last 7 days avg)
> rank_in_region: 2 (second in East)
●sql_patterns.sql
Window FunctionsL1–9
1-- Window functions: running total, rank, moving average
2SELECT
3    order_date,
4    revenue,
5    SUM(revenue) OVER (ORDER BY order_date) AS running_total,
6    AVG(revenue) OVER (
7        ORDER BY order_date
8        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
9    ) AS moving_avg_7d,
10    RANK() OVER (PARTITION BY region ORDER BY revenue DESC) AS rank_in_region
11FROM daily_sales;
12 
13-- CTE: readable multi-step query
14WITH
15high_value_customers AS (
16    SELECT user_id, SUM(total) AS lifetime_value
17    FROM orders
18    WHERE created_at > NOW() - INTERVAL '1 year'
19    GROUP BY user_id
20    HAVING SUM(total) > 1000
21),
22customer_segments AS (
23    SELECT c.id, c.name, h.lifetime_value,
24           CASE WHEN h.lifetime_value > 5000 THEN 'VIP'
25                WHEN h.lifetime_value > 2000 THEN 'Gold'
26                ELSE 'Silver' END AS segment
27    FROM customers c
28    JOIN high_value_customers h ON h.user_id = c.id
29)
30SELECT segment, COUNT(*), AVG(lifetime_value)
31FROM customer_segments
32GROUP BY segment;
33 
34-- UPSERT: insert or update atomically
35INSERT INTO product_inventory (product_id, quantity, updated_at)
36VALUES ($1, $2, NOW())
37ON CONFLICT (product_id)
38DO UPDATE SET
39    quantity = product_inventory.quantity + EXCLUDED.quantity,
40    updated_at = NOW();
40 lines
Timeline — 4 Steps
Window Functions
SUM OVER ORDER BY = running total; AVG OVER ROWS BETWEEN = moving average💡 Analogy
Like a sports scoreboard that shows both the current score and the running total without collapsing all games.
Window functions compute across rows related to the current row without collapsing them (unlike GROUP BY). Running total: sum of all rows up to current. Moving average: average over last 7 rows.
Console Output
> row: 2024-01-07, revenue=1200
> running_total: 8450 (sum so far)
> moving_avg_7d: 942 (last 7 days avg)
> rank_in_region: 2 (second in East)

Was this page helpful?

Discussion

SUM OVER ORDER BY = running total; AVG OVER ROWS BETWEEN = moving average

💡 Analogy

Like a sports scoreboard that shows both the current score and the running total without collapsing all games.

Window functions compute across rows related to the current row without collapsing them (unlike GROUP BY). Running total: sum of all rows up to current. Moving average: average over last 7 rows.

Console Output

> row: 2024-01-07, revenue=1200
> running_total: 8450 (sum so far)
> moving_avg_7d: 942 (last 7 days avg)
> rank_in_region: 2 (second in East)

SUM OVER ORDER BY = running total; AVG OVER ROWS BETWEEN = moving average

💡 Analogy

Like a sports scoreboard that shows both the current score and the running total without collapsing all games.

Window functions compute across rows related to the current row without collapsing them (unlike GROUP BY). Running total: sum of all rows up to current. Moving average: average over last 7 rows.

Console Output

> row: 2024-01-07, revenue=1200
> running_total: 8450 (sum so far)
> moving_avg_7d: 942 (last 7 days avg)
> rank_in_region: 2 (second in East)