# Novi Wells recipes

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

> Ready-to-run Snowflake SQL recipes for well, production, forecast and activity questions on Novi UDM Data (v4).

Each recipe answers one engineering question. Copy it into a Snowflake worksheet, swap in your own basin, subbasin and
direction filters, and run it.

Run this once per session first:

```sql title="Set your session"
USE DATABASE NOVI_DATA_ACCESS;
USE SCHEMA NOVI_WELLS;
```

Recipes for one wellbore read a session variable, set here to an example Permian horizontal. Use your own `WELLBORE_KEY` if you have one.

```sql title="Pick an example wellbore"
SET wellbore_key = (
    SELECT MIN(WELLBORE_KEY)
    FROM NOVI_WELLS.WELLBORE
    WHERE BASIN = 'Permian'
      AND DIRECTION = 'HORIZONTAL'
      AND MIDPOINT_LATITUDE IS NOT NULL
      AND HEELPOINT_LATITUDE IS NOT NULL
      AND BOTTOM_HOLE_LATITUDE IS NOT NULL
);
SET well_key = (SELECT WELL_KEY FROM NOVI_WELLS.WELLBORE WHERE WELLBORE_KEY = $wellbore_key);
```

## 1. Find wells from a list of API numbers

**Job:** turn a pasted list of API numbers in any format into Novi wells.
`WELL.NATIVE_ID` holds the API10, so the recipe keeps each value's first 10 digits. Replace the sample list with your own.

```sql title="Look up wells by API number"
SELECT w.WELL_KEY, w.WELL_NAME, w.NATIVE_ID, w.STATE, w.COUNTY, w.STATUS
FROM NOVI_WELLS.WELL w
WHERE w.NATIVE_ID IN (
    SELECT LEFT(REGEXP_REPLACE(value, '[^0-9]', ''), 10)
    FROM TABLE(SPLIT_TO_TABLE('42-000-00001, 4200000002, 42000000030000', ','))
)
ORDER BY w.WELL_KEY;
```

## 2. Screen wellbores with no joins

**Job:** shortlist horizontal wellbores by early productivity and completion design.
`WELL_DETAIL` already joins header, completion, spacing, and production roll-ups, one row per wellbore.

```sql title="Screen with WELL_DETAIL"
SELECT wd.WELLBORE_KEY, wd.WELL_NAME, wd.CURRENT_OPERATOR, wd.SUBBASIN, wd.FORMATION,
       wd.FIRST_PRODUCTION_DATE, wd.LATERAL_LENGTH, wd.PROPPANT_LOADING, wd.STAGE_COUNT,
       wd.CUM_OIL_12_REPORTED, wd.CUM_BOE_12_REPORTED
FROM NOVI_WELLS.WELL_DETAIL wd
WHERE wd.BASIN = 'Permian'
  AND wd.DIRECTION = 'HORIZONTAL'
  AND wd.LAST_REPORTED_MONTHS_ON_PRODUCTION IS NOT NULL
ORDER BY wd.CUM_BOE_12_REPORTED DESC NULLS LAST, wd.WELLBORE_KEY
LIMIT 100;
```

> **Note:**
> Reported production totals default to 0 for a wellbore that never produced, so `IS NOT NULL` on a cumulative does not exclude non-producers.
> `LAST_REPORTED_MONTHS_ON_PRODUCTION` is NULL exactly when there is no reported producing history. Filter on it instead.

## 3. Wells inside a drawn polygon

**Job:** select wells inside an area of interest drawn on a map.
Coordinates are WGS 84. Keep the cheap bounding-box filter first so `ST_WITHIN` only checks the candidates.

```sql title="Wells in a polygon"
SELECT sl.WELL_KEY, sl.LATITUDE, sl.LONGITUDE
FROM NOVI_WELLS.SURFACE_LOCATION sl
WHERE sl.LATITUDE BETWEEN 31.0 AND 32.5
  AND sl.LONGITUDE BETWEEN -103.0 AND -101.5
  AND ST_WITHIN(
        ST_MAKEPOINT(sl.LONGITUDE, sl.LATITUDE),
        TO_GEOGRAPHY('POLYGON((-103 31, -101.5 31, -101.5 32.5, -103 32.5, -103 31))'))
ORDER BY sl.WELL_KEY
LIMIT 200;
```

