view: cm_social_union {
  sql_table_name: intelligence.dbt_prod.cm_social_union ;;

#  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,
#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 ;;
# }

  dimension: account_id {
    type: string
    hidden: yes
    sql: ${TABLE}.account_id;;
  }

  dimension: target {
    type: string
    hidden: yes
    sql: ${TABLE}.target;;
  }

  dimension: type {
    type: string
    hidden: yes
    sql: ${TABLE}.type;;
  }

  dimension: target_id {
    type: number
    hidden: yes
    sql: ${TABLE}.target_id;;
  }

  dimension_group: chart {
      type: time
      hidden: no
      sql: ${TABLE}.timestp ;;
      timeframes: [
        date,
        week,
        month,
        quarter,
        year
      ]
      view_label: "Date"
    }

  measure: ig_posts {
    label: "Posts"
    type: max
    hidden: no
    sql: ${TABLE}.ig_posts;;
    view_label: "Instagram"
  }

  measure: ig_followers {
    label: "Followers"
    type: max
    hidden: no
    sql: zeroifnull(${TABLE}.ig_followers);;
    view_label: "Instagram"
  }

  measure: ig_follower_growth {
    label: "IG Follower Growth"
    type: percent_of_previous
    sql: ${ig_followers} ;;
    view_label: "Instagram"
  }

  measure: ig_avg_likes {
    label: "Average Likes"
    type: max
    hidden: no
    sql: ${TABLE}.ig_avg_likes;;
    view_label: "Instagram"
  }

  measure: ig_avg_comments {
    label: "Average Comments"
    type: max
    hidden: no
    sql: ${TABLE}.ig_avg_comments;;
    view_label: "Instagram"
  }

  measure: ig_avg_views {
    label: "Average Views"
    type: max
    hidden: no
    sql: ${TABLE}.ig_avg_views;;
    view_label: "Instagram"
  }

  measure: fb_likes {
    label: "Likes"
    type: max
    hidden: no
    sql: zeroifnull(${TABLE}.fb_likes);;
    view_label: "Facebook"
  }

  measure: fb_talks {
    label: "Talks"
    type: max
    hidden: no
    sql: ${TABLE}.fb_talks;;
    view_label: "Facebook"
  }

  measure: tw_followers {
    label: "Followers"
    type: max
    hidden: no
    sql: zeroifnull(${TABLE}.tw_followers);;
    view_label: "Twitter"
  }

  measure: tw_follower_growth {
    label: "Follower Growth"
    type: percent_of_previous
    sql: ${tw_followers} ;;
    view_label: "Twitter"
  }

  measure: tw_retweets {
    label: "Retweets"
    type: max
    hidden: no
    sql: ${TABLE}.tw_retweets;;
    view_label: "Twitter"
  }

  measure: tw_favorites {
    label: "Favorites"
    type: max
    hidden: no
    sql: ${TABLE}.tw_favorites;;
    view_label: "Twitter"
  }

  measure: tw_friends {
    label: "Friends"
    type: max
    hidden: no
    sql: ${TABLE}.tw_friends;;
    view_label: "Twitter"
  }

  measure: tw_listed {
    label: "Listed"
    type: max
    hidden: no
    sql: ${TABLE}.tw_listed;;
    view_label: "Twitter"
  }

  measure: yt_views {
    label: "Views"
    type: max
    hidden: no
    sql: ${TABLE}.yt_views;;
    view_label: "YouTube"
  }

  measure: yt_videos {
    label: "Videos"
    type: max
    hidden: no
    sql: ${TABLE}.yt_videos;;
    view_label: "YouTube"
  }

  measure: yt_subs {
    label: "Subscribers"
    type: max
    hidden: no
    sql: zeroifnull(${TABLE}.yt_subs);;
    view_label: "YouTube"
  }

  measure: yt_sub_growth {
    label: "Subscriber Growth"
    type: percent_of_previous
    sql: ${yt_subs} ;;
    view_label: "YouTube"
  }

  measure: yt_comments {
    label: "Comments"
    type: max
    hidden: no
    sql: ${TABLE}.yt_comments;;
    view_label: "YouTube"
  }

  measure: w_views {
    label: "Views"
    type: max
    hidden: no
    sql: ${TABLE}.w_views;;
    view_label: "Wikipedia"
  }

  measure: w_view_growth {
    label: "Wiki View Growth"
    type: percent_of_previous
    sql: ${w_views} ;;
    view_label: "Wikipedia"
  }

  measure: tt_likes {
    label: "Likes"
    type: max
    hidden: no
    sql: ${TABLE}.tt_likes;;
    view_label: "TikTok"
  }

  measure: tt_followers {
    label: "Followers"
    type: max
    hidden: no
    sql: zeroifnull(${TABLE}.tt_followers);;
    view_label: "TikTok"
  }

  measure: tt_videos {
    label: "Videos"
    type: max
    hidden: no
    sql: ${TABLE}.tt_videos;;
    view_label: "TikTok"
  }
}