Advanced Queries

-- JOINs
SELECT
    u.username,
    u.email,
    COUNT(p.id) AS post_count,
    MAX(p.created_at) AS last_post
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.username, u.email
ORDER BY post_count DESC;

-- Window functions
SELECT
    username,
    created_at,
    COUNT(*) OVER (PARTITION BY DATE_TRUNC('month', created_at)) AS monthly_signups,
    ROW_NUMBER() OVER (ORDER BY created_at) AS signup_rank,
    LAG(created_at) OVER (ORDER BY created_at) AS previous_signup
FROM users
ORDER BY created_at;

-- CTE
WITH active_users AS (
    SELECT id, username, email
    FROM users
    WHERE is_active = TRUE
),
user_stats AS (
    SELECT
        user_id,
        COUNT(*) AS post_count,
        SUM(views) AS total_views
    FROM posts
    WHERE published = TRUE
    GROUP BY user_id
)
SELECT
    au.username,
    au.email,
    COALESCE(us.post_count, 0) AS posts,
    COALESCE(us.total_views, 0) AS views
FROM active_users au
LEFT JOIN user_stats us ON au.id = us.user_id
ORDER BY views DESC
LIMIT 10;

-- JSONB queries
SELECT id, metadata->>'browser' AS browser
FROM posts
WHERE metadata @> '{"platform": "mobile"}';

-- Array operations
SELECT * FROM posts
WHERE 'postgresql' = ANY(tags);

SELECT * FROM posts
WHERE tags @> ARRAY['postgresql', 'tutorial'];

-- Full-text search
SELECT title, ts_rank(to_tsvector('english', title || ' ' || content),
       query) AS rank
FROM posts, plainto_tsquery('english', 'postgresql tutorial') query
WHERE to_tsvector('english', title || ' ' || content) @@ query
ORDER BY rank DESC;