name: TADAS_SEMANTIC_VIEW
description: Data containing TADAS scores that show tracks that are trending.
tables:
  - name: ISRC_BRAND_MAPPING
    description: |-
      Maps ISRCs to their company brand (e.g. The Orchard, AWAL, Sony Music). An ISRC can belong to multiple 
        brands.
    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 (e.g. The Orchard, AWAL, Sony Music, Altafonte).
        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
        synonyms:
          - song id
          - track id
        description: International Standard Recording Code (ISRC) uniquely identifying a recorded track or music video.
        expr: ISRC
        data_type: VARCHAR(16777216)
        sample_values:
          - TR0392000115
          - QMFMG2362071
          - US52U0510003
    primary_key:
      columns:
        - ISRC
  - name: TADAS_TRENDS
    description: Music tracks with TADAS trending scores by country, including trend duration and engagement signals.
    base_table:
      database: LORELAI_AGENT
      schema: DEV
      table: TADAS_TRENDS
    dimensions:
      - name: FLAG_NAME
        synonyms:
          - engagement signal
          - platform signal
          - trend driver
        description: Platform engagement signal driving the trend (e.g. TIKTOK LIKES, SPOTIFY SEARCH). NULL means untracked drivers.
        expr: FLAG_NAME
        data_type: VARCHAR(16777216)
        is_enum: true
        sample_values:
          - TIKTOK LIKES
          - SPOTIFY COLLECTION
          - SPOTIFY LEAN FORWARD
          - SPOTIFY SEARCH
          - TIKTOK VIEWS
          - TIKTOK CREATIONS
          - APPLE LEAN FORWARD
          - APPLE SEARCH
      - name: GEO_COUNTRY
        description: The geographic country related to TADAS score. Value can be 2 letter iso code for countries or GLOBAL.
        expr: GEO_COUNTRY
        data_type: VARCHAR(16777216)
        sample_values:
          - US
          - FB
          - EE
          - GLOBAL
          - IT
          - FR
          - AU
      - name: ISRC
        synonyms:
          - song id
          - track id
        description: International Standard Recording Code (ISRC) used to uniquely identify sound recordings and music video recordings.
        expr: ISRC
        data_type: VARCHAR(16777216)
        sample_values:
          - BR27R2500139
          - JPP301900716
          - USRC11700144
    time_dimensions:
      - name: REPORT_DATE
        description: Date when the latest TADAS scores were calculated.
        expr: REPORT_DATE
        data_type: DATE
        sample_values:
          - '2026-03-18'
          - '2026-02-15'
          - '2026-01-29'
    facts:
      - name: CONSECUTIVE_TREND
        description: The number of consecutive days song/track has been trending.
        expr: CONSECUTIVE_TREND
        data_type: FLOAT
        access_modifier: public_access
        sample_values:
          - '1'
          - '21'
          - '10'
      - name: TADAS_30DAY_SCORE
        description: The 30-day score associated with the TADAS trending score. Can be between 0.1 and 1.0
        expr: TADAS_30DAY_SCORE
        data_type: FLOAT
        access_modifier: public_access
        sample_values:
          - '0.19'
          - '0.15'
          - '0.06'
          - '0.01'
          - '1.00'
    metrics:
      - name: AVERAGE_TADAS_SCORE
        expr: avg(tadas_30day_score)
        access_modifier: public_access
      - name: MAX_TADAS_SCORE
        expr: max(tadas_30day_score)
        access_modifier: public_access
    primary_key:
      columns:
        - ISRC
  - name: ISRC_GLOBAL_PARTICIPANT_MAPPING
    description: Maps ISRCs to artist names, track names, and global participant IDs.
    base_table:
      database: LORELAI_AGENT
      schema: DEV
      table: ISRC_GLOBAL_PARTICIPANT_MAPPING
    dimensions:
      - name: ARTISTNAME
        description: The name of the artist associated with the song/track.
        expr: ARTISTNAME
        data_type: VARCHAR(16777216)
        sample_values:
          - Tara
          - Frank Sinatra
          - Various Artists
      - name: GLOBAL_PARTICIPANT_ID
        description: Unique identifier assigned to music artist.
        expr: GLOBAL_PARTICIPANT_ID
        data_type: VARCHAR(16777216)
        sample_values:
          - 190f82d5-86cc-4c87-abae-ba841abe88ec
          - 17bb3916-4646-4744-b438-4a5763a0a9cb
          - 0a9cfe73-6b26-4fb3-9092-aaae0c56a0b0
      - name: ISRC
        synonyms:
          - song id
          - track id
        description: International Standard Recording Code (ISRC) used to uniquely identify sound recordings and music video recordings.
        expr: ISRC
        data_type: VARCHAR(16777216)
        sample_values:
          - CACA30411145
          - CZA161102449
          - USA370979140
      - name: TRACKNAME
        description: The name of a music track or song.
        expr: TRACKNAME
        data_type: VARCHAR(2000)
        sample_values:
          - 'Magnificat in D Major, Bwv 243: X. Suscepit Israel'
          - Metal, Heavy, Rip, Crush, 26 Sound Effect
          - Midnight Radio
    primary_key:
      columns:
        - GLOBAL_PARTICIPANT_ID
        - ISRC
