label: "Fraudulent Streaming"
connection: "snowflake_via_lookerappadmin"

include: "int_dbt_prod_spotify_monthly_fraudulent_streaming.view.lkml"
include: "orch_app_ar_releases.view.lkml"
include: "facts_prod_dim_release.view.lkml"
include: "facts_prod_dim_label.view.lkml"
include: "orch_app_ar_country.view.lkml"
include: "facts_prod_dim_country.view.lkml"
include: "int_dbt_prod_awal_tier_groups.view.lkml"
include: "facts_prod_dim_track.view.lkml"
include: "facts_prod_dim_artist.view.lkml"
include: "int_dbt_prod_apple_excluded_streams.view.lkml"
include: "int_dbt_prod_spotify_fraudulent_fact_sales_combined.view.lkml"
include: "orch_app_ar_vendor_contract.view.lkml"
include: "orch_app_ar_orchadmin_users.view.lkml"
include: "orch_app_ar_artist_info.view.lkml"




week_start_day: friday

fiscal_month_offset: -9


explore: int_dbt_prod_spotify_monthly_fraudulent_streaming {
  label: "Spotify Frauduelent Streaming Monthly Report"
  case_sensitive: no
  hidden: yes
  sql_always_where: ${int_dbt_prod_spotify_monthly_fraudulent_streaming.label_id} != 32551 ;;

  join: orch_app_ar_releases {
    view_label: "Release"
    relationship: one_to_one
    sql_on: ${int_dbt_prod_spotify_monthly_fraudulent_streaming.upc} = ${orch_app_ar_releases.upc} ;;
    fields: [
      format,
      is_active,
      project_id,
      release_date,release_week,release_month,release_year,
      sale_start_date,sale_start_week,sale_start_month,sale_start_year,
      vendor_catalog_number,
      version,
      number_of_releases
    ]
  }
  join: facts_prod_dim_release {
    view_label: "Release"
    relationship: many_to_one
    sql_on: ${int_dbt_prod_spotify_monthly_fraudulent_streaming.upc} = ${facts_prod_dim_release.upc} ;;
    fields: []
  }
  join: facts_prod_dim_artist {
    view_label: "Artist"
    relationship: many_to_one
    sql_on: ${facts_prod_dim_release.artist_id} = ${facts_prod_dim_artist.artist_id} ;;
    fields: [
      artist_name, artist_id
    ]
  }
  join: facts_prod_dim_label {
    view_label: "Label"
    relationship: many_to_one
    sql_on: ${int_dbt_prod_spotify_monthly_fraudulent_streaming.label_id} = ${facts_prod_dim_label.label_id} ;;
    fields: [
      label_name,
      number_of_labels
    ]
  }
  join: label_country {
    from: orch_app_ar_country
    view_label: "Label"
    relationship: many_to_one
    sql_on: ${facts_prod_dim_label.country_id} = ${label_country.id} ;;
    fields: [name]
  }
  join: transaction_country {
    from: facts_prod_dim_country
    view_label: "Transaction Country"
    relationship: many_to_one
    sql_on: ${int_dbt_prod_spotify_monthly_fraudulent_streaming.country_code} = ${transaction_country.country_code} ;;
    fields: [transaction_country.country_name, transaction_country.continent_id, transaction_country.continent_name, transaction_country.country_id]
  }
  join: int_dbt_prod_awal_tier_groups {
    view_label: "Orchard/AWAL Tiers"
    relationship: many_to_one
    sql_on: ${int_dbt_prod_spotify_monthly_fraudulent_streaming.label_id}= ${int_dbt_prod_awal_tier_groups.label_id};;
    fields: [brand_name,parent_company,service_tier_name, owner, is_distributor]

  }
  join: facts_prod_dim_track {
    view_label: "Track"
    relationship: many_to_one
    sql_on: ${int_dbt_prod_spotify_monthly_fraudulent_streaming.ISRC} = ${facts_prod_dim_track.isrc} and ${int_dbt_prod_spotify_monthly_fraudulent_streaming.upc} = ${facts_prod_dim_track.upc};;
    fields: [
      track_id,
      track_unique_id,
      duration
    ]
  }
}

explore: int_dbt_prod_spotify_fraudulent_fact_sales_combined {
  label: "Spotify Fraudulent Streaming(Tracks with >90% frauduelent streams)"
  case_sensitive: no
  hidden: yes

  join: orch_app_ar_vendor_contract {
    view_label: "Label Contract"
    relationship: one_to_many
    sql_on: ${int_dbt_prod_spotify_fraudulent_fact_sales_combined.label_id} = ${orch_app_ar_vendor_contract.vendor_id} ;;
    fields: []
  }

  join: closer {
    from: orch_app_ar_orchadmin_users
    view_label: "Label Contract"
    relationship: many_to_one
    sql_on: ${orch_app_ar_vendor_contract.orchrep_name} = ${closer.id} ;;
  }

  join: facts_prod_dim_release {
    view_label: "Release"
    relationship: many_to_one
    sql_on: ${int_dbt_prod_spotify_fraudulent_fact_sales_combined.upc} = ${facts_prod_dim_release.upc} ;;
    fields: []
  }
  join: facts_prod_dim_artist {
    view_label: "Artist"
    relationship: many_to_one
    sql_on: ${facts_prod_dim_release.artist_id} = ${facts_prod_dim_artist.artist_id} ;;
    fields: [
      artist_name
    ]
  }
}