name: TRACK_SOS_METRICS_SEMANTIC_VIEW description: This view helps to aggregate streams by track and platform/dsp using the pre-unpivoted SOS streams view. tables: - name: CHATBOT_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: DEV_ENGINEERING schema: DEV_RBOMBERG table: CHATBOT_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 description: International Standard Recording Code (ISRC) uniquely identifying a recorded track. expr: ISRC data_type: VARCHAR(16777216) sample_values: - US5NR1900208 - QMDA72549717 - QMDA72551391 primary_key: columns: - ISRC - name: DIM_STORE description: The table contains records of digital music platform (DSP) names 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: Digital service provider identifier 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: V_STREAMS_SOS_UNPIVOTED description: Pre-unpivoted view of music streaming Source of Streams (SOS) data. Each row represents a single stream source metric for a track on a given day, with DSP, source type, business name, and activity type already resolved. base_table: database: DEV_ENGINEERING schema: DEV_RBOMBERG table: V_STREAMS_SOS_UNPIVOTED dimensions: - name: ACTIVITY_TYPE synonyms: - active or passive - lean forward or lean backward - stream category description: Whether the stream source is active (lean forward) or passive (lean backward), independent of DSP. expr: ACTIVITY_TYPE data_type: VARCHAR(16777216) is_enum: true sample_values: - ACTIVE - PASSIVE - name: BUSINESS_NAME synonyms: - human readable source - source name description: Human-readable name for the Source of Streams (SOS) (e.g. Album Page, Collection, Search). expr: BUSINESS_NAME data_type: VARCHAR(16777216) sample_values: - Album Page - Collection - Artist Page - Search - Play Queue - Playlist - Radio - Mix - Chart - name: COUNTRY_CODE description: Short name of the country where the streaming activity occurred. Usually contains 2-letter ISO codes. expr: COUNTRY_CODE data_type: VARCHAR(2) sample_values: - US - GB - DE - PT - BR - name: DSP synonyms: - dsp - platform - service provider description: Name of the digital service provider (SPOTIFY, APPLE, AMAZON, YOUTUBE). expr: DSP data_type: VARCHAR(16777216) is_enum: true sample_values: - SPOTIFY - APPLE - AMAZON - YOUTUBE - name: ISRC description: International Standard Recording Code (ISRC) uniquely identifying a recorded track. expr: ISRC data_type: VARCHAR(16777216) sample_values: - USSM12501572 - USSM16901106 - USSM19902989 - name: PRODUCT_ID description: A unique identifier for the product. expr: PRODUCT_ID data_type: NUMBER(32,0) sample_values: - '6219484' - '4208806' - '6105392' - name: SOURCE_TYPE synonyms: - dsp source type description: DSP-specific classification combining the platform and activity (e.g. SPOTIFY_ACTIVE, APPLE_PASSIVE). expr: SOURCE_TYPE data_type: VARCHAR(16777216) is_enum: true sample_values: - SPOTIFY_ACTIVE - SPOTIFY_PASSIVE - APPLE_ACTIVE - APPLE_PASSIVE - AMAZON_ACTIVE - AMAZON_PASSIVE - YOUTUBE_ACTIVE - YOUTUBE_PASSIVE - name: STORE_ID description: A unique identifier for the store or distributor location. expr: STORE_ID data_type: NUMBER(32,0) sample_values: - '286' - '187' - '348' - name: STREAM_SOURCE synonyms: - column name - source column description: The original column name from the wide table identifying the specific Source of Streams (SOS) (e.g. streams_sos_spotify_albumpage). expr: STREAM_SOURCE data_type: VARCHAR(134217728) is_enum: true sample_values: - streams_sos_spotify_albumpage - streams_sos_spotify_collection - streams_sos_spotify_artistpage - streams_sos_spotify_search - streams_sos_spotify_playqueue - streams_sos_spotify_playlists - streams_sos_amazon_userplaylist - streams_sos_apple_library - streams_sos_youtube_youtube_search time_dimensions: - name: DOWNLOAD_ACTIVITY_DATE description: The date when the streaming activity occurred. expr: DOWNLOAD_ACTIVITY_DATE data_type: DATE sample_values: - '2026-01-30' - '2026-02-07' - '2026-01-05' facts: - name: VALUE synonyms: - stream count - streams description: The number of streams for this specific source on this day. expr: VALUE data_type: NUMBER(38,0) access_modifier: public_access sample_values: - '17' - '4' - '1' default_aggregation: sum metrics: - name: TOTAL_ACTIVE_STREAMS description: Total streams from active/lean forward sources. expr: sum(CASE WHEN ACTIVITY_TYPE = 'ACTIVE' THEN VALUE ELSE 0 END) access_modifier: public_access - name: TOTAL_PASSIVE_STREAMS description: Total streams from passive/lean backward sources. expr: sum(CASE WHEN ACTIVITY_TYPE = 'PASSIVE' THEN VALUE ELSE 0 END) access_modifier: public_access - name: TOTAL_STREAMS description: Total streams across all sources. expr: sum(VALUE) access_modifier: public_access primary_key: columns: - ISRC - PRODUCT_ID - STORE_ID - DOWNLOAD_ACTIVITY_DATE - COUNTRY_CODE - STREAM_SOURCE relationships: - name: STREAMS_TO_BRAND left_table: V_STREAMS_SOS_UNPIVOTED right_table: CHATBOT_ISRC_BRAND_MAPPING relationship_columns: - left_column: ISRC right_column: ISRC - name: STREAMS_TO_DIM_STORE left_table: V_STREAMS_SOS_UNPIVOTED right_table: DIM_STORE relationship_columns: - left_column: STORE_ID right_column: STOREID verified_queries: - name: '"Give me daily Spotify detailed active streams by source from 2nd of February until 4th of February this year for track with isrc USSM12501572"' sql: |- SELECT ISRC, DOWNLOAD_ACTIVITY_DATE, SOURCE_TYPE, STREAM_SOURCE, BUSINESS_NAME, SUM(VALUE) AS total_streams FROM DEV_ENGINEERING.DEV_RBOMBERG.V_STREAMS_SOS_UNPIVOTED WHERE ISRC = 'USSM12501572' AND DSP = 'SPOTIFY' AND ACTIVITY_TYPE = 'ACTIVE' AND DOWNLOAD_ACTIVITY_DATE BETWEEN '2026-02-02' AND '2026-02-04' GROUP BY ISRC, DOWNLOAD_ACTIVITY_DATE, SOURCE_TYPE, STREAM_SOURCE, BUSINESS_NAME ORDER BY STREAM_SOURCE, DOWNLOAD_ACTIVITY_DATE DESC question: Give me daily Spotify detailed active streams by source from 2nd of February until 4th of February this year for track with isrc USSM12501572 verified_at: 1777891629 verified_by: Rain Bomberg use_as_onboarding_question: false - name: '"Give source of streams by percentage from January 2026 for track USSM12501572 and use only Apple streams."' sql: |- WITH aggregate AS ( SELECT ISRC, STREAM_SOURCE, BUSINESS_NAME, SUM(VALUE) AS total_streams FROM DEV_ENGINEERING.DEV_RBOMBERG.V_STREAMS_SOS_UNPIVOTED WHERE ISRC = 'USSM12501572' AND DSP = 'APPLE' AND DOWNLOAD_ACTIVITY_DATE BETWEEN '2026-01-01' AND '2026-01-31' GROUP BY ISRC, STREAM_SOURCE, BUSINESS_NAME ) SELECT ISRC, STREAM_SOURCE, BUSINESS_NAME, RATIO_TO_REPORT(total_streams) OVER () AS percent_profit FROM aggregate ORDER BY percent_profit DESC question: Give source of streams by percentage from January 2026 for track USSM12501572 and use only Apple streams. verified_at: 1777891635 verified_by: Rain Bomberg use_as_onboarding_question: false - name: '"I want to see daily Amazon user playlist streams for track USSM12405456 from the beginning of 2026."' sql: |- SELECT ISRC, DOWNLOAD_ACTIVITY_DATE, SUM(VALUE) AS total_streams FROM DEV_ENGINEERING.DEV_RBOMBERG.V_STREAMS_SOS_UNPIVOTED WHERE ISRC = 'USSM12405456' AND STREAM_SOURCE = 'streams_sos_amazon_userplaylist' AND DOWNLOAD_ACTIVITY_DATE >= '2026-01-01' GROUP BY ISRC, DOWNLOAD_ACTIVITY_DATE ORDER BY DOWNLOAD_ACTIVITY_DATE ASC question: I want to see daily Amazon user playlist streams for track USSM12405456 from the beginning of 2026. verified_at: 1777891641 verified_by: Rain Bomberg use_as_onboarding_question: false - name: '"What are song''s USSM16901106 total active Spotify streams between 7th and 8th of February 2026."' sql: |- SELECT ISRC, ACTIVITY_TYPE, SUM(VALUE) AS total_streams FROM DEV_ENGINEERING.DEV_RBOMBERG.V_STREAMS_SOS_UNPIVOTED WHERE ISRC = 'USSM16901106' AND DSP = 'SPOTIFY' AND ACTIVITY_TYPE = 'ACTIVE' AND DOWNLOAD_ACTIVITY_DATE BETWEEN '2026-02-07' AND '2026-02-08' GROUP BY ISRC, ACTIVITY_TYPE question: What are song's USSM16901106 total active Spotify streams between 7th and 8th of February 2026. verified_at: 1777891646 verified_by: Rain Bomberg use_as_onboarding_question: false - name: '"Show Spotify source of streams breakdown for The Orchard tracks with ISRC USSM12501572 in January 2026."' sql: |- SELECT s.STREAM_SOURCE, s.BUSINESS_NAME, s.SOURCE_TYPE, SUM(s.VALUE) AS total_streams FROM DEV_ENGINEERING.DEV_RBOMBERG.V_STREAMS_SOS_UNPIVOTED s JOIN DEV_ENGINEERING.DEV_RBOMBERG.CHATBOT_ISRC_BRAND_MAPPING b ON s.ISRC = b.ISRC WHERE s.ISRC = 'USSM12501572' AND s.DSP = 'SPOTIFY' AND s.DOWNLOAD_ACTIVITY_DATE BETWEEN '2026-01-01' AND '2026-01-31' AND b.COMPANY_BRAND = 'The Orchard' GROUP BY s.STREAM_SOURCE, s.BUSINESS_NAME, s.SOURCE_TYPE ORDER BY total_streams DESC question: Show Spotify source of streams breakdown for The Orchard tracks with ISRC USSM12501572 in January 2026. verified_at: 1777891654 verified_by: Rain Bomberg use_as_onboarding_question: false - name: '"Show daily Spotify playlist streams for track USSM12501572 in February 2026"' sql: |- SELECT ISRC, DOWNLOAD_ACTIVITY_DATE, SUM(VALUE) AS playlist_streams FROM DEV_ENGINEERING.DEV_RBOMBERG.V_STREAMS_SOS_UNPIVOTED WHERE ISRC = 'USSM12501572' AND STREAM_SOURCE = 'streams_sos_spotify_playlists' AND DOWNLOAD_ACTIVITY_DATE BETWEEN '2026-02-01' AND '2026-02-28' GROUP BY ISRC, DOWNLOAD_ACTIVITY_DATE ORDER BY DOWNLOAD_ACTIVITY_DATE question: Show daily Spotify playlist streams for track USSM12501572 in February 2026 verified_at: 1777891670 verified_by: Rain Bomberg use_as_onboarding_question: false - name: '"What are track''s USSM19902989 daily Spotify artist page streams in Portugal between 23 and 25 of April 2026."' sql: |- SELECT ISRC, COUNTRY_CODE, DOWNLOAD_ACTIVITY_DATE, SUM(VALUE) AS artist_page_streams FROM DEV_ENGINEERING.DEV_RBOMBERG.V_STREAMS_SOS_UNPIVOTED WHERE ISRC = 'USSM19902989' AND COUNTRY_CODE = 'PT' AND STREAM_SOURCE = 'streams_sos_spotify_artistpage' AND DOWNLOAD_ACTIVITY_DATE BETWEEN '2026-04-23' AND '2026-04-25' GROUP BY ISRC, COUNTRY_CODE, DOWNLOAD_ACTIVITY_DATE ORDER BY DOWNLOAD_ACTIVITY_DATE ASC question: What are track's USSM19902989 daily Spotify artist page streams in Portugal between 23 and 25 of April 2026. verified_at: 1777891911 verified_by: Rain Bomberg use_as_onboarding_question: false module_custom_instructions: sql_generation: |- - MANDATORY: Always filter by ISRC. This table has billions of rows — unfiltered queries will time out. - This view is already unpivoted. Each row has a single STREAM_SOURCE with its VALUE. No need to UNPIVOT. - To filter by DSP, use the DSP column (values: SPOTIFY, APPLE, AMAZON, YOUTUBE). - To filter active vs passive sources, use the ACTIVITY_TYPE column (values: ACTIVE, PASSIVE). This works across all DSPs. - The SOURCE_TYPE column combines DSP + activity (e.g. SPOTIFY_ACTIVE, APPLE_PASSIVE) for DSP-specific filtering. - The STREAM_SOURCE column contains the original column name (e.g. streams_sos_spotify_albumpage). - The BUSINESS_NAME column contains the human-readable source name (e.g. Album Page, Collection). - To get total streams for a DSP: SUM(VALUE) WHERE DSP = 'SPOTIFY' - To get active streams for a DSP: SUM(VALUE) WHERE DSP = 'SPOTIFY' AND ACTIVITY_TYPE = 'ACTIVE' - To get all active streams across DSPs: SUM(VALUE) WHERE ACTIVITY_TYPE = 'ACTIVE' - PERFORMANCE: When filtering by all-time stream thresholds, always include a date floor of 2020-01-01 to limit scan range. - When user asks about a specific brand, JOIN to CHATBOT_ISRC_BRAND_MAPPING on ISRC and filter by COMPANY_BRAND. - Brand name mappings: "SME" = "Sony Music", "Orchard" = "The Orchard", "AWAL" = "AWAL". - When user does not specify a date range, default to the last 14 days. - COUNTRY_CODE is available for country-level breakdowns (2-letter ISO codes, e.g. US, GB, DE, PT).