## 4. Type-well profile

**Job:** build an average-well curve for a cohort, aligned on months since first production, with oil per 1,000 ft of lateral.
Normalize each well-month, then average by month on production.

```sql title="Average type well, months 1 to 60"
WITH scope AS (
    SELECT wb.WELLBORE_KEY, wb.LATERAL_LENGTH
    FROM NOVI_WELLS.WELLBORE wb
    WHERE wb.BASIN = 'Permian'
      AND wb.SUBBASIN = 'Midland'
      AND wb.DIRECTION = 'HORIZONTAL'
),
monthly AS (
    SELECT p.WELLBORE_KEY, p.YEAR, p.MONTH,
           MAX(p.MONTHS_ON_PRODUCTION) AS mop,
           SUM(p.OIL) AS oil,
           SUM(p.GAS) AS gas
    FROM NOVI_WELLS.PRODUCTION p
    JOIN scope s ON p.WELLBORE_KEY = s.WELLBORE_KEY
    WHERE p.PERIOD_GRANULARITY = 'monthly'
    GROUP BY p.WELLBORE_KEY, p.YEAR, p.MONTH
)
SELECT m.mop AS months_on_production,
       COUNT(*) AS well_months,
       ROUND(AVG(m.oil), 1) AS avg_oil_bbl,
       ROUND(AVG(m.gas), 1) AS avg_gas_mcf,
       ROUND(AVG(COALESCE(m.oil, 0) + COALESCE(m.gas, 0) / 6), 1) AS avg_boe,
       ROUND(AVG(m.oil / NULLIF(s.LATERAL_LENGTH / 1000, 0)), 2) AS avg_oil_bbl_per_1000ft
FROM monthly m
JOIN scope s ON m.WELLBORE_KEY = s.WELLBORE_KEY
WHERE m.mop BETWEEN 1 AND 60
GROUP BY m.mop
ORDER BY m.mop;
```

`MONTHS_ON_PRODUCTION` starts at 1 and counts every calendar month after first production, shut-in months included.
`PRODUCTION` can hold more than one row for a wellbore and month, so the recipe collapses to one row per wellbore-month first.

## 5. 12-month cumulative oil and gas per wellbore

**Job:** compute first-12-month cumulatives yourself from monthly production, and keep only wellbores whose history supports it.
The `HAVING` checks matter: a unique, gap-free month index in step with the calendar, months 1 to 12 all present, and production through month 12.

```sql title="Cumulative oil and gas, months 1 to 12"
WITH scope AS (
    SELECT wb.WELLBORE_KEY
    FROM NOVI_WELLS.WELLBORE wb
    WHERE wb.BASIN = 'Permian'
      AND wb.SUBBASIN = 'Midland'
      AND wb.DIRECTION = 'HORIZONTAL'
),
monthly AS (
    SELECT p.WELLBORE_KEY, p.YEAR, p.MONTH,
           MAX(p.MONTHS_ON_PRODUCTION) AS mop,
           SUM(p.OIL) AS oil, SUM(p.GAS) AS gas, SUM(p.WATER) AS water,
           SUM(p.CONDENSATE) AS condensate, SUM(p.GAS_FLARED) AS gas_flared
    FROM NOVI_WELLS.PRODUCTION p
    JOIN scope s ON p.WELLBORE_KEY = s.WELLBORE_KEY
    WHERE p.PERIOD_GRANULARITY = 'monthly'
    GROUP BY p.WELLBORE_KEY, p.YEAR, p.MONTH
)
SELECT WELLBORE_KEY,
       SUM(CASE WHEN mop BETWEEN 1 AND 12 THEN COALESCE(oil, 0) END) AS cum12_oil_bbl,
       SUM(CASE WHEN mop BETWEEN 1 AND 12 THEN COALESCE(gas, 0) END) AS cum12_gas_mcf
FROM monthly
GROUP BY WELLBORE_KEY
HAVING COUNT(DISTINCT CASE WHEN mop BETWEEN 1 AND 12 THEN mop END) = 12
   AND MAX(mop) - MIN(mop) + 1 = COUNT(*)
   AND COUNT(DISTINCT mop) = COUNT(*)
   AND MIN(mop) >= 0
   AND MIN(mop - (YEAR * 12 + MONTH)) = MAX(mop - (YEAR * 12 + MONTH))
   AND MAX(CASE WHEN COALESCE(oil, 0) > 0 OR COALESCE(gas, 0) > 0 OR COALESCE(water, 0) > 0
                  OR COALESCE(condensate, 0) > 0 OR COALESCE(gas_flared, 0) > 0
                THEN mop END) >= 12
   AND SUM(CASE WHEN mop BETWEEN 1 AND 12 THEN oil END) > 0
ORDER BY cum12_oil_bbl DESC, WELLBORE_KEY
LIMIT 100;
```

