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.
post_datelikes + shares + commentstotal_engagements / impressions * 100post_date is NULLpost_date, total_likes, total_shares, total_comments, total_impressions, and engagement_ratepost_date ascendingpost_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 |
| 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 |
| 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 |
| Column | Type | Description |
|---|---|---|
| idPK | 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 |
| 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 |
| 6 | 2024-06-02 | 5 | 1 | 0 | 50 | video |
| 7 | 12 | 3 | 1 | 100 | story | |
| 8 | 2024-06-03 | 0 | 0 | 0 | text | |
| 9 | 2024-06-02 | 0 | 0 | 0 | story | |
| 10 | 2024-06-01 | 0 | 0 | 0 | reel | |
| 11 | 2024-06-03 | 48 | 3 | 3 | 291 | ugc |
| 12 | 2024-06-06 | -1 | 9 | 2 | 306 | live |
| 13 | null | 28 | -1 | -1 | 302 | tutorial |
| 14 | 2024-06-01 | -4 | 2 | 6 | 267 | announcement |
| 15 | 2024-06-03 | -4 | 8 | 5 | -53 | podcast |
| 16 | null | null | 5 | 2 | 480 | image |
| 17 | 2024-05-29 | 9 | 5 | 0 | 559 | reel |
| 18 | 2024-06-04 | -1 | 3 | 0 | 32 | image |
| 19 | 2024-06-06 | 38 | 1 | -1 | 457 | podcast |
| 20 | 2024-06-03 | 12 | 12 | 3 | -25 | story |
| 21 | 2024-06-04 | null | -2 | 2 | 541 | behind-the-scenes |
| 22 | 2024-05-30 | 25 | 8 | 3 | 134 | text |
| 23 | null | 1 | -2 | 3 | 572 | behind-the-scenes |
| 24 | 2024-06-03 | 28 | 3 | 3 | 365 | thread |
| 25 | 2024-06-01 | 6 | 1 | null | -24 | text |
| 26 | 2024-06-02 | -5 | 7 | 1 | 580 | interview |
| post_date | total_likes | total_shares | total_comments | total_impressions | engagement_rate |
|---|---|---|---|---|---|
| 2024-05-29 | 9 | 5 | 0 | 559 | 2.50 |
| 2024-05-30 | 25 | 8 | 3 | 134 | 26.87 |
| 2024-06-01 | 42 | 10 | 10 | 843 | 7.35 |
| 2024-06-02 | 30 | 16 | 5 | 1080 | 4.72 |
| 2024-06-03 | 144 | 40 | 21 | 1378 | 14.88 |
| 2024-06-04 | -1 | 1 | 2 | 573 | 0.35 |
| 2024-06-06 | 37 | 10 | 1 | 763 | 6.29 |
