# View: cm_social_union

**View Name:** cm_social_union
**Table Source:** `intelligence.dbt_prod.cm_social_union`
**File Path:** `cm_social_union.view.lkml`

## Overview

- **File Size:** 8019 bytes
- **Lines of Code:** 305
- **Dimensions:** 4
- **Measures:** 24
- **Dimension Groups:** 1
- **Filters:** 0

## Comments & Notes

- derived_table:  {
- sql:with social_union as (select s.account_id, timestp, u.target, u.target_id, u.type,
- posts as ig_posts, followers as ig_followers, avg_likes as ig_avg_likes, avg_comments as ig_avg_comments, avg_views as ig_avg_views,

## Dimensions

| Name | Type |
|------|------|
| `account_id` | string |
| `target` | string |
| `type` | string |
| `target_id` | number |

## Measures

| Name | Type |
|------|------|
| `ig_posts` | max |
| `ig_followers` | max |
| `ig_follower_growth` | percent_of_previous |
| `ig_avg_likes` | max |
| `ig_avg_comments` | max |
| `ig_avg_views` | max |
| `fb_likes` | max |
| `fb_talks` | max |
| `tw_followers` | max |
| `tw_follower_growth` | percent_of_previous |
| `tw_retweets` | max |
| `tw_favorites` | max |
| `tw_friends` | max |
| `tw_listed` | max |
| `yt_views` | max |
| `yt_videos` | max |
| `yt_subs` | max |
| `yt_sub_growth` | percent_of_previous |
| `yt_comments` | max |
| `w_views` | max |
| `w_view_growth` | percent_of_previous |
| `tt_likes` | max |
| `tt_followers` | max |
| `tt_videos` | max |

## Dimension Groups

| Name | Type |
|------|------|
| `chart` | time |

## Derived Table

```sql
#    sql:with social_union as (select s.account_id, timestp, u.target, u.target_id, u.type,
#posts as ig_posts, followers as ig_followers, avg_likes as ig_avg_likes, avg_comments as ig_avg_comments, avg_views as ig_avg_views,
#NULL as fb_likes, NULL as fb_talks,
#NULL as tw_followers, NULL as tw_retweets, NULL as tw_favorites, NULL as tw_friends, NULL as tw_listed,
#NULL as yt_views, NULL as yt_videos, NULL as yt_subs, NULL as yt_comments,
#NULL as w_views, null as tt_followers, null as tt_likes, null as tt_videos
#from CHARTMETRIC.RAW_DATA.INSTAGRAM_STAT s
#inner join CHARTMETRIC.RAW_DATA.CM_URL u on u.account_id = s.account_id
#where u.type = 2
#
#union all
#
#select s.account_id, timestp, u.target, u.target_id, u.type,
#NULL as ig_posts, NULL as ig_followers, NULL as ig_avg_likes, NULL as ig_avg_comments, NULL as ig_avg_views,
#likes as fb_likes, talks as fb_talks,
#NULL as tw_followers, NULL as tw_retweets, NULL as tw_favorites, NULL as tw_friends, NULL as tw_listed,
#NULL as yt_views, NULL as yt_videos, NULL as yt_subs, NULL as yt_comments,
#NULL as w_views, null as tt_followers, null as tt_likes, null as tt_videos
#from CHARTMETRIC.RAW_DATA.FACEBOOK_STAT s
#inner join CHARTMETRIC.RAW_DATA.CM_URL u on u.account_id = s.account_id
#where u.type = 0
#
#union all
#
#select s.account_id, timestp,  u.target, u.target_id, u.type,
#NULL as ig_posts, NULL as ig_followers, NULL as ig_avg_likes, NULL as ig_avg_comments, NULL as ig_avg_views,
#NULL as fb_likes, NULL as fb_talks,
#followers as tw_followers, propagated_retweets as tw_retweets, favorites as tw_favorites, friends as tw_friends, listed as tw_listed,
#NULL as yt_views, NULL as yt_videos, NULL as yt_subs, NULL as yt_comments,
#NULL as w_views, null as tt_followers, null as tt_likes, null as tt_videos
#from CHARTMETRIC.RAW_DATA.TWITTER_STAT s
#inner join CHARTMETRIC.RAW_DATA.CM_URL u on u.account_id = s.account_id
#where u.type = 1
#
#union all
#
#select s.account_id, timestp, u.target, u.target_id, u.type,
#NULL as ig_posts, NULL as ig_followers, NULL as ig_avg_likes, NULL as ig_avg_comments, NULL as ig_avg_views,
#NULL as fb_likes, NULL as fb_talks,
#NULL as tw_followers, NULL as tw_retweets, NULL as tw_favorites, NULL as tw_friends, NULL as tw_listed,
#s.views as yt_views, videos as yt_videos, subscribers as yt_subs, comments as yt_comments,
#NULL as w_views, null as tt_followers, null as tt_likes, null as tt_videos
#from CHARTMETRIC.RAW_DATA.YOUTUBE_CHANNEL_STAT s
#inner join CHARTMETRIC.RAW_DATA.CM_URL u on u.account_id = s.account_id
#where u.type = 3
#
#union all
#
#select s.account_id, timestp, u.target, u.target_id, u.type,
#NULL as ig_posts, NULL as ig_followers, NULL as ig_avg_likes, NULL as ig_avg_comments, NULL as ig_avg_views,
#NULL as fb_likes, NULL as fb_talks,
#NULL as tw_followers, NULL as tw_retweets, NULL as tw_favorites, NULL as tw_friends, NULL as tw_listed,
#NULL as yt_views, NULL as yt_videos, NULL as yt_subs, NULL as yt_comments,
#s.views as w_views, null as tt_followers, null as tt_likes, null as tt_videos
#from CHARTMETRIC.RAW_DATA.WIKIPEDIA_STAT s
#inner join CHARTMETRIC.RAW_DATA.CM_URL u on u.account_id = s.account_id
#where u.type = 4
#
#union all
#
#select t.user_id as account_id, timestp, u.target, u.target_id, u.type,
#NULL as ig_posts, NULL as ig_followers, NULL as ig_avg_likes, NULL as ig_avg_comments, NULL as ig_avg_views,
#NULL as fb_likes, NULL as fb_talks,
#NULL as tw_followers, NULL as tw_retweets, NULL as tw_favorites, NULL as tw_friends, NULL as tw_listed,
#tu.followers as tt_followers, tu.likes as tt_likes, tu.videos as tt_videos
#NULL as yt_views, NULL as yt_videos, NULL as yt_subs, NULL as yt_comments,null as w_views,
#from CHARTMETRIC.RAW_DATA.TIKTOK_USER t
#left join CHARTMETRIC.RAW_DATA.TIKTOK_USER_STAT tu on t.id = tu.tiktok_user
#inner join CHARTMETRIC.RAW_DATA.CM_URL u on u.account_id = t.user_id
#where u.type = 19)
#
#select *
#from social_union ;;
#
```