User Analytics Query

sql Copy Code

-- User Analytics: Cohort retention, activity metrics, and growth
-- Works with PostgreSQL 12+

-- Monthly cohort retention analysis
WITH user_cohorts AS (
    SELECT
        u.id AS user_id,
        DATE_TRUNC('month', u.created_at) AS cohort_month,
        DATE_TRUNC('month', a.activity_date) AS activity_month
    FROM users u
    LEFT JOIN user_activity a ON u.id = a.user_id
),
cohort_size AS (
    SELECT cohort_month, COUNT(DISTINCT user_id) AS total_users
    FROM user_cohorts
    GROUP BY cohort_month
),
retention AS (
    SELECT
        uc.cohort_month,
        EXTRACT(MONTH FROM AGE(uc.activity_month, uc.cohort_month)) AS month_number,
        COUNT(DISTINCT uc.user_id) AS active_users
    FROM user_cohorts uc
    WHERE uc.activity_month IS NOT NULL
    GROUP BY uc.cohort_month, month_number
)
SELECT
    TO_CHAR(r.cohort_month, 'YYYY-MM') AS cohort,
    cs.total_users,
    r.month_number,
    r.active_users,
    ROUND(100.0 * r.active_users / cs.total_users, 1) AS retention_pct
FROM retention r
JOIN cohort_size cs ON r.cohort_month = cs.cohort_month
WHERE r.month_number BETWEEN 0 AND 12
ORDER BY r.cohort_month, r.month_number;

-- Daily active users with 7-day moving average
SELECT
    activity_date,
    COUNT(DISTINCT user_id) AS dau,
    ROUND(AVG(COUNT(DISTINCT user_id)) OVER (
        ORDER BY activity_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ), 0) AS dau_7d_avg
FROM user_activity
WHERE activity_date >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY activity_date
ORDER BY activity_date;

analytics sql reporting window-functions

javascript

MongoDB Connection with Retry

bash

MongoDB Backup Script

sql

Efficient Pagination Query

sql

Full-Text Search Setup