SQL
Analyze Daily or Monthly User Activity Trends
Track and visualize user engagement or record creation by grouping data by specific date parts (day, month, year) for effective trend analysis.
SELECT
DATE(created_at) AS activity_day, -- Use DATE_TRUNC('day', created_at) for PostgreSQL, or CONVERT(date, created_at) for SQL Server
COUNT(DISTINCT user_id) AS distinct_users_count
FROM user_activities
WHERE created_at >= '2023-01-01' AND created_at < '2023-02-01'
GROUP BY activity_day
ORDER BY activity_day;
How it works: This query analyzes activity trends by grouping records by day. It extracts the date part from the `created_at` timestamp (using `DATE()` for MySQL, adapt for other databases) and counts the distinct users for each day within a specified date range. This is useful for monitoring daily engagement or creation rates.