view: dt_fact_sales_market_share {
  sql_table_name: INTELLIGENCE.DBT_PROD.DT_FACT_SALES_MARKETSHARE ;;
 # derived_table: {
 #  sql:
#    SELECT
#        DATE_FROM_PARTS(fs.activityyear, fs.activitymonth, 1) AS activity_month,
#        fs.country_code,
#        fs.storeid,
#        fs.labelid,
#        fs.is_adsup,
#        fs.net_receipt,
#        fs.gross,
#        fs.units,
#        ms.orchard_streams,
#        ms.store_streams,
#        ms.users,
#        ms.orchard_gross_revenue_usd,
#        ms.store_revenue_usd
#    FROM (
#        SELECT
#            fs.activityyear,
#            fs.activitymonth,
#            dc.country_code,
#            fs.storeid,
#            fs.labelid,
#            CASE
#                WHEN fs.transactiontypeid IN (9,10,27,39) THEN TRUE
#                WHEN fs.transactiontypeid IN (1,14,16,17,20,29,48) THEN FALSE
#                END AS is_adsup,
#            SUM(fs.net_receipt) AS net_receipt,
#            SUM(fs.gross) AS gross,
#            SUM(fs.sales) AS units
#        FROM royalty_accounting.prod.workstation_fact_sales_unified_dbt fs
#            LEFT JOIN facts.prod.dim_country dc ON fs.countryid = dc.countryid
#        WHERE storeid IN (1,187,286,348,496,569,716)
#            AND transactiontypeid IN (9,10,27,39,1,14,16,17,20,29,48)
#        GROUP BY 1,2,3,4,5,6
#    ) fs
#        INNER JOIN (
#            SELECT
#                YEAR(activity_month544) AS activityyear,
#                MONTH(activity_month544) AS activitymonth,
#                country_code,
#                CASE store
#                    WHEN 'Amazon Prime' THEN 187
#                    WHEN 'Amazon Unlimited' THEN 716
#                    WHEN 'Apple Music' THEN 1
#                    WHEN 'Deezer' THEN 348
#                    WHEN 'Google Play' THEN 496
#                    WHEN 'Spotify' THEN 286
#                    WHEN 'Youtube Red' THEN 569
#                    END AS storeid,
#                is_adsup,
#                SUM(orchard_streams) AS orchard_streams,
#                SUM(store_streams) AS store_streams,
#                SUM(users) AS users,
#                SUM(orchard_gross_revenue_usd) AS orchard_gross_revenue_usd,
#                SUM(store_revenue_usd) AS store_revenue_usd
#            FROM facts.prod.fact_market_share
#            GROUP BY 1,2,3,4,5
#        ) ms ON fs.activityyear = ms.activityyear
#            AND fs.activitymonth = ms.activitymonth
#            AND fs.country_code = ms.country_code
#            AND fs.storeid = ms.storeid
#            AND fs.is_adsup = ms.is_adsup ;;
#  }

  dimension_group: activity_month {
    view_label: "Date"
    label: "Activity"
    description: "Resolves for 'Activity Month 544' as provided by iTunes/Apple"
    type: time
    timeframes: [month, month_name, month_num, quarter, year]
    sql: ${TABLE}.activity_month ;;
  }

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

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

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

  dimension: is_adsup {
    view_label: "Transaction Type"
    label: "Is Ad-Supported"
    type: yesno
    sql: ${TABLE}.is_adsup ;;
  }

  measure: net_receipt {
    view_label: "Accounting"
    label: "Client Net Receipt (USD)"
    type: sum
    sql: ${TABLE}.net_receipt ;;
  }

  measure: gross {
    view_label: "Accounting"
    label: "Gross Revenue (USD)"
    type: sum
    sql: ${TABLE}.gross ;;
  }

  measure: units {
    view_label: "Accounting"
    label: "Streams"
    type: sum
    sql: ${TABLE}.units ;;
  }

  measure: orchard_streams {
    view_label: "Market Share"
    label: "Streams (Orchard)"
    type: sum
    sql: ${TABLE}.orchard_streams ;;
    value_format: "[>=1000000]#,##0.0,,\" M\"; #,###"
  }

  measure: store_streams {
    view_label: "Market Share"
    label: "Streams (Storewide)"
    type: sum
    sql: ${TABLE}.store_streams ;;
    value_format: "[>=1000000]#,##0.0,,\" M\"; [>=10 OR =0]#,##0; [>=.0000001]#,##0.0000"
  }

  measure: users {
    view_label: "Market Share"
    label: "Number of Accounts (Storewide)"
    description: "Previously referred to as 'Users'"
    type: sum
    sql: TRUNC(${TABLE}.users, 0) ;;
    value_format: "#,###"
  }

  measure: orchard_gross_revenue_usd {
    view_label: "Market Share"
    label: "Revenue USD (Orchard)"
    type: sum
    sql: ${TABLE}.orchard_gross_revenue_usd ;;
    value_format: "[>=1000000]$ #,##0.0,,\" M\"; [>=10 OR =0]$ #,##0; [>=.0000001]$ #,##0.0000;"

  }

  measure: store_revenue_usd {
    view_label: "Market Share"
    label: "Revenue USD (Storewide)"
    type: sum
    sql: ${TABLE}.store_revenue_usd ;;
    value_format: "[>=1000000]$ #,##0.0,,\" M\"; [>=10 OR =0]$ #,##0; [>=.0000001]$ #,##0.0000;"
  }
}