queries

-- Analytics queries for a blog platform

-- Most popular posts this month
SELECT
    p.title,
    p.slug,
    COUNT(v.id) AS views,
    COUNT(DISTINCT v.visitor_id) AS unique_visitors,
    ROUND(AVG(v.read_time_seconds)::numeric, 1) AS avg_read_time
FROM posts p
JOIN page_views v ON v.post_id = p.id
WHERE v.created_at >= DATE_TRUNC('month', CURRENT_DATE)
  AND p.is_published = true
GROUP BY p.id, p.title, p.slug
ORDER BY views DESC
LIMIT 20;

-- User retention cohorts
WITH cohorts AS (
    SELECT
        DATE_TRUNC('week', u.created_at) AS cohort_week,
        u.id AS user_id,
        MIN(DATE_TRUNC('week', s.created_at)) AS first_session,
        MAX(DATE_TRUNC('week', s.created_at)) AS last_session
    FROM users u
    LEFT JOIN sessions s ON s.user_id = u.id
    GROUP BY cohort_week, u.id
)
SELECT
    cohort_week,
    COUNT(*) AS cohort_size,
    COUNT(*) FILTER (WHERE last_session >= cohort_week + INTERVAL '1 week') AS retained_1w,
    COUNT(*) FILTER (WHERE last_session >= cohort_week + INTERVAL '4 weeks') AS retained_4w
FROM cohorts
GROUP BY cohort_week
ORDER BY cohort_week DESC;