name: TIKTOK_SEMANTIC_VIEW
description: TikTok engagement metrics for music tracks (ISRCs) by country and date. Includes creations, views, likes, comments, shares, and favorites. Joined with brand mapping to filter by company brand (e.g. The Orchard, AWAL, Sony Music). Also joined with artist mapping.
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
        description: The company brand the track belongs to (e.g. The Orchard, AWAL, Sony Music)
        expr: COMPANY_BRAND
        data_type: VARCHAR(16777216)
        sample_values:
          - The Orchard
          - Sony Music
          - AWAL
          - Foundation Media
          - Mass Appeal
      - name: ISRC
        expr: ISRC
        data_type: VARCHAR(16777216)
    primary_key:
      columns:
        - ISRC
  - name: ISRC_GLOBAL_PARTICIPANT_MAPPING
    description: Maps ISRCs to artists (global participants) with track names. An ISRC can map to multiple artists (e.g. featured artists).
    base_table:
      database: LORELAI_AGENT
      schema: DEV
      table: ISRC_GLOBAL_PARTICIPANT_MAPPING
    dimensions:
      - name: ARTISTNAME
        synonyms:
          - artist
        description: The name of the artist associated with the song/track.
        expr: ARTISTNAME
        data_type: VARCHAR(16777216)
        sample_values:
          - Frank Sinatra
          - Various Artists
          - Tara
      - 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
      - name: ISRC
        description: International Standard Recording Code (ISRC) used to uniquely identify a recorded track or music video.
        expr: ISRC
        data_type: VARCHAR(16777216)
      - name: TRACKNAME
        synonyms:
          - song
          - track
        description: The name of a music track or song.
        expr: TRACKNAME
        data_type: VARCHAR(2000)
    primary_key:
      columns:
        - ISRC
  - name: V_TIKTOK_BY_ISRC_COUNTRY_DAILY
    base_table:
      database: FACTS
      schema: PROD
      table: V_TIKTOK_BY_ISRC_COUNTRY_DAILY
    dimensions:
      - name: CONTENT_TYPE
        synonyms:
          - type
          - video type
        description: Type of TikTok content (e.g. UGC for user-generated content)
        expr: CONTENT_TYPE
        data_type: VARCHAR(16777216)
      - name: COUNTRY_CODE
        synonyms:
          - country
        description: Two-letter ISO country code where the TikTok engagement occurred
        expr: COUNTRY_CODE
        data_type: VARCHAR(16777216)
      - name: ISRC
        synonyms:
          - song id
          - track id
        description: International Standard Recording Code - unique identifier for a music track
        expr: ISRC
        data_type: VARCHAR(16777216)
    time_dimensions:
      - name: DOWNLOAD_ACTIVITY_DATE
        synonyms:
          - date
        description: The date of the TikTok activity
        expr: DOWNLOAD_ACTIVITY_DATE
        data_type: DATE
    facts:
      - name: COMMENTS
        description: Number of comments on TikTok videos using this track
        expr: COMMENTS
        data_type: NUMBER(38,0)
        access_modifier: public_access
      - name: CREATIONS
        synonyms:
          - videos created
        description: Number of TikTok videos created using this track
        expr: CREATIONS
        data_type: NUMBER(38,0)
        access_modifier: public_access
      - name: FAVORITES
        synonyms:
          - saved
        description: Number of times TikTok videos using this track were favorited
        expr: FAVORITES
        data_type: NUMBER(38,0)
        access_modifier: public_access
      - name: LIKES
        description: Number of likes on TikTok videos using this track
        expr: LIKES
        data_type: NUMBER(38,0)
        access_modifier: public_access
      - name: SHARES
        description: Number of shares of TikTok videos using this track
        expr: SHARES
        data_type: NUMBER(38,0)
        access_modifier: public_access
      - name: VIEWS
        synonyms:
          - video views
          - watch count
        description: Number of views on TikTok videos using this track
        expr: VIEWS
        data_type: NUMBER(38,0)
        access_modifier: public_access
    metrics:
      - name: TOTAL_COMMENTS
        expr: sum(comments)
        access_modifier: public_access
      - name: TOTAL_CREATIONS
        expr: sum(creations)
        access_modifier: public_access
      - name: TOTAL_FAVORITES
        expr: sum(favorites)
        access_modifier: public_access
      - name: TOTAL_LIKES
        expr: sum(likes)
        access_modifier: public_access
      - name: TOTAL_SHARES
        expr: sum(shares)
        access_modifier: public_access
      - name: TOTAL_VIEWS
        expr: sum(views)
        access_modifier: public_access
    primary_key:
      columns:
        - ISRC
        - DOWNLOAD_ACTIVITY_DATE
        - COUNTRY_CODE
        - CONTENT_TYPE
