# Luminate Sample Queries

Common analyst questions mapped to complete, ready-to-adapt SQL. Each query follows the universal rules from `query-standards.md` and Luminate-specific rules from `query-guide.md`.

> **Usage**: Always verify column names via `DESCRIBE TABLE` before running. All queries require REPORT_DATE (or model equivalent), COUNTRY_CODE, and METRIC_CATEGORY filters.

## How many streams does a song have this year?

**Source**: `LUMINATE_MODELS.PROD.YTD_SONG_SUMMARY` — pre-aggregated YTD model, no date filter needed.

```sql
SELECT
    SONG_ID,
    COUNTRY_CODE,
    SUM(QUANTITY) AS ytd_streams
FROM LUMINATE_MODELS.PROD.YTD_SONG_SUMMARY
WHERE SONG_ID = '<song_id>'
  AND COUNTRY_CODE = 'US'
  AND METRIC_CATEGORY = 'Streams'
GROUP BY ALL;
```

**Adapt**: Swap `SONG_ID` for `ARTIST_ID`, `MR_ID`, etc. and use the corresponding YTD model.

## Total streams for all songs on an album

**Source**: Summary fact + mapping shortcut — joins song-level data to album via `VW_SONG_MRELG_MAP_DS`.

```sql
SELECT
    m.MRELG_ID,
    SUM(f.QUANTITY) AS total_streams
FROM LUMINATE_DB_LISTING_DETAIL.EXTRACT_S.VW_DAILY_FACT_SONG_SUMMARY_DS f
JOIN LUMINATE_DB_LISTING_DETAIL.EXTRACT_S.VW_SONG_MRELG_MAP_DS m
  ON f.SONG_ID = m.SONG_ID
WHERE m.MRELG_ID = '<mrelg_id>'
  AND f.COUNTRY_CODE = 'US'
  AND f.METRIC_CATEGORY = 'Streams'
  AND f.REPORT_DATE BETWEEN '2026-01-01' AND '2026-03-31'
GROUP BY m.MRELG_ID;
```

**Caution**: Song ↔ Album is many-to-many. If a song appears on multiple albums, its streams count toward each. See `entity-relationships.md` for details.

## Streaming by metro market (US/CA only)

**Source**: `VW_DAILY_FACT_SONG_DETAIL_DS` — Detail view required for metro-level data.

```sql
SELECT
    f.SONG_ID,
    f.MARKET_NAME,
    SUM(f.QUANTITY) AS total_streams
FROM LUMINATE_DB_LISTING_DETAIL.EXTRACT_S.VW_DAILY_FACT_SONG_DETAIL_DS f
WHERE f.SONG_ID = '<song_id>'
  AND f.COUNTRY_CODE = 'US'
  AND f.MARKET_ID != -1  -- metro markets only (exclude national to avoid double-count)
  AND f.METRIC_CATEGORY = 'Streams'
  AND f.REPORT_DATE BETWEEN '2026-01-01' AND '2026-01-07'
GROUP BY f.SONG_ID, f.MARKET_NAME
ORDER BY total_streams DESC;
```

**Critical**: `MARKET_ID != -1` excludes the national row. Without this, metro + national data double-counts.

## Monthly streaming trend for a recording (ISRC)

**Source**: `LUMINATE_MODELS.PROD.MONTHLY_MR_SUMMARY` — monthly grain, no date-range filter needed.

```sql
SELECT
    MR_ID,
    MONTH_START_DATE,
    SUM(QUANTITY) AS monthly_streams
FROM LUMINATE_MODELS.PROD.MONTHLY_MR_SUMMARY
WHERE MR_ID = '<mr_id>'
  AND COUNTRY_CODE = 'US'
  AND METRIC_CATEGORY = 'Streams'
GROUP BY MR_ID, MONTH_START_DATE
ORDER BY MONTH_START_DATE;
```

## Find all ISRCs on a product (barcode)

