name: ARTIST_PERFORMANCE_SEMANTIC_VIEW
description: This semantic view to connect artist and music product/song level performance metrics.
tables:
  - name: GLOBAL_PARTICIPANT_BRAND_MAPPING
    description: Maps global participant IDs to company brands.
    base_table:
      database: LORELAI_AGENT
      schema: DEV
      table: GLOBAL_PARTICIPANT_BRAND_MAPPING
    dimensions:
      - name: COMPANY_BRAND
        synonyms:
          - artist brand
          - participant brand
        description: The company brand the artist belongs to.
        expr: COMPANY_BRAND
        data_type: VARCHAR(16777216)
        is_enum: true
        sample_values:
          - Sony Music
          - Above Board
          - Kollective Neighbouring Rights
          - Human Re:Sources
          - Mass Appeal
          - DRM
          - Magic Star Kids
          - AWAL
          - Altafonte
          - The Orchard
          - Foundation Media
      - name: GLOBAL_PARTICIPANT_ID
        description: Global participant ID from brand mapping table.
        expr: GLOBAL_PARTICIPANT_ID
        data_type: VARCHAR(16777216)
        sample_values:
          - a3ea28f5-9412-4522-a080-3fe3e8a3880e
          - 25f83e78-a6ed-4299-a158-cc2b40da5944
          - 3d378380-53c6-420f-9a48-4a64f88e1b54
    primary_key:
      columns:
        - GLOBAL_PARTICIPANT_ID
  - name: ISRC_ACCOUNT_MAPPING
    description: Maps ISRCs to distributor accounts (vendor and subaccount level) via the product/project chain. Use this to filter by named accounts like Som Livre, KONDZILLA, etc. VENDOR_NAME is the top-level account; SUBACCOUNT_NAME is the sub-level. One ISRC may belong to multiple accounts.
    base_table:
      database: LORELAI_AGENT
      schema: DEV
      table: ISRC_ACCOUNT_MAPPING
    dimensions:
      - name: ISRC
        description: ISRC from account mapping table.
        expr: ISRC
        data_type: VARCHAR(16777216)
        sample_values:
          - QM4TX1981258
          - USA371455556
          - QMFMG2500579
      - name: SUBACCOUNT_ID
        description: Numeric ID of the subaccount. May be NULL.
        expr: SUBACCOUNT_ID
        data_type: NUMBER(38,0)
        sample_values:
          - '33806'
          - '27518'
          - '39319'
      - name: SUBACCOUNT_NAME
        synonyms:
          - sub-account
          - subaccount
          - sublabel
        description: 'The name of the sub-level account within the distributor. Examples: Som Livre Distributed Label, KONDZILLA FILMES LTDA. May be NULL if content is at vendor level only.'
        expr: SUBACCOUNT_NAME
        data_type: VARCHAR(255)
        sample_values:
          - Factotum
          - LaMúsica.fm
          - Sara Music
      - name: VENDOR_ID
        description: Numeric ID of the top-level distributor account.
        expr: VENDOR_ID
        data_type: NUMBER(38,0)
        sample_values:
          - '35167'
          - '77338'
          - '22197'
      - name: VENDOR_NAME
        synonyms:
          - account
          - client
          - distributor
          - label account
        description: 'The name of the top-level distributor account. Examples: Som Livre, KONDZILLA. Use this to filter by named accounts rather than top-level brands like Sony Music or The Orchard.'
        expr: VENDOR_NAME
        data_type: VARCHAR(256)
        sample_values:
          - RMR (RMRHasIt, LLC)
          - Nadrisk
          - Savage Trax Limited
    primary_key:
      columns:
        - ISRC
  - name: ISRC_BRAND_MAPPING
    description: The table contains records mapping International Standard Recording Codes (ISRCs) to company brands. Each record associates a specific recording with its corresponding brand.
    base_table:
      database: LORELAI_AGENT
      schema: DEV
      table: ISRC_BRAND_MAPPING
    dimensions:
      - name: COMPANY_BRAND
        synonyms:
          - brand
          - company
          - label brand
        description: 'The company brand the track belongs to. Values include: Sony Music (SME), The Orchard, AWAL, Altafonte, DRM, Foundation Media, Human Re Sources, Above Board, and others.'
        expr: COMPANY_BRAND
        data_type: VARCHAR(16777216)
        is_enum: true
        sample_values:
          - Sony Music
          - Above Board
          - Kollective Neighbouring Rights
          - Human Re:Sources
          - Mass Appeal
          - DRM
          - Magic Star Kids
          - AWAL
          - Altafonte
          - The Orchard
          - Foundation Media
      - name: ISRC
        description: ISRC from brand mapping table.
        expr: ISRC
        data_type: VARCHAR(16777216)
        sample_values:
          - US5NR1900208
          - QMDA72549717
          - QMDA72551391
    primary_key:
      columns:
        - ISRC
  - name: DATA_AVAILABILITY_BY_STORE_DAILY
    description: Lightweight table (1,680 rows) tracking data freshness per DSP store. Contains STORE_HIGH_WATERMARK (the last date with available data for each store) and STREAMING_STORES_HIGH_WATERMARK (the latest date across all major streaming stores). Use this table to determine the latest available data date instead of scanning the streaming tables.
    base_table:
      database: FACTS
      schema: PROD
      table: DATA_AVAILABILITY_BY_STORE_DAILY
    dimensions:
      - name: IS_STREAMING_STORE
        description: Flag indicating if the store is a streaming store (1) or not (null).
        expr: IS_STREAMING_STORE
        data_type: NUMBER(1,0)
        sample_values:
          - '1'
      - name: STORE_HIGH_WATERMARK
        synonyms:
          - data freshness
          - last available date
          - latest available date
          - most recent data date
        description: The most recent date for which data is available for a specific DSP store. This is a lightweight lookup — use it instead of scanning the streaming tables for MAX(DOWNLOAD_ACTIVITY_DATE).
        expr: STORE_HIGH_WATERMARK
        data_type: DATE
        sample_values:
          - '2026-04-05'
          - '2026-04-07'
          - '2026-04-08'
      - name: STOREID
        description: Store ID in the data availability table.
        expr: STOREID
        data_type: NUMBER(38,0)
        sample_values:
          - '708'
          - '213'
          - '1600'
      - name: STREAMING_STORES_HIGH_WATERMARK
        description: The latest date across all major streaming stores (Spotify and Apple Music). Represents the overall streaming data freshness.
        expr: STREAMING_STORES_HIGH_WATERMARK
        data_type: DATE
        sample_values:
          - '2026-04-08'
  - name: DIM_COUNTRY
    description: The table contains records of countries and their geographic information. Each record represents a single country and includes standardized identification codes.
    base_table:
      database: FACTS
      schema: PROD
      table: DIM_COUNTRY
    dimensions:
      - name: COUNTRY_CODE
        description: Two-letter country codes following international standards.
        expr: COUNTRY_CODE
        data_type: VARCHAR(2)
        sample_values:
          - MA
          - BJ
          - RS
          - US
          - GB
      - name: COUNTRYID
        description: Unique numeric identifier for each country in the dimension table.
        expr: COUNTRYID
        data_type: NUMBER(32,0)
        sample_values:
          - '120'
          - '4'
          - '247'
      - name: COUNTRYNAME
        description: Names of countries from around the world.
        expr: COUNTRYNAME
        data_type: VARCHAR(255)
        sample_values:
          - Serbia
          - Timor-Leste
          - Montenegro
    primary_key:
      columns:
        - COUNTRY_CODE
  - name: DIM_RELEASE
    description: 'The table contains data(id, name and release date) about music releases like song, EP, or album. '
    base_table:
      database: FACTS
      schema: PROD
      table: DIM_RELEASE
    dimensions:
      - name: COMPILATION
        synonyms:
          - compilation
          - compilation_flag
          - is_compilation
        description: Whether the release is a compilation (Y) or an original artist release (N). Use this to exclude compilations when finding an artist''s own latest album.
        expr: COMPILATION
        data_type: VARCHAR(2)
        is_enum: true
        sample_values:
          - 'N'
          - 'Y'
      - name: DAYS_SINCE_RELEASE
        description: Number of days since the album/single was released
        expr: DATEDIFF(day, RELEASEDATE, CURRENT_DATE())
        data_type: NUMBER(9,0)
      - name: PRODUCT_ID
        description: Unique identifier for a product.
        expr: PRODUCT_ID
        data_type: NUMBER(32,0)
        sample_values:
          - '1677957'
          - '1810792'
          - '5962446'
      - name: RELEASEDATE
        synonyms:
          - album_release_date
          - product_release_date
        description: Date when product like single, EP, Full Length etc was released.
        expr: RELEASEDATE
        data_type: TIMESTAMP_NTZ(3)
      - name: RELEASEID
        description: Unique release identifier
        expr: RELEASEID
        data_type: NUMBER(38,0)
        sample_values:
          - '886445773187'
          - '820200156334'
          - '820200126054'
      - name: RELEASENAME
        description: The name or title of a music release such as an album or single.
        expr: RELEASENAME
        data_type: VARCHAR(1400)
        sample_values:
          - Længe leve kærligheden
          - Man On The Moon
          - Crazy Town
      - name: TYPE_OF_PRODUCT
        synonyms:
          - album_type
          - product_format
          - release_format
          - release_type
          - format
        description: 'Product format: Single, Full Length (album), EP, etc.'
        expr: FORMAT
        data_type: VARCHAR(100)
        is_enum: true
        sample_values:
          - Single
          - EP
          - Full Length
    primary_key:
      columns:
        - PRODUCT_ID
  - name: DIM_STORE
    description: The table contains records of digital music platform(DSP like Spotify, Apple Music or Amazon Music.
    base_table:
      database: FACTS
      schema: PROD
      table: DIM_STORE
    dimensions:
      - name: STOREID
        synonyms:
          - digital service provider id
          - platform id
        description: Unique identifier for each DSP.
        expr: STOREID
        data_type: NUMBER(32,0)
        sample_values:
          - '1649'
          - '1657'
          - '1979'
      - name: STORENAME
        synonyms:
          - digital service provider name
          - dsp name
          - platform name
        description: Names of DSP platforms for music-related businesses.
        expr: STORENAME
        data_type: VARCHAR(255)
        is_enum: true
        sample_values:
          - iTunes/Apple
          - Napster
          - Amazon Music
          - Beatport Digital Download Network
          - Spotify
          - Deezer
          - YouTube
          - Google Play
          - Pandora
          - LINE Japan
          - SoundCloud
          - AWA
          - Kuwo
          - Kugou
          - QQ
    primary_key:
      columns:
        - STOREID
  - name: DOWNLOADS_BY_PARTICIPANT_PRODUCT_COUNTRY_FEED_DISTRIBUTOR_DAILY
    description: The table contains records of daily download activity tracked by participant and distributor. Each record represents download metrics for a specific date, including geographic location, feed and store identifiers, product information, and download counts.
    base_table:
      database: FACTS
      schema: PROD
      table: DOWNLOADS_BY_PARTICIPANT_PRODUCT_COUNTRY_FEED_DISTRIBUTOR_DAILY
    dimensions:
      - name: COUNTRY_CODE
        synonyms:
          - country
          - market
          - region
        description: Two-letter country codes representing the countries where downloads occurred.
        expr: COUNTRY_CODE
        data_type: VARCHAR(2)
        sample_values:
          - US
          - DE
          - TR
      - name: GLOBAL_PARTICIPANT_ID
        description: Unique identifier for participants in the global system.
        expr: GLOBAL_PARTICIPANT_ID
        data_type: VARCHAR(16777216)
        sample_values:
          - f66fec17-c8d4-4f29-81c3-a86dd62c6689
          - f670447e-bad0-4631-a968-491da864d0a3
          - 2cac7ffd-f133-4143-9d53-d5a2099c88ae
      - name: PRODUCT_ID
        description: Unique identifier for a product.
        expr: PRODUCT_ID
        data_type: NUMBER(32,0)
        sample_values:
          - '1566642'
          - '1523395'
          - '337399'
      - name: STORE_ID
        description: Unique identifier for a digital service platform(DSP) like Spotify or Apple Music.
        expr: STORE_ID
        data_type: NUMBER(32,0)
        sample_values:
          - '1'
          - '7'
          - '187'
    time_dimensions:
      - name: DOWNLOAD_ACTIVITY_DATE
        description: The date when music content related download took place.
        expr: DOWNLOAD_ACTIVITY_DATE
        data_type: DATE
        sample_values:
          - '2026-01-17'
          - '2026-01-19'
          - '2026-01-21'
    facts:
      - name: DOWNLOADS
        description: The number of downloads recorded for the specified part and distributor.
        expr: DOWNLOADS
        data_type: NUMBER(38,0)
        access_modifier: public_access
        sample_values:
          - '1'
          - '8'
          - '29'
        default_aggregation: 1
  - name: DOWNLOADS_BY_PARTICIPANT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY
    description: The table contains records of daily download activity tracked by distributor and participant. Each record represents download metrics for specific products across different countries and stores, including identifying information for participants, distributors, and the downloaded content.
    base_table:
      database: FACTS
      schema: PROD
      table: DOWNLOADS_BY_PARTICIPANT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY
    dimensions:
      - name: COUNTRY_CODE
        synonyms:
          - country
          - market
          - region
        description: Two-letter country codes representing the countries where downloads occurred.
        expr: COUNTRY_CODE
        data_type: VARCHAR(2)
        sample_values:
          - DE
          - CA
          - AU
      - name: GLOBAL_PARTICIPANT_ID
        description: Unique identifier for participants in the global system.
        expr: GLOBAL_PARTICIPANT_ID
        data_type: VARCHAR(16777216)
        sample_values:
          - 7cfe9770-a8be-40e1-94d8-8428065c1c6d
          - 4562505c-2f5a-44d2-a37c-128aee36e60d
          - 802aa82a-d338-46c4-8dc5-25a011779787
      - name: ISRC
        description: International Standard Recording Code used to uniquely identify sound recordings and music video recordings.
        expr: ISRC
        data_type: VARCHAR(200)
        sample_values:
          - USA371421452
          - INH100252590
          - USA560524762
      - name: PRODUCT_ID
        description: Unique identifier for a product.
        expr: PRODUCT_ID
        data_type: NUMBER(32,0)
        sample_values:
          - '159524'
          - '4403505'
          - '4403492'
      - name: STORE_ID
        description: Unique identifier for a digital service platform(DSP) like Spotify or Apple Music.
        expr: STORE_ID
        data_type: NUMBER(32,0)
        sample_values:
          - '7'
          - '1'
          - '187'
    time_dimensions:
      - name: DOWNLOAD_ACTIVITY_DATE
        description: The date when download activity occurred.
        expr: DOWNLOAD_ACTIVITY_DATE
        data_type: DATE
        sample_values:
          - '2021-08-30'
          - '2021-08-28'
          - '2021-08-29'
    facts:
      - name: DOWNLOADS
        description: The number of downloads recorded for the specified time period.
        expr: DOWNLOADS
        data_type: NUMBER(38,0)
        access_modifier: public_access
        sample_values:
          - '1'
          - '9'
          - '2'
        default_aggregation: 1
  - name: GLOBAL_PARTICIPANT
    description: The table contains records of music artists or performers with their associated identifier and name.
    base_table:
      database: FACTS
      schema: PROD
      table: GLOBAL_PARTICIPANT
    dimensions:
      - name: ID
        description: Unique identifier for each music artist/performer using universally unique identifier format.
        expr: ID
        data_type: VARCHAR(16777216)
        sample_values:
          - 6f6e3f1e-640e-4fb6-9ff1-42eec3817aed
          - ff0c7e4a-1cc2-43fe-b1da-c190a2ba1302
          - 3600b26b-fafe-47c7-9236-8db3efb9c9bc
      - name: NAME
        synonyms:
          - artist
          - artist_name
          - performer
        description: Names of artist/music performer
        expr: NAME
        data_type: VARCHAR(16777216)
        sample_values:
          - ☆SK!TKP23
          - Selax Kids
          - Antmaster
    primary_key:
      columns:
        - ID
  - name: GLOBAL_SOUND_RECORDING
    description: The table contains records of sound recordings(songs) from various music artists. Each record represents an individual recording and includes metadata such as release name, release date and unique identifiers.
    base_table:
      database: FACTS
      schema: PROD
      table: GLOBAL_SOUND_RECORDING
    dimensions:
      - name: ISRC
        synonyms:
          - recording code
          - track identifier
        description: International Standard Recording Code (ISRC) uniquely identifying sound recordings.
        expr: ISRC
        data_type: VARCHAR(16777216)
        sample_values:
          - QZP7F2590974
          - DEA559113746
          - DEB261200153
      - name: NAME
        synonyms:
          - track_name
        description: The name or title of the sound recording(song).
        expr: NAME
        data_type: VARCHAR(16777216)
        sample_values:
          - Social Anxiety
          - Stay Focus
          - favorite holiday
    time_dimensions:
      - name: RELEASE_DATE
        synonyms:
          - song_release_date
          - track_release_date
        description: Date when song was released.
        expr: RELEASE_DATE
        data_type: DATE
    primary_key:
      columns:
        - ISRC
  - name: REGION_COUNTRY_MAPPING
    description: The table contains records mapping countries to their respective regions. Each record associates a country with a geographic region designation.
    base_table:
      database: LORELAI_AGENT
      schema: DEV
      table: REGION_COUNTRY_MAPPING
    dimensions:
      - name: COUNTRY_CODE
        description: Two-letter country codes.
        expr: COUNTRY_CODE
        data_type: VARCHAR(2)
        sample_values:
          - VA
          - GN
          - AZ
      - name: REGION_NAME
        description: The name of the geographic region.
        expr: REGION_NAME
        data_type: VARCHAR(50)
        is_enum: true
        sample_values:
          - Africa
          - Antarctica
          - Asia
          - CIS
          - Europe
          - Latin-Iberia
          - MiddleEast
          - North America
          - Oceania
          - South America
          - GSA
          - Nordics
    primary_key:
      columns:
        - COUNTRY_CODE
  - name: V_STREAMS_BY_PARTICIPANT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY
    description: The table contains daily country based music streaming activity records for music content across different platforms. Each record captures streaming metrics and user engagement data for specific products, including various types of streaming behavior and user interactions.
    base_table:
      database: FACTS
      schema: PROD
      table: V_STREAMS_BY_PARTICIPANT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY
    dimensions:
      - name: COUNTRY_CODE
        synonyms:
          - country
          - market
          - region
        description: Two-letter country codes.
        expr: COUNTRY_CODE
        data_type: VARCHAR(2)
        sample_values:
          - MY
          - CH
          - NA
      - name: GLOBAL_PARTICIPANT_ID
        description: Unique identifier of music artist/performer
        expr: GLOBAL_PARTICIPANT_ID
        data_type: VARCHAR(16777216)
        sample_values:
          - c4cd3b7e-fba1-4154-9bf8-cbefd429ee22
          - 9ea1ae90-3ae6-44bc-8d51-287cef084325
          - 13ee84bf-33e8-419b-9ccf-9853c07bb5da
      - name: ISRC
        description: International Standard Recording Code (ISRC) for identifying sound recordings and music videos.
        expr: ISRC
        data_type: VARCHAR(16777216)
        sample_values:
          - US4DG0700028
          - DK4YA2305001
          - COF011800389
      - name: PRODUCT_ID
        description: Unique identifier for a product.
        expr: PRODUCT_ID
        data_type: NUMBER(32,0)
        sample_values:
          - '5256691'
          - '4717824'
          - '4390543'
      - name: STORE_ID
        description: Unique identifier for a digital service platform(DSP) like Spotify or Apple Music.
        expr: STORE_ID
        data_type: NUMBER(32,0)
        sample_values:
          - '1'
          - '453'
          - '286'
    time_dimensions:
      - name: DOWNLOAD_ACTIVITY_DATE
        synonyms:
          - activity_date
          - streaming_date
        description: The date when music listening related activity took place.
        expr: DOWNLOAD_ACTIVITY_DATE
        data_type: DATE
        sample_values:
          - '2025-12-04'
          - '2025-10-14'
          - '2026-01-17'
    facts:
      - name: SAVES
        synonyms:
          - saves to collections
        description: The number of times content was saved by users.
        expr: SAVES
        data_type: NUMBER(38,0)
        access_modifier: public_access
        sample_values:
          - '0'
          - '1'
          - '2'
        default_aggregation: 1
      - name: SKIPS
        description: The number of times content was skipped during streaming.
        expr: SKIPS
        data_type: NUMBER(38,0)
        access_modifier: public_access
        sample_values:
          - '1'
          - '11'
          - '3'
        default_aggregation: 1
      - name: STREAMS
        description: The number of streams recorded for the specified time period.
        expr: STREAMS
        data_type: NUMBER(38,0)
        access_modifier: public_access
        sample_values:
          - '1'
          - '34'
          - '51'
        default_aggregation: 1
      - name: STREAMS_ACTIVE
        synonyms:
          - user-initiated streams
          - active discovery streams
        description: The number of streams from active discovery for the given time period.
        expr: STREAMS_ACTIVE
        data_type: NUMBER(38,0)
        access_modifier: public_access
        sample_values:
          - '0'
          - '9'
          - '3'
        default_aggregation: 1
      - name: STREAMS_COLLECTION
        description: The number of streams from DSP collections on  a daily basis.
        expr: STREAMS_COLLECTION
        data_type: NUMBER(38,0)
        access_modifier: public_access
        sample_values:
          - '9'
          - '44'
          - '11'
        default_aggregation: 1
      - name: STREAMS_PASSIVE
        description: The number of streams from dsp using passive listening.
        expr: STREAMS_PASSIVE
        data_type: NUMBER(38,0)
        access_modifier: public_access
        sample_values:
          - '134'
          - '64'
          - '8'
        default_aggregation: 1
      - name: STREAMS_UNKNOWN
        description: Streams collected from DSP but type not known.
        expr: streams-streams_passive-streams_active-streams_collection
        data_type: NUMBER(38,0)
        access_modifier: public_access
        sample_values:
          - '0'
          - '4'
          - '12'
        default_aggregation: 1
      - name: STREAMS_WITH_SKIPS
        description: Streams that has been skipped
        expr: CASE WHEN skips IS NOT NULL THEN streams ELSE 0 END
        data_type: NUMBER(38,0)
        access_modifier: public_access
        default_aggregation: 1
    metrics:
      - name: SKIP_RATE
        description: |-
          Skip rate calculated as skips divided by total streams that have skip data available. Only meaningful when skip data 
            exists.
        expr: |-
          CASE 
                   WHEN SUM(SKIPS) > 0 
                   THEN SUM(SKIPS) / (SUM(SKIPS) + SUM(CASE WHEN SKIPS IS NOT NULL THEN STREAMS ELSE 0 END))
                   ELSE NULL 
                 END
        access_modifier: public_access
relationships:
  - name: PARTICIPANT_BRAND_TO_GLOBAL_PARTICIPANT
    left_table: GLOBAL_PARTICIPANT_BRAND_MAPPING
    right_table: GLOBAL_PARTICIPANT
    relationship_columns:
      - left_column: GLOBAL_PARTICIPANT_ID
        right_column: ID
  - name: ACCOUNT_ISRC_TO_SOUND_RECORDING
    left_table: ISRC_ACCOUNT_MAPPING
    right_table: GLOBAL_SOUND_RECORDING
    relationship_columns:
      - left_column: ISRC
        right_column: ISRC
  - name: BRAND_ISRC_TO_SOUND_RECORDING
    left_table: ISRC_BRAND_MAPPING
    right_table: GLOBAL_SOUND_RECORDING
    relationship_columns:
      - left_column: ISRC
        right_column: ISRC
  - name: DATA_AVAILABILITY_TO_DIM_STORE
    left_table: DATA_AVAILABILITY_BY_STORE_DAILY
    right_table: DIM_STORE
    relationship_columns:
      - left_column: STOREID
        right_column: STOREID
  - name: COUNTRY_DIM_TO_REGION_MAPPING
    left_table: DIM_COUNTRY
    right_table: REGION_COUNTRY_MAPPING
    relationship_columns:
      - left_column: COUNTRY_CODE
        right_column: COUNTRY_CODE
  - name: DOWNLOADS_BY_PARTICIPANT_PRODUCT_COUNTRY_FEED_TO_DIM_COUNTRY
    left_table: DOWNLOADS_BY_PARTICIPANT_PRODUCT_COUNTRY_FEED_DISTRIBUTOR_DAILY
    right_table: DIM_COUNTRY
    relationship_columns:
      - left_column: COUNTRY_CODE
        right_column: COUNTRY_CODE
  - name: DOWNLOADS_BY_PARTICIPANT_PRODUCT_COUNTRY_FEED_TO_DIM_RELEASE
    left_table: DOWNLOADS_BY_PARTICIPANT_PRODUCT_COUNTRY_FEED_DISTRIBUTOR_DAILY
    right_table: DIM_RELEASE
    relationship_columns:
      - left_column: PRODUCT_ID
        right_column: PRODUCT_ID
  - name: DOWNLOADS_BY_PARTICIPANT_PRODUCT_COUNTRY_TO_DIM_STORE
    left_table: DOWNLOADS_BY_PARTICIPANT_PRODUCT_COUNTRY_FEED_DISTRIBUTOR_DAILY
    right_table: DIM_STORE
    relationship_columns:
      - left_column: STORE_ID
        right_column: STOREID
  - name: DOWNLOADS_BY_PARTICIPANT_PRODUCT_COUNTRY_TO_GLOBAL_PARTICIPANT
    left_table: DOWNLOADS_BY_PARTICIPANT_PRODUCT_COUNTRY_FEED_DISTRIBUTOR_DAILY
    right_table: GLOBAL_PARTICIPANT
    relationship_columns:
      - left_column: GLOBAL_PARTICIPANT_ID
        right_column: ID
  - name: DOWNLOADS_BY_PARTICIPANT_TRACK_COUNTRY_FEED_TO_DIM_RELEASE
    left_table: DOWNLOADS_BY_PARTICIPANT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY
    right_table: DIM_RELEASE
    relationship_columns:
      - left_column: PRODUCT_ID
        right_column: PRODUCT_ID
  - name: DOWNLOADS_BY_PARTICIPANT_TRACK_COUNTRY_TO_DIM_COUNTRY
    left_table: DOWNLOADS_BY_PARTICIPANT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY
    right_table: DIM_COUNTRY
    relationship_columns:
      - left_column: COUNTRY_CODE
        right_column: COUNTRY_CODE
  - name: DOWNLOADS_BY_PARTICIPANT_TRACK_COUNTRY_TO_DIM_STORE
    left_table: DOWNLOADS_BY_PARTICIPANT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY
    right_table: DIM_STORE
    relationship_columns:
      - left_column: STORE_ID
        right_column: STOREID
  - name: DOWNLOADS_BY_PARTICIPANT_TRACK_COUNTRY_TO_GLOBAL_PARTICIPANT
    left_table: DOWNLOADS_BY_PARTICIPANT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY
    right_table: GLOBAL_PARTICIPANT
    relationship_columns:
      - left_column: GLOBAL_PARTICIPANT_ID
        right_column: ID
  - name: DOWNLOADS_BY_PARTICIPANT_TRACK_COUNTRY_TO_GL_SOUND_RECORDING
    left_table: DOWNLOADS_BY_PARTICIPANT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY
    right_table: GLOBAL_SOUND_RECORDING
    relationship_columns:
      - left_column: ISRC
        right_column: ISRC
  - name: V_STREAMS_BY_PARTICIPANT_TRACK_COUNTRY_TO_DIM_COUNTRY
    left_table: V_STREAMS_BY_PARTICIPANT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY
    right_table: DIM_COUNTRY
    relationship_columns:
      - left_column: COUNTRY_CODE
        right_column: COUNTRY_CODE
  - name: V_STREAMS_BY_PARTICIPANT_TRACK_COUNTRY_TO_DIM_GL_SOUND_RECORDING
    left_table: V_STREAMS_BY_PARTICIPANT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY
    right_table: GLOBAL_SOUND_RECORDING
    relationship_columns:
      - left_column: ISRC
        right_column: ISRC
  - name: V_STREAMS_BY_PARTICIPANT_TRACK_COUNTRY_TO_DIM_RELEASE
    left_table: V_STREAMS_BY_PARTICIPANT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY
    right_table: DIM_RELEASE
    relationship_columns:
      - left_column: PRODUCT_ID
        right_column: PRODUCT_ID
  - name: V_STREAMS_BY_PARTICIPANT_TRACK_COUNTRY_TO_DIM_STORE
    left_table: V_STREAMS_BY_PARTICIPANT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY
    right_table: DIM_STORE
    relationship_columns:
      - left_column: STORE_ID
        right_column: STOREID
  - name: V_STREAMS_BY_PARTICIPANT_TRACK_COUNTRY_TO_GLOBAL_PARTICIPANT
    left_table: V_STREAMS_BY_PARTICIPANT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY
    right_table: GLOBAL_PARTICIPANT
    relationship_columns:
      - left_column: GLOBAL_PARTICIPANT_ID
        right_column: ID
verified_queries:
  - name: '"What are total streams, track downloads, saves to collection and album downloads  for afd1585b-ec13-4b4f-908a-14900543335f between 10th of January and 20th of January this year."'
    sql: |-
      WITH streams_data AS (
        SELECT
          gp.name,
          SUM(s.streams) AS total_streams,
          SUM(s.saves) AS total_saves
        FROM
          v_streams_by_participant_track_country_feed_distributor_daily AS s
          LEFT OUTER JOIN global_participant AS gp ON s.global_participant_id = gp.id
        WHERE
          gp.id = 'afd1585b-ec13-4b4f-908a-14900543335f'
          AND s.download_activity_date BETWEEN '2026-01-10'
          AND '2026-01-20'
        GROUP BY
          gp.name
      ),
      track_downloads_data AS (
        SELECT
          gp.name,
          SUM(d.downloads) AS total_track_downloads
        FROM
          downloads_by_participant_track_country_feed_distributor_daily AS d
          LEFT OUTER JOIN global_participant AS gp ON d.global_participant_id = gp.id
        WHERE
          gp.id = 'afd1585b-ec13-4b4f-908a-14900543335f'
          AND d.download_activity_date BETWEEN '2026-01-10'
          AND '2026-01-20'
        GROUP BY
          gp.name
      ),
      album_downloads_data AS (
        SELECT
          gp.name,
          SUM(d.downloads) AS total_album_downloads
        FROM
          downloads_by_participant_product_country_feed_distributor_daily AS d
          LEFT OUTER JOIN global_participant AS gp ON d.global_participant_id = gp.id
        WHERE
          gp.id =  'afd1585b-ec13-4b4f-908a-14900543335f'
          AND d.download_activity_date BETWEEN '2026-01-10'
          AND '2026-01-20'
        GROUP BY
          gp.name
      )
      SELECT
        COALESCE(s.name, t.name, a.name) AS artist_name,
        COALESCE(s.total_streams, 0) AS total_streams,
        COALESCE(t.total_track_downloads, 0) AS total_track_downloads,
        COALESCE(s.total_saves, 0) AS saves_to_collection,
        COALESCE(a.total_album_downloads, 0) AS total_album_downloads
      FROM
        streams_data AS s FULL
        OUTER JOIN track_downloads_data AS t ON s.name = t.name FULL
        OUTER JOIN album_downloads_data AS a ON COALESCE(s.name, t.name) = a.name
        /* Generated by Cortex Analyst (request_id: b41c3690-c846-4bc0-b4f7-e8a4754da187) */
    question: What are total streams, track downloads, saves to collection and album downloads  for afd1585b-ec13-4b4f-908a-14900543335f between 10th of January and 20th of January this year.
    verified_at: 1776946427
    verified_by: Rain Bomberg
    use_as_onboarding_question: false
  - name: '"What is the daily streams for Bad Bunny between 10th of January and 20th of January 2026? His artist id is: e49ea9f9-0a74-4623-b3c6-ff819c7c0a4d"'
    sql: |-
      SELECT
        s.download_activity_date,
        gp.name AS artist_name,
        SUM(s.streams) AS daily_streams
      FROM
        v_streams_by_participant_track_country_feed_distributor_daily AS s
        LEFT OUTER JOIN global_participant AS gp ON s.global_participant_id = gp.id
      WHERE
        gp.id='e49ea9f9-0a74-4623-b3c6-ff819c7c0a4d'
        AND s.download_activity_date BETWEEN '2026-01-10'
        AND '2026-01-20'
      GROUP BY
        s.download_activity_date,
        gp.name
      ORDER BY
        s.download_activity_date DESC NULLS LAST
        /* Generated by Cortex Analyst (request_id: 020bb6a3-2a49-4606-aa88-7e2cd1f08330) */
    question: 'What is the daily streams for Bad Bunny between 10th of January and 20th of January 2026? His artist id is: e49ea9f9-0a74-4623-b3c6-ff819c7c0a4d'
    verified_at: 1770019107
    verified_by: Rain Bomberg
    use_as_onboarding_question: false
  - name: '"How many daily collection saves there was for artist with id e49ea9f9-0a74-4623-b3c6-ff819c7c0a4d between 15th of January and 20th of January from Apple Music?"'
    sql: |-
      SELECT
        s.download_activity_date,
        gp.name AS artist_name,
        SUM(s.saves) AS daily_collection_saves
      FROM
        v_streams_by_participant_track_country_feed_distributor_daily AS s
        LEFT OUTER JOIN global_participant AS gp ON s.global_participant_id = gp.id
      WHERE
        gp.id='e49ea9f9-0a74-4623-b3c6-ff819c7c0a4d'
        AND s.download_activity_date BETWEEN '2026-01-15'
        AND '2026-01-20'
        AND s.store_id = 1
      GROUP BY
        s.download_activity_date,
        gp.name
      ORDER BY
        s.download_activity_date DESC NULLS LAST
        /* Generated by Cortex Analyst (request_id: 1d295468-acb8-46c9-bb9e-72dd3879e1f7) */
    question: How many daily collection saves there was for artist with id e49ea9f9-0a74-4623-b3c6-ff819c7c0a4d between 15th of January and 20th of January from Apple Music?
    verified_at: 1770019265
    verified_by: Rain Bomberg
    use_as_onboarding_question: false
  - name: '"How many daily track downloads there was for artist with id afd1585b-ec13-4b4f-908a-14900543335f between 15th of January and 20th of January 2026 from Amazon Music?"'
    sql: |-
      SELECT
        d.download_activity_date,
        gp.name AS artist_name,
        SUM(d.downloads) AS daily_track_downloads
      FROM
        downloads_by_participant_track_country_feed_distributor_daily AS d
        LEFT OUTER JOIN global_participant AS gp ON d.global_participant_id = gp.id
        LEFT OUTER JOIN dim_store AS ds ON d.store_id = ds.storeid
      WHERE
        gp.id = 'afd1585b-ec13-4b4f-908a-14900543335f'
        AND d.download_activity_date BETWEEN '2026-01-15'
        AND '2026-01-20'
        AND ds.storename = 'Amazon Music'
      GROUP BY
        d.download_activity_date,
        gp.name
      ORDER BY
        d.download_activity_date DESC NULLS LAST
        /* Generated by Cortex Analyst (request_id: 22189206-3aff-4432-befb-55ff637d6d9d) */
    question: How many daily track downloads there was for artist with id afd1585b-ec13-4b4f-908a-14900543335f between 15th of January and 20th of January 2026 from Amazon Music?
    verified_at: 1776947544
    verified_by: Rain Bomberg
    use_as_onboarding_question: false
  - name: '"How many daily track downloads there was for Adele(global participant id b7f45359-976f-46fe-828b-0343562e0c84) 15th of January and 20th of January 2026 from Amazon Music and from Germany?"'
    sql: |-
      SELECT
        d.download_activity_date,
        gp.name AS artist_name,
        SUM(d.downloads) AS daily_track_downloads
      FROM
        downloads_by_participant_track_country_feed_distributor_daily AS d
        LEFT OUTER JOIN global_participant AS gp ON d.global_participant_id = gp.id
        LEFT OUTER JOIN dim_store AS ds ON d.store_id = ds.storeid
      WHERE
        gp.id = 'b7f45359-976f-46fe-828b-0343562e0c84'
        AND d.download_activity_date BETWEEN '2026-01-15'
        AND '2026-01-20'
        AND ds.storename = 'Amazon Music'
        AND d.country_code = 'DE'
      GROUP BY
        d.download_activity_date,
        gp.name
      ORDER BY
        d.download_activity_date DESC NULLS LAST
        /* Generated by Cortex Analyst (request_id: 4ce0a2c3-0f4c-44f3-bf27-8bd56934a113) */
    question: How many daily track downloads there was for Adele(global participant id b7f45359-976f-46fe-828b-0343562e0c84) 15th of January and 20th of January 2026 from Amazon Music and from Germany?
    verified_at: 1770019461
    verified_by: Rain Bomberg
    use_as_onboarding_question: false
  - name: '"I want to see daily total streams and track downloads for artist with b7f45359-976f-46fe-828b-0343562e0c84 starting from 18th of January by product. Keep only top 5 products per day. Just show 3 days."'
    sql: |-
      WITH daily_metrics AS (
        SELECT
          s.download_activity_date,
          gp.name AS artist_name,
          dr.product_id,
          dr.releasename,
          SUM(s.streams) AS total_streams
        FROM
          v_streams_by_participant_track_country_feed_distributor_daily AS s
          LEFT OUTER JOIN global_participant AS gp ON s.global_participant_id = gp.id
          INNER JOIN dim_release AS dr ON s.product_id = dr.product_id
        WHERE
          gp.id='b7f45359-976f-46fe-828b-0343562e0c84'
          AND s.download_activity_date BETWEEN '2026-01-18'
          AND '2026-01-20'
        GROUP BY
          s.download_activity_date,
          gp.name,
          dr.product_id,
          dr.releasename
      ),
      daily_downloads AS (
        SELECT
          d.download_activity_date,
          dr.product_id,
          SUM(d.downloads) AS total_track_downloads
        FROM
          downloads_by_participant_track_country_feed_distributor_daily AS d
          LEFT OUTER JOIN global_participant AS gp ON d.global_participant_id = gp.id
          LEFT OUTER JOIN dim_release AS dr ON d.product_id = dr.product_id
        WHERE
          gp.id='b7f45359-976f-46fe-828b-0343562e0c84'
          AND d.download_activity_date BETWEEN '2026-01-18'
          AND '2026-01-20'
        GROUP BY
          d.download_activity_date,
          dr.product_id
      ),
      combined_metrics AS (
        SELECT
          dm.download_activity_date,
          dm.artist_name,
          dm.product_id,
          dm.releasename,
          dm.total_streams,
          COALESCE(dd.total_track_downloads, 0) AS total_track_downloads
        FROM
          daily_metrics AS dm
          LEFT JOIN daily_downloads AS dd ON dm.download_activity_date = dd.download_activity_date
          AND dm.product_id = dd.product_id
      ),
      ranked_products AS (
        SELECT
          download_activity_date,
          artist_name,
          product_id,
          releasename,
          total_streams,
          total_track_downloads,
          RANK() OVER (
            PARTITION BY download_activity_date
            ORDER BY
              total_streams DESC NULLS LAST
          ) AS stream_rank
        FROM
          combined_metrics
      )
      SELECT
        download_activity_date,
        artist_name,
        product_id,
        releasename,
        total_streams,
        total_track_downloads
      FROM
        ranked_products
      WHERE
        stream_rank <= 5
      ORDER BY
        download_activity_date DESC NULLS LAST,
        stream_rank
        /* Generated by Cortex Analyst (request_id: e6230f37-6f5b-4023-b550-59d7a612c358) */
    question: I want to see daily total streams and track downloads for artist with b7f45359-976f-46fe-828b-0343562e0c84 starting from 18th of January by product. Keep only top 5 products per day. Just show 3 days.
    verified_at: 1770019781
    verified_by: Rain Bomberg
    use_as_onboarding_question: false
  - name: '"I want to see daily streams by source for 5fb974f1-77e6-4bf3-b089-373e018fa9e5 starting from 15th of January year. Include only data from US and I am only interested in 3 days."'
    sql: |-
      SELECT
        s.download_activity_date,
        gp.name AS artist_name,
        SUM(s.streams_active) AS streams_active,
        SUM(s.streams_passive) AS streams_passive,
        SUM(s.streams_collection) AS streams_collection,
        SUM(s.streams_unknown) AS streams_unknown
      FROM
        v_streams_by_participant_track_country_feed_distributor_daily AS s
        LEFT OUTER JOIN global_participant AS gp ON s.global_participant_id = gp.id
      WHERE
        gp.id = '5fb974f1-77e6-4bf3-b089-373e018fa9e5'
        AND s.download_activity_date BETWEEN '2026-01-15'
        AND '2026-01-17'
        AND s.country_code = 'US'
      GROUP BY
        s.download_activity_date,
        gp.name
      ORDER BY
        s.download_activity_date DESC NULLS LAST
        /* Generated by Cortex Analyst (request_id: b5442bed-c792-4860-9137-654357364611) */
    question: I want to see daily streams by source for 5fb974f1-77e6-4bf3-b089-373e018fa9e5 starting from 15th of January year. Include only data from US and I am only interested in 3 days.
    verified_at: 1776946527
    verified_by: Rain Bomberg
    use_as_onboarding_question: false
  - name: '"Give me artist''s 5fb974f1-77e6-4bf3-b089-373e018fa9e5 total streams and skip rate between 10th of January to 20th of January 2026."'
    sql: |-
      with streams as (
        SELECT
          gp.name,
          SUM(s.streams) AS streams,
          SUM(
            CASE
              WHEN s.skips IS NOT NULL THEN s.streams
              ELSE 0
            END
          ) AS streams_with_skips,
          SUM(s.skips) AS skips,
          MIN(s.download_activity_date) AS stream_start_date,
          MAX(s.download_activity_date) AS stream_end_date
        FROM
      V_STREAMS_BY_PARTICIPANT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY s
          INNER JOIN DIM_RELEASE dr ON s.product_id = dr.product_id
          inner join global_participant gp on s.global_participant_id = gp.id
        WHERE
          s.download_activity_date >= '2026-01-10'
          and s.download_activity_date <= '2026-01-20'
          AND gp.id = '5fb974f1-77e6-4bf3-b089-373e018fa9e5'
          AND store_id != 1202 -- exclude TikTok
        GROUP BY
          gp.name
      )
      SELECT
        streams.name,
        streams.streams,
        (
          CASE
            WHEN streams.skips > 0 THEN streams.skips / (streams.skips + streams.streams_with_skips)
            ELSE NULL
          END
        ) AS skip_rate,
        streams.stream_start_date,
        streams.stream_end_date
      FROM
        streams;
    question: Give me artist's 5fb974f1-77e6-4bf3-b089-373e018fa9e5 total streams and skip rate between 10th of January to 20th of January 2026.
    verified_at: 1776946571
    verified_by: Rain Bomberg
    use_as_onboarding_question: false
  - name: '"What are total streams for DON''T BE DUMB / TRIP BABY(isrc USRC12502335) song from 16th of January until 18th of January 2026."'
    sql: |-
      SELECT
            gsr.name AS song_name,
            SUM(s.streams) AS total_streams
          FROM
            v_streams_by_participant_track_country_feed_distributor_daily AS s
            LEFT OUTER JOIN global_sound_recording AS gsr ON s.isrc = gsr.isrc
          WHERE
            s.isrc = 'USRC12502335'
            AND s.download_activity_date BETWEEN '2026-01-16' AND '2026-01-18'
          GROUP BY
            gsr.name
    question: What are total streams for DON'T BE DUMB / TRIP BABY(isrc USRC12502335) song from 16th of January until 18th of January 2026.
    verified_at: 1776946896
    verified_by: Rain Bomberg
    use_as_onboarding_question: false
  - name: '"What is the release date of the song ''DON''T BE DUMB / TRIP BABY''/ USRC12502335?"'
    sql: |-
      SELECT
            gsr.name AS song_name,
            gsr.isrc,
            gsr.release_date
          FROM
            global_sound_recording AS gsr
          WHERE
            gsr.isrc = 'USRC12502335'
    question: What is the release date of the song 'DON'T BE DUMB / TRIP BABY'/ USRC12502335?
    verified_at: 1776947379
    verified_by: Rain Bomberg
    use_as_onboarding_question: false
  - name: '"What were A$AP Rocky''s(global participant id=afd1585b-ec13-4b4f-908a-14900543335f) top 3 streaming weeks in 2025?"'
    sql: |-
      SELECT
        DATE_TRUNC('WEEK', s.download_activity_date) AS week_start_date,
        gp.name AS artist_name,
        SUM(s.streams) AS total_streams
      FROM
        v_streams_by_participant_track_country_feed_distributor_daily AS s
        LEFT OUTER JOIN global_participant AS gp ON s.global_participant_id = gp.id
      WHERE
        gp.id = 'afd1585b-ec13-4b4f-908a-14900543335f'
        AND YEAR(s.download_activity_date) = 2025
      GROUP BY
        1,
        2
      ORDER BY
        total_streams DESC
      LIMIT
        3
    question: What were A$AP Rocky's(global participant id=afd1585b-ec13-4b4f-908a-14900543335f) top 3 streaming weeks in 2025?
    verified_at: 1777014214
    verified_by: Rain Bomberg
    use_as_onboarding_question: false
  - name: '"List me top 5 songs by their streams from ASAP Rocky(afd1585b-ec13-4b4f-908a-14900543335f) from past 4 days"'
    sql: |-
      SELECT
        gsr.name AS song_name,
        SUM(s.streams) AS total_streams
      FROM
        v_streams_by_participant_track_country_feed_distributor_daily AS s
        LEFT OUTER JOIN global_participant AS gp ON s.global_participant_id = gp.id
        LEFT OUTER JOIN global_sound_recording AS gsr ON s.isrc = gsr.isrc
      WHERE
        gp.id = 'afd1585b-ec13-4b4f-908a-14900543335f'
        AND s.download_activity_date >= DATEADD(DAY, -4, CURRENT_DATE)
      GROUP BY
        gsr.name
      ORDER BY
        total_streams DESC NULLS LAST
      LIMIT
        5
        /* Generated by Cortex Analyst (request_id: 3c91ff93-cdcd-4702-b7ba-77a35ee45835) */
    question: List me top 5 songs by their streams from ASAP Rocky(afd1585b-ec13-4b4f-908a-14900543335f) from past 4 days
    verified_at: 1776947706
    verified_by: Rain Bomberg
    use_as_onboarding_question: false
  - name: |-
      "Ella Langley(d251e798-bfd8-45bf-b195-47a4198dbf07) and her two songs:
      Choosin Texas(USSM12504190), weren't for the wind(USSM12403681), 
      I want to see weekly streams for specified songs, grouped by week and song title. Take only this year."
    sql: |-
      WITH weekly_streams AS (
        SELECT
          DATE_TRUNC('WEEK', s.download_activity_date) AS week,
          gsr.name AS song_title,
          SUM(s.streams) AS weekly_streams
        FROM
          v_streams_by_participant_track_country_feed_distributor_daily AS s
          LEFT OUTER JOIN global_participant AS gp ON s.global_participant_id = gp.id
          LEFT OUTER JOIN global_sound_recording AS gsr ON s.isrc = gsr.isrc
        WHERE
          gp.id = 'd251e798-bfd8-45bf-b195-47a4198dbf07'
          AND gsr.isrc IN ('USSM12504190', 'USSM12403681')
          AND s.download_activity_date >= '2026-01-01'
          AND s.download_activity_date <= CURRENT_DATE()
        GROUP BY
          DATE_TRUNC('WEEK', s.download_activity_date),
          gsr.name
      )
      SELECT
        week,
        song_title,
        weekly_streams
      FROM
        weekly_streams
      ORDER BY
        week DESC NULLS LAST,
        song_title
    question: |-
      Ella Langley(d251e798-bfd8-45bf-b195-47a4198dbf07) and her two songs:
      Choosin Texas(USSM12504190), weren't for the wind(USSM12403681), 
      I want to see weekly streams for specified songs, grouped by week and song title. Take only this year.
    verified_at: 1776947757
    verified_by: Rain Bomberg
    use_as_onboarding_question: false
  - name: '"Find all Full Length albums named ''THIS MUSIC MAY CONTAIN HOPE'' for artist with GLOBAL_PARTICIPANT_ID 884cc442-2838-40f4-bfb2-1540ee260465. Show PRODUCT_ID, RELEASENAME, RELEASEDATE, and FORMAT"'
    sql: |-
      SELECT
            dr.product_id,
            dr.releasename,
            dr.releasedate,
            dr.type_of_product AS format
          FROM
            v_streams_by_participant_track_country_feed_distributor_daily AS s
            INNER JOIN dim_release AS dr ON s.product_id = dr.product_id
          WHERE
            s.global_participant_id = '884cc442-2838-40f4-bfb2-1540ee260465'
            AND dr.releasename ILIKE '%THIS MUSIC MAY CONTAIN HOPE%'
            AND dr.type_of_product = 'Full Length'
            AND dr.releasedate <= CURRENT_TIMESTAMP()
          GROUP BY
            dr.product_id,
            dr.releasename,
            dr.releasedate,
            dr.type_of_product
          ORDER BY
            dr.releasedate DESC NULLS LAST
          LIMIT 10
    question: Find all Full Length albums named 'THIS MUSIC MAY CONTAIN HOPE' for artist with GLOBAL_PARTICIPANT_ID 884cc442-2838-40f4-bfb2-1540ee260465. Show PRODUCT_ID, RELEASENAME, RELEASEDATE, and FORMAT
    verified_at: 1776161530
    verified_by: Rain Bomberg
    use_as_onboarding_question: false
  - name: '"What is the latest available streaming data date for Spotify?"'
    sql: |-
      SELECT DISTINCT
            ds.storename,
            da.store_high_watermark AS latest_data_date
          FROM
            data_availability_by_store_daily AS da
            INNER JOIN dim_store AS ds ON da.storeid = ds.storeid
          WHERE
            da.storeid = 286
            AND da.is_streaming_store = 1
    question: What is the latest available streaming data date for Spotify?
    verified_at: 1776948185
    verified_by: Rain Bomberg
    use_as_onboarding_question: false
  - name: '"What are total streams for The Orchard artists in the last 7 days?"'
    sql: |-
      SELECT
            SUM(s.streams) AS total_streams
          FROM
            v_streams_by_participant_track_country_feed_distributor_daily AS s
            INNER JOIN global_participant_brand_mapping AS bm
              ON s.global_participant_id = bm.global_participant_id
          WHERE
            bm.company_brand = 'The Orchard'
            AND s.download_activity_date >= DATEADD(DAY, -7, CURRENT_DATE())
    question: What are total streams for The Orchard artists in the last 7 days?
    verified_at: 1776948250
    verified_by: Rain Bomberg
    use_as_onboarding_question: false
  - name: |-
      "What are the top 10 most streamed songs across all artists in the last 7 days?
      "
    sql: |-
      WITH song_streams AS (
            SELECT
              s.isrc,
              SUM(s.streams) / COUNT(DISTINCT s.global_participant_id) AS adjusted_streams
            FROM
              v_streams_by_participant_track_country_feed_distributor_daily AS s
            WHERE
              s.download_activity_date >= DATEADD(DAY, -7, CURRENT_DATE())
            GROUP BY
              s.isrc
          )
          SELECT
            gsr.name AS song_name,
            ss.isrc,
            ss.adjusted_streams AS total_streams
          FROM
            song_streams AS ss
            LEFT OUTER JOIN global_sound_recording AS gsr ON ss.isrc = gsr.isrc
          ORDER BY
            ss.adjusted_streams DESC NULLS LAST
          LIMIT 10
    question: |
      What are the top 10 most streamed songs across all artists in the last 7 days?
    verified_at: 1776948524
    verified_by: Rain Bomberg
    use_as_onboarding_question: false
  - name: |-
      "Give me total streams by DSP from artist b7f45359-976f-46fe-828b-0343562e0c84 for her last released album.
      "
    sql: |-
      WITH latest_album AS (
        SELECT
          dr.product_id,
          dr.releasename,
          dr.releasedate
        FROM
          v_streams_by_participant_track_country_feed_distributor_daily AS s
          INNER JOIN dim_release AS dr ON s.product_id = dr.product_id
        WHERE
          s.global_participant_id = 'b7f45359-976f-46fe-828b-0343562e0c84'
          AND dr.type_of_product = 'Full Length'
          AND dr.compilation = 'N'
          AND dr.releasedate <= CURRENT_TIMESTAMP()
        GROUP BY
          dr.product_id,
          dr.releasename,
          dr.releasedate
        ORDER BY
          dr.releasedate DESC
        LIMIT
          1
      )
      SELECT
        (
          SELECT
            releasename
          FROM
            latest_album
        ) AS album_name,
        (
          SELECT
            releasedate
          FROM
            latest_album
        ) AS release_date,
        ds.storename AS dsp_name,
        SUM(s.streams) AS total_streams
      FROM
        v_streams_by_participant_track_country_feed_distributor_daily AS s
        INNER JOIN dim_store AS ds ON s.store_id = ds.storeid
        INNER JOIN latest_album on s.product_id = latest_album.product_id
      WHERE
        s.global_participant_id = 'b7f45359-976f-46fe-828b-0343562e0c84'
        and s.DOWNLOAD_ACTIVITY_DATE >= CURRENT_DATE() - 28
      GROUP BY
        ds.storename
      ORDER BY
        total_streams DESC
    question: |
      Give me total streams by DSP from artist b7f45359-976f-46fe-828b-0343562e0c84 for her last released album.
    verified_at: 1777014253
    verified_by: Rain Bomberg
    use_as_onboarding_question: false
module_custom_instructions:
  sql_generation: |2-
     # ID Usage
        When provided by artist name and artist id then use artist unique identifier in filter. Same for song - use
      unique identifier in filters instead of name, if provided.

        # Table Selection
        - If question is about a song/track, first try to use GLOBAL_SOUND_RECORDING.
        - If question is about releases (albums, singles, EPs), first try using DIM_RELEASE table.

        # Multi-Artist Song Handling
        IMPORTANT: The streaming and download tables contain one row per participant (artist) per song. When a question
       asks about top songs/tracks WITHOUT specifying or filtering by a particular artist name, you MUST first
      aggregate metrics to the song level (ISRC) to avoid double-counting. Use a subquery to deduplicate: first
      aggregate SUM(STREAMS) grouped by ISRC, DOWNLOAD_ACTIVITY_DATE, COUNTRY_CODE, then divide by the number of
      distinct GLOBAL_PARTICIPANT_IDs per ISRC to get the true song-level metric. For example: SUM(STREAMS) /
      COUNT(DISTINCT GLOBAL_PARTICIPANT_ID) when grouping by ISRC. Only when a specific artist name IS mentioned in the
       question should you use the participant-level data directly.

        # Default Filters (apply when user does not specify)
        - Date range: If no date/time period mentioned, filter to last 28 days: DOWNLOAD_ACTIVITY_DATE >= CURRENT_DATE() - 28
        - Results limit: If user asks for "top" without a number, use LIMIT 10
        - Country/Region: If no geography mentioned, do not add country filter (return worldwide)


    # Data Freshness - IMPORTANT
        Streaming data does NOT arrive in real-time. Use DATA_AVAILABILITY_BY_STORE_DAILY to check the latest available date per DSP.
        - To get the latest date for a specific store: SELECT DISTINCT STORE_HIGH_WATERMARK FROM DATA_AVAILABILITY_BY_STORE_DAILY WHERE STOREID = <store_id> AND IS_STREAMING_STORE = 1
        - To get the overall latest streaming date: SELECT DISTINCT STREAMING_STORES_HIGH_WATERMARK FROM DATA_AVAILABILITY_BY_STORE_DAILY WHERE IS_STREAMING_STORE = 1 LIMIT 1
        - Key store IDs: 286 = Spotify, 1 = iTunes/Apple, 187 = Amazon Music, 453 = YouTube, 348 = Deezer
        - When the user asks about "today", "yesterday", "latest", "most recent", or "how is X doing right now", FIRST query the data availability table to determine the actual latest date, use that as the date filter upper bound, and tell the user what date range the data covers.
        - NEVER use MAX(DOWNLOAD_ACTIVITY_DATE) on the streaming or download tables — they have 200+ billion rows and the query will timeout.


        # Performance Rules - CRITICAL
        1. For date ranges within the last 90 days, query
      FACTS.PROD.STREAMS_BY_PARTICIPANT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY_RECENT directly instead of
      V_STREAMS_BY_PARTICIPANT_TRACK_COUNTRY_FEED_DISTRIBUTOR_DAILY. The V_ view unions RECENT + HISTORY (200B+ rows)
      and is slow.
        2. ALWAYS include a LIMIT clause to prevent runaway queries.
        3. ALWAYS include a date filter - never query without restricting DOWNLOAD_ACTIVITY_DATE.

        # Country Code Reference
        Use ISO 3166-1 alpha-2 codes:
        - United Kingdom / UK / Britain = GB (not UK)
        - United States / USA / America = US

    # Album / Product Queries
        When the user asks about an album, EP, product, or release:
        - Use DIM_RELEASE to identify the product. Filter by RELEASENAME (use ILIKE with wildcards) and optionally FORMAT (e.g. 'Full Length' for albums, 'Single', 'EP').
        - CRITICAL: An album may have MULTIPLE PRODUCT_IDs (e.g. different regional releases or reissues). Always filter by RELEASENAME using ILIKE, NOT by a single PRODUCT_ID. This ensures all product versions are included.
        - DISAMBIGUATION: An album name like "Something Beautiful" may also match variants like "Something Beautiful (Deluxe)" or "Something Beautiful (Edits)". When ILIKE returns multiple distinct album titles:
          1. If the user specifies a variant (e.g. "Deluxe", "Edits", "Special Edition"), match that specific version.
          2. Otherwise, use the ORIGINAL release only. Add exclusion filters: AND RELEASENAME NOT ILIKE '%(Deluxe)%' AND RELEASENAME NOT ILIKE '%(Edits)%' AND RELEASENAME NOT ILIKE '%(Special%)%' AND RELEASENAME NOT ILIKE '%(Bonus%)%'
          3. Among remaining matches, use the earliest RELEASEDATE to identify the original release.
        - Additionally, songs on the album may also appear on separate single products. To match what the Insights app shows ("Smart Products"), include streams from ALL products that share the same ISRCs as the album.
        - The recommended pattern for album total streams is:
          1. First find all PRODUCT_IDs matching the album name: SELECT PRODUCT_ID FROM DIM_RELEASE WHERE RELEASENAME ILIKE '%album name%' AND FORMAT = 'Full Length'
          2. Then find all ISRCs on those products: SELECT DISTINCT ISRC FROM the streaming table WHERE PRODUCT_ID IN (album product IDs)
          3. Finally, sum ALL streams for those ISRCs regardless of which product they were streamed from
        - For "top tracks on the album", GROUP BY ISRC, join to GLOBAL_SOUND_RECORDING for track names, and filter by ISRCs that appear on the album's PRODUCT_IDs.
        - For "latest album", use ORDER BY RELEASEDATE DESC with FORMAT = 'Full Length' and LIMIT 1. Do NOT use MAX(RELEASEDATE).
        - A single song may appear on multiple products (album + single). Each product has its own PRODUCT_ID. Streams are attributed to the product the listener streamed from.

      # Brand and Account Filtering
        Users may refer to either a top-level brand or a named client/distributor account. Always query the data — NEVER infer brand or account from ISRC prefixes or any other heuristic.

        ## Top-level brands (use ISRC_BRAND_MAPPING or GLOBAL_PARTICIPANT_BRAND_MAPPING)
        When user asks about Sony Music, The Orchard, AWAL, or other top-level brands:
        - Track-level: filter using COMPANY_BRAND from ISRC_BRAND_MAPPING
        - Artist-level: filter using COMPANY_BRAND from GLOBAL_PARTICIPANT_BRAND_MAPPING
        - Brand name mappings: "SME" = "Sony Music", "Orchard" = "The Orchard", "AWAL" = "AWAL"

        ## Named accounts / distributor accounts (use ISRC_ACCOUNT_MAPPING)
        When user asks about a named account like "Som Livre", "KONDZILLA", or any other client/label name that is NOT a top-level brand (Sony Music, The Orchard, AWAL):
        - Filter using VENDOR_NAME from ISRC_ACCOUNT_MAPPING (use ILIKE for fuzzy matching) — this captures all ISRCs under that account regardless of subaccount
        - Example: WHERE isrc_account_mapping.vendor_name ILIKE '%Som Livre%'
        - If the user mentions a specific sub-label within an account, also filter by SUBACCOUNT_NAME
        - CRITICAL: Never infer account from ISRC prefix (e.g. "BRSME" does NOT mean SME Brasil). Always join to ISRC_ACCOUNT_MAPPING to determine account ownership.

        ## When unsure whether brand or account
        If the name does not clearly match a known top-level brand (Sony Music, The Orchard, AWAL), try ISRC_ACCOUNT_MAPPING first (VENDOR_NAME ILIKE). If no match, fall back to ISRC_BRAND_MAPPING.