Финальный проект — SQL-дашборд менеджера социальной сети

Первый блок дашборда — сводные цифры: сколько пользователей, лайков, просмотров и каков средний score постов.

SELECT
    (SELECT COUNT(*) FROM users)         AS total_users,
    (SELECT COUNT(*) FROM communities)    AS total_communities,
    (SELECT COUNT(*) FROM posts)          AS total_posts,
    (SELECT COUNT(*) FROM likes)          AS total_likes,
    (SELECT COUNT(*) FROM views)          AS total_views,
    (SELECT ROUND(AVG(score), 1) FROM posts) AS avg_score;

Какие публикации вызвали наибольший отклик аудитории? Объединяем posts и likes.

SELECT
    p.title,
    c.name              AS community,
    p.score,
    COUNT(l.id)         AS likes_count
FROM   posts p
JOIN   communities c ON c.id = p.community_id
LEFT JOIN likes l    ON l.post_id = p.id
GROUP BY p.id, p.title, c.name, p.score
ORDER BY likes_count DESC, p.score DESC
LIMIT 5;

Самые активные пользователи — те, кто поставил больше всего лайков. Считаем и добавляем их город.

SELECT
    u.name,
    u.city,
    COUNT(l.id)  AS likes_count
FROM   users u
LEFT JOIN likes l ON l.user_id = u.id
GROUP BY u.id, u.name, u.city
ORDER BY likes_count DESC
LIMIT 5;

Для каждого сообщества: рейтинг, количество постов, средний score постов, количество лайков и просмотров.

SELECT
    c.name,
    c.rating,
    COUNT(DISTINCT p.id)   AS posts_count,
    ROUND(AVG(p.score), 1) AS avg_score,
    COUNT(DISTINCT l.id)   AS likes_count,
    COUNT(DISTINCT v.id)   AS views_count
FROM      communities c
LEFT JOIN posts p ON p.community_id = c.id
LEFT JOIN likes l ON l.post_id = p.id
LEFT JOIN views v ON v.community_id = c.id
GROUP BY c.id, c.name, c.rating
ORDER BY likes_count DESC;

«Новый» пользователь — тот, кто поставил первый лайк в данном месяце. Смотрим, как растёт аудитория.

WITH first_like AS (
    SELECT
        user_id,
        MIN(liked_at)                            AS first_at,
        DATE_TRUNC('month', MIN(liked_at))::date AS cohort_month
    FROM   likes
    GROUP BY user_id
)
SELECT
    cohort_month,
    COUNT(*) AS new_users
FROM   first_like
GROUP BY cohort_month
ORDER BY cohort_month;

7 пользователей сделали первый лайк в январе 2026, ещё 2 впервые проявили активность в феврале.

Оконные функции (RANK, DENSE_RANK, ROW_NUMBER) позволяют ранжировать строки внутри групп, не теряя детализацию.

-- Ранг пользователей по лайкам внутри каждого города
SELECT
    u.name,
    u.city,
    COUNT(l.id)                                     AS likes_count,
    RANK()       OVER (PARTITION BY u.city ORDER BY COUNT(l.id) DESC) AS rnk,
    DENSE_RANK() OVER (PARTITION BY u.city ORDER BY COUNT(l.id) DESC) AS dense_rnk
FROM   users u
LEFT JOIN likes l ON l.user_id = u.id
GROUP BY u.id, u.name, u.city
ORDER BY u.city, rnk;

-- Накопительный итог лайков по датам
SELECT
    liked_at,
    COUNT(*)                              AS daily_likes,
    SUM(COUNT(*)) OVER (ORDER BY liked_at) AS cumulative_likes
FROM   likes
GROUP BY liked_at
ORDER BY liked_at;