**Source**: `VW_MR_MP_MAP_DS` — mapping table, no fact data involved.

```sql
SELECT m.MR_ID, m.ISRC, m.MP_ID, m.ICPN
FROM LUMINATE_DB_LISTING_DETAIL.EXTRACT_S.VW_MR_MP_MAP_DS m
WHERE m.ICPN = '<barcode_value>';
```

## Discover valid values for a filter column

**Source**: `VW_FACT_VALUES_DS` — Luminate-specific reference table listing all valid metric categories and breakout values.

```sql
SELECT METRIC_CATEGORY, METRIC_BREAKOUT, BREAKOUT_VALUE
FROM LUMINATE_DB_LISTING_DETAIL.EXTRACT_S.VW_FACT_VALUES_DS
ORDER BY METRIC_CATEGORY, METRIC_BREAKOUT, BREAKOUT_VALUE;
```

## What are the top 10 tracks with most streams in US for this year mapped to distributor

**Source**: `LUMINATE_MODELS.PROD.YTD_MR_SUMMARY` joined to `VW_MUSICAL_RECORDING_DS` and `VW_RIGHT_HOLDER_DS`.

```sql
select 
    mr.display_artist,
    mr.title,
    mr.isrc,
    rd.business_unit_name as distributor,
    sum(ytd.quantity) as ytd_streams
-- internal ytd mr model
from luminate_models.prod.ytd_mr_summary ytd
-- join to recording metadata
join luminate_db_listing_detail.extract_s.vw_musical_recording_ds mr 
    on ytd.mr_id = mr.mr_id
-- join to distributor of recordings
left join LUMINATE_DB_LISTING_DETAIL.EXTRACT_S.VW_RIGHT_HOLDER_DS rd 
     -- entity_id corresponds to mr_id 
    on rd.entity_id = mr.mr_id
    -- filter to distributor owner
    and rd.business_unit_role = 'DISTRIBUTOR'
    -- filter to active distributor of recording
    and rd.start_date <= current_date() 
    and rd.end_date >= current_date()
where 
    ytd.country_code = 'US'
    and ytd.metric_category = 'Streams'
    and ytd.content_type = 'Audio'
group by all
order by sum(ytd.quantity) desc 
limit 10;
```

## How can I pull a specific song chart for one week and map to distributor

**Source**: `luminate_models.prod.ytd_mr_summary` — pre-aggregated YTD model, no date filter needed.