relationships:
  - name: TIKTOK_TO_ARTIST_MAPPING
    left_table: V_TIKTOK_BY_ISRC_COUNTRY_DAILY
    right_table: ISRC_GLOBAL_PARTICIPANT_MAPPING
    relationship_columns:
      - left_column: ISRC
        right_column: ISRC
  - name: TIKTOK_TO_BRAND_MAPPING
    left_table: V_TIKTOK_BY_ISRC_COUNTRY_DAILY
    right_table: ISRC_BRAND_MAPPING
    relationship_columns:
      - left_column: ISRC
        right_column: ISRC
verified_queries:
  - name: '"What are the daily TikTok engagement metrics including creations, views, favorites, likes, and comments for track USSM12600097 since March 15, 2026 until 20th of March?"'
    sql: |-
      SELECT
        isrc,
        download_activity_date,
        SUM(creations) AS total_creations,
        SUM(views) AS total_views,
        SUM(favorites) AS total_favorites,
        SUM(likes) AS total_likes,
        SUM(comments) AS total_comments
      FROM
        v_tiktok_by_isrc_country_daily
      WHERE
        isrc = 'USSM12600097'
        AND download_activity_date >= '2026-03-15'
        AND download_activity_date <= '2026-03-20'
      GROUP BY
        isrc,
        download_activity_date
      ORDER BY
        download_activity_date ASC
        /* Generated by Cortex Analyst (request_id: 73eb262f-5e48-4c6b-9ffd-c730b2c66644) */
    question: What are the daily TikTok engagement metrics including creations, views, favorites, likes, and comments for track USSM12600097 since March 15, 2026 until 20th of March?
    verified_at: 1776251044
    verified_by: Rain Bomberg
    use_as_onboarding_question: false
  - name: '"What are artist''s with id d5065b8d-733f-44d9-84dd-d98a0f661e38 top tracks by TikTok creations in the last 7 days?"'
    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 = 'd5065b8d-733f-44d9-84dd-d98a0f661e38'
          )
          SELECT
            at.trackname,
            at.artistname,
            t.isrc,
            SUM(t.creations) AS total_creations,
            SUM(t.views) AS total_views,
            SUM(t.likes) AS total_likes,
            SUM(t.comments) AS total_comments
          FROM artist_tracks AS at
          INNER JOIN v_tiktok_by_isrc_country_daily AS t ON at.isrc = t.isrc
          WHERE t.download_activity_date >= CURRENT_DATE - 7
            AND t.download_activity_date <= CURRENT_DATE
          GROUP BY at.trackname, at.artistname, t.isrc
          ORDER BY total_creations DESC NULLS LAST
          LIMIT 10
    question: What are artist's with id d5065b8d-733f-44d9-84dd-d98a0f661e38 top tracks by TikTok creations in the last 7 days?
    verified_at: 1776876401
    verified_by: Rain Bomberg
    use_as_onboarding_question: false
  - name: '"Show me the daily TikTok creations, views, likes, comments, shares and favorites for ISRC USSM12504190 in the US over the past week."'
    sql: |2-
       WITH daily_metrics AS (
            SELECT
              t.isrc,
              t.download_activity_date,
              t.country_code,
              SUM(t.creations) AS total_creations,
              SUM(t.views) AS total_views,
              SUM(t.likes) AS total_likes,
              SUM(t.comments) AS total_comments,
              SUM(t.shares) AS total_shares,
              SUM(t.favorites) AS total_favorites
            FROM v_tiktok_by_isrc_country_daily AS t
            WHERE t.isrc = 'USSM12504190'
              AND t.country_code = 'US'
              AND t.download_activity_date >= CURRENT_DATE - 7
              AND t.download_activity_date <= CURRENT_DATE
            GROUP BY t.isrc, t.download_activity_date, t.country_code
          )
          SELECT
            dm.isrc,
            dm.country_code,
            dm.download_activity_date,
            dm.total_creations,
            dm.total_views,
            dm.total_likes,
            dm.total_comments,
            dm.total_shares,
            dm.total_favorites
          FROM daily_metrics AS dm
          ORDER BY dm.download_activity_date ASC
          LIMIT 30
    question: Show me the daily TikTok creations, views, likes, comments, shares and favorites for ISRC USSM12504190 in the US over the past week.
    verified_at: 1776876570
    verified_by: Rain Bomberg
    use_as_onboarding_question: false
  - name: '"What are the top countries by TikTok creations for ISRC VNA0R2503731 this past week?"'
    sql: |-
      WITH country_metrics AS (
        SELECT
          t.country_code,
          SUM(t.creations) AS total_creations
        FROM
          v_tiktok_by_isrc_country_daily AS t
        WHERE
          t.isrc = 'VNA0R2503731'
          AND t.download_activity_date >= CURRENT_DATE - 7
          AND t.download_activity_date <= CURRENT_DATE
        GROUP BY
          t.country_code
      )
      SELECT
        cm.country_code,
        cm.total_creations
      FROM
        country_metrics AS cm
      ORDER BY
        cm.total_creations DESC NULLS LAST
      LIMIT
        10
    question: What are the top countries by TikTok creations for ISRC VNA0R2503731 this past week?
    verified_at: 1776877091
    verified_by: Rain Bomberg
    use_as_onboarding_question: false
  - name: '"What are the top 10 tracks by TikTok likes in the US over the last 7 days?"'
    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
        ai.trackname,
        ai.artistname,
        t.isrc,
        SUM(t.likes) AS total_likes
      FROM
        v_tiktok_by_isrc_country_daily AS t
        LEFT JOIN artist_info AS ai ON ai.isrc = t.isrc
      WHERE
        t.country_code = 'US'
        AND t.download_activity_date >= CURRENT_DATE - 7
        AND t.download_activity_date <= CURRENT_DATE
      GROUP BY
        ai.trackname,
        ai.artistname,
        t.isrc
      ORDER BY
        total_likes DESC NULLS LAST
      LIMIT
        10
    question: What are the top 10 tracks by TikTok likes in the US over the last 7 days?
    verified_at: 1776877129
    verified_by: Rain Bomberg
    use_as_onboarding_question: false
  - name: '"Show me the TikTok engagement by country for artist ef3f1214-6ba1-48b1-b0a9-1ec6883ba8c0 from March 21 to April 18, 2026."'
    sql: |2-
          WITH artist_tracks AS (
            SELECT DISTINCT m.isrc
            FROM isrc_global_participant_mapping AS m
            WHERE m.global_participant_id = 'ef3f1214-6ba1-48b1-b0a9-1ec6883ba8c0'
          )
          SELECT
            t.country_code,
            SUM(t.creations) AS total_creations,
            SUM(t.views) AS total_views,
            SUM(t.likes) AS total_likes,
            SUM(t.comments) AS total_comments,
            SUM(t.shares) AS total_shares
          FROM artist_tracks AS at
          INNER JOIN v_tiktok_by_isrc_country_daily AS t ON at.isrc = t.isrc
          WHERE t.download_activity_date >= '2026-03-21'
            AND t.download_activity_date <= '2026-04-18'
          GROUP BY t.country_code
          ORDER BY total_views DESC NULLS LAST
          LIMIT 15
    question: Show me the TikTok engagement by country for artist ef3f1214-6ba1-48b1-b0a9-1ec6883ba8c0 from March 21 to April 18, 2026.
    verified_at: 1776876816
    verified_by: Rain Bomberg
    use_as_onboarding_question: false
module_custom_instructions:
  sql_generation: |2-
        DEFAULT FILTERS:
        - Date range: If no date/time period mentioned, filter to last 7 days: DOWNLOAD_ACTIVITY_DATE >= CURRENT_DATE() - 7
        - Results limit: If user asks for "top" without a number, use LIMIT 10
        - Always include both a LIMIT clause and a date filter on DOWNLOAD_ACTIVITY_DATE.

        COUNTRY CODES:
        - Use ISO 3166-1 alpha-2: UK/Britain = GB, USA/America = US