Meta Senior Data Analyst Interview Questions and Answers 2026

-- DAU
SELECT
    event_date,
    COUNT(DISTINCT user_id) AS dau
FROM events
WHERE event_date >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY event_date;
-- WAU (rolling 7-day window)
SELECT
    event_date,
    COUNT(DISTINCT user_id) OVER (
        ORDER BY event_date
        RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
    ) AS wau
FROM (
    SELECT DISTINCT event_date, user_id FROM events
    WHERE event_date >= CURRENT_DATE - INTERVAL '90 days'
) t;
-- MAU and stickiness
WITH dau_cte AS (
    SELECT event_date, COUNT(DISTINCT user_id) AS dau
    FROM events
    WHERE event_date >= CURRENT_DATE - INTERVAL '90 days'
    GROUP BY event_date
),
mau_cte AS (
    SELECT
        DATE_TRUNC('month', event_date)::DATE AS month,
        COUNT(DISTINCT user_id) AS mau
    FROM events
    WHERE event_date >= CURRENT_DATE - INTERVAL '90 days'
    GROUP BY DATE_TRUNC('month', event_date)
)
SELECT
    d.event_date,
    d.dau,
    m.mau,
    ROUND(100.0 * d.dau / NULLIF(m.mau, 0), 1) AS stickiness_pct
FROM dau_cte d
JOIN mau_cte m
  ON DATE_TRUNC('month', d.event_date) = m.month
ORDER BY d.event_date;

WITH first_login AS (
    SELECT user_id, MIN(login_date) AS cohort_date
    FROM logins
    GROUP BY user_id
),
weekly_activity AS (
    SELECT
        l.user_id,
        DATE_TRUNC('week', f.cohort_date)::DATE    AS cohort_week,
        FLOOR(
            EXTRACT(EPOCH FROM (l.login_date - f.cohort_date))
            / 604800                                -- seconds in 7 days
        )                                           AS weeks_since_start
    FROM logins l
    JOIN first_login f ON l.user_id = f.user_id
    WHERE FLOOR(
        EXTRACT(EPOCH FROM (l.login_date - f.cohort_date)) / 604800
    ) BETWEEN 0 AND 4
),
cohort_size AS (
    SELECT cohort_week, COUNT(DISTINCT user_id) AS total
    FROM weekly_activity WHERE weeks_since_start = 0
    GROUP BY cohort_week
)
SELECT
    wa.cohort_week,
    cs.total           AS cohort_size,
    wa.weeks_since_start,
    COUNT(DISTINCT wa.user_id) AS active_users,
    ROUND(
        100.0 * COUNT(DISTINCT wa.user_id) / cs.total, 1
    ) AS retention_pct
FROM weekly_activity wa
JOIN cohort_size cs ON wa.cohort_week = cs.cohort_week
GROUP BY wa.cohort_week, cs.total, wa.weeks_since_start
ORDER BY wa.cohort_week, wa.weeks_since_start;

WITH interaction_counts AS (
    SELECT
        p.post_type,
        COUNT(i.interaction_id) AS total_interactions
    FROM posts p
    LEFT JOIN interactions i ON p.post_id = i.post_id
    WHERE p.created_at >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY p.post_type
),
impression_counts AS (
    SELECT
        p.post_type,
        SUM(im.impression_count) AS total_impressions
    FROM posts p
    LEFT JOIN impressions im ON p.post_id = im.post_id
    WHERE p.created_at >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY p.post_type
)
SELECT
    ic.post_type,
    ic.total_interactions,
    im.total_impressions,
    ROUND(
        100.0 * ic.total_interactions / NULLIF(im.total_impressions, 0), 2
    ) AS engagement_rate_pct,
    CASE
        WHEN 100.0 * ic.total_interactions / NULLIF(im.total_impressions, 0) < 2
        THEN 'Below threshold'
        ELSE 'Healthy'
    END AS status
FROM interaction_counts ic
JOIN impression_counts im ON ic.post_type = im.post_type
ORDER BY engagement_rate_pct DESC;

WITH user_scores AS (
    SELECT
        user_id,
        SUM(CASE WHEN activity = 'post'    THEN 3
                 WHEN activity = 'comment' THEN 2
                 WHEN activity = 'like'    THEN 1 END) AS activity_score
    FROM (
        SELECT user_id, 'post'    AS activity FROM posts
        WHERE created_at >= CURRENT_DATE - INTERVAL '28 days'
        UNION ALL
        SELECT user_id, type      AS activity FROM interactions
        WHERE interaction_date >= CURRENT_DATE - INTERVAL '28 days'
          AND type IN ('comment', 'like')
    ) all_activity
    GROUP BY user_id
),
ranked AS (
    SELECT
        user_id,
        activity_score,
        NTILE(10) OVER (ORDER BY activity_score DESC) AS decile,
        ROUND(
            100.0 * RANK() OVER (ORDER BY activity_score DESC)
                  / COUNT(*) OVER (), 1
        ) AS percentile_rank
    FROM user_scores
)
SELECT user_id, activity_score, percentile_rank
FROM ranked
WHERE decile = 1
ORDER BY activity_score DESC;