```sql
with params as (
    select 
        'CR01006' as chart_id,
        202606 as week_id,
        'US' as country_code
),
-- since distributor is only at recording level, map song_ids to mr_ids
isrc_map AS (
SELECT cd.entity_id, mr_song_map.mr_id 
FROM LUMINATE_DB_LISTING_DETAIL.EXTRACT_S.VW_CHART_DATA_DS cd
-- map song_id to all mr_id
JOIN luminate_db_listing_detail.extract_s.vw_mr_song_map_ds as mr_song_map 
    ON cd.entity_id = mr_song_map.song_id
-- use param filter for chart_id / week
CROSS JOIN params p 
where 
    cd.chart_id = p.chart_id
    and cd.week_id = p.week_id
group by all
),
-- use the top isrc/mr_id from each song_id as basis for mapping song_id
    -- find top isrc by using internal rtd model (luminate_models.prod.rtd_mr_summary)
top_isrc_table AS ( 
  SELECT
    t.entity_id,
    t.mr_id,
    b.ISRC,
    rd.business_unit_name as distributor,
    SUM(a.quantity) AS streaming_on_demand_total_current_day
  FROM isrc_map t
  JOIN luminate_models.prod.rtd_mr_summary a
    ON a.mr_id = t.mr_id
  JOIN luminate_db_listing_detail.extract_s.vw_musical_recording_ds b
    ON a.mr_id = b.mr_id
  CROSS JOIN params p 
  -- join to right holder 
  join LUMINATE_DB_LISTING_DETAIL.EXTRACT_S.VW_RIGHT_HOLDER_DS rd 
    on rd.entity_id = t.mr_id
   -- filter to active distributor
    and rd.business_unit_role = 'DISTRIBUTOR'
    and rd.start_date <= current_date() 
    and rd.end_date >= current_date()
   AND a.country_code = p.country_code
  WHERE a.service_type = 'OnDemand'
    AND a.metric_category = 'Streams'
  GROUP BY t.entity_id, t.mr_id, b.ISRC,rd.business_unit_name
  -- keep only the top isrc per song_id
  QUALIFY ROW_NUMBER() OVER (PARTITION BY t.entity_id ORDER BY streaming_on_demand_total_current_day DESC)  <= 1
)
select 
ld.week_end_date,
    sd.display_artist,
    sd.title,
    t.distributor,
    cd.rank,
    -- pull out chart_history values to columns
    cd.CHART_HISTORY:"LOWEST_POSITION"::NUMBER AS LOWEST_POSITION,
    cd.CHART_HISTORY:"PEAK_POSITION"::NUMBER AS PEAK_POSITION,
    cd.CHART_HISTORY:"PREMIERE_WEEK"::NUMBER AS PREMIERE_WEEK,
    cd.CHART_HISTORY:"WEEKS_ON_CHART"::NUMBER AS WEEKS_ON_CHART,
    -- pull out chart_metrics values to columns
    cd.CHART_METRICS:"AD_SUPPORTED_STREAMS"::NUMBER AS AD_SUPPORTED_STREAMS,
    cd.CHART_METRICS:"AUDIO_STREAMS"::NUMBER AS AUDIO_STREAMS,
    cd.CHART_METRICS:"PREMIUM_STREAMS"::NUMBER AS PREMIUM_STREAMS,
    cd.CHART_METRICS:"SONG_EQUIVALENT"::NUMBER AS SONG_EQUIVALENT,
    cd.CHART_METRICS:"SONG_EQUIVALENT_AD_SUPPORTED_STREAMS"::NUMBER AS SONG_EQUIVALENT_AD_SUPPORTED_STREAMS,
    cd.CHART_METRICS:"SONG_EQUIVALENT_AUDIO_STREAMS"::NUMBER AS SONG_EQUIVALENT_AUDIO_STREAMS,
    cd.CHART_METRICS:"SONG_EQUIVALENT_PREMIUM_STREAMS"::NUMBER AS SONG_EQUIVALENT_PREMIUM_STREAMS,
    cd.CHART_METRICS:"SONG_EQUIVALENT_SONG_SALES"::NUMBER AS SONG_EQUIVALENT_SONG_SALES,
    cd.CHART_METRICS:"SONG_EQUIVALENT_STREAMS"::NUMBER AS SONG_EQUIVALENT_STREAMS,
    cd.CHART_METRICS:"SONG_EQUIVALENT_VIDEO_STREAMS"::NUMBER AS SONG_EQUIVALENT_VIDEO_STREAMS,
    cd.CHART_METRICS:"SONG_SALES"::NUMBER AS SONG_SALES,
    cd.CHART_METRICS:"VIDEO_STREAMS"::NUMBER AS VIDEO_STREAMS
from luminate_db_listing_detail.extract_s.vw_chart_data_ds cd  
-- pull in vw_date_ds to get week_end_date of chart 
left join luminate_db_listing_detail.extract_s.vw_date_ds ld
    on cd.week_id = ld.weekid
-- pull in song metadata using entity_id ->song_id since it's a song chart
left join luminate_db_listing_detail.extract_s.vw_song_ds sd 
    on cd.entity_id = sd.song_id
-- pull in distributor of top isrc from song
left join top_isrc_table t 
    on t.entity_id = cd.entity_id
cross join params p 
where cd.chart_id = p.chart_id
    and cd.week_id = p.week_id
group by all 
order by rank;
```