relationships:
  - name: BRAND_TO_TADAS
    left_table: TADAS_TRENDS
    right_table: ISRC_BRAND_MAPPING
    relationship_columns:
      - left_column: ISRC
        right_column: ISRC
  - name: ISRC_GLOBAL_PARTICIPANT_MAPPING_TO_TADAS_TRENDS
    left_table: ISRC_GLOBAL_PARTICIPANT_MAPPING
    right_table: TADAS_TRENDS
    relationship_columns:
      - left_column: ISRC
        right_column: ISRC
verified_queries:
  - name: '"What platform is driving artist with id 20e14b6b-e2fc-4bc5-9a49-75b5439713e6 track''s USSM12502221 trends in Turkey?"'
    sql: |-
      WITH artist_track AS (
        SELECT
          gpm.isrc
        FROM
          isrc_global_participant_mapping AS gpm
        WHERE
          gpm.global_participant_id = '20e14b6b-e2fc-4bc5-9a49-75b5439713e6'
          AND gpm.isrc = 'USSM12502221'
      )
      SELECT
        ti.flag_name as platform_flag,
        ti.geo_country,
        ti.report_date,
        ti.tadas_30day_score,
        ti.consecutive_trend
      FROM
        isrc_global_participant_mapping AS gpm
        LEFT OUTER JOIN tadas_trends AS ti ON gpm.isrc = ti.isrc
      WHERE
        gpm.global_participant_id = '20e14b6b-e2fc-4bc5-9a49-75b5439713e6'
        AND gpm.isrc = 'USSM12502221'
        AND ti.geo_country = 'TR'
      ORDER BY
        ti.report_date DESC NULLS LAST,
        ti.tadas_30day_score DESC NULLS LAST
        /* Generated by Cortex Analyst (request_id: 2b5d1669-804c-4b0e-89b0-74bb6b8fa1cf) */
    question: What platform is driving artist with id 20e14b6b-e2fc-4bc5-9a49-75b5439713e6 track's USSM12502221 trends in Turkey?
    verified_at: 1776872186
    verified_by: Rain Bomberg
    use_as_onboarding_question: false
  - name: '"what songs and where are trending for artist with id 20e14b6b-e2fc-4bc5-9a49-75b5439713e6?"'
    sql: |-
      WITH artist_tracks AS (
        SELECT
          DISTINCT m.isrc,
          m.trackname,
          m.artistname
        FROM
          isrc_global_participant_mapping AS m
        WHERE
          m.global_participant_id = '20e14b6b-e2fc-4bc5-9a49-75b5439713e6'
      )
      SELECT
        DISTINCT t.geo_country,
        at.trackname,
        at.artistname,
        t.consecutive_trend,
        t.tadas_30day_score
      FROM
        artist_tracks AS at
        INNER JOIN tadas_trends AS t ON at.isrc = t.isrc
    question: what songs and where are trending for artist with id 20e14b6b-e2fc-4bc5-9a49-75b5439713e6?
    verified_at: 1776872254
    verified_by: Rain Bomberg
    use_as_onboarding_question: false
  - name: '"What can you tell me about song''s SEWEE1500509 trending characteristics?"'
    sql: |-
      WITH track_trends AS (
        SELECT
          t.isrc,
          t.flag_name,
          t.consecutive_trend,
          t.tadas_30day_score,
          t.geo_country,
          t.report_date
        FROM
          tadas_trends AS t
        WHERE
          t.isrc = 'SEWEE1500509'
      )
      SELECT
        DISTINCT tt.flag_name,
        tt.consecutive_trend,
        tt.tadas_30day_score,
        tt.geo_country,
        m.trackname,
        m.artistname
      FROM
        track_trends AS tt
        INNER JOIN isrc_global_participant_mapping AS m ON tt.isrc = m.isrc
    question: What can you tell me about song's SEWEE1500509 trending characteristics?
    verified_at: 1776872267
    verified_by: Rain Bomberg
    use_as_onboarding_question: false
  - name: '"Which top 10 The Orchard tracks are trending in Brazil, and what engagement signals are driving their trends?"'
    sql: |-
      WITH brand_filtered AS (
            SELECT
              DISTINCT ibm.isrc
            FROM
              isrc_brand_mapping AS ibm
            WHERE
              ibm.company_brand = 'The Orchard'
          ),
          tadas_brazil AS (
            SELECT
              ti.isrc,
              ti.tadas_30day_score,
              ti.consecutive_trend,
              ti.flag_name,
              ti.report_date
            FROM
              tadas_trends AS ti
            WHERE
              ti.geo_country = 'BR'
              AND ti.report_date <= CURRENT_DATE
          ),
          artist_info AS (
            SELECT
              DISTINCT gpm.isrc,
              ANY_VALUE(gpm.trackname) AS trackname,
              ANY_VALUE(gpm.artistname) AS artistname
            FROM
              isrc_global_participant_mapping AS gpm
            GROUP BY
              gpm.isrc
          )
          SELECT
            ai.trackname,
            ai.artistname,
            tb.isrc,
            MAX(tb.tadas_30day_score) AS tadas_30day_score,
            MAX(tb.consecutive_trend) AS consecutive_trend,
            LISTAGG(DISTINCT tb.flag_name, ', ') WITHIN GROUP (ORDER BY tb.flag_name) AS flag_names,
            MAX(tb.report_date) AS report_date
          FROM
            tadas_brazil AS tb
            JOIN brand_filtered AS bf ON bf.isrc = tb.isrc
            JOIN artist_info AS ai ON ai.isrc = tb.isrc
          GROUP BY
            ai.trackname, ai.artistname, tb.isrc
          ORDER BY
            tadas_30day_score DESC NULLS LAST
          LIMIT 10
    question: Which top 10 The Orchard tracks are trending in Brazil, and what engagement signals are driving their trends?
    verified_at: 1776872482
    verified_by: Rain Bomberg
    use_as_onboarding_question: false
  - name: '"What are the top trending tracks in France for the artist with id 3121cd5b-a770-41c6-9390-baf9f7571003, and what is driving their trends?"'
    sql: |-
      WITH artist_tracks AS (
        SELECT
          DISTINCT m.isrc,
          m.trackname,
          m.artistname
        FROM
          isrc_global_participant_mapping AS m
        WHERE
          m.global_participant_id = '3121cd5b-a770-41c6-9390-baf9f7571003'
      ),
      trending AS (
        SELECT
          at.trackname,
          at.artistname,
          t.flag_name,
          t.tadas_30day_score,
          t.consecutive_trend,
          t.geo_country,
          t.report_date
        FROM
          artist_tracks AS at
          LEFT OUTER JOIN tadas_trends AS t ON at.isrc = t.isrc
        WHERE
          t.geo_country = 'FR'
          AND t.report_date <= CURRENT_DATE
      )
      SELECT
        trackname,
        artistname,
        flag_name,
        tadas_30day_score,
        consecutive_trend,
        geo_country,
        MIN(report_date) AS min_report_date,
        MAX(report_date) AS max_report_date,
        COUNT(report_date) AS count_report_date
      FROM
        trending
      GROUP BY
        trackname,
        artistname,
        flag_name,
        tadas_30day_score,
        consecutive_trend,
        geo_country
      ORDER BY
        tadas_30day_score DESC NULLS LAST
      LIMIT
        10
    question: What are the top trending tracks in France for the artist with id 3121cd5b-a770-41c6-9390-baf9f7571003, and what is driving their trends?
    verified_at: 1776872519
    verified_by: Rain Bomberg
    use_as_onboarding_question: false
  - name: '"What are the trending tracks for artist with id 288b4a12-ffad-4091-9753-51e245ab8b6e in Australia?"'
    sql: |-
      WITH artist_tracks AS (
        SELECT
          DISTINCT m.isrc,
          m.trackname,
          m.artistname
        FROM
          isrc_global_participant_mapping AS m
        WHERE
          m.global_participant_id = '06183d1d-7c5b-444b-87ef-6a9231378f0b'
      )
      SELECT distinct
        at.trackname,
        at.artistname,
        t.isrc,
        t.tadas_30day_score,
        t.consecutive_trend
      FROM
        artist_tracks AS at
        INNER JOIN tadas_trends AS t ON at.isrc = t.isrc
      WHERE
        t.geo_country = 'AU'
        AND t.report_date <= CURRENT_DATE
      ORDER BY
        t.tadas_30day_score DESC NULLS LAST
      LIMIT
        10
    question: What are the trending tracks for artist with id 288b4a12-ffad-4091-9753-51e245ab8b6e in Australia?
    verified_at: 1776872554
    verified_by: Rain Bomberg
    use_as_onboarding_question: false
  - name: '"What are the top trending tracks for Sony Music in the US?"'
    sql: |-
      WITH brand_tracks AS (
        SELECT
          DISTINCT ibm.isrc
        FROM
          isrc_brand_mapping AS ibm
        WHERE
          ibm.company_brand = 'Sony Music'
      ),
      artist_info AS (
        SELECT
          DISTINCT gpm.isrc,
          ANY_VALUE(gpm.trackname) AS trackname,
          ANY_VALUE(gpm.artistname) AS artistname
        FROM
          isrc_global_participant_mapping AS gpm
        GROUP BY
          gpm.isrc
      )
      SELECT distinct
        ai.trackname,
        ai.artistname,
        t.isrc,
        t.tadas_30day_score,
        t.consecutive_trend,
        t.report_date
      FROM
        tadas_trends AS t
        INNER JOIN brand_tracks AS bt ON bt.isrc = t.isrc
        LEFT JOIN artist_info AS ai ON ai.isrc = t.isrc
      WHERE
        t.geo_country = 'US'
        AND t.report_date <= CURRENT_DATE
      ORDER BY
        t.tadas_30day_score DESC NULLS LAST
      LIMIT
        10
    question: What are the top trending tracks for Sony Music in the US?
    verified_at: 1776872582
    verified_by: Rain Bomberg
    use_as_onboarding_question: false
  - name: '"What tracks are trending due to TikTok activity in the Netherlands?"'
    sql: |-
      WITH artist_info AS (
        SELECT
          DISTINCT gpm.isrc,
          ANY_VALUE(gpm.trackname) AS trackname,
          ANY_VALUE(gpm.artistname) AS artistname
        FROM
          isrc_global_participant_mapping AS gpm
        GROUP BY
          gpm.isrc
      )
      SELECT distinct
        ai.trackname,
        ai.artistname,
        t.isrc,
        t.tadas_30day_score,
        t.consecutive_trend,
        t.report_date
      FROM
        tadas_trends AS t
        LEFT JOIN artist_info AS ai ON ai.isrc = t.isrc
      WHERE
        t.flag_name LIKE 'TIKTOK%'
        AND t.geo_country = 'NL'
        AND t.report_date <= CURRENT_DATE
      ORDER BY
        t.tadas_30day_score DESC NULLS LAST
      LIMIT
        10
    question: What tracks are trending due to TikTok activity in the Netherlands?
    verified_at: 1776872617
    verified_by: Rain Bomberg
    use_as_onboarding_question: false
  - name: '"Show me all trending tracks for artist with id 06183d1d-7c5b-444b-87ef-6a9231378f0b with TADAS details."'
    sql: |-
      WITH artist_tracks AS (
            SELECT
              DISTINCT m.isrc,
              m.trackname,
              m.artistname
            FROM
              isrc_global_participant_mapping AS m
            WHERE
              m.global_participant_id = '06183d1d-7c5b-444b-87ef-6a9231378f0b'
          )
          SELECT
            at.trackname,
            at.artistname,
            t.isrc,
            t.geo_country,
            MAX(t.tadas_30day_score) AS tadas_30day_score,
            MAX(t.consecutive_trend) AS consecutive_trend,
            LISTAGG(DISTINCT t.flag_name, ', ') WITHIN GROUP (ORDER BY t.flag_name) AS flag_names,
            MAX(t.report_date) AS report_date
          FROM
            artist_tracks AS at
            INNER JOIN tadas_trends AS t ON at.isrc = t.isrc
          WHERE
            t.report_date <= CURRENT_DATE
          GROUP BY
            at.trackname, at.artistname, t.isrc, t.geo_country
          ORDER BY
            tadas_30day_score DESC NULLS LAST,
            t.geo_country ASC NULLS LAST
          LIMIT 10
    question: Show me all trending tracks for artist with id 06183d1d-7c5b-444b-87ef-6a9231378f0b with TADAS details.
    verified_at: 1776872720
    verified_by: Rain Bomberg
    use_as_onboarding_question: false
  - name: '"Which Sony tracks are trending in Brazil?"'
    sql: |-
      WITH brand_tracks AS (
        SELECT
          DISTINCT ibm.isrc
        FROM
          isrc_brand_mapping AS ibm
        WHERE
          ibm.company_brand = 'Sony Music'
      ),
      artist_info AS (
        SELECT
          DISTINCT gpm.isrc,
          ANY_VALUE(gpm.trackname) AS trackname,
          ANY_VALUE(gpm.artistname) AS artistname
        FROM
          isrc_global_participant_mapping AS gpm
        GROUP BY
          gpm.isrc
      ),
      tadas_brazil AS (
        SELECT
          ti.isrc,
          ti.tadas_30day_score,
          ti.consecutive_trend,
          ti.flag_name,
          ti.report_date
        FROM
          tadas_trends AS ti
        WHERE
          ti.geo_country = 'BR'
          AND ti.report_date <= CURRENT_DATE
      )
      SELECT distinct
        ai.trackname,
        ai.artistname,
        tb.tadas_30day_score,
        tb.consecutive_trend,
        tb.report_date
      FROM
        tadas_brazil AS tb
        JOIN brand_tracks AS bt ON tb.isrc = bt.isrc
        JOIN artist_info AS ai ON ai.isrc = tb.isrc
      ORDER BY
        tb.tadas_30day_score DESC NULLS LAST
      LIMIT
        10
        /* Generated by Cortex Analyst (request_id: 0e640895-41a6-4a19-bb52-45de95e468b5) */
    question: Which Sony tracks are trending in Brazil?
    verified_at: 1777022544
    verified_by: Rain Bomberg
    use_as_onboarding_question: false
  - name: '"What are the top 10 trending Sony Music tracks in the US?"'
    sql: |-
      WITH sony_tadas AS (
        SELECT
          m.isrc,
          MAX(t.tadas_30day_score) AS max_tadas_score
        FROM
          isrc_brand_mapping AS m
          LEFT OUTER JOIN tadas_trends AS t ON m.isrc = t.isrc
        WHERE
          m.company_brand = 'Sony Music'
          AND t.geo_country = 'US'
        GROUP BY
          m.isrc
      ),
      track_info AS (
        SELECT
          p.isrc,
          p.trackname,
          p.artistname
        FROM
          isrc_global_participant_mapping AS p
          LEFT OUTER JOIN tadas_trends AS t ON p.isrc = t.isrc
        WHERE
          t.geo_country = 'US'
        GROUP BY
          p.isrc,
          p.trackname,
          p.artistname
      )
      SELECT distinct
        s.isrc,
        ti.trackname,
        ti.artistname,
        s.max_tadas_score
      FROM
        sony_tadas AS s
        LEFT OUTER JOIN track_info AS ti ON s.isrc = ti.isrc
      ORDER BY
        s.max_tadas_score DESC NULLS LAST
      LIMIT
        10
    question: What are the top 10 trending Sony Music tracks in the US?
    verified_at: 1777022616
    verified_by: Rain Bomberg
    use_as_onboarding_question: false
module_custom_instructions:
  sql_generation: |2-
      QUERY DEFAULTS:
        - When user mentions only artist (no specific track), return top 10 by tadas_30day_score.

        FILTERS:
        - Filter by COMPANY_BRAND from ISRC_BRAND_MAPPING when user asks about a brand.
        - Only include FLAG_NAME when user asks about platforms, engagement signals, or trend drivers.