Task
A digital marketing agency needs to analyze user activity across multiple platforms (web and mobile) to ensure data accuracy. Write a SQL query to calculate the total activity count for each user, along with their last activity date, while ensuring that data from both platforms is accurately represented.
Requirements
- Join the
users, web_activity, and mobile_activity tables.
- Sum the total activities from both platforms for each user.
- Identify the last activity date for each user.
- Return results ordered by total activity count in descending order.
Schema
users (id, name)
web_activity (user_id, activity_date)
mobile_activity (user_id, activity_date)
| users | web_activity | mobile_activity |
|---|
| id | user_id | user_id |
| ------- | -------------- | ----------------- |
| INT | INT | INT |
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 3 | 3 | NULL |
Expected Output
| user_id | total_activity | last_activity_date |
|---|
| 1 | 5 | 2024-01-05 |
| 2 | 3 | 2024-01-03 |
| 3 | 1 | 2024-01-01 |
1
