name: SOCIAL_STATS_SEMANTIC_VIEW
description: |-
  Artist social media and streaming platform statistics across spotify, deezer, youtube, twitter, soundcloud, facebook, instagram, and tiktok. Includes followers, monthly
    listeners, popularity, and views over time.
tables:
  - name: GLOBAL_PARTICIPANT_BRAND_MAPPING
    description: Maps global participant IDs to their company brand (e.g. The Orchard, AWAL, Sony Music). An artist can belong to multiple brands.
    base_table:
      database: LORELAI_AGENT
      schema: DEV
      table: GLOBAL_PARTICIPANT_BRAND_MAPPING
    dimensions:
      - name: COMPANY_BRAND
        synonyms:
          - brand
          - company
          - label brand
        description: The company brand the artist 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: GLOBAL_PARTICIPANT_ID
        description: Unique identifier for participants in the global artist
        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: FACT_SOCIALS
    description: The table contains records of social media and streaming platform metrics for artists. Each record represents a snapshot of an artist's performance on a specific platform at a point in time, including audience size and engagement measurements.
    base_table:
      database: FACTS
      schema: PROD
      table: FACT_SOCIALS
    dimensions:
      - name: CHARTMETRIC_ARTIST_ID
        synonyms:
          - chartmetric id
          - cm artist id
        description: The unique Chartmetric identifier for an artist. Use this to filter by artist.
        expr: CHARTMETRIC_ARTIST_ID
        data_type: NUMBER(38,0)
        sample_values:
          - '3822945'
          - '3822942'
          - '3822944'
      - name: PLATFORM
        synonyms:
          - digital service provider
          - dsp
          - music streaming platform
        description: The social media or music streaming platform where the interaction or presence occurred.
        expr: PLATFORM
        data_type: VARCHAR(16777216)
        is_enum: true
        sample_values:
          - deezer
          - spotify
          - youtube
          - twitter
          - soundcloud
          - facebook
          - instagram
          - tiktok
    time_dimensions:
      - name: SNAPSHOT_DATE
        synonyms:
          - date
          - day
        description: The date when the snapshot of social media data was captured.
        expr: SNAPSHOT_DATE
        data_type: DATE
        sample_values:
          - '2023-04-02'
          - '2021-01-29'
          - '2025-06-02'
    facts:
      - name: FOLLOWERS
        synonyms:
          - follower count
          - follower number
        description: The number of followers associated with the social media account.
        expr: FOLLOWERS
        data_type: NUMBER(38,0)
        access_modifier: public_access
        sample_values:
          - '16888746'
          - '20000'
          - '58714'
      - name: MONTHLY_LISTENERS
        description: The number of listeners in a given month.
        expr: MONTHLY_LISTENERS
        data_type: NUMBER(38,0)
        access_modifier: public_access
        sample_values:
          - '15527403'
          - '329918'
          - '17332'
      - name: POPULARITY
        description: A metric indicating the level of popularity or engagement on social media.
        expr: POPULARITY
        data_type: NUMBER(38,0)
        access_modifier: public_access
        sample_values:
          - '77'
          - '80'
      - name: VIEWS
        description: The number of views recorded for social media content.
        expr: VIEWS
        data_type: NUMBER(38,0)
        access_modifier: public_access
        sample_values:
          - '2322829'
          - '2331979'
          - '2322703'
    metrics:
      - name: MAXIMUM_FOLLOWERS
        expr: max(followers)
        access_modifier: public_access
    primary_key:
      columns:
        - CHARTMETRIC_ARTIST_ID
        - SNAPSHOT_DATE
        - PLATFORM
  - name: GLOBAL_PARTICIPANT
    description: The table contains records of music artists or performers with their associated identifier, name, and chartmetric_id.
    base_table:
      database: FACTS
      schema: PROD
      table: GLOBAL_PARTICIPANT
    dimensions:
      - name: CHARTMETRIC_ID
        description: Chartmetric identifier on the global participant table. Used as join key to fact_socials.
        expr: CHARTMETRIC_ID
        data_type: NUMBER(38,0)
        sample_values:
          - '15312343'
          - '14359528'
          - '15252492'
      - name: ID
        description: Unique identifier for each music artist/performer.
        expr: ID
        data_type: VARCHAR(16777216)
        sample_values:
          - b0854531-b1ea-4f71-9bd3-429d0f7e74b4
          - fe4d0896-7035-4ced-b2f0-16831e56e452
          - b77682e8-df0c-42ee-a459-601bda3c1bc4
      - name: NAME
        synonyms:
          - artist
          - performer
        description: Names of artist/music performer.
        expr: NAME
        data_type: VARCHAR(16777216)
        sample_values:
          - Aisha Aftab
          - Irshad Khan
          - Jamal Ahmed
    primary_key:
      columns:
        - CHARTMETRIC_ID
