function() OVER (
    [PARTITION BY col1, col2]
    [ORDER BY col3]
    [ROWS/RANGE frame_spec]
)

SELECT 
    t.Name,
    g.Name AS genre,
    t.Milliseconds,
    ROW_NUMBER() OVER (PARTITION BY g.Name ORDER BY t.Milliseconds DESC) AS row_num,
    RANK() OVER (PARTITION BY g.Name ORDER BY t.Milliseconds DESC) AS rank_gap,
    DENSE_RANK() OVER (PARTITION BY g.Name ORDER BY t.Milliseconds DESC) AS dense_rank
FROM Track t
JOIN Genre g ON t.GenreId = g.GenreId
WHERE g.Name = 'Rock'
LIMIT 10;

-- Monthly revenue with running total
SELECT 
    strftime('%Y-%m', InvoiceDate) AS month,
    SUM(Total) AS monthly_revenue,
    SUM(SUM(Total)) OVER (ORDER BY strftime('%Y-%m')) AS running_total
FROM Invoice
GROUP BY month
ORDER BY month;

SELECT 
    month,
    monthly_revenue,
    AVG(monthly_revenue) OVER (
        ORDER BY month 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3m
FROM (
    SELECT strftime('%Y-%m') AS month, SUM(Total) AS monthly_revenue
    FROM Invoice
    GROUP BY month
) sub;

-- Next purchase date for each customer
SELECT 
    CustomerId,
    InvoiceDate,
    LEAD(InvoiceDate) OVER (PARTITION BY CustomerId ORDER BY InvoiceDate) AS next_purchase,
    JULIANDAY(LEAD(InvoiceDate) OVER (PARTITION BY CustomerId ORDER BY InvoiceDate)) 
        - JULIANDAY(InvoiceDate) AS days_to_next
FROM Invoice
ORDER BY CustomerId, InvoiceDate;

-- First and last purchase per customer
SELECT DISTINCT
    CustomerId,
    FIRST_VALUE(InvoiceDate) OVER (PARTITION BY CustomerId ORDER BY InvoiceDate) AS first_purchase,
    LAST_VALUE(InvoiceDate) OVER (
        PARTITION BY CustomerId 
        ORDER BY InvoiceDate 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_purchase
FROM Invoice;

-- 3-month trailing average
AVG(revenue) OVER (
    ORDER BY month
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)

WITH first_purchase AS (
    SELECT 
        CustomerId,
        MIN(InvoiceDate) AS cohort_date
    FROM Invoice
    GROUP BY CustomerId
),
monthly_spend AS (
    SELECT 
        c.CustomerId,
        strftime('%Y-%m', i.InvoiceDate) AS invoice_month,
        strftime('%Y-%m', fp.cohort_date) AS cohort_month,
        SUM(i.Total) AS spend
    FROM Invoice i
    JOIN first_purchase fp ON i.CustomerId = fp.CustomerId
    GROUP BY c.CustomerId, invoice_month, cohort_month
)
SELECT 
    cohort_month,
    invoice_month,
    COUNT(DISTINCT CustomerId) AS customers,
    SUM(spend) AS revenue,
    ROUND(AVG(spend), 2) AS arpu
FROM monthly_spend
GROUP BY cohort_month, invoice_month
ORDER BY cohort_month, invoice_month;

-- Assign session ID when gap > 30 min
WITH flagged AS (
    SELECT 
        user_id,
        event_time,
        LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS prev_time,
        CASE 
            WHEN JULIANDAY(event_time) - JULIANDAY(LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time)) > 30/1440 
            THEN 1 ELSE 0 
        END AS new_session
    FROM events
),
sessioned AS (
    SELECT *,
           SUM(new_session) OVER (PARTITION BY user_id ORDER BY event_time) AS session_id
    FROM flagged
)
SELECT user_id, session_id, MIN(event_time), MAX(event_time)
FROM sessioned
GROUP BY user_id, session_id;

-- Top 3 longest tracks per genre
WITH ranked AS (
    SELECT 
        t.*,
        g.Name AS genre,
        ROW_NUMBER() OVER (PARTITION BY g.Name ORDER BY Milliseconds DESC) AS rn
    FROM Track t
    JOIN Genre g ON t.GenreId = g.GenreId
)
SELECT TrackId, Name, genre, Milliseconds
FROM ranked
WHERE rn <= 3
ORDER BY genre, rn;

-- 01_running_revenue.sql
-- 02_top3_per_genre.sql
-- 03_customer_retention_cohort.sql
-- 04_sessionization_example.sql
-- README.md

# SQL Window Functions Mastery

## Key Queries
- **Running Revenue**: Cumulative sales over time
- **Top 3 Tracks per Genre**: Using `ROW_NUMBER()`
- **Cohort Retention**: Monthly active users by signup cohort
- **Sessionization**: Group events into sessions

## How to Run
```sql
-- SQLite
sqlite3 chinook.db < 01_running_revenue.sql

---

## Cheat Sheet (Copy-Paste)

```sql
-- RANKING
ROW_NUMBER() OVER (PARTITION BY x ORDER BY y)
RANK() OVER (PARTITION BY x ORDER BY y DESC)
DENSE_RANK() OVER (PARTITION BY x ORDER BY y DESC)

-- AGGREGATE
SUM(col) OVER (PARTITION BY cat ORDER BY date) AS running_total
AVG(col) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS ma_3

-- LEAD/LAG
LAG(col, 1) OVER (PARTITION BY user ORDER BY time) AS prev_val
LEAD(col) OVER (PARTITION BY user ORDER BY time) AS next_val

-- FRAME
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING

SELECT 
    CustomerId,
    InvoiceDate,
    Total,
    SUM(Total) OVER (PARTITION BY CustomerId ORDER BY InvoiceDate) AS running_spend
FROM Invoice
ORDER BY CustomerId, InvoiceDate
LIMIT 10;