Skip to content
Tutorials Center
My learning

Topics

Lesson 116 of 125 ยท Projects

Project: Social Media Engagement Analytics

Analyze a social network with SQL. Avoid the join fan-out trap, compute engagement rates, rank creators, track hashtag trends and chart follower growth and active users.

Every social app lives on a few numbers: how many people interact with a post, which creators are worth promoting, what topics are rising, and whether the network is still growing. The community team asks for them every Monday, and the answers come from five tables of simple events.

In this project you work with the social sample database: 30 users, 220 posts, 616 likes, 130 comments, 259 follow relationships and 316 hashtag tags from January to June 2025. You will compute engagement per post and per creator, rank creators fairly, find rising and fading hashtags, chart follower growth, and count monthly active users. Along the way you'll meet the most common bug in analytics SQL, the join fan-out, and fix it.

You'll use joins, aggregation, CTEs, ranking window functions, running totals and UNION.

The tables

Table One row is...
users an account (handle, country, join date)
posts something a user posted, with a kind of photo, video or text
post_tags one hashtag on one post (a post can have several)
likes one user liking one post (the pair is the primary key, so no double likes)
comments one comment on a post
follows follower_id follows followee_id since followed_on

The follower count of a creator is the number of follows rows where they are the followee_id. It is not stored anywhere; you always derive it. The first thing to find out is whether the data has the shape you think it has:

SQL Size of each table and posts by kind ยท database: social

Press Run (or Ctrl + Enter) to see the result here.

Photos are the most common post (113 of 220, 51.4%), then videos (68) and text (39).

Stage 1: the fan-out trap

The natural way to count likes and comments per post is to join both tables to posts and count. It looks right and gives wrong numbers. Run it and compare the naive counts with the true ones:

SQL Joining two child tables at once ยท database: social

Press Run (or Ctrl + Enter) to see the result here.

Post 167 really has 11 likes and 5 comments, yet the naive counts both say 55. A join pairs every like with every comment of the same post, so 11 likes and 5 comments make 11 x 5 = 55 rows, and COUNT(*) counts the pairs. That multiplication is the fan-out. It can silently inflate sums too, where COUNT(DISTINCT ...) is no help.

The reliable fix is to aggregate each child table separately, then join the results, so each post appears once on each side:

SQL
WITH like_counts AS (
  SELECT post_id, COUNT(*) AS n FROM likes GROUP BY post_id
),
comment_counts AS (
  SELECT post_id, COUNT(*) AS n FROM comments GROUP BY post_id
)
SELECT p.id,
       COALESCE(lc.n, 0) AS likes,
       COALESCE(cc.n, 0) AS comments
FROM posts AS p
LEFT JOIN like_counts AS lc ON lc.post_id = p.id
LEFT JOIN comment_counts AS cc ON cc.post_id = p.id;

COALESCE turns the NULL of a post with no likes into 0. The rest of the project builds on this pattern.

Whenever a query joins two "many" tables to one parent, stop and ask whether rows will multiply. If the numbers look suspiciously round or large, they probably did.

Stage 2: engagement rate

A creator with 10,000 followers who gets 100 likes is doing worse than one with 50 followers who gets 40. Raw counts reward popularity; engagement rate corrects for audience size: interactions (likes plus comments) per follower. We compute it per post, then average by post kind to see which format works best.

SQL Engagement by post kind ยท database: social

Press Run (or Ctrl + Enter) to see the result here.

Videos win on every measure: 3.21 likes and 0.82 comments per post, an average engagement rate of 46.5%, against 34.0% for photos and 30.1% for text. The inner join to followers drops posts by users nobody follows (three posts here), because dividing by zero followers has no meaning. The * 1.0 forces decimal division; without it SQLite would divide integers and round everything to 0 or 1.

Engagement rates above 100% are possible when non-followers interact (a post goes viral). Here the rate is high because the network is tiny: with 20 followers, 6 interactions is 30%.

Stage 3: ranking creators fairly

Which creators should the community team feature? Ranking by engagement rate alone puts accounts with two followers on top, because one like is already 50%. A sensible report sets minimum thresholds so the numbers are meaningful: here, at least 8 posts and at least 10 followers.

SQL Top creators with minimum thresholds ยท database: social

Press Run (or Ctrl + Enter) to see the result here.

Two things to notice. The WHERE f.n >= 10 filters before grouping (a fact about the follower count), while HAVING COUNT(*) >= 8 filters after (a fact about the group). And the RANK() window function runs after GROUP BY, which is why it can order by aggregates like SUM(...). See Ranking for how RANK handles ties. The top six all sit between 27% and 29%, so the ranking is close, and the follow-up question for a human is whether 10 posts is enough evidence.

