Question: Number of Comments Per User
"Using a comments table (user_id, post_id, comment_timestamp), find how many comments each user has made in the past year."

Step-by-Step Explanation:

You have a table called comments with:

• user_id
• post_id
• comment_timestamp (when the comment was made)

You need to:

1. Find out how many comments each user made.
2. Only include comments from the past year (from today).

Sample Data (comments):

Assume today's date is 2024-02-20.

Step 1: Define the Past Year

• From today's date (2024-02-20), the past year means:
  • From 2023-02-20 to 2024-02-20.

We only want comments made after 2023-02-20.

Step 2: Filter Comments within the Past Year

Only include rows where comment_timestamp >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR).

Step 3: Group Comments by User

After filtering, group by user_id to count how many comments each user made.

Use COUNT(*) for the number of comments.

SQL Query:

SELECT
  user_id,
  COUNT(*) AS number_of_comments
FROM
  comments
WHERE
  comment_timestamp >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR)
GROUP BY
  user_id;

Explanation of SQL:

• WHERE comment_timestamp >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR):
  • Filters comments from the last 365 days.
• GROUP BY user_id:
  • Groups the data by each user.
• COUNT(*):
  • Counts the number of comments each user made.

Expected Output:

Explanation:

• User 1 made 1 comment within the past year.
• User 2 made 2 comments.
• User 3's comment is from 2022, which is older than 1 year, so not counted.

Important Points:

• CURRENT_DATE() is used to get today’s date dynamically.
• DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR) subtracts exactly 1 year from today.
• Comments older than 1 year are ignored.
• Always use GROUP BY when you want counts per user.