`WELLBORE_PRODUCTION_SUMMARY` also publishes 12-month cumulatives, as reported by the source rather than computed from the monthly rows. The two are not guaranteed to agree.

## 6. Benchmark operators

**Job:** rank operators in a cohort by average 12-month cumulative oil per well.
Compare per-well averages, not totals. The window checks are the same as recipe 5.

```sql title="Operator ranking, cohorts of 25 wells or more"
WITH cohort AS (
    SELECT wb.WELLBORE_KEY, o.NAME AS operator
    FROM NOVI_WELLS.WELLBORE wb
    JOIN NOVI_WELLS.WELL w     ON wb.WELL_KEY = w.WELL_KEY
    JOIN NOVI_WELLS.OPERATOR o ON w.OPERATOR_ID = o.OPERATOR_ID
    WHERE wb.BASIN = 'Permian'
      AND wb.SUBBASIN = 'Midland'
      AND wb.DIRECTION = 'HORIZONTAL'
),
monthly AS (
    SELECT c.operator, p.WELLBORE_KEY, p.YEAR, p.MONTH,
           MAX(p.MONTHS_ON_PRODUCTION) AS mop,
           SUM(p.OIL) AS oil, SUM(p.GAS) AS gas, SUM(p.WATER) AS water,
           SUM(p.CONDENSATE) AS condensate, SUM(p.GAS_FLARED) AS gas_flared
    FROM NOVI_WELLS.PRODUCTION p
    JOIN cohort c ON p.WELLBORE_KEY = c.WELLBORE_KEY
    WHERE p.PERIOD_GRANULARITY = 'monthly'
    GROUP BY c.operator, p.WELLBORE_KEY, p.YEAR, p.MONTH
),
well_cum AS (
    SELECT operator, WELLBORE_KEY,
           SUM(CASE WHEN mop BETWEEN 1 AND 12 THEN COALESCE(oil, 0) END) AS cum12_oil_bbl
    FROM monthly
    GROUP BY operator, WELLBORE_KEY
    HAVING COUNT(DISTINCT CASE WHEN mop BETWEEN 1 AND 12 THEN mop END) = 12
       AND MAX(mop) - MIN(mop) + 1 = COUNT(*)
       AND COUNT(DISTINCT mop) = COUNT(*)
       AND MIN(mop) >= 0
       AND MIN(mop - (YEAR * 12 + MONTH)) = MAX(mop - (YEAR * 12 + MONTH))
       AND MAX(CASE WHEN COALESCE(oil, 0) > 0 OR COALESCE(gas, 0) > 0 OR COALESCE(water, 0) > 0
                      OR COALESCE(condensate, 0) > 0 OR COALESCE(gas_flared, 0) > 0
                    THEN mop END) >= 12
       AND SUM(CASE WHEN mop BETWEEN 1 AND 12 THEN oil END) > 0
)
SELECT operator,
       COUNT(*)                     AS wells,
       ROUND(AVG(cum12_oil_bbl))    AS avg_cum12_oil_bbl,
       ROUND(MEDIAN(cum12_oil_bbl)) AS median_cum12_oil_bbl
FROM well_cum
GROUP BY operator
HAVING COUNT(*) >= 25
ORDER BY avg_cum12_oil_bbl DESC, operator
LIMIT 25;
```

## 7. Forecast and decline parameters for one wellbore

**Job:** read the monthly P50 forecast and the Arps segments behind it.
Order forecast rows by `FORECAST_DAY`. Align them with actual production on `YEAR` and `MONTH`, not on `FORECAST_DAY`.