Trending topics are the ones whose share changes. First the monthly top three per tag with RANK, collapsed into one readable line per month with group_concat:

SQL Top three hashtags each month ยท database: social

Press Run (or Ctrl + Enter) to see the result here.

#fitness leads January and February, #gaming leads April, and #travel takes over in May and June with 15 posts each month. April lists four tags because RANK gives ties the same rank: #books and #music both had 6 posts and share third place. Now compare the first two months with the last two to see which tags are rising and which are fading:

SQL Rising and fading hashtags ยท database: social

Press Run (or Ctrl + Enter) to see the result here.

#travel nearly quadrupled (8 posts to 30, +22) while #fitness collapsed (27 to 8, -19) and #tech nearly vanished. This is a classic New Year's resolution curve for fitness and a summer curve for travel. A product team would use it to adjust what the app promotes by season. Counting tags per month with CASE inside SUM is a conditional aggregate, a pattern you'll use constantly.

Stage 5: follower growth

The follows table is an event log with a date, so the network's size over time is a running total of new follows. The window function adds up the monthly counts, and a scalar subquery supplies the starting point, the follows that existed before 2025:

SQL Monthly follower growth ยท database: social

Press Run (or Ctrl + Enter) to see the result here.

The network began 2025 with 45 follow links and ended June with 259. Monthly additions rose from about 29 in January and February to 42, 44 and 39 in the second quarter, so growth is speeding up. The second query shows who is gaining followers fastest: marco.lab gained 15 of its 25 followers since April. A creator whose growth is mostly recent is an emerging account, which is a different story from one with the same follower count built slowly.

One more relationship metric uses a self join. A follow is mutual when two users follow each other:

SQL Mutual follows ยท database: social

Press Run (or Ctrl + Enter) to see the result here.

The WHERE a.follower_id < a.followee_id keeps one row per pair instead of two (A-B and B-A). There are 53 mutual pairs, which means 106 of the 259 follow links (40.9%) are part of a friendship that goes both ways. The rest are one-way, "fan" style follows.

Stage 6: monthly active users

Is the community engaged? Monthly active users (MAU) counts the distinct users who did anything that month: posted, liked or commented. Three different tables record those actions, so UNION them into one list of (user, month) events first:

SQL Monthly active users ยท database: social

Press Run (or Ctrl + Enter) to see the result here.

UNION (without ALL) removes duplicate rows, so each user counts once per month however much they did; COUNT(*) over the deduplicated rows is the number of active users. In March, May and June all 30 accounts were active, and the quietest month was January with 26 (86.7%). The WHERE month <= '2025-06' ignores a few likes that arrived in early July on late-June posts, so the report does not show a partial month. High MAU on a small dataset isn't a surprise; what matters is the method, which works unchanged on millions of rows.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why does COUNT(*) report 55 for a post with 11 likes and 5 comments when both tables are joined to posts?

    Joining two one-to-many tables to one parent multiplies the rows per parent (a fan-out). Aggregate each child table separately in a CTE, then join the results.
  2. 2. Why is engagement rate a better ranking measure than raw like counts?

    Raw counts favor large audiences. Interactions per follower measures how strongly the audience responds. Add minimum thresholds so tiny accounts do not distort the ranking.
  3. 3. What is the difference between WHERE f.n >= 10 and HAVING COUNT(*) >= 8 in the creators query?

    WHERE decides which rows enter the groups; HAVING can use aggregate results such as COUNT(*) because it runs after GROUP BY.
  4. 4. Why does the active-users query use UNION instead of UNION ALL?

    A user who posted ten times in March produces ten identical (user, month) rows with UNION ALL. UNION collapses them, so the later COUNT(*) counts people, not actions.

Summary

  • Never join two one-to-many tables to the same parent in one query: aggregate each child table in its own CTE first, then join, or the counts multiply.
  • Engagement rate (interactions per follower) is fairer than raw counts; guard against zero followers and set minimum thresholds before ranking.
  • WHERE filters rows before grouping, HAVING filters groups, and window functions such as RANK() run after both.
  • Conditional aggregates (SUM(CASE WHEN ... THEN ... END)) compare periods side by side, which shows rising and fading trends.
  • A running total with SUM() OVER (ORDER BY ...) turns an event log such as follows into growth over time.
  • UNION merges events from several tables and deduplicates them, which is exactly what "active users" needs.

Next: Project: Healthcare Data and KPIs, where the focus moves to rates, averages and readmissions.