name: CHARTS_SEMANATIC_VIEW description: This semantic view gives access to chart related data from major DSPs. tables: - name: CHART description: This table contains song/track placements on different charts provide by DSPs like Spotify, Amazon Music or Youtube. base_table: database: LORELAI_AGENT schema: DEV table: CHART_ARTIST_TRACK dimensions: - name: CHARTID synonyms: - chart id description: Unique identifier for each chart in the table. expr: CHARTID data_type: VARCHAR(16777216) sample_values: - 55ac2a4d-8362-4b1e-9952-d9cb64ed2804 - c996d25b-daca-4d5a-821a-b54af9177390 - fa8d96bd-fdbd-4b50-a9d5-fe479724a2ef - name: CM_ARTIST synonyms: - chartmetric id - cm_artist_id description: Unique identifier from Chartmertic for artist. expr: CM_ARTIST data_type: NUMBER(38,0) sample_values: - '5564704' - '1716405' - '13097801' - name: COUNTRY description: 'Short name of the country where particular chart belongs to. Usually contains 2 letter iso codes expect one special use case: GLOBAL. This represents world wide chart.' expr: COUNTRY data_type: VARCHAR(16777216) sample_values: - CX - ET - GLOBAL - US - GB - name: GENRE description: The music genre classification or category for chart. expr: GENRE data_type: VARCHAR(16777216) sample_values: - Latin Pop - Alternative Rock - Indie - name: ISRC synonyms: - song id - track id description: International Standard Recording Code used to uniquely identify sound recordings and music video recordings. expr: ISRC data_type: VARCHAR(16777216) sample_values: - CA5KR2603885 - QM4DW1628860 - QZK6F2389363 - name: PLATFORM synonyms: - digital service platform - dsp description: The music streaming or social media platform where chart belongs to. expr: PLATFORM data_type: VARCHAR(16777216) is_enum: true sample_values: - youtube - amazon - appleMusic - tiktok - deezer - shazam - spotify - linemusic - soundcloud - recochoku - name: PLAYLIST_NAME description: The name of the music chart. expr: PLAYLIST_NAME data_type: VARCHAR(16777216) sample_values: - Amazon Daily Forró - Amazon Daily [RE]DISCOVER - Amazon Daily Musicals & Cabaret - name: SOUND_RECORDING_NAME description: Name of the track or song expr: sound_recording_name data_type: VARCHAR(16777216) sample_values: - NO L'S (LOFI) - Life or Death - Sinking Boat - name: TYPE_OF_PLAYLIST description: Shows if track/song placement on the chart is currently active or took place in the past. expr: TYPE data_type: VARCHAR(7) is_enum: true sample_values: - CURRENT - PAST time_dimensions: - name: ENTRY_TIMESTAMP description: The date of the first timestamp when song/track entered the chart. expr: ENTRY_TIMESTAMP data_type: DATE sample_values: - '2019-09-19' - '2023-03-28' - '2023-12-07' - name: MOST_RECENT_TIMESTAMP description: The most recent timestamp when the placement in the chart was updated for particular song/track. expr: MOST_RECENT_TIMESTAMP data_type: DATE sample_values: - '2021-01-20' - '2024-12-31' - '2023-04-18' - name: PEAK_TIMESTAMP description: The date when track/song achieved it's highest placement on particular chart. expr: PEAK_TIMESTAMP data_type: DATE sample_values: - '2023-12-25' - '2025-09-09' - '2025-03-09' facts: - name: DAYS_ON_CHART description: The number of days a song/track has been on the chart. expr: DAYS_ON_CHART data_type: NUMBER(19,0) access_modifier: public_access sample_values: - '286' - '11' - '39' - name: FIRST_POSITION synonyms: - entry position description: First position song/track achieved when placed on the chart. expr: FIRST_POSITION data_type: NUMBER(38,0) access_modifier: public_access sample_values: - '87' - '75' - '8994' - name: MOST_RECENT_POSITION description: The most recent position of song/track on a chart. expr: MOST_RECENT_POSITION data_type: NUMBER(38,0) access_modifier: public_access sample_values: - '132' - '93' - '353' - name: PEAK_POSITION description: The highest ranking position song/track has reached on the chart. expr: PEAK_POSITION data_type: NUMBER(38,0) access_modifier: public_access sample_values: - '31' - '91' - '7882' metrics: - name: BEST_CHART_POSITION description: Best/highest position on the chart for a track. expr: MIN(PEAK_POSITION) access_modifier: public_access - name: MAX_DAYS_ON_CHART description: Total days track has been on the chart. expr: MAX(DAYS_ON_CHART) access_modifier: public_access - 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 description: The brand or company name associated with a music distribution or rights management organization. expr: COMPANY_BRAND data_type: VARCHAR(16777216) is_enum: true sample_values: - Above Board - Magic Star Kids - Sony Music - Foundation Media - DRM - Altafonte - The Orchard - Mass Appeal - AWAL - Human Re:Sources - Kollective Neighbouring Rights - name: ISRC synonyms: - song id - track id description: International Standard Recording Code (ISRC) uniquely identifying a recorded track. expr: ISRC data_type: VARCHAR(16777216) sample_values: - US4R31335980 - QM4TX2373168 - QM4TX1709621 primary_key: columns: - ISRC - name: CM_TRACK description: The table contains records of musical tracks. Each record represents a single track and includes identifying information, metadata, and associated media references. base_table: database: DELPHI_EXPLORATION schema: CHARTMETRIC table: CM_TRACK dimensions: - name: ISRC synonyms: - song id - track id description: International Standard Recording Code uniquely identifying sound recordings and music video recordings. expr: ISRC data_type: VARCHAR(16777216) sample_values: - QZE2Q1800006 - QZES82419585 - QZEQU2040505 - name: NAME description: The name of the music track/song. expr: NAME data_type: VARCHAR(16777216) sample_values: - 'Part 26 - War and Peace - Book 7: 1810-11' - Kapitel 16 - Die Augen der Jolante (Der Detektiv-Harald Harst, Folge 7) - 'Double Concerto in F Major, Hob.XVIII:6: I. Allegro moderato' primary_key: columns: - ISRC - name: DIM_CHART description: The table contains records of music or media charts tracked across different platforms and markets. Each record represents a distinct chart and includes details about its geographic scope, content classification, publication schedule, and platform-specific identifiers. base_table: database: FACTS schema: PROD table: DIM_CHART dimensions: - name: CHARTID description: Unique identifier for each chart. expr: CHARTID data_type: VARCHAR(16777216) sample_values: - c2c9059f-3227-4fb9-96f8-38392353775a - 95b4d6e3-e1b9-46d4-afd7-2560ec400bad - 931c6732-7fac-45ca-97ce-de23f9823c24 - name: COUNTRY description: Name of the country where chart belongs to. expr: COUNTRY data_type: VARCHAR(16777216) sample_values: - NE - CRT - DE - GB - US - GLOBAL - name: NAME description: The name of the music chart. expr: NAME data_type: VARCHAR(16777216) sample_values: - New Music Friday HK - Amazon Daily Holiday - New Music Friday DE primary_key: columns: - CHARTID - name: FACT_CHARTS description: The table contains records of music chart performance metrics tracked over time. Each record represents a single chart position entry and includes streaming data, ranking information, and identifiers linking to tracks, artists, videos, and products. base_table: database: FACTS schema: PROD table: FACT_CHARTS dimensions: - name: CHARTID description: Unique identifier for each chart. expr: CHARTID data_type: VARCHAR(16777216) sample_values: - ec34d926-a1ed-45a7-8bc1-b0b7657b20be - eb6caec0-c107-4ddd-8f6a-635fe176c9a8 - ebcd7f04-6082-46d5-a233-59e4ce368d58 - name: ISRC synonyms: - song id - track id description: International Standard Recording Code uniquely identifying sound recordings and music video recordings. expr: ISRC data_type: VARCHAR(16777216) sample_values: - BGA741800556 - USQX91702676 - FIUM71800012 time_dimensions: - name: CHART_DATE description: The date associated with the chart record. expr: CHART_DATE data_type: DATE sample_values: - '2022-09-01' - '2025-06-19' - '2025-03-28' facts: - name: POSITION synonyms: - placement - rank description: The position or rank of a song/track on chart on a given day. expr: POSITION data_type: NUMBER(38,0) access_modifier: public_access sample_values: - '13' - '33' - '99' primary_key: columns: - CHARTID - ISRC - CHART_DATE relationships: - name: CHART_TO_ISRC_BRAND_MAPPING left_table: CHART right_table: ISRC_BRAND_MAPPING relationship_columns: - left_column: ISRC right_column: ISRC - name: FACT_CHARTS_TO_ISRC_BRAND_MAPPING left_table: FACT_CHARTS right_table: ISRC_BRAND_MAPPING relationship_columns: - left_column: ISRC right_column: ISRC - name: FACT_CHARTS_TO_CM_TRACK left_table: FACT_CHARTS right_table: CM_TRACK relationship_columns: - left_column: ISRC right_column: ISRC - name: FACT_CHARTS_TO_DIM_CHART left_table: FACT_CHARTS right_table: DIM_CHART relationship_columns: - left_column: CHARTID right_column: CHARTID verified_queries: - name: '"Can you show me chart data for chart with id f270a2f6-fb82-4be8-9923-56d53e507fe8 and track isrc NL8RL2610976 for a artist with id 214945? I am interested in Australia(AU)."' sql: |- SELECT * FROM chart WHERE chartid = 'f270a2f6-fb82-4be8-9923-56d53e507fe8' AND isrc = 'NL8RL2610976' AND cm_artist = 214945 AND country = 'AU' /* Generated by Cortex Analyst (request_id: 5cae340b-5c37-48f4-9105-4d4328dfef5e) */ question: Can you show me chart data for chart with id f270a2f6-fb82-4be8-9923-56d53e507fe8 and track isrc NL8RL2610976 for a artist with id 214945? I am interested in Australia(AU). verified_at: 1771398313 verified_by: Rain Bomberg use_as_onboarding_question: false - name: '"What are the names of the tracks that artist 214945 have on the active chart with id ed4a6a27-4c96-499b-9191-c220cf25af82 for Australia (AU)? "' sql: |- SELECT DISTINCT sound_recording_name FROM chart WHERE cm_artist = 214945 AND chartid = 'ed4a6a27-4c96-499b-9191-c220cf25af82' AND country = 'AU' AND type_of_playlist = 'CURRENT' /* Generated by Cortex Analyst (request_id: 47cee7a4-1721-4228-bd32-2bab9b3726a0) */ question: 'What are the names of the tracks that artist 214945 have on the active chart with id ed4a6a27-4c96-499b-9191-c220cf25af82 for Australia (AU)? ' verified_at: 1771398616 verified_by: Rain Bomberg use_as_onboarding_question: false - name: '"What are the names of the tracks and charts that artist 214945 have on the active chart with for UK? I am only interested in songs that are at highest on position 5."' sql: |- SELECT DISTINCT sound_recording_name, playlist_name, peak_position FROM chart WHERE cm_artist = 214945 AND country = 'GB' AND type_of_playlist = 'CURRENT' AND peak_position <= 5 order by sound_recording_name, playlist_name question: What are the names of the tracks and charts that artist 214945 have on the active chart with for UK? I am only interested in songs that are at highest on position 5. verified_at: 1776929924 verified_by: Rain Bomberg use_as_onboarding_question: false - name: '"What are tracks QMFMF2447057 placements in world wide or US based charts? I am only interested in top 10 positions."' sql: |- SELECT DISTINCT sound_recording_name, playlist_name, country, peak_position, platform, type_of_playlist FROM chart WHERE isrc = 'QMFMF2447057' AND ( country = 'GLOBAL' OR country = 'US' ) AND most_recent_position <= 10 and type_of_playlist IN ('CURRENT', 'PAST') ORDER BY country, peak_position asc /* Generated by Cortex Analyst (request_id: f8a86313-3724-4360-a171-2c61849733e0) */ question: What are tracks QMFMF2447057 placements in world wide or US based charts? I am only interested in top 10 positions. verified_at: 1776933603 verified_by: Rain Bomberg use_as_onboarding_question: false - name: '"What are track''s QMFME2535037 historical placements on the chart 34f76bdb-4f5b-4917-a47e-02916f7291f5 this year?"' sql: |- SELECT fc.chart_date, fc.position, ct.name AS track_name, dc.name AS chart_name FROM fact_charts AS fc LEFT OUTER JOIN cm_track AS ct ON fc.isrc = ct.isrc LEFT OUTER JOIN dim_chart AS dc ON fc.chartid = dc.chartid WHERE fc.isrc = 'QMFME2535037' AND fc.chartid = '34f76bdb-4f5b-4917-a47e-02916f7291f5' AND DATE_PART('YEAR', fc.chart_date) = 2026 ORDER BY fc.chart_date DESC NULLS LAST /* Generated by Cortex Analyst (request_id: 809e9b96-1007-4ab6-b69d-5ec913075819) */ question: What are track's QMFME2535037 historical placements on the chart 34f76bdb-4f5b-4917-a47e-02916f7291f5 this year? verified_at: 1776933613 verified_by: Rain Bomberg use_as_onboarding_question: false - name: '"I want to see daily comparison of charts ''34f76bdb-4f5b-4917-a47e-02916f7291f5'' and ''2934be11-59d5-488b-b94b-76151a1e1887'' for track QMFME2535037. Look at period from 1st of January 2026 until 15th of January."' sql: |- SELECT fc.chart_date, dc.name AS chart_name, dc.country as chart_country, fc.position, ct.name AS track_name FROM fact_charts AS fc JOIN dim_chart dc ON fc.chartid = dc.chartid LEFT JOIN cm_track ct ON fc.isrc = ct.isrc WHERE fc.isrc = 'QMFME2535037' AND fc.chartid IN ( '34f76bdb-4f5b-4917-a47e-02916f7291f5', '2934be11-59d5-488b-b94b-76151a1e1887' ) AND fc.chart_date BETWEEN '2026-01-01' AND '2026-01-15' ORDER BY fc.chart_date DESC, dc.country, dc.name question: I want to see daily comparison of charts '34f76bdb-4f5b-4917-a47e-02916f7291f5' and '2934be11-59d5-488b-b94b-76151a1e1887' for track QMFME2535037. Look at period from 1st of January 2026 until 15th of January. verified_at: 1776934780 verified_by: Rain Bomberg use_as_onboarding_question: false - name: '"Current placement on Spotify Daily Top 200 chart (chart name contains daily)"' sql: |- SELECT MOST_RECENT_POSITION, MOST_RECENT_TIMESTAMP, SOUND_RECORDING_NAME, PLAYLIST_NAME, PEAK_POSITION, DAYS_ON_CHART, TYPE_OF_PLAYLIST FROM CHART WHERE ISRC = 'QMFME2535037' AND CHARTID = '34f76bdb-4f5b-4917-a47e-02916f7291f5' AND COUNTRY = 'GLOBAL' AND TYPE_OF_PLAYLIST IN ('CURRENT','PAST') question: What is the current placement of track QMFME2535037 on the Spotify Daily Top 200 chart (34f76bdb-4f5b-4917-a47e-02916f7291f5) for GLOBAL? verified_at: 1776933664 verified_by: Rain Bomberg use_as_onboarding_question: false - name: '"Give me ten tracks from AWAL on spotify global charts"' sql: |- SELECT DISTINCT ac.sound_recording_name, ac.playlist_name, ac.most_recent_position, ac.type_of_playlist FROM chart AS ac LEFT OUTER JOIN isrc_brand_mapping AS ibm ON ac.isrc = ibm.isrc WHERE ibm.company_brand = 'AWAL' AND ac.platform = 'spotify' AND ac.country = 'GLOBAL' AND ac.type_of_playlist IN('CURRENT','PAST') ORDER BY ac.most_recent_position ASC LIMIT 10 /* Generated by Cortex Analyst (request_id: 5e3378a4-0142-46be-aca0-df2a04a972ab) */ question: Give me ten tracks from AWAL on spotify global charts verified_at: 1776933717 verified_by: Rain Bomberg use_as_onboarding_question: false - name: '"What are the top 10 current chart placements for artist 214945 across all platforms?"' sql: |- SELECT sound_recording_name, playlist_name, platform, country, most_recent_position, most_recent_timestamp, peak_position, days_on_chart FROM chart WHERE cm_artist = 214945 AND type_of_playlist = 'CURRENT' ORDER BY most_recent_position ASC LIMIT 10 question: What are the top 10 current chart placements for artist 214945 across all platforms? verified_at: 1776934021 verified_by: Rain Bomberg use_as_onboarding_question: false - name: '"Top 10 Orchard tracks on Spotify charts in the US. Look at current/active positions."' sql: |- SELECT DISTINCT ac.sound_recording_name, ac.playlist_name, ac.platform, ac.most_recent_position, ac.most_recent_timestamp, ac.peak_position FROM chart AS ac JOIN isrc_brand_mapping AS ibm ON ac.isrc = ibm.isrc WHERE ibm.company_brand = 'The Orchard' AND ac.country = 'US' AND ac.platform = 'spotify' AND ac.type_of_playlist = 'CURRENT' ORDER BY ac.most_recent_position ASC LIMIT 10 question: Top 10 Orchard tracks on Spotify charts in the US. Look at current/active positions. verified_at: 1776934130 verified_by: Rain Bomberg use_as_onboarding_question: false - name: '"Show daily positions for track QMFME2535037 on chart 34f76bdb-4f5b-4917-a47e-02916f7291f5 since January 2026"' sql: |- SELECT fc.chart_date, fc.position, ct.name AS track_name, dc.name AS chart_name, dc.country AS chart_country FROM fact_charts AS fc LEFT JOIN cm_track AS ct ON fc.isrc = ct.isrc LEFT JOIN dim_chart AS dc ON fc.chartid = dc.chartid WHERE fc.isrc = 'QMFME2535037' AND fc.chartid = '34f76bdb-4f5b-4917-a47e-02916f7291f5' AND fc.chart_date >= '2026-01-01' ORDER BY fc.chart_date DESC LIMIT 15 question: Show daily positions for track QMFME2535037 on chart 34f76bdb-4f5b-4917-a47e-02916f7291f5 since January 2026 verified_at: 1776934179 verified_by: Rain Bomberg use_as_onboarding_question: false - name: '"Tracks on Spotify charts currently in 1 position and sorted by their days on chart"' sql: |- SELECT DISTINCT sound_recording_name, playlist_name, country, most_recent_position, peak_position, days_on_chart, entry_timestamp, most_recent_timestamp FROM chart WHERE platform = 'spotify' AND type_of_playlist = 'CURRENT' AND most_recent_position = 1 ORDER BY days_on_chart DESC NULLS LAST LIMIT 10 /* Generated by Cortex Analyst (request_id: 077cebe1-5860-4068-94f2-5017297fb133) */ question: Tracks on Spotify charts currently in 1 position and sorted by their days on chart verified_at: 1776934504 verified_by: Rain Bomberg use_as_onboarding_question: false module_custom_instructions: sql_generation: |- If questions are about general chart placement then use table CHART. - NOTE: Chart names like "Daily Top 200", "Daily Viral 50" contain "daily" in the chart NAME - this does NOT mean daily/historical data is requested. - If user doesn't specify chart type then use current/past like this: type_of_playlist IN('CURRENT', 'PAST'). - Only use FACT_CHARTS and its related relations when the user explicitly asks for daily TRENDS, historical positions over time, or time-series data. If user hasn't specified date/period then BY DEFAULT take most recent 15 rows per window function. - When users ask about tracks for a specific brand (SME, Orchard, AWAL, etc.), JOIN to ISRC_BRAND_MAPPING on ISRC and filter by COMPANY_BRAND. - Brand name mappings: "SME" = "Sony Music", "Orchard" = "The Orchard", "AWAL" = "AWAL". - Not all chart ISRCs have a brand mapping. Only tracks distributed through Orchard/SME/AWAL ecosystem will match.