```sql title="Monthly P50 forecast"
SELECT FORECAST_DATE, FORECAST_MONTH, OIL_PER_DAY, GAS_PER_DAY, CUMULATIVE_OIL, CUMULATIVE_GAS
FROM NOVI_WELLS.PRODUCTION_FORECAST
WHERE WELLBORE_KEY = $wellbore_key
  AND SCENARIO = 'P50'
  AND PERIOD_GRANULARITY = 'monthly'
ORDER BY FORECAST_DAY;
```

```sql title="Arps segments and parameters"
SELECT seg.PRODUCTION_STREAM, seg.SEGMENT_NUMBER, seg.KIND, seg.SEGMENT_CURVE_TYPE,
       seg.SEGMENT_START_DATE, seg.SEGMENT_END_DATE,
       prm.B_FACTOR, prm.NOMINAL_DECLINE_RATE, prm.SEGMENT_START_RATE
FROM NOVI_WELLS.PRODUCTION_FORECAST_ARPS_SEGMENT seg
JOIN NOVI_WELLS.PRODUCTION_FORECAST_ARPS_SEGMENT_PARAMETER prm
  ON prm.FORECAST_SEGMENT_ID = seg.SEGMENT_UID
WHERE seg.WELLBORE_KEY = $wellbore_key
ORDER BY seg.PRODUCTION_STREAM, seg.SEGMENT_NUMBER;
```

Forecast cumulatives start at the first forecast period and exclude earlier production. Decline rates are per-day fractions unless noted.
`b = 0` is exponential, `b = 1` is harmonic, and values between are hyperbolic.

## 8. EUR by subbasin

**Job:** compare 30-year forecast EUR per well across subbasins.
EUR columns are additive across wells at one horizon, never across horizons. Wells with no forecast are NULL and drop out.

```sql title="Average 30-year EUR per well"
SELECT wb.SUBBASIN,
       COUNT(wps.EUR_BOE_30YR)      AS wells_with_forecast,
       ROUND(AVG(wps.EUR_OIL_30YR)) AS avg_eur_oil_30yr_bbl,
       ROUND(AVG(wps.EUR_GAS_30YR)) AS avg_eur_gas_30yr_mcf,
       ROUND(AVG(wps.EUR_BOE_30YR)) AS avg_eur_boe_30yr
FROM NOVI_WELLS.WELL_PRODUCTION_SUMMARY wps
JOIN NOVI_WELLS.WELLBORE wb ON wb.WELLBORE_KEY = wps.WELLBORE_KEY
WHERE wb.BASIN = 'Permian'
  AND wb.DIRECTION = 'HORIZONTAL'
GROUP BY wb.SUBBASIN
ORDER BY avg_eur_boe_30yr DESC NULLS LAST;
```

## 9. Lifecycle timeline for one well

**Job:** list permit, spud, total depth, completion, and first production events in order.
Keep this wellbore's events plus well-level events, which have no `WELLBORE_KEY`. Per-event details live in the `PAYLOAD` map.

```sql title="Well events in date order"
SELECT EVENT_TYPE, EVENT_DT, EFFECTIVE_DT, WELLBORE_KEY,
       PAYLOAD['operator_reported']::string AS operator_reported
FROM NOVI_WELLS.WELL_EVENT
WHERE WELL_KEY = $well_key
  AND (WELLBORE_KEY = $wellbore_key OR WELLBORE_KEY IS NULL)
ORDER BY EVENT_DT;
```

`EVENT_DT` is when something happened. `EFFECTIVE_DT` is when it counts for status.

## 10. Gunbarrel: offsets around a wellbore

**Job:** find neighbors within about 1,000 m of a target lateral's midpoint, with lateral spacing and vertical offset, for a cross-section plot.
`lateral_spacing_ft` is the distance from each neighbor's midpoint to the target's heel-to-bottom-hole line, on either side.

