Финальный проект — 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, p.title
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, u.name
LIMIT 5;

По два лайка у восьми пользователей из десяти — топ-5 здесь мало что говорит, это особенность маленькой учебной базы. На живых данных распределение неравномерное, и такой отчёт показывает ядро аудитории.

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

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

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

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

DATE_TRUNC('week', …) возвращает понедельник недели, поэтому первая когорта датирована 29 декабря 2025, хотя лайки в ней — 1–4 января. За первую неделю пришли 3 пользователя, за вторую — 6, за третью — 1.

Оконные функции (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, u.name;

Разница между функциями видна на Москве: три пользователя делят первое место, и RANK отдаёт Maria четвёртое — пропуская 2 и 3, а DENSE_RANK — второе, без пропусков.

-- Накопительный итог лайков по датам
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;