Question: User Activity Trends
"Group user activities (logins, file uploads) weekly. Plot user engagement trends across the last 6 months."

Step-by-Step Explanation:

You have a table called user_activity with:

• user_id
• activity_type (like 'login', 'file_upload', etc.)
• activity_time (timestamp of the activity)

You need to:

1. Group user activities by week.
2. Separate by activity type (logins, uploads).
3. Plot trends over the last 6 months.

Sample Data (user_activity):

Assume today's date is 2024-04-20.
So last 6 months = from 2023-10-20 to 2024-04-20.

Step 1: Filter Data for the Last 6 Months

We only want activity after 2023-10-20.

Use WHERE activity_time >= DATE_SUB(CURRENT_DATE(), INTERVAL 6 MONTH).

Step 2: Extract the Week and Year

Use:

• YEAR(activity_time)
• WEEK(activity_time)
  to group activities week by week.

Step 3: Count Activities by Week and Activity Type

Group by:

• YEAR(activity_time)
• WEEK(activity_time)
• activity_type

Use COUNT(*) to get the number of activities per type per week.

SQL Query:

SELECT
  YEAR(activity_time) AS activity_year,
  WEEK(activity_time) AS activity_week,
  activity_type,
  COUNT(*) AS activity_count
FROM
  user_activity
WHERE
  activity_time >= DATE_SUB(CURRENT_DATE(), INTERVAL 6 MONTH)
GROUP BY
  activity_year,
  activity_week,
  activity_type
ORDER BY
  activity_year,
  activity_week,
  activity_type;

Explanation of SQL:

• YEAR(activity_time), WEEK(activity_time):
  Groups activities by week of the year.
• COUNT(*):
  Counts number of activities of each type in each week.
• WHERE activity_time >= DATE_SUB(CURRENT_DATE(), INTERVAL 6 MONTH):
  • Filters only the last 6 months.
• ORDER BY activity_year, activity_week:
  • So results are in time order.

Expected Output:

Step 4: Plotting the Trends (Simple Instructions):

You can export the SQL result to a tool like:

• Excel
• Google Sheets
• Tableau
• Power BI

And plot:

• X-axis = Week (you can combine year-week into a label like "2024-W03")
• Y-axis = Activity count
• Series (color) = Activity type (logins vs file_uploads)

This way, you can easily see if logins are going up or down over time, and how uploads are trending too.

Important Points:

• WEEK(activity_time) may vary: some databases start weeks on Sunday, others on Monday.
• For clean charts, you might concatenate YEAR and WEEK into one field like '2024-W03'.
• If you want to group by "starting Monday date" of the week, a more advanced calculation would be needed.