relationships:
  - name: SOCIALS_TO_GLOBAL_PARTICIPANT
    left_table: FACT_SOCIALS
    right_table: GLOBAL_PARTICIPANT
    relationship_columns:
      - left_column: CHARTMETRIC_ARTIST_ID
        right_column: CHARTMETRIC_ID
  - name: PARTICIPANT_TO_BRAND
    left_table: GLOBAL_PARTICIPANT
    right_table: GLOBAL_PARTICIPANT_BRAND_MAPPING
    relationship_columns:
      - left_column: ID
        right_column: GLOBAL_PARTICIPANT_ID
verified_queries:
  - name: '"Daily Spotify followers for artist with chartmetric_artist_id 407402 in January 2026"'
    sql: |-
      SELECT
            snapshot_date,
            followers
          FROM fact_socials
          WHERE chartmetric_artist_id = 407402
            AND platform = 'spotify'
            AND snapshot_date BETWEEN '2026-01-01' AND '2026-01-31'
          ORDER BY snapshot_date ASC
    question: Daily Spotify followers for artist with chartmetric_artist_id 407402 in January 2026
    verified_at: 1776883497
    verified_by: Rain Bomberg
    use_as_onboarding_question: false
  - name: '"Show me all distinct company brand values"'
    sql: |-
      WITH __global_participant_brand_mapping AS (
            SELECT company_brand
            FROM global_participant_brand_mapping
          )
          SELECT DISTINCT company_brand
          FROM __global_participant_brand_mapping
          ORDER BY company_brand
    question: Show me all distinct company brand values
    verified_at: 1776883525
    verified_by: Rain Bomberg
    use_as_onboarding_question: false
  - name: '"Show followers across all platforms for artist with chartmetric_artist_id 550577 over the last 14 days"'
    sql: |-
      SELECT
            platform,
            snapshot_date,
            followers
          FROM fact_socials
          WHERE chartmetric_artist_id = 550577
            AND snapshot_date >= DATEADD(DAY, -14, CURRENT_DATE())
          ORDER BY platform, snapshot_date DESC
    question: Show followers across all platforms for artist with chartmetric_artist_id 550577 over the last 14 days
    verified_at: 1776883559
    verified_by: Rain Bomberg
    use_as_onboarding_question: false
  - name: '"What are the latest Spotify followers, monthly listeners, and popularity for artist with chartmetric_artist_id 11918082?"'
    sql: |-
      SELECT
            snapshot_date,
            followers,
            monthly_listeners,
            popularity
          FROM fact_socials
          WHERE chartmetric_artist_id = 11918082
            AND platform = 'spotify'
            AND snapshot_date >= DATEADD(DAY, -14, CURRENT_DATE())
          ORDER BY snapshot_date DESC
    question: What are the latest Spotify followers, monthly listeners, and popularity for artist with chartmetric_artist_id 11918082?
    verified_at: 1776883791
    verified_by: Rain Bomberg
    use_as_onboarding_question: false
  - name: '"Show daily Spotify followers and monthly listeners for artist with chartmetric_artist_id 214945 in February 2026"'
    sql: |-
      SELECT
            snapshot_date,
            followers,
            monthly_listeners,
            popularity
          FROM fact_socials
          WHERE chartmetric_artist_id = 214945
            AND platform = 'spotify'
            AND snapshot_date BETWEEN '2026-02-01' AND '2026-02-28'
          ORDER BY snapshot_date ASC
    question: Show daily Spotify followers and monthly listeners for artist with chartmetric_artist_id 214945 in February 2026
    verified_at: 1776883815
    verified_by: Rain Bomberg
    use_as_onboarding_question: false
  - name: '"Show the most recent Spotify followers, monthly listeners, and popularity for artists with chartmetric_artist_id 208, 4015, and 214945"'
    sql: |2-
       SELECT
            chartmetric_artist_id,
            snapshot_date,
            followers,
            monthly_listeners,
            popularity
          FROM fact_socials
          WHERE chartmetric_artist_id IN (208, 4015, 214945)
            AND platform = 'spotify'
            AND snapshot_date >= DATEADD(DAY, -14, CURRENT_DATE())
          ORDER BY chartmetric_artist_id, snapshot_date DESC
    question: Show the most recent Spotify followers, monthly listeners, and popularity for artists with chartmetric_artist_id 208, 4015, and 214945
    verified_at: 1776883959
    verified_by: Rain Bomberg
    use_as_onboarding_question: false
module_custom_instructions:
  sql_generation: |-
    When the user does not specify a date range, default to the last 14 days of data using snapshot_date >= DATEADD(DAY, -14, CURRENT_DATE()). Always filter by chartmetric_artist_id
       when provided. Always filter by platform when the user specifies one (e.g. platform = 'spotify').
        When user asks about a specific brand, join through GLOBAL_PARTICIPANT to GLOBAL_PARTICIPANT_BRAND_MAPPING and filter on COMPANY_BRAND.
        This view does NOT contain columns named timestp, cm_artist, or tables named spotify_artist_ml_chart, spotify_artist_followers_chart, or spotify_artist_popularity_chart. Use
      only the columns defined in this semantic view