# Luminate Query Guide

> **Note**: Universal query standards (value discovery, SUM aggregation, catalog-first approach, date-range interpretation) are in the parent skill's `references/query-standards.md`. This file covers Luminate-specific query rules only.

## Required Filters

Every consumption query MUST include these three filters:

1. **REPORT_DATE** (or `WEEK_ID` for weekly views) — limits the time range
2. **COUNTRY_CODE** — ISO 3166-1 alpha-2 (e.g., 'US', 'CA', 'GB', 'AA' for worldwide)
3. **METRIC_CATEGORY** — separates streaming from sales. Discover exact values using the process in "Discovering Valid Filter Values" below

Omitting any of these produces inaccurate results: mixed metric types, unbounded scans, or cross-territory aggregation.

### MARKET_ID Rule (Detail and Provider Views Only)

In **Detail** and **Provider** fact views, US and CA data exists at both metro and national level. To get national-only data:
```sql
WHERE MARKET_ID = -1
```
Without this, quantities double-count (metro data + national data both included).

**Summary views do NOT have MARKET_ID** — they are already national-level.

## LUMINATE_NULL Sentinel Value

When a breakout column doesn't apply to a record, Luminate uses the string literal `'LUMINATE_NULL'` — not a SQL NULL. This means:
- `WHERE SERVICE_TYPE IS NOT NULL` still includes LUMINATE_NULL records
- To exclude non-applicable breakouts: `WHERE SERVICE_TYPE != 'LUMINATE_NULL'`
- To include everything: don't filter the column (LUMINATE_NULL rows carry valid quantities)

## Metric-Specific Breakout Columns

Not all breakout columns apply to all metric categories. Using irrelevant breakouts produces misleading GROUP BY results.

**Streaming breakouts** (METRIC_CATEGORY for streams):
- `SERVICE_TYPE` — on-demand vs programmed playback
- `CONTENT_TYPE` — audio vs video (US/CA/AA only)
- `COMMERCIAL_MODEL` — premium vs ad-supported

**Sales breakouts** (METRIC_CATEGORY for sales):
- `STORE_STRATA` — retailer category
- `DISTRIBUTION_CHANNEL` — digital vs physical
- `PURCHASE_METHOD` — online vs storefront
- `PRODUCT_FORMAT` — media format (CD, vinyl, digital, etc.)
- `RELEASE_TYPE` — release classification (album, single, EP, etc.)

Discover exact valid values for any of these columns using the process in "Discovering Valid Filter Values" below.

**When querying streams**, ignore/exclude sales columns (they'll be LUMINATE_NULL).
**When querying sales**, ignore/exclude streaming columns.

## Cluster Keys for Performance

Use cluster key columns in WHERE clauses for optimal query performance.

### Metadata Views
| View | Cluster Keys |
|------|-------------|
| `VW_MUSICAL_RECORDING_DS` | MR_ID |
| `VW_SONG_DS` | SONG_ID |
| `VW_MUSICAL_PRODUCT_DS` | MP_ID |
| `VW_MUSICAL_RELEASE_DS` | MREL_ID |
| `VW_MUSICAL_RELEASE_GROUP_DS` | MRELG_ID |
| `VW_ARTIST_DS` | COUNTRY_OF_ORIGIN |

### Fact Views (all types: Detail, Summary, Provider, Claim)
| Entity | Cluster Keys |
|--------|-------------|
| MR views | COUNTRY_CODE, MR_ID, REPORT_DATE |
| Song views | COUNTRY_CODE, SONG_ID, REPORT_DATE |
| MP views | COUNTRY_CODE, MP_ID, REPORT_DATE |
| MREL views | COUNTRY_CODE, MREL_ID, REPORT_DATE |
| MRELG views | COUNTRY_CODE, MRELG_ID, REPORT_DATE |
| Artist views | COUNTRY_CODE, ARTIST_ID, REPORT_DATE |

### Weekly Views
| View | Cluster Keys |
|------|-------------|
| `VW_WEEKLY_FACT_ANALYSIS_DS` | COUNTRY_CODE, WEEK_ID, MARKET_ID |
| `VW_WEEKLY_FACT_MARKET_SHARE_DS` | COUNTRY_CODE, WEEK_ID |

## Finding the Latest Available REPORT_DATE

Only look this up when the analyst explicitly needs the most recent date (e.g., "show me the latest day's data", "what's the most recent date available"). For queries with an explicit time range, use the range directly — no date lookup needed.

When a lookup is necessary, use in order of preference:

1. **Default to `CURRENT_DATE() - 1`** — datashare views refresh nightly at 2:00am EST. For most analyses this is correct with no query needed.
2. **`VW_DATE_DS`** — small date dimension, safe to aggregate:
   ```sql
   SELECT MAX(CALENDAR_DATE)
   FROM LUMINATE_DB_LISTING_DETAIL.EXTRACT_S.VW_DATE_DS;
   ```
3. **`VW_DATA_SOURCES_DS`** — use when verifying data provider coverage for a specific country/timeframe.
4. **Internal model `REFRESHED_AT`** — accurate and cheap when querying `LUMINATE_MODELS.PROD`:
   ```sql
   SELECT MAX(REFRESHED_AT) FROM LUMINATE_MODELS.PROD.YTD_ARTIST_SUMMARY;
   ```
5. **Fact view (last resort)** — only when you have a specific entity ID; never on country alone:
   ```sql
   SELECT MAX(REPORT_DATE)
   FROM LUMINATE_DB_LISTING_DETAIL.EXTRACT_S.VW_DAILY_FACT_ARTIST_SUMMARY_DS
   WHERE COUNTRY_CODE = 'US'
     AND ARTIST_ID = '<specific_artist_id>';
   ```

## Discovering Valid Filter Values

> Universal value discovery rules (column descriptions first, scoped SELECT DISTINCT fallback) are in the parent skill's `references/query-standards.md`. The rules below are Luminate-specific additions.

### Luminate-Specific Shortcut: VW_FACT_VALUES_DS

Luminate provides `LUMINATE_DB_LISTING_DETAIL.EXTRACT_S.VW_FACT_VALUES_DS` which contains the complete list of METRIC_CATEGORY, METRIC_BREAKOUT, and BREAKOUT_VALUE combinations. This is the fastest way to discover all valid filter values for Luminate fact views:

```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;
```