LinkedIn Data Analyst Interview Questions and Answers 2026

WITH direct AS (
    -- All direct connections of user 12345
    SELECT user_id_2 AS friend_id
    FROM connections
    WHERE user_id_1 = 12345
),
second_degree_raw AS (
    -- Friends of direct connections
    SELECT c.user_id_2 AS candidate_id
    FROM connections c
    JOIN direct d ON c.user_id_1 = d.friend_id
    WHERE c.user_id_2 != 12345
      AND c.user_id_2 NOT IN (SELECT friend_id FROM direct)
),
mutual_count AS (
    -- Count mutual connections for each candidate
    SELECT
        s.candidate_id,
        COUNT(DISTINCT d.friend_id) AS mutual_connections
    FROM second_degree_raw s
    JOIN direct d
        ON d.friend_id IN (
            SELECT user_id_2
            FROM connections
            WHERE user_id_1 = s.candidate_id
        )
    GROUP BY s.candidate_id
)
SELECT
    candidate_id,
    mutual_connections
FROM mutual_count
ORDER BY mutual_connections DESC;

WITH interaction_counts AS (
    SELECT
        post_id,
        SUM(CASE WHEN action_type = 'like'    THEN 1 ELSE 0 END) AS likes,
        SUM(CASE WHEN action_type = 'comment' THEN 1 ELSE 0 END) AS comments,
        SUM(CASE WHEN action_type = 'share'   THEN 1 ELSE 0 END) AS shares,
        SUM(CASE WHEN action_type = 'skip'    THEN 1 ELSE 0 END) AS skips
    FROM interactions
    GROUP BY post_id
),
post_scores AS (
    SELECT
        p.post_id,
        p.author_id,
        p.post_type,
        p.created_at,
        ic.likes,
        ic.comments,
        ic.shares,
        ic.skips,
        EXTRACT(EPOCH FROM (NOW() - p.created_at)) / 3600.0 AS hours_since_posted,
        (
            ic.likes    * 3
          + ic.comments * 5
          + ic.shares   * 7
          - ic.skips    * 2
        ) / SQRT(
            GREATEST(EXTRACT(EPOCH FROM (NOW() - p.created_at)) / 3600.0, 1)
        ) AS ranking_score
    FROM posts p
    LEFT JOIN interaction_counts ic ON p.post_id = ic.post_id
    WHERE p.created_at >= NOW() - INTERVAL '7 days'
)
SELECT
    post_id,
    author_id,
    post_type,
    ranking_score,
    RANK() OVER (
        PARTITION BY post_type
        ORDER BY ranking_score DESC
    ) AS rank_within_type
FROM post_scores
ORDER BY post_type, rank_within_type;

-- (a) View-to-apply rate by job category
WITH view_counts AS (
    SELECT
        j.job_category,
        COUNT(DISTINCT jv.user_id || '-' || jv.job_id) AS total_views
    FROM job_views jv
    JOIN jobs j ON jv.job_id = j.job_id
    GROUP BY j.job_category
),
apply_counts AS (
    SELECT
        j.job_category,
        COUNT(DISTINCT a.app_id) AS total_applications
    FROM applications a
    JOIN jobs j ON a.job_id = j.job_id
    GROUP BY j.job_category
),
-- (b) Application-to-shortlist rate by company size
shortlist_by_size AS (
    SELECT
        j.company_size,
        COUNT(DISTINCT a.app_id)                                          AS total_apps,
        COUNT(DISTINCT CASE WHEN a.status = 'shortlisted' THEN a.app_id END) AS shortlisted
    FROM applications a
    JOIN jobs j ON a.job_id = j.job_id
    GROUP BY j.company_size
),
-- (c) Median days from application to rejection
rejection_times AS (
    SELECT
        a.app_id,
        EXTRACT(DAY FROM (r.rejected_at - a.applied_at)) AS days_to_rejection
    FROM applications a
    JOIN (
        SELECT app_id, MAX(applied_at) AS rejected_at
        FROM applications
        WHERE status = 'rejected'
        GROUP BY app_id
    ) r ON a.app_id = r.app_id
    WHERE a.status = 'rejected'
)
-- (a) view-to-apply, filtered for high performers
SELECT
    v.job_category,
    v.total_views,
    COALESCE(ap.total_applications, 0)                             AS total_applications,
    ROUND(100.0 * COALESCE(ap.total_applications, 0) / NULLIF(v.total_views, 0), 2) AS apply_rate_pct
FROM view_counts v
LEFT JOIN apply_counts ap ON v.job_category = ap.job_category
ORDER BY apply_rate_pct DESC;

-- Shortlist rate by company size
SELECT
    company_size,
    total_apps,
    shortlisted,
    ROUND(100.0 * shortlisted / NULLIF(total_apps, 0), 2) AS shortlist_rate_pct
