# Luminate Internal Models (LUMINATE_MODELS.PROD)

## Overview

The `LUMINATE_MODELS.PROD` schema contains pre-aggregated tables derived from the datashare's Summary Fact views. These models exist to improve query performance — use them instead of the raw datashare when their grain and coverage match the analyst's need.

## Period-End Aggregate Models

These aggregate all available quantities for an entity up to the current reporting period boundary.

**Naming pattern:** `LUMINATE_MODELS.PROD.{PERIOD}_{ENTITY}_SUMMARY`

**Available periods:**
| Period | Meaning |
|--------|---------|
| `RTD` | Release to Date — all activity since the entity's first appearance |
| `WTD` | Week to Date — activity from the start of the current chart week |
| `YTD` | Year to Date — activity from January 1 of the current year |
| `RTCW` | Release to Chart Week — activity from release through the current chart week |

**Available entities:** Artist, MR, MP, MREL, MRELG, Song

**Full list of period-end views:**
- `RTD_ARTIST_SUMMARY`, `WTD_ARTIST_SUMMARY`, `YTD_ARTIST_SUMMARY`, `RTCW_ARTIST_SUMMARY`
- `RTD_MR_SUMMARY`, `WTD_MR_SUMMARY`, `YTD_MR_SUMMARY`, `RTCW_MR_SUMMARY`
- `RTD_MP_SUMMARY`, `WTD_MP_SUMMARY`, `YTD_MP_SUMMARY`, `RTCW_MP_SUMMARY`
- `RTD_MREL_SUMMARY`, `WTD_MREL_SUMMARY`, `YTD_MREL_SUMMARY`, `RTCW_MREL_SUMMARY`
- `RTD_MRELG_SUMMARY`, `WTD_MRELG_SUMMARY`, `YTD_MRELG_SUMMARY`, `RTCW_MRELG_SUMMARY`
- `RTD_SONG_SUMMARY`, `WTD_SONG_SUMMARY`, `YTD_SONG_SUMMARY`, `RTCW_SONG_SUMMARY`

**Columns:** Mirror the corresponding Summary Fact view columns, EXCEPT:
- `REPORT_DATE` is **removed** (data is pre-aggregated to the period level)
- `MODIFIED_AT` is **removed**
- `REFRESHED_AT` is **added** — timestamp of when the model was last refreshed

## Monthly Aggregate Models

Aggregate quantities at the calendar-month grain. Refreshed on the 5th of every month with all historical data for completed months.

**Naming pattern:** `LUMINATE_MODELS.PROD.MONTHLY_{ENTITY}_SUMMARY`

**Available entities (monthly only):** Artist, MR, MP

**Full list of monthly views:**
- `MONTHLY_ARTIST_SUMMARY`
- `MONTHLY_MR_SUMMARY`
- `MONTHLY_MP_SUMMARY`

**Columns:** Mirror the corresponding Summary Fact view columns, EXCEPT:
- `REPORT_DATE` is **removed**
- `MODIFIED_AT` is **removed**
- `MONTH_START_DATE` is **added** — first day of the month
- `REFRESHED_AT` is **added** — timestamp of when the model was last refreshed

Note: Monthly models are NOT available for Song, MREL, or MRELG entities. For monthly aggregates on those entities, aggregate the datashare Summary views manually using DATE_TRUNC('MONTH', REPORT_DATE).

## When to Use Internal Models vs Datashare

| Need | Use |
|------|-----|
| Year-to-date total streams for an artist | `LUMINATE_MODELS.PROD.YTD_ARTIST_SUMMARY` |
| All-time (release-to-date) streams for a song | `LUMINATE_MODELS.PROD.RTD_SONG_SUMMARY` |
| Monthly trend for a recording (MR) | `LUMINATE_MODELS.PROD.MONTHLY_MR_SUMMARY` |
| Daily-grain data for a specific date range | Datashare Summary/Detail views (models don't have daily grain) |
| Market-level (metro) breakdown | Datashare Detail views (models are national only) |
| Provider-level breakdown | Datashare Provider views (models don't include provider info) |
| Weekly chart-week aggregation | Datashare or RTCW model for current chart week |
| Custom date range aggregation | Datashare Summary views (models use fixed period boundaries) |

## Sample Queries

### Year-to-date streams for an artist
```sql
SELECT
    ARTIST_ID,
    COUNTRY_CODE,
    SERVICE_TYPE,
    CONTENT_TYPE,
    COMMERCIAL_MODEL,
    SUM(QUANTITY) AS ytd_streams
FROM LUMINATE_MODELS.PROD.YTD_ARTIST_SUMMARY
WHERE ARTIST_ID = '<artist_id>'
  AND COUNTRY_CODE = 'US'
  AND METRIC_CATEGORY = 'Streams'
GROUP BY ALL;
```

### Monthly streaming trend for a recording
```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;
```

### Compare all-time vs current-year streams
```sql
SELECT
    r.ARTIST_ID,
    r.COUNTRY_CODE,
    SUM(r.QUANTITY) AS rtd_streams,
    SUM(y.QUANTITY) AS ytd_streams
FROM LUMINATE_MODELS.PROD.RTD_ARTIST_SUMMARY r
JOIN LUMINATE_MODELS.PROD.YTD_ARTIST_SUMMARY y
  ON r.ARTIST_ID = y.ARTIST_ID
  AND r.COUNTRY_CODE = y.COUNTRY_CODE
  AND r.METRIC_CATEGORY = y.METRIC_CATEGORY
  AND r.SERVICE_TYPE = y.SERVICE_TYPE
  AND r.CONTENT_TYPE = y.CONTENT_TYPE
  AND r.COMMERCIAL_MODEL = y.COMMERCIAL_MODEL
  AND r.STORE_STRATA = y.STORE_STRATA
  AND r.DISTRIBUTION_CHANNEL = y.DISTRIBUTION_CHANNEL
  AND r.PURCHASE_METHOD = y.PURCHASE_METHOD
  AND r.PRODUCT_FORMAT = y.PRODUCT_FORMAT
  AND r.RELEASE_TYPE = y.RELEASE_TYPE
WHERE r.ARTIST_ID = '<artist_id>'
  AND r.COUNTRY_CODE = 'US'
  AND r.METRIC_CATEGORY = 'Streams'
GROUP BY r.ARTIST_ID, r.COUNTRY_CODE;
```

## Checking Freshness

All internal models include `REFRESHED_AT`. See `data-overview.md` for the full refresh schedule.

```sql
SELECT MAX(REFRESHED_AT) FROM LUMINATE_MODELS.PROD.YTD_ARTIST_SUMMARY;
```