# Novi Intelligence recipes

Source: https://developers.novilabs.com/cookbook/intelligence/

> Ready-to-run Snowflake SQL recipes for Novi Intelligence, from the cookbook your welcome email points to.

These recipes query the `NOVI_INTEL` schema. They match Novi Intelligence release 26.2.4, the release your account reads, and the full cookbook your welcome email points to holds the same SQL.

Run this once per session first:

```sql
USE DATABASE NOVI_DATA_ACCESS;
```

## 1. Basin overview

Basin-level summary: well counts by development class (PDP vs planned inventory: Base Case + Emerging).

It reproduces Basin Report cover page well counts.

Tables: `WELL`, `PLANNED_WELL`, `BASIN`.

**PDP wells by basin and subbasin**

```sql
SELECT
    w.BASIN,
    w.SUBBASIN,
    w.REPORT_NAME,
    COUNT(*) AS pdp_well_count
FROM NOVI_INTEL.WELL w
GROUP BY w.BASIN, w.SUBBASIN, w.REPORT_NAME
ORDER BY w.BASIN, w.SUBBASIN
```

**Base Case / Emerging inventory by basin**

```sql
SELECT
    pw.BASIN,
    pw.SUBBASIN,
    pw.REPORT_NAME,
    COUNT(*) AS planned_well_count,
    COUNT(CASE WHEN pw.INVENTORY_CLASS = 'BASE_CASE' THEN 1 END) AS base_case_count,
    COUNT(CASE WHEN pw.INVENTORY_CLASS = 'EMERGING' THEN 1 END) AS emerging_count
FROM NOVI_INTEL.PLANNED_WELL pw
GROUP BY pw.BASIN, pw.SUBBASIN, pw.REPORT_NAME
ORDER BY pw.BASIN, pw.SUBBASIN
```

**Combined PDP + planned inventory by basin (planned covers BASE_CASE + EMERGING)**

```sql
SELECT
    BASIN,
    SUM(CASE WHEN dev_class = 'PDP' THEN cnt ELSE 0 END) AS pdp,
    SUM(CASE WHEN dev_class = 'PLANNED' THEN cnt ELSE 0 END) AS planned,
    SUM(cnt) AS total
FROM (
    SELECT BASIN, 'PDP' AS dev_class, COUNT(*) AS cnt FROM NOVI_INTEL.WELL GROUP BY BASIN
    UNION ALL
    SELECT BASIN, 'PLANNED' AS dev_class, COUNT(*) AS cnt FROM NOVI_INTEL.PLANNED_WELL GROUP BY BASIN
) x
GROUP BY BASIN
ORDER BY total DESC
```

## 2. Well header by operator

Well header with operator, formation, and wellbore geometry.

It reproduces Report Figures 11-14 (well counts by operator, formation, county).

Tables: `WELL`, `WELLBORE`, `OPERATOR`.

**Well count by operator (top 10)**

```sql
SELECT
    o.NAME_NORMALIZED AS operator,
    COUNT(*) AS well_count
FROM NOVI_INTEL.WELL w
JOIN NOVI_INTEL.OPERATOR o ON w.OPERATOR_ID = o.OPERATOR_ID
GROUP BY o.NAME_NORMALIZED
ORDER BY well_count DESC
LIMIT 10
```

**Well count by formation (from wellbore, since formation moved from well to wellbore)**

```sql
SELECT
    wb.FORMATION AS formation,
    COUNT(DISTINCT w.WELL_ID) AS well_count
FROM NOVI_INTEL.WELL w
JOIN NOVI_INTEL.WELLBORE wb ON wb.WELL_ID = w.WELL_ID
WHERE w.BASIN = 'Haynesville'
GROUP BY wb.FORMATION
ORDER BY well_count DESC
```

**Well header detail: well + wellbore + operator joined**

```sql
SELECT
    w.UWI_API,
    o.NAME_NORMALIZED AS operator,
    w.BASIN,
    w.SUBBASIN,
    w.STATUS,
    w.FIRST_PRODUCTION_DATE,
    wb.FORMATION,
    wb.LATERAL_LENGTH,
    wb.TVD_TD,
    wb.MD_TD
FROM NOVI_INTEL.WELL w
JOIN NOVI_INTEL.WELLBORE wb ON wb.WELL_ID = w.WELL_ID
JOIN NOVI_INTEL.OPERATOR o ON w.OPERATOR_ID = o.OPERATOR_ID
WHERE w.BASIN = 'Haynesville'
ORDER BY w.FIRST_PRODUCTION_DATE DESC NULLS LAST
LIMIT 20
```

## 3. Production forecast EUR

Production forecast EUR and type curve analysis.

It reproduces Executive Summary EUR estimates and forecast volumes.

Tables: `WELL_ECONOMICS_SUMMARY`, `WELL`, `OPERATOR`.

For what P10 and P90 mean in each Novi product, see [P-levels](https://developers.novilabs.com/concepts/p-levels/).

**EUR by operator (gas-focused, Haynesville)**

```sql
SELECT
    o.NAME_NORMALIZED AS operator,
    COUNT(*) AS well_count,
    ROUND(AVG(es.EUR_GAS_30YR), 0) AS avg_eur_gas_mcf,
    ROUND(AVG(es.EUR_DRY_GAS_30YR), 0) AS avg_eur_dry_gas_mcf,
    ROUND(AVG(es.EUR_NGL_30YR), 0) AS avg_eur_ngl_bbl,
    ROUND(AVG(es.IP_GAS), 1) AS avg_ip_gas_mcfd
FROM NOVI_INTEL.WELL_ECONOMICS_SUMMARY es
JOIN NOVI_INTEL.WELL w ON es.WELL_ID = w.WELL_ID
JOIN NOVI_INTEL.OPERATOR o ON w.OPERATOR_ID = o.OPERATOR_ID
WHERE w.BASIN = 'Haynesville'
  AND es.WELL_ID IS NOT NULL  -- PDP wells only
GROUP BY o.NAME_NORMALIZED
HAVING COUNT(*) >= 10
ORDER BY avg_eur_gas_mcf DESC
```

**EUR distribution by formation**

```sql
SELECT
    wb.FORMATION,
    COUNT(*) AS well_count,
    ROUND(PERCENTILE_CONT(0.10) WITHIN GROUP (ORDER BY es.EUR_GAS_30YR), 0) AS p10_eur_gas,
    ROUND(PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY es.EUR_GAS_30YR), 0) AS p50_eur_gas,
    ROUND(PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY es.EUR_GAS_30YR), 0) AS p90_eur_gas
FROM NOVI_INTEL.WELL_ECONOMICS_SUMMARY es
JOIN NOVI_INTEL.WELL w ON es.WELL_ID = w.WELL_ID
JOIN NOVI_INTEL.WELLBORE wb ON wb.WELL_ID = w.WELL_ID
WHERE w.BASIN = 'Haynesville'
GROUP BY wb.FORMATION
ORDER BY p50_eur_gas DESC
```

**Daily forecast for a sample well (first 365 days)**

Note: PRODUCTION_FORECAST only has PLANNED_WELL_ID (WELL_ID is NULL), so query via PLANNED_WELL_ID directly.

```sql
SELECT
    pf.PLANNED_WELL_ID,
    pf.FORECAST_DAY,
    pf.GAS_PER_DAY,
    pf.CUMULATIVE_GAS,
    pf.WATER_PER_DAY,
    pf.CUMULATIVE_WATER
FROM NOVI_INTEL.PRODUCTION_FORECAST pf
WHERE pf.PLANNED_WELL_ID IS NOT NULL
  AND pf.BASIN = 'Haynesville'
  AND pf.FORECAST_DAY <= 365
ORDER BY pf.PLANNED_WELL_ID, pf.FORECAST_DAY
LIMIT 50
```

## 4. Economics breakeven

Well economics: NPV, IRR, breakeven analysis.

It reproduces Report Figure 1 (Remaining vs Drilled by NPV25 Breakeven Tranche) and Executive Summary average NPV25 breakeven of $3.06/Mcf.

Tables: `WELL_ECONOMICS_SUMMARY`, `WELL`, `PLANNED_WELL`, `ECON_PRICE_ASSUMPTION`.

**Average breakeven and NPV by basin (all planned inventory: Base Case + Emerging)**

For Base Case only, add: AND pw.INVENTORY_CLASS = 'BASE_CASE' to the WHERE clause.

```sql
SELECT
    pw.BASIN,
    COUNT(*) AS planned_well_count,
    ROUND(AVG(es.BREAKEVEN_1YR), 2) AS avg_breakeven_1yr,
    ROUND(AVG(es.NPV25_BREAKEVEN), 2) AS avg_npv25_breakeven,
    ROUND(AVG(es.IRR * 100), 1) AS avg_irr_pct,
    ROUND(AVG(es.PAYBACK_MONTHS), 0) AS avg_payback_months
FROM NOVI_INTEL.WELL_ECONOMICS_SUMMARY es
JOIN NOVI_INTEL.PLANNED_WELL pw ON es.PLANNED_WELL_ID = pw.PLANNED_WELL_ID
WHERE pw.BASIN = 'Haynesville'
GROUP BY pw.BASIN
```

**NPV25 breakeven tranche distribution (all planned inventory)**

Matches Report Figure 1: % drilled vs undrilled by breakeven bracket.

```sql
SELECT
    CASE
        WHEN es.NPV25_BREAKEVEN <= 1.5 THEN '<= $1.50/Mcf'
        WHEN es.NPV25_BREAKEVEN <= 2.0 THEN '<= $2.00/Mcf'
        WHEN es.NPV25_BREAKEVEN <= 2.5 THEN '<= $2.50/Mcf'
        WHEN es.NPV25_BREAKEVEN <= 3.0 THEN '<= $3.00/Mcf'
        WHEN es.NPV25_BREAKEVEN <= 3.5 THEN '<= $3.50/Mcf'
        ELSE '> $3.50/Mcf'
    END AS breakeven_tranche,
    COUNT(*) AS location_count,
    ROUND(AVG(es.NPV10), 0) AS avg_npv10_usd,
    ROUND(AVG(es.IRR * 100), 1) AS avg_irr_pct
FROM NOVI_INTEL.WELL_ECONOMICS_SUMMARY es
JOIN NOVI_INTEL.PLANNED_WELL pw ON es.PLANNED_WELL_ID = pw.PLANNED_WELL_ID
WHERE pw.BASIN = 'Haynesville'
GROUP BY breakeven_tranche
ORDER BY breakeven_tranche
```

**Remaining planned-inventory value by discount rate (Base Case + Emerging)**

```sql
SELECT
    pw.BASIN,
    ROUND(SUM(es.NPV5), 0) AS total_npv5_usd,
    ROUND(SUM(es.NPV10), 0) AS total_npv10_usd,
    ROUND(SUM(es.NPV25), 0) AS total_npv25_usd
FROM NOVI_INTEL.WELL_ECONOMICS_SUMMARY es
JOIN NOVI_INTEL.PLANNED_WELL pw ON es.PLANNED_WELL_ID = pw.PLANNED_WELL_ID
WHERE pw.BASIN = 'Haynesville'
GROUP BY pw.BASIN
```

**Price deck assumptions**

```sql
SELECT
    epa.NAME,
    epa.OIL_PRICE AS wti_price,
    epa.GAS_PRICE AS hh_price,
    epa.NGL_PRICE,
    epa.OIL_PRICE_DIFFERENTIAL AS wti_diff,
    epa.GAS_PRICE_DIFFERENTIAL AS hh_diff
FROM NOVI_INTEL.ECON_PRICE_ASSUMPTION epa
```

## 5. Rock quality tiers

Rock quality tier analysis by operator and formation.

It reproduces Executive Summary Figures 1-2 (PDP / Base Case by tier), Report Figure 2 ~5.2k tier-1, ~5.4k tier-2, ~6.1k tier-3, ~5.9k tier-4.

Tables: `WELL_ROCK_QUALITY`, `WELL`, `PLANNED_WELL`, `OPERATOR`.

**Tier distribution for PDP wells (drilled)**

```sql
SELECT
    rq.ROCK_QUALITY_TIER,
    COUNT(*) AS well_count
FROM NOVI_INTEL.WELL_ROCK_QUALITY rq
WHERE rq.WELL_CLASS = 'PDP'
  AND rq.BASIN = 'Haynesville'
  AND rq.STREAM = 'gas'
GROUP BY rq.ROCK_QUALITY_TIER
ORDER BY rq.ROCK_QUALITY_TIER
```

**Tier distribution for Base Case wells (remaining inventory)**

```sql
SELECT
    rq.ROCK_QUALITY_TIER,
    COUNT(*) AS location_count
FROM NOVI_INTEL.WELL_ROCK_QUALITY rq
WHERE rq.WELL_CLASS = 'BASE_CASE'
  AND rq.BASIN = 'Haynesville'
  AND rq.STREAM = 'gas'
GROUP BY rq.ROCK_QUALITY_TIER
ORDER BY rq.ROCK_QUALITY_TIER
```

**Operator benchmarking: remaining inventory weighting by tier**

Reproduces: Report Figure 2 (Estimated Operated Remaining Location Weighting).

```sql
SELECT
    o.NAME_NORMALIZED AS operator,
    COUNT(*) AS total_locations,
    COUNT(CASE WHEN rq.ROCK_QUALITY_TIER = 'Tier-1' THEN 1 END) AS tier_1,
    COUNT(CASE WHEN rq.ROCK_QUALITY_TIER = 'Tier-2' THEN 1 END) AS tier_2,
    COUNT(CASE WHEN rq.ROCK_QUALITY_TIER = 'Tier-3' THEN 1 END) AS tier_3,
    COUNT(CASE WHEN rq.ROCK_QUALITY_TIER = 'Tier-4' THEN 1 END) AS tier_4,
    ROUND(100.0 * COUNT(CASE WHEN rq.ROCK_QUALITY_TIER IN ('Tier-1', 'Tier-2') THEN 1 END)
          / NULLIF(COUNT(*), 0), 1) AS pct_top_tier
FROM NOVI_INTEL.WELL_ROCK_QUALITY rq
JOIN NOVI_INTEL.OPERATOR o ON rq.OPERATOR_ID = o.OPERATOR_ID
WHERE rq.WELL_CLASS = 'BASE_CASE'
  AND rq.BASIN = 'Haynesville'
  AND rq.STREAM = 'gas'
GROUP BY o.NAME_NORMALIZED
HAVING COUNT(*) >= 50
ORDER BY pct_top_tier DESC
```

**Rock quality by formation**

```sql
SELECT
    rq.FORMATION,
    rq.ROCK_QUALITY_TIER,
    COUNT(*) AS cnt
FROM NOVI_INTEL.WELL_ROCK_QUALITY rq
WHERE rq.BASIN = 'Haynesville'
  AND rq.STREAM = 'gas'
GROUP BY rq.FORMATION, rq.ROCK_QUALITY_TIER
ORDER BY rq.FORMATION, rq.ROCK_QUALITY_TIER
```

## 6. Completion design

Completion design analysis: proppant, fluid, lateral length trends.

It reproduces Report Figures 18-21 (lateral length over time, proppant/rig efficiency).

Tables: `WELL_COMPLETION`, `WELLBORE`, `WELL`, `OPERATOR`.

**Average completion design metrics (all wells by basin)**

Note: WELL_COMPLETION only has PLANNED_WELL_ID (WELL_ID is NULL).

```sql
SELECT
    wc.BASIN,
    COUNT(*) AS well_count,
    ROUND(AVG(wc.LATERAL_LENGTH_FT), 0) AS avg_lateral_ft,
    ROUND(AVG(wc.PROPPANT_MASS), 0) AS avg_proppant_lbs,
    ROUND(AVG(wc.FLUID_VOLUME), 0) AS avg_fluid_gal,
    ROUND(AVG(wc.PROPPANT_LOADING), 0) AS avg_proppant_lbs_per_ft
FROM NOVI_INTEL.WELL_COMPLETION wc
WHERE wc.BASIN = 'Haynesville'
GROUP BY wc.BASIN
```

**Long lateral trend (>12,500 ft)**

Reproduces: Report Figure 19.

```sql
SELECT
    YEAR(w.FIRST_PRODUCTION_DATE) AS fpy,
    COUNT(*) AS total_wells,
    COUNT(CASE WHEN wb.LATERAL_LENGTH > 12500 THEN 1 END) AS long_laterals,
    ROUND(100.0 * COUNT(CASE WHEN wb.LATERAL_LENGTH > 12500 THEN 1 END)
          / NULLIF(COUNT(*), 0), 1) AS pct_long_lateral
FROM NOVI_INTEL.WELL w
JOIN NOVI_INTEL.WELLBORE wb ON wb.WELL_ID = w.WELL_ID
WHERE w.BASIN = 'Haynesville'
  AND w.FIRST_PRODUCTION_DATE IS NOT NULL
GROUP BY fpy
ORDER BY fpy
```

**Completion design by operator (top 10)**

Join to OPERATOR via WELLBORE -> WELL or PLANNED_WELL for operator name.

```sql
SELECT
    o.NAME_NORMALIZED AS operator,
    COUNT(*) AS well_count,
    ROUND(AVG(wc.LATERAL_LENGTH_FT), 0) AS avg_lateral_ft,
    ROUND(AVG(wc.PROPPANT_LOADING), 0) AS avg_proppant_lbs_per_ft,
    ROUND(AVG(wc.FLUID_LOADING), 0) AS avg_fluid_gal_per_ft
FROM NOVI_INTEL.WELL_COMPLETION wc
JOIN NOVI_INTEL.PLANNED_WELL pw ON pw.PLANNED_WELL_ID = wc.PLANNED_WELL_ID
JOIN NOVI_INTEL.OPERATOR o ON o.OPERATOR_ID = pw.OPERATOR_ID
WHERE wc.BASIN = 'Haynesville'
GROUP BY o.NAME_NORMALIZED
HAVING COUNT(*) >= 20
ORDER BY well_count DESC
LIMIT 10
```

## 7. Arps decline curve

Arps decline curve parameters per well per stream. ARPS_FORECAST pre-joins the segment structure with the Arps parameters (qi, Di, b), so reconstructing the decline model is a single SELECT.

View: `ARPS_FORECAST`.

**Arps segments for wells in the Williston basin**

Note: Arps segments are keyed by WELL_REF (UWI_API for completed wells, 'PW-`<planned_well_id>`' for planned wells).

```sql
SELECT
    WELL_REF,
    STREAM,
    SEGMENT_NUMBER,
    SEGMENT_CURVE_TYPE,
    DAY_START,
    DAY_STOP,
    B_FACTOR,
    NOMINAL_DECLINE_RATE AS di,
    SEGMENT_START_RATE AS qi,
    SEGMENT_END_RATE
FROM NOVI_INTEL.ARPS_FORECAST
WHERE BASIN = 'Williston'
ORDER BY WELL_REF, STREAM, SEGMENT_NUMBER
LIMIT 50
```

**Average Arps parameters by basin (oil stream, segment 1 only)**

Note: Haynesville has no arps data; Williston, Permian, Appalachia do.

```sql
SELECT
    BASIN,
    COUNT(*) AS segment_count,
    ROUND(AVG(B_FACTOR), 3) AS avg_b_factor,
    ROUND(AVG(NOMINAL_DECLINE_RATE), 4) AS avg_di,
    ROUND(AVG(SEGMENT_START_RATE), 1) AS avg_qi
FROM NOVI_INTEL.ARPS_FORECAST
WHERE STREAM = 'oil'
  AND SEGMENT_NUMBER = 1
GROUP BY BASIN
ORDER BY avg_qi DESC
```

## 8. Cost summary

D&C and DCET cost analysis by operator and basin.

It reproduces Inventory sheet cost columns.

Tables: `WELL_COST_SUMMARY`, `WELL`, `PLANNED_WELL`, `OPERATOR`.

**Average D&C costs by operator (PDP wells)**

```sql
SELECT
    o.NAME_NORMALIZED AS operator,
    COUNT(*) AS well_count,
    ROUND(AVG(cs.TOTAL_DC_COST), 0) AS avg_dc_cost_usd,
    ROUND(AVG(cs.TOTAL_DCET_COST), 0) AS avg_dcet_cost_usd,
    ROUND(AVG(cs.NORMALIZED_DC_COST_PER_FT), 0) AS avg_dc_per_ft_usd,
    ROUND(AVG(cs.NORMALIZED_DCET_COST_PER_FT), 0) AS avg_dcet_per_ft_usd
FROM NOVI_INTEL.WELL_COST_SUMMARY cs
JOIN NOVI_INTEL.WELL w ON cs.WELL_ID = w.WELL_ID
JOIN NOVI_INTEL.OPERATOR o ON w.OPERATOR_ID = o.OPERATOR_ID
WHERE w.BASIN = 'Haynesville'
GROUP BY o.NAME_NORMALIZED
HAVING COUNT(*) >= 10
ORDER BY avg_dc_cost_usd
```

**Cost breakdown: D&C vs equip + tie-in by basin**

Note: the intelligence inventory source carries only total D&C and DCET costs, not a discrete drilling / completion / facilities / other split (those columns are always NULL for rows from basin research), so the equip + tie-in portion is derived as DCET - D&C.

```sql
SELECT
    w.BASIN,
    COUNT(*) AS well_count,
    ROUND(AVG(cs.TOTAL_DC_COST), 0) AS avg_dc_cost_usd,
    ROUND(AVG(cs.TOTAL_DCET_COST), 0) AS avg_dcet_cost_usd,
    ROUND(AVG(cs.TOTAL_DCET_COST - cs.TOTAL_DC_COST), 0) AS avg_equip_tiein_usd
FROM NOVI_INTEL.WELL_COST_SUMMARY cs
JOIN NOVI_INTEL.WELL w ON cs.WELL_ID = w.WELL_ID
WHERE w.BASIN = 'Haynesville'
GROUP BY w.BASIN
```

## 9. Inventory by operator

Remaining inventory analysis by operator.

It reproduces Report "Total Remaining by Operator" sheet and Figure 2.

Tables: `PLANNED_WELL`, `WELLBORE`, `OPERATOR`, `WELL_ECONOMICS_SUMMARY`.

**Remaining Base Case locations by operator with economics**

```sql
SELECT
    o.NAME_NORMALIZED AS operator,
    COUNT(*) AS remaining_locations,
    ROUND(AVG(pw.LATERAL_LENGTH), 0) AS avg_lateral_ft,
    ROUND(SUM(pw.LATERAL_LENGTH), 0) AS total_lateral_ft,
    ROUND(AVG(es.NPV25_BREAKEVEN), 2) AS avg_npv25_breakeven,
    ROUND(AVG(es.IRR * 100), 1) AS avg_irr_pct
FROM NOVI_INTEL.PLANNED_WELL pw
JOIN NOVI_INTEL.OPERATOR o ON pw.OPERATOR_ID = o.OPERATOR_ID
LEFT JOIN NOVI_INTEL.WELL_ECONOMICS_SUMMARY es ON es.PLANNED_WELL_ID = pw.PLANNED_WELL_ID
WHERE pw.BASIN = 'Haynesville'
  AND pw.INVENTORY_CLASS = 'BASE_CASE'
GROUP BY o.NAME_NORMALIZED
HAVING COUNT(*) >= 20
ORDER BY remaining_locations DESC
```

**Implied inventory life at current drilling cadence**

Assumes ~500 wells/year (~4.9 MM ft/year) based on 2023-2024 cadence.

```sql
SELECT
    o.NAME_NORMALIZED AS operator,
    COUNT(*) AS remaining_locations,
    ROUND(SUM(pw.LATERAL_LENGTH), 0) AS total_remaining_ft,
    ROUND(SUM(pw.LATERAL_LENGTH) / 4900000.0, 1) AS implied_years_at_basin_cadence
FROM NOVI_INTEL.PLANNED_WELL pw
JOIN NOVI_INTEL.OPERATOR o ON pw.OPERATOR_ID = o.OPERATOR_ID
WHERE pw.BASIN = 'Haynesville'
  AND pw.INVENTORY_CLASS = 'BASE_CASE'
GROUP BY o.NAME_NORMALIZED
HAVING COUNT(*) >= 50
ORDER BY implied_years_at_basin_cadence DESC
```

**Inventory by pad (planned wells grouped by pad)**

```sql
SELECT
    p.NAME AS pad_name,
    p.OPERATOR_NAME,
    p.BASIN,
    COUNT(pw.PLANNED_WELL_ID) AS wells_on_pad
FROM NOVI_INTEL.PAD p
JOIN NOVI_INTEL.PLANNED_WELL pw ON pw.PAD_ID = p.PAD_ID
WHERE p.BASIN = 'Haynesville'
GROUP BY p.NAME, p.OPERATOR_NAME, p.BASIN
ORDER BY wells_on_pad DESC
LIMIT 20
```

## 11. Data freshness

Check which basin reports are loaded and when they were last refreshed. Useful for verifying data currency before analysis.

Tables: `SOURCE`, `WELL`, `BASIN`.

**Available basin reports**

```sql
SELECT
    s.COLLECTION AS report_name,
    s.SYSTEM,
    MIN(s.CREATED_AT) AS first_loaded,
    COUNT(DISTINCT w.WELL_ID) AS well_count
FROM NOVI_INTEL.SOURCE s
LEFT JOIN NOVI_INTEL.WELL w ON w.SOURCE_ID = s.SOURCE_ID
GROUP BY s.COLLECTION, s.SYSTEM
ORDER BY s.COLLECTION
```

**Basin dimension: all available basins and subbasins**

```sql
SELECT
    b.BASIN,
    b.SUBBASIN
FROM NOVI_INTEL.BASIN b
ORDER BY b.BASIN, b.SUBBASIN
```

**Row counts per entity (data completeness check)**

```sql
SELECT 'WELL' AS entity, COUNT(*) AS row_count FROM NOVI_INTEL.WELL
UNION ALL SELECT 'WELLBORE', COUNT(*) FROM NOVI_INTEL.WELLBORE
UNION ALL SELECT 'PLANNED_WELL', COUNT(*) FROM NOVI_INTEL.PLANNED_WELL
UNION ALL SELECT 'WELL_COMPLETION', COUNT(*) FROM NOVI_INTEL.WELL_COMPLETION
UNION ALL SELECT 'PRODUCTION_FORECAST', COUNT(*) FROM NOVI_INTEL.PRODUCTION_FORECAST
UNION ALL SELECT 'WELL_ECONOMICS_SUMMARY', COUNT(*) FROM NOVI_INTEL.WELL_ECONOMICS_SUMMARY
UNION ALL SELECT 'WELL_ROCK_QUALITY', COUNT(*) FROM NOVI_INTEL.WELL_ROCK_QUALITY
UNION ALL SELECT 'WELL_COST_SUMMARY', COUNT(*) FROM NOVI_INTEL.WELL_COST_SUMMARY
ORDER BY entity
```

## 12. Oil basin economics

Oil-focused economics for basins like Williston (Bakken/Three Forks). Haynesville uses $/Mcf (gas) breakevens, Williston uses $/bbl (oil) breakevens.

It reproduces Williston Exec Summary avg NPV25 breakeven ~$89/bbl (WTI).

Tables: `WELL_ECONOMICS_SUMMARY`, `PLANNED_WELL`, `OPERATOR`.

**NPV25 breakeven tranche distribution ($/bbl for oil basins)**

```sql
SELECT
    CASE
        WHEN es.NPV25_BREAKEVEN <= 40 THEN '<= $40/bbl'
        WHEN es.NPV25_BREAKEVEN <= 50 THEN '<= $50/bbl'
        WHEN es.NPV25_BREAKEVEN <= 60 THEN '<= $60/bbl'
        WHEN es.NPV25_BREAKEVEN <= 70 THEN '<= $70/bbl'
        WHEN es.NPV25_BREAKEVEN <= 80 THEN '<= $80/bbl'
        WHEN es.NPV25_BREAKEVEN <= 90 THEN '<= $90/bbl'
        ELSE '> $90/bbl'
    END AS breakeven_tranche,
    COUNT(*) AS location_count,
    ROUND(AVG(es.IRR * 100), 1) AS avg_irr_pct,
    ROUND(AVG(es.PAYBACK_MONTHS), 0) AS avg_payback_months
FROM NOVI_INTEL.WELL_ECONOMICS_SUMMARY es
JOIN NOVI_INTEL.PLANNED_WELL pw ON es.PLANNED_WELL_ID = pw.PLANNED_WELL_ID
WHERE pw.BASIN = 'Williston'
GROUP BY breakeven_tranche
ORDER BY breakeven_tranche
```

**Oil EUR by operator (Williston is oil-primary)**

```sql
SELECT
    o.NAME_NORMALIZED AS operator,
    COUNT(*) AS well_count,
    ROUND(AVG(es.EUR_OIL_30YR), 0) AS avg_eur_oil_bbl,
    ROUND(AVG(es.EUR_GAS_30YR), 0) AS avg_eur_gas_mcf,
    ROUND(AVG(es.IP_OIL), 1) AS avg_ip_oil_bpd,
    ROUND(AVG(es.NGL_YIELD), 1) AS avg_ngl_yield
FROM NOVI_INTEL.WELL_ECONOMICS_SUMMARY es
JOIN NOVI_INTEL.WELL w ON es.WELL_ID = w.WELL_ID
JOIN NOVI_INTEL.OPERATOR o ON w.OPERATOR_ID = o.OPERATOR_ID
WHERE w.BASIN = 'Williston'
GROUP BY o.NAME_NORMALIZED
HAVING COUNT(*) >= 20
ORDER BY avg_eur_oil_bbl DESC
```

**Two-year payout breakeven by first production year**

Williston report: wells drilled now to 2030 can generate sub-$70/bbl payout.

```sql
SELECT
    YEAR(w.FIRST_PRODUCTION_DATE) AS fpy,
    COUNT(*) AS well_count,
    ROUND(AVG(es.BREAKEVEN_2YR), 2) AS avg_2yr_breakeven,
    ROUND(AVG(es.PAYBACK_MONTHS), 0) AS avg_payback_months
FROM NOVI_INTEL.WELL_ECONOMICS_SUMMARY es
JOIN NOVI_INTEL.WELL w ON es.WELL_ID = w.WELL_ID
WHERE w.BASIN = 'Williston'
  AND w.FIRST_PRODUCTION_DATE IS NOT NULL
GROUP BY fpy
ORDER BY fpy
```

## 13. Base case vs emerging inventory

Base Case vs Emerging inventory split. Williston: ~37.8k Base Case + ~5.2k Emerging locations.

It reproduces Exec Summary "37,801 Base Case locations and 5,184 Emerging locations".

Tables: `PLANNED_WELL`, `WELL_ROCK_QUALITY`, `OPERATOR`.

**Base Case vs Emerging counts by basin**

```sql
SELECT
    pw.BASIN,
    pw.INVENTORY_CLASS,
    COUNT(*) AS location_count,
    ROUND(AVG(pw.LATERAL_LENGTH), 0) AS avg_lateral_ft
FROM NOVI_INTEL.PLANNED_WELL pw
GROUP BY pw.BASIN, pw.INVENTORY_CLASS
ORDER BY pw.BASIN, pw.INVENTORY_CLASS
```

**Rock quality tier distribution by well class (Base Case vs Emerging)**

```sql
SELECT
    rq.WELL_CLASS,
    rq.ROCK_QUALITY_TIER,
    COUNT(*) AS location_count
FROM NOVI_INTEL.WELL_ROCK_QUALITY rq
WHERE rq.BASIN = 'Williston'
  AND rq.WELL_CLASS IN ('BASE_CASE', 'EMERGING')
  AND rq.STREAM = 'oil'
GROUP BY rq.WELL_CLASS, rq.ROCK_QUALITY_TIER
ORDER BY rq.WELL_CLASS, rq.ROCK_QUALITY_TIER
```

**Top operators by Base Case + Emerging inventory**

```sql
SELECT
    o.NAME_NORMALIZED AS operator,
    COUNT(CASE WHEN pw.INVENTORY_CLASS = 'BASE_CASE' THEN 1 END) AS base_case_count,
    COUNT(CASE WHEN pw.INVENTORY_CLASS = 'EMERGING' THEN 1 END) AS emerging_count,
    COUNT(*) AS total
FROM NOVI_INTEL.PLANNED_WELL pw
JOIN NOVI_INTEL.OPERATOR o ON pw.OPERATOR_ID = o.OPERATOR_ID
WHERE pw.BASIN = 'Williston'
GROUP BY o.NAME_NORMALIZED
ORDER BY total DESC
LIMIT 15
```

## 14. Multi formation comparison

Compare performance across formations within a basin. Williston: Middle Bakken vs Three Forks (primary targets). Haynesville: Haynesville Shale vs Bossier Shale.

It reproduces Report Figures 13-14 (production by formation).

Tables: `WELLBORE`, `WELL`, `WELL_ECONOMICS_SUMMARY`, `WELL_ROCK_QUALITY`.

**Well count and EUR by formation (Williston)**

```sql
SELECT
    wb.FORMATION,
    COUNT(DISTINCT w.WELL_ID) AS pdp_well_count,
    ROUND(AVG(es.EUR_OIL_30YR), 0) AS avg_eur_oil_bbl,
    ROUND(AVG(es.EUR_GAS_30YR), 0) AS avg_eur_gas_mcf,
    ROUND(AVG(es.IP_OIL), 1) AS avg_ip_oil_bpd,
    ROUND(AVG(wb.LATERAL_LENGTH), 0) AS avg_lateral_ft
FROM NOVI_INTEL.WELL w
JOIN NOVI_INTEL.WELLBORE wb ON wb.WELL_ID = w.WELL_ID
LEFT JOIN NOVI_INTEL.WELL_ECONOMICS_SUMMARY es ON es.WELL_ID = w.WELL_ID
WHERE w.BASIN = 'Williston'
GROUP BY wb.FORMATION
HAVING COUNT(DISTINCT w.WELL_ID) >= 10
ORDER BY pdp_well_count DESC
```

**Rock quality tier distribution by formation**

```sql
SELECT
    rq.FORMATION,
    rq.ROCK_QUALITY_TIER,
    COUNT(*) AS cnt,
    ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (PARTITION BY rq.FORMATION), 1) AS pct
FROM NOVI_INTEL.WELL_ROCK_QUALITY rq
WHERE rq.BASIN = 'Williston'
  AND rq.STREAM = 'oil'
  AND rq.WELL_CLASS = 'BASE_CASE'
GROUP BY rq.FORMATION, rq.ROCK_QUALITY_TIER
ORDER BY rq.FORMATION, rq.ROCK_QUALITY_TIER
```

**Average breakeven by formation (all planned inventory: Base Case + Emerging)**

For Base Case only, add: AND pw.INVENTORY_CLASS = 'BASE_CASE' to the WHERE clause.

```sql
SELECT
    wb.FORMATION,
    COUNT(*) AS planned_well_count,
    ROUND(AVG(es.NPV25_BREAKEVEN), 2) AS avg_npv25_breakeven,
    ROUND(AVG(es.BREAKEVEN_2YR), 2) AS avg_2yr_breakeven
FROM NOVI_INTEL.WELL_ECONOMICS_SUMMARY es
JOIN NOVI_INTEL.PLANNED_WELL pw ON es.PLANNED_WELL_ID = pw.PLANNED_WELL_ID
JOIN NOVI_INTEL.WELLBORE wb ON wb.PLANNED_WELL_ID = pw.PLANNED_WELL_ID
WHERE pw.BASIN = 'Williston'
GROUP BY wb.FORMATION
HAVING COUNT(*) >= 50
ORDER BY avg_npv25_breakeven
```

## 15. Cross basin comparison

Compare key metrics across all loaded basins. Useful when your subscription covers several basins.

Tables: `WELL`, `PLANNED_WELL`, `WELL_ECONOMICS_SUMMARY`, `WELL_ROCK_QUALITY`.

**Basin-level summary card**

```sql
SELECT
    BASIN,
    COUNT(*) AS pdp_wells
FROM NOVI_INTEL.WELL
GROUP BY BASIN
ORDER BY pdp_wells DESC
```

**Base Case inventory and economics by basin**

```sql
SELECT
    pw.BASIN,
    COUNT(*) AS base_case_locations,
    ROUND(AVG(es.NPV25_BREAKEVEN), 2) AS avg_npv25_breakeven,
    ROUND(AVG(es.IRR * 100), 1) AS avg_irr_pct,
    ROUND(AVG(es.PAYBACK_MONTHS), 0) AS avg_payback_months,
    ROUND(AVG(es.EUR_OIL_30YR), 0) AS avg_eur_oil_bbl,
    ROUND(AVG(es.EUR_GAS_30YR), 0) AS avg_eur_gas_mcf
FROM NOVI_INTEL.PLANNED_WELL pw
LEFT JOIN NOVI_INTEL.WELL_ECONOMICS_SUMMARY es ON es.PLANNED_WELL_ID = pw.PLANNED_WELL_ID
WHERE pw.INVENTORY_CLASS = 'BASE_CASE'
GROUP BY pw.BASIN
ORDER BY base_case_locations DESC
```

**Rock quality distribution comparison across basins**

```sql
SELECT
    rq.BASIN,
    rq.ROCK_QUALITY_TIER,
    COUNT(*) AS cnt
FROM NOVI_INTEL.WELL_ROCK_QUALITY rq
WHERE rq.WELL_CLASS = 'BASE_CASE'
GROUP BY rq.BASIN, rq.ROCK_QUALITY_TIER
ORDER BY rq.BASIN, rq.ROCK_QUALITY_TIER
```

**Top operators across all basins (counts all planned inventory: Base Case + Emerging)**

For Base Case only, add: AND pw.INVENTORY_CLASS = 'BASE_CASE' to the LEFT JOIN ON clause.

```sql
SELECT
    o.NAME_NORMALIZED AS operator,
    COUNT(DISTINCT w.BASIN) AS basins_active,
    COUNT(DISTINCT w.WELL_ID) AS total_pdp_wells,
    COUNT(DISTINCT pw.PLANNED_WELL_ID) AS total_planned_locations
FROM NOVI_INTEL.OPERATOR o
LEFT JOIN NOVI_INTEL.WELL w ON w.OPERATOR_ID = o.OPERATOR_ID
LEFT JOIN NOVI_INTEL.PLANNED_WELL pw ON pw.OPERATOR_ID = o.OPERATOR_ID
GROUP BY o.NAME_NORMALIZED
HAVING COUNT(DISTINCT w.WELL_ID) > 0 OR COUNT(DISTINCT pw.PLANNED_WELL_ID) > 0
ORDER BY total_pdp_wells + total_planned_locations DESC
LIMIT 20
```

**Completion design evolution comparison across basins**

Average completion design by basin (all wells). Note: WELL_COMPLETION only has PLANNED_WELL_ID (WELL_ID is NULL).

```sql
SELECT
    wc.BASIN,
    COUNT(*) AS well_count,
    ROUND(AVG(wc.LATERAL_LENGTH_FT), 0) AS avg_lateral_ft,
    ROUND(AVG(wc.PROPPANT_LOADING), 0) AS avg_proppant_lbs_per_ft
FROM NOVI_INTEL.WELL_COMPLETION wc
GROUP BY wc.BASIN
ORDER BY wc.BASIN
```

## 16. Breakeven by stream

Breakeven analysis faceted by commodity stream. The STREAM column on WELL_ECONOMICS_SUMMARY lets you separate breakevens by commodity unit basis. Without this faceting, gas wells (avg ~$3/MMBtu HH) and oil wells (avg ~$57/bbl WTI) get co-mingled on the same color scale, which makes gas basins dominate the lower end. Important: STREAM is a well-classification tag, NOT a unit discriminator. Wet Gas wells are tagged 'ngl' (consistent with WELL_ROCK_QUALITY.STREAM) but their breakeven values are still in gas $/MMBtu units. For unit-aware faceting: STREAM IN ('gas', 'ngl') -> gas-unit breakevens ($/MMBtu HH). STREAM = 'oil' -> oil-unit breakevens ($/bbl WTI).

Tables: `WELL_ECONOMICS_SUMMARY`, `WELL`, `PLANNED_WELL`, `BASIN`.

**Average breakeven by basin and stream (UNION across drilled + planned for full coverage)**

```sql
WITH well_basin_econ AS (
    SELECT e.STREAM, e.NPV10_BREAKEVEN, b.BASIN AS basin_name
    FROM NOVI_INTEL.WELL_ECONOMICS_SUMMARY e
    JOIN NOVI_INTEL.WELL w ON w.WELL_ID = e.WELL_ID
    JOIN NOVI_INTEL.BASIN b ON b.BASIN_ID = w.BASIN_ID
    WHERE e.WELL_ID IS NOT NULL
    UNION ALL
    SELECT e.STREAM, e.NPV10_BREAKEVEN, b.BASIN AS basin_name
    FROM NOVI_INTEL.WELL_ECONOMICS_SUMMARY e
    JOIN NOVI_INTEL.PLANNED_WELL pw ON pw.PLANNED_WELL_ID = e.PLANNED_WELL_ID
    JOIN NOVI_INTEL.BASIN b ON b.BASIN_ID = pw.BASIN_ID
    WHERE e.PLANNED_WELL_ID IS NOT NULL
)
SELECT
    basin_name,
    STREAM,
    ROUND(AVG(NPV10_BREAKEVEN), 2) AS avg_npv10_breakeven,
    ROUND(MIN(NPV10_BREAKEVEN), 2) AS min_breakeven,
    ROUND(MAX(NPV10_BREAKEVEN), 2) AS max_breakeven,
    COUNT(*) AS wells
FROM well_basin_econ
WHERE STREAM IN ('oil', 'gas', 'ngl')
GROUP BY 1, 2
ORDER BY 1, 2
```

**Gas-unit breakeven only (STREAM IN ('gas', 'ngl')): color-by-breakeven on a single map**

```sql
WITH well_basin_econ AS (
    SELECT e.STREAM, e.NPV10_BREAKEVEN, b.BASIN AS basin_name
    FROM NOVI_INTEL.WELL_ECONOMICS_SUMMARY e
    JOIN NOVI_INTEL.WELL w ON w.WELL_ID = e.WELL_ID
    JOIN NOVI_INTEL.BASIN b ON b.BASIN_ID = w.BASIN_ID
    WHERE e.WELL_ID IS NOT NULL
    UNION ALL
    SELECT e.STREAM, e.NPV10_BREAKEVEN, b.BASIN AS basin_name
    FROM NOVI_INTEL.WELL_ECONOMICS_SUMMARY e
    JOIN NOVI_INTEL.PLANNED_WELL pw ON pw.PLANNED_WELL_ID = e.PLANNED_WELL_ID
    JOIN NOVI_INTEL.BASIN b ON b.BASIN_ID = pw.BASIN_ID
    WHERE e.PLANNED_WELL_ID IS NOT NULL
)
SELECT
    basin_name,
    ROUND(AVG(NPV10_BREAKEVEN), 2) AS avg_be_per_mmbtu,
    COUNT(*) AS wells
FROM well_basin_econ
WHERE STREAM IN ('gas', 'ngl')
GROUP BY 1
ORDER BY 2
```

**Oil-unit breakeven only (STREAM = 'oil')**

```sql
WITH well_basin_econ AS (
    SELECT e.STREAM, e.NPV10_BREAKEVEN, b.BASIN AS basin_name
    FROM NOVI_INTEL.WELL_ECONOMICS_SUMMARY e
    JOIN NOVI_INTEL.WELL w ON w.WELL_ID = e.WELL_ID
    JOIN NOVI_INTEL.BASIN b ON b.BASIN_ID = w.BASIN_ID
    WHERE e.WELL_ID IS NOT NULL
    UNION ALL
    SELECT e.STREAM, e.NPV10_BREAKEVEN, b.BASIN AS basin_name
    FROM NOVI_INTEL.WELL_ECONOMICS_SUMMARY e
    JOIN NOVI_INTEL.PLANNED_WELL pw ON pw.PLANNED_WELL_ID = e.PLANNED_WELL_ID
    JOIN NOVI_INTEL.BASIN b ON b.BASIN_ID = pw.BASIN_ID
    WHERE e.PLANNED_WELL_ID IS NOT NULL
)
SELECT
    basin_name,
    ROUND(AVG(NPV10_BREAKEVEN), 2) AS avg_be_per_bbl,
    COUNT(*) AS wells
FROM well_basin_econ
WHERE STREAM = 'oil'
GROUP BY 1
ORDER BY 2
```

## 17. Well master overview

Unified well inventory: PDP + Base Case + Emerging in one view. REPORT_NAME identifies the basin report quarter (e.g., 'basin_research__Midland_Basin__2025Q3').

**Basin summary: count by inventory class (single report)**

```sql
SELECT
    BASIN, INVENTORY_CLASS, COUNT(*) AS well_count
FROM NOVI_INTEL.WELL_MASTER
WHERE REPORT_NAME = 'basin_research__Midland_Basin__2025Q3'
GROUP BY 1, 2 ORDER BY 1, 2
```

**Top 10 operators by total inventory**

```sql
SELECT
    OPERATOR_NAME, COUNT(*) AS total_wells,
    COUNT(CASE WHEN INVENTORY_CLASS = 'PDP' THEN 1 END) AS pdp,
    COUNT(CASE WHEN INVENTORY_CLASS = 'BASE_CASE' THEN 1 END) AS base_case,
    COUNT(CASE WHEN INVENTORY_CLASS = 'EMERGING' THEN 1 END) AS emerging
FROM NOVI_INTEL.WELL_MASTER
GROUP BY 1 ORDER BY total_wells DESC LIMIT 10
```

**Available reports**

```sql
SELECT REPORT_NAME, INVENTORY_CLASS, COUNT(*) AS well_count
FROM NOVI_INTEL.WELL_MASTER
GROUP BY 1, 2 ORDER BY 1, 2
```

**Wells with geometry for a specific report (for GIS mapping)**

```sql
SELECT WELL_REF, INVENTORY_CLASS, LATITUDE, LONGITUDE, GEOMETRY_GEO, OPERATOR_NAME, FORMATION
FROM NOVI_INTEL.WELL_MASTER
WHERE GEOMETRY_GEO IS NOT NULL AND REPORT_NAME = 'basin_research__Midland_Basin__2025Q3'
LIMIT 100
```

## 18. Economics scorecard

Economics scorecard: breakevens faceted by stream (oil vs gas vs ngl). Note: STREAM is a well-classification tag, NOT a unit discriminator. Wet Gas wells are tagged 'ngl' but breakeven values are in gas $/MMBtu. For unit-aware faceting: STREAM IN ('gas','ngl') = gas-unit, STREAM = 'oil' = oil-unit.

**Average breakeven by stream for a specific report**

```sql
SELECT
    REPORT_NAME, STREAM, COUNT(*) AS wells,
    ROUND(AVG(NPV10_BREAKEVEN), 2) AS avg_npv10_be,
    ROUND(AVG(IRR * 100), 1) AS avg_irr_pct,
    ROUND(AVG(PAYBACK_MONTHS), 0) AS avg_payback
FROM NOVI_INTEL.WELL_ECONOMICS
WHERE STREAM IN ('oil', 'gas', 'ngl')
  AND REPORT_NAME = 'basin_research__Midland_Basin__2025Q3'
GROUP BY 1, 2 ORDER BY 1, 2
```

**Oil-only: top basins by NPV10 (cross-report comparison)**

```sql
SELECT
    BASIN, REPORT_NAME, COUNT(*) AS wells,
    ROUND(SUM(NPV10), 0) AS total_npv10_usd,
    ROUND(AVG(NPV10_BREAKEVEN), 2) AS avg_be_per_bbl
FROM NOVI_INTEL.WELL_ECONOMICS
WHERE STREAM = 'oil'
GROUP BY 1, 2 ORDER BY total_npv10_usd DESC
```

**Cross-view join: economics + well master for operator benchmarking (single report)**

```sql
SELECT
    wm.OPERATOR_NAME, wm.BASIN,
    COUNT(*) AS wells,
    ROUND(AVG(we.NPV10_BREAKEVEN), 2) AS avg_be,
    ROUND(AVG(we.IRR * 100), 1) AS avg_irr_pct,
    ROUND(AVG(wm.LATERAL_LENGTH), 0) AS avg_lateral_ft
FROM NOVI_INTEL.WELL_ECONOMICS we
JOIN NOVI_INTEL.WELL_MASTER wm USING (WELL_REF)
WHERE we.STREAM = 'oil' AND we.REPORT_NAME = 'basin_research__Midland_Basin__2025Q3'
GROUP BY 1, 2 HAVING COUNT(*) >= 20
ORDER BY avg_be
```

## 19. ML score analysis

Rock quality scores by stream and tier.

**Tier distribution by basin (gas stream)**

```sql
SELECT
    BASIN, ROCK_QUALITY_TIER, COUNT(*) AS well_count
FROM NOVI_INTEL.ML_SCORE
WHERE STREAM = 'gas'
GROUP BY 1, 2 ORDER BY 1, 2
```

**Average score by operator (oil, Base Case only)**

```sql
SELECT
    OPERATOR_NAME, COUNT(*) AS locations,
    ROUND(AVG(ROCK_QUALITY_SCORE), 3) AS avg_score,
    COUNT(CASE WHEN ROCK_QUALITY_TIER IN ('Tier-1','Tier-2') THEN 1 END) AS top_tier_count,
    ROUND(100.0 * COUNT(CASE WHEN ROCK_QUALITY_TIER IN ('Tier-1','Tier-2') THEN 1 END)
          / NULLIF(COUNT(*), 0), 1) AS pct_top_tier
FROM NOVI_INTEL.ML_SCORE
WHERE STREAM = 'oil' AND INVENTORY_CLASS = 'BASE_CASE'
GROUP BY 1 HAVING COUNT(*) >= 50
ORDER BY pct_top_tier DESC
```

**Cross-view: economics + rock quality for tier-weighted breakeven**

```sql
SELECT
    ms.ROCK_QUALITY_TIER,
    COUNT(*) AS wells,
    ROUND(AVG(we.NPV10_BREAKEVEN), 2) AS avg_be,
    ROUND(AVG(we.IRR * 100), 1) AS avg_irr_pct
FROM NOVI_INTEL.ML_SCORE ms
JOIN NOVI_INTEL.WELL_ECONOMICS we USING (WELL_REF)
WHERE ms.STREAM = 'oil' AND ms.BASIN = 'Permian'
GROUP BY 1 ORDER BY 1
```

## 20. Inventory forecast

Time-series production forecast for Base Case / Emerging inventory.

**First 5 years of monthly oil forecast for a report**

```sql
SELECT
    WELL_REF, OPERATOR_NAME, YEAR, MONTH,
    OIL_PER_DAY, CUMULATIVE_OIL
FROM NOVI_INTEL.INVENTORY_FORECAST
WHERE REPORT_NAME = 'basin_research__Midland_Basin__2025Q3'
  AND FORECAST_DAY <= 1825
ORDER BY WELL_REF, FORECAST_DAY
LIMIT 100
```

**Basin-level aggregate forecast by year (derived from FORECAST_DAY)**

YEAR/MONTH columns are NULL in intelligence data; use FORECAST_DAY instead.

```sql
SELECT
    BASIN,
    FLOOR(FORECAST_DAY / 365) + 1 AS forecast_year,
    COUNT(DISTINCT WELL_REF) AS wells,
    ROUND(AVG(OIL_PER_DAY), 0) AS avg_oil_bpd,
    ROUND(AVG(GAS_PER_DAY), 0) AS avg_gas_mcfd
FROM NOVI_INTEL.INVENTORY_FORECAST
WHERE FORECAST_DAY <= 1825
GROUP BY 1, 2 ORDER BY 1, 2
```

## 21. Arps decline parameters

Arps decline curve parameters by basin and stream.

**Average decline parameters by basin (oil, segment 1)**

```sql
SELECT
    BASIN, COUNT(*) AS segment_count,
    ROUND(AVG(B_FACTOR), 3) AS avg_b,
    ROUND(AVG(NOMINAL_DECLINE_RATE), 4) AS avg_di,
    ROUND(AVG(SEGMENT_START_RATE), 1) AS avg_qi
FROM NOVI_INTEL.ARPS_FORECAST
WHERE STREAM = 'oil' AND SEGMENT_NUMBER = 1
GROUP BY 1 ORDER BY avg_qi DESC
```

**Full decline model for a specific report**

```sql
SELECT
    WELL_REF, STREAM, SEGMENT_NUMBER,
    SEGMENT_CURVE_TYPE, B_FACTOR,
    NOMINAL_DECLINE_RATE AS di,
    SEGMENT_START_RATE AS qi, SEGMENT_END_RATE,
    DAY_START, DAY_STOP
FROM NOVI_INTEL.ARPS_FORECAST
WHERE REPORT_NAME = 'basin_research__Williston_Basin__2025Q4'
ORDER BY WELL_REF, STREAM, SEGMENT_NUMBER
LIMIT 50
```

## 22. Report deep dive

Single-report deep dive using all 5 presentation views. Replace the REPORT_NAME value with any loaded report (e.g., 'basin_research__Midland_Basin__2025Q3', 'basin_research__Haynesville_Basin__2025Q4', 'basin_research__Delaware_Basin__2025Q3').

**1. Well Master: full inventory for the report**

```sql
SELECT
    WELL_REF, INVENTORY_CLASS, OPERATOR_NAME, FORMATION,
    LATERAL_LENGTH, LATITUDE, LONGITUDE, STATUS
FROM NOVI_INTEL.WELL_MASTER
WHERE REPORT_NAME = 'basin_research__Midland_Basin__2025Q3'
ORDER BY INVENTORY_CLASS, OPERATOR_NAME
```

**2. Well Economics: breakeven + returns for the report**

```sql
SELECT
    WELL_REF, INVENTORY_CLASS, STREAM, OPERATOR_NAME,
    NPV10_BREAKEVEN, IRR, PAYBACK_MONTHS,
    EUR_OIL_30YR, EUR_GAS_30YR, TOTAL_DCET_COST
FROM NOVI_INTEL.WELL_ECONOMICS
WHERE REPORT_NAME = 'basin_research__Midland_Basin__2025Q3'
ORDER BY NPV10_BREAKEVEN
```

**3. ML Score: rock quality distribution for the report**

```sql
SELECT
    WELL_REF, INVENTORY_CLASS, STREAM,
    ROCK_QUALITY_SCORE, ROCK_QUALITY_TIER, OPERATOR_NAME
FROM NOVI_INTEL.ML_SCORE
WHERE REPORT_NAME = 'basin_research__Midland_Basin__2025Q3'
ORDER BY ROCK_QUALITY_SCORE DESC
```

**4. Inventory Forecast: first-year rates for the report (YEAR is NULL; use FORECAST_DAY)**

```sql
SELECT
    WELL_REF, NAME, OPERATOR_NAME, SCENARIO,
    FORECAST_DAY, OIL_PER_DAY, GAS_PER_DAY,
    CUMULATIVE_OIL, CUMULATIVE_GAS
FROM NOVI_INTEL.INVENTORY_FORECAST
WHERE REPORT_NAME = 'basin_research__Midland_Basin__2025Q3'
  AND FORECAST_DAY <= 365
ORDER BY WELL_REF, FORECAST_DAY
LIMIT 100
```

**5. Arps Forecast: decline parameters for the report**

```sql
SELECT
    WELL_REF, STREAM, SEGMENT_NUMBER,
    B_FACTOR, NOMINAL_DECLINE_RATE AS di,
    SEGMENT_START_RATE AS qi, SEGMENT_END_RATE,
    DAY_START, DAY_STOP
FROM NOVI_INTEL.ARPS_FORECAST
WHERE REPORT_NAME = 'basin_research__Midland_Basin__2025Q3'
ORDER BY WELL_REF, STREAM, SEGMENT_NUMBER
```

**6. Cross-view: report summary dashboard**

```sql
SELECT
    wm.INVENTORY_CLASS,
    COUNT(DISTINCT wm.WELL_REF) AS total_wells,
    ROUND(AVG(we.NPV10_BREAKEVEN), 2) AS avg_breakeven,
    ROUND(AVG(we.IRR * 100), 1) AS avg_irr_pct,
    ROUND(AVG(wm.LATERAL_LENGTH), 0) AS avg_lateral_ft,
    ROUND(AVG(ms.ROCK_QUALITY_SCORE), 2) AS avg_rq_score
FROM NOVI_INTEL.WELL_MASTER wm
LEFT JOIN NOVI_INTEL.WELL_ECONOMICS we ON we.WELL_REF = wm.WELL_REF AND we.REPORT_NAME = wm.REPORT_NAME
LEFT JOIN NOVI_INTEL.ML_SCORE ms ON ms.WELL_REF = wm.WELL_REF AND ms.REPORT_NAME = wm.REPORT_NAME AND ms.STREAM = 'oil'
WHERE wm.REPORT_NAME = 'basin_research__Midland_Basin__2025Q3'
GROUP BY 1 ORDER BY 1
```

## Join patterns

Most queries join through the `WELL` or `PLANNED_WELL` tables as the central dimension:

```
WELL → WELLBORE (geometry, formation)
WELL → OPERATOR (name, contact)
WELL → WELL_COMPLETION (proppant, fluid)
WELL → WELL_ECONOMICS_SUMMARY (NPV, IRR, EUR)
WELL → WELL_COST_SUMMARY (D&C costs)
WELL → WELL_ROCK_QUALITY (ML tier scores)
WELL → PRODUCTION_FORECAST (daily rates)
WELL → ARPS_FORECAST (presentation view: pre-joined Arps qi, Di, b)
```

## Notes

- Monetary values (NPV, PV, costs) are stored as exact decimals (`NUMBER(28,7)`) in whole currency units: query them directly, no scaling.
- IRR is stored as a decimal fraction (0.15 = 15%). Multiply by 100 for percentage.
- Formation is on `WELLBORE`, not `WELL`.
- `BASIN` and `SUBBASIN` columns are available on all entity views for filtering.
- `REPORT_NAME` identifies which basin research report the data came from.
- A report can carry more than one price deck; `PRICE_DECK_ID` is scoped to its report, and a forward-curve deck has NULL headline prices.
