Task
StreamLine, a social media platform, wants a daily engagement report for its content team. Write a SQL query to calculate the daily engagement rate using likes, shares, and comments from a single table of post metrics.
Requirements
- Group results by
post_date
- Calculate total daily engagements as
likes + shares + comments
- Calculate daily engagement rate as
total_engagements / impressions * 100
- Exclude rows where
post_date is NULL
- Return
post_date, total_likes, total_shares, total_comments, total_impressions, and engagement_rate
- Order the result by
post_date ascending
Table Definition
post_metrics
| Column | Type | Description |
|---|
| id | INT | Unique row identifier |
| post_date | DATE | Date the post was published |
| likes | INT | Number of likes for the post |
| shares | INT | Number of shares for the post |
| comments | INT | Number of comments for the post |
| impressions | INT | Number of impressions for the post |
| post_type | VARCHAR(20) | Content format |
Sample Data
| id | post_date | likes | shares | comments | impressions | post_type |
|---|
| 1 | 2024-06-03 | 40 | 10 | 5 | 500 | video |
| 2 | 2024-06-01 | 25 | 5 | 3 | 400 | image |
| 3 | 2024-06-02 | 30 | 8 | 4 | 450 | carousel |
| 4 | 2024-06-01 | 15 | 2 | 1 | 200 | text |
| 5 | 2024-06-03 | 20 | 4 | 2 | 300 | image |
Expected Output
| post_date | total_likes | total_shares | total_comments | total_impressions | engagement_rate |
|---|
| 2024-06-01 | 40 | 7 | 4 | 600 | 8.50 |
| 2024-06-02 | 35 | 9 | 4 | 500 | 9.60 |
| 2024-06-03 | 60 | 14 | 7 | 800 | 10.13 |