```sql title="Gunbarrel neighbors"
WITH target AS (
    SELECT wb.WELLBORE_KEY, wb.TVD_TD,
           wb.MIDPOINT_LATITUDE  AS lat,
           wb.MIDPOINT_LONGITUDE AS lon,
           ST_MAKEPOINT(wb.MIDPOINT_LONGITUDE, wb.MIDPOINT_LATITUDE) AS mid,
           TO_GEOGRAPHY('LINESTRING('
               || wb.HEELPOINT_LONGITUDE || ' ' || wb.HEELPOINT_LATITUDE || ', '
               || wb.BOTTOM_HOLE_LONGITUDE || ' ' || wb.BOTTOM_HOLE_LATITUDE || ')'
           ) AS lateral_line
    FROM NOVI_WELLS.WELLBORE wb
    WHERE wb.WELLBORE_KEY = $wellbore_key
)
SELECT n.WELLBORE_KEY AS neighbor,
       n.FORMATION,
       ROUND(n.TVD_TD) AS tvd_ft,
       ROUND(ST_DISTANCE(ST_MAKEPOINT(n.MIDPOINT_LONGITUDE, n.MIDPOINT_LATITUDE),
           t.lateral_line) * 3.28084) AS lateral_spacing_ft,
       ROUND(n.TVD_TD - t.TVD_TD) AS tvd_offset_ft
FROM target t
JOIN NOVI_WELLS.WELLBORE n
  ON n.WELLBORE_KEY <> t.WELLBORE_KEY
 AND n.MIDPOINT_LATITUDE  BETWEEN t.lat - 0.02 AND t.lat + 0.02
 AND n.MIDPOINT_LONGITUDE BETWEEN t.lon - 0.02 AND t.lon + 0.02
 AND ST_DWITHIN(ST_MAKEPOINT(n.MIDPOINT_LONGITUDE, n.MIDPOINT_LATITUDE), t.mid, 1000)
ORDER BY lateral_spacing_ft, neighbor
LIMIT 30;
```

`WELL_SPACING` has precomputed distances, neighbor counts, and parent and child flags for each wellbore, but not the neighbor list.

## 11. Rig counts by basin and week

**Job:** track drilling activity.
Counts add up across geography but not across time.

```sql title="Weekly rig counts"
SELECT r.BASIN, r.YEAR, r.WEEK,
       SUM(r.OIL_RIGS)   AS oil_rigs,
       SUM(r.GAS_RIGS)   AS gas_rigs,
       SUM(r.TOTAL_RIGS) AS total_rigs
FROM NOVI_WELLS.RIG_ACTIVITY_WEEKLY r
GROUP BY r.BASIN, r.YEAR, r.WEEK
ORDER BY r.YEAR DESC, r.WEEK DESC, total_rigs DESC, r.BASIN
LIMIT 100;
```

## 12. Gas producers by state

**Job:** count gas-producing wellbores using the declared function and stream.
For gas producers, read `GAS`, `OIL_AND_GAS`, and `CBM`, not `GAS` alone. States that file no well-type code report only `PRODUCER` or NULL.

```sql title="Gas producers by state"
SELECT w.STATE,
       wb.PRIMARY_STREAM,
       COUNT(*) AS wellbores
FROM NOVI_WELLS.WELLBORE wb
JOIN NOVI_WELLS.WELL w ON w.WELL_KEY = wb.WELL_KEY
WHERE wb.WELLBORE_TYPE_NORMALIZED = 'PRODUCER'
  AND wb.PRIMARY_STREAM IN ('GAS', 'OIL_AND_GAS', 'CBM')
GROUP BY w.STATE, wb.PRIMARY_STREAM
ORDER BY wellbores DESC;
```

## More recipes

The notebook also covers well status mixes, slant and survey checks, well tests and gas gravity, permit expiry, lease numbers, legal land lookups, completion ratios and soak time.
Reader accounts get it as notebooks in a shared workspace or as a link to the online cookbook; the welcome email says which.

The full cookbook holds 18 recipes for this release (26.4.3):

- Cookbook recipe 1: Well lookup and filters
- Cookbook recipe 2: Spatial map
- Cookbook recipe 3: Production timeseries
- Cookbook recipe 4: Cumulative and IP
- Cookbook recipe 5: Forecast and decline
- Cookbook recipe 6: Well card detail
- Cookbook recipe 7: Benchmarking rankings
- Cookbook recipe 8: Activity and rigs
- Cookbook recipe 9: Well status
- Cookbook recipe 10: Bulk export
- Cookbook recipe 11: Gunbarrel
- Cookbook recipe 12: Wellbore slant and surveys
- Cookbook recipe 13: Well tests permits and land
- Cookbook recipe 14: Lease numbers permit expiry and primary stream
- Cookbook recipe 15: Presentation views
- Cookbook recipe 16: Completion ratios soak and EUR
- Cookbook recipe 17: Wellbore type and primary stream
- Cookbook recipe 18: Bulk downloads