FROM shortlist_by_size
ORDER BY shortlist_rate_pct DESC;

-- Median time to rejection
SELECT
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY days_to_rejection) AS median_days_to_rejection
FROM rejection_times;

-- (a) Acceptance rate by mutual friends bucket
WITH bucketed AS (
    SELECT
        user_id,
        recommended_user_id,
        accepted,
        mutual_friends_count,
        CASE
            WHEN mutual_friends_count = 0       THEN '0 mutuals'
            WHEN mutual_friends_count BETWEEN 1 AND 2 THEN '1-2 mutuals'
            WHEN mutual_friends_count BETWEEN 3 AND 5 THEN '3-5 mutuals'
            ELSE '6+ mutuals'
        END AS mutual_bucket
    FROM recommendations
)
SELECT
    mutual_bucket,
    COUNT(*)                                              AS total_recommendations,
    SUM(CASE WHEN accepted = TRUE THEN 1 ELSE 0 END)     AS accepted_count,
    ROUND(100.0 * SUM(CASE WHEN accepted = TRUE THEN 1 ELSE 0 END)
          / NULLIF(COUNT(*), 0), 2)                       AS acceptance_rate_pct
FROM bucketed
GROUP BY mutual_bucket
ORDER BY MIN(mutual_friends_count);

-- (b) Time-to-accept for accepted recommendations
SELECT
    user_id,
    AVG(EXTRACT(DAY FROM (accepted_date - recommendation_date))) AS avg_days_to_accept,
    PERCENTILE_CONT(0.5) WITHIN GROUP (
        ORDER BY EXTRACT(DAY FROM (accepted_date - recommendation_date))
    )                                                             AS median_days_to_accept
FROM recommendations
WHERE accepted = TRUE
GROUP BY user_id
ORDER BY avg_days_to_accept;

-- (c) Users with 10+ recommendations and 0 acceptances
SELECT
    user_id,
    COUNT(*)                                              AS total_recommendations,
    SUM(CASE WHEN accepted = TRUE THEN 1 ELSE 0 END)     AS total_accepted
FROM recommendations
GROUP BY user_id
HAVING COUNT(*) >= 10
   AND SUM(CASE WHEN accepted = TRUE THEN 1 ELSE 0 END) = 0
ORDER BY total_recommendations DESC;

WITH weekly_posts AS (
    SELECT
        post_id,
        creator_id,
        content_type,
        published_at,
        DATE_TRUNC('week', published_at) AS post_week
    FROM posts
),
interaction_stats AS (
    SELECT
        p.creator_id,
        p.post_week,
        COUNT(DISTINCT CASE WHEN i.action_type = 'view' THEN i.interaction_id END)  AS impressions,
        COUNT(DISTINCT CASE WHEN i.action_type != 'view' THEN i.interaction_id END) AS total_engagements,
        COUNT(DISTINCT CASE WHEN i.action_type = 'share' THEN i.interaction_id END) AS shares,
        COUNT(DISTINCT i.interaction_id)                                              AS total_interactions,
        AVG(CASE WHEN i.dwell_time_seconds > 0 THEN i.dwell_time_seconds END)        AS avg_dwell_time
    FROM weekly_posts p
    JOIN interactions i ON p.post_id = i.post_id
    GROUP BY p.creator_id, p.post_week
),
follow_attribution AS (
    SELECT
        p.creator_id,
        p.post_week,
        COUNT(DISTINCT f.follower_id) AS new_follows_48h
    FROM weekly_posts p
    JOIN follows f
        ON f.creator_id = p.creator_id
        AND f.followed_at BETWEEN p.published_at
                              AND p.published_at + INTERVAL '48 hours'
    GROUP BY p.creator_id, p.post_week
)
SELECT
    ist.creator_id,
    ist.post_week,
    ist.impressions,
    ROUND(100.0 * ist.total_engagements / NULLIF(ist.impressions, 0), 2)            AS engagement_rate_pct,
    ROUND(1.0 * ist.shares / NULLIF(ist.total_interactions, 0), 4)                  AS viral_coefficient,
    ROUND(ist.avg_dwell_time, 1)                                                     AS avg_dwell_time_seconds,
    COALESCE(fa.new_follows_48h, 0)                                                  AS new_follows_48h,
    RANK() OVER (
        PARTITION BY ist.post_week
        ORDER BY ist.impressions DESC
    )                                                                                AS impressions_rank
FROM interaction_stats ist
LEFT JOIN follow_attribution fa
    ON ist.creator_id = fa.creator_id
    AND ist.post_week = fa.post_week
ORDER BY ist.post_week DESC, impressions_rank;