Question: User Engagement Metrics
"Write a query that calculates total likes, total comments, and total shares for each user across all their posts."

Step-by-Step Explanation:

You have a table (or tables) that record actions on posts.
Assume one table called post_actions with:

• user_id (who owns the post)
• post_id
• action_type (either 'like', 'comment', or 'share')
• action_time (timestamp when the action happened)

You need to:

1. Calculate:
   • Total likes a user's posts received.
   • Total comments a user's posts received.
   • Total shares a user's posts received.
2. Group the results per user.

Sample Data (post_actions):

Step 1: Focus on action_type

Each row tells you whether the action was a like, comment, or share.

We need to count how many times each action type happened for each user.

Step 2: Group by user_id

We want the final numbers per user.

Step 3: Use Conditional Counting

We can use SUM(CASE WHEN action_type = 'like' THEN 1 ELSE 0 END) to count likes, and similarly for comments and shares.

SQL Query:

SELECT
  user_id,
  SUM(CASE WHEN action_type = 'like' THEN 1 ELSE 0 END) AS total_likes,
  SUM(CASE WHEN action_type = 'comment' THEN 1 ELSE 0 END) AS total_comments,
  SUM(CASE WHEN action_type = 'share' THEN 1 ELSE 0 END) AS total_shares
FROM
  post_actions
GROUP BY
  user_id;

Explanation of SQL:

• CASE WHEN action_type = 'like' THEN 1 ELSE 0 END:
  • For each row, give 1 if it's a like, otherwise 0.
• SUM(...):
  • Add up all the 1's to count total likes.
• Same logic applies for comments and shares.
• GROUP BY user_id groups the results by each user.

Expected Output:

Important Points:

• Always use CASE inside SUM() for conditional counting.
• If a user has no shares, the total_shares will be 0 automatically.
• Make sure you group by user_id, not by post_id.