-- ==========================================
-- SQL Query Cheatsheet
-- ==========================================

-- 1. Basic JOIN with aggregation
SELECT
    u.username,
    COUNT(o.id) AS order_count,
    COALESCE(SUM(o.total), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at >= '2024-01-01'
GROUP BY u.id, u.username
HAVING COUNT(o.id) > 0
ORDER BY total_spent DESC
LIMIT 10;

-- 2. Common Table Expression (CTE)
WITH monthly_sales AS (
    SELECT
        DATE_FORMAT(created_at, '%Y-%m') AS month,
        SUM(total) AS revenue,
        COUNT(*) AS orders
    FROM orders
    WHERE status = 'completed'
    GROUP BY DATE_FORMAT(created_at, '%Y-%m')
)
SELECT
    month,
    revenue,
    orders,
    revenue / orders AS avg_order_value,
    SUM(revenue) OVER (ORDER BY month) AS running_total
FROM monthly_sales
ORDER BY month;

-- 3. Window function: rank users by activity
SELECT
    user_id,
    username,
    post_count,
    RANK() OVER (ORDER BY post_count DESC) AS activity_rank,
    NTILE(4) OVER (ORDER BY post_count DESC) AS quartile
FROM (
    SELECT
        u.id AS user_id,
        u.username,
        COUNT(p.id) AS post_count
    FROM users u
    LEFT JOIN posts p ON u.id = p.author_id
    GROUP BY u.id, u.username
) user_activity;

-- 4. UPSERT (INSERT ... ON DUPLICATE KEY UPDATE)
INSERT INTO settings (user_id, setting_key, setting_value, updated_at)
VALUES (1, 'theme', 'dark', NOW())
ON DUPLICATE KEY UPDATE
    setting_value = VALUES(setting_value),
    updated_at = NOW();