SQL Running Total and Rank | Dataford Interview Questions

Enverus Prism analysts need a basin-level view of active well performance. Write a PostgreSQL query that aggregates 2024 oil production, ranks wells within each basin, and calculates a running basin total.

Requirements

Include only wells whose status is active and whose basin is not null.
Aggregate oil production from production_monthly for calendar year 2024, retaining active wells with no matching production.
Rank wells within each basin by total oil production in descending order. Wells with equal totals must receive the same rank.
Calculate a cumulative oil total within each basin, ordered by total production descending and then well_id.

Schema

wells

Column	Type	Description
well_idPK	INTEGER	Unique well identifier
well_name	VARCHAR(100)	Well name
basin	VARCHAR(50)	Production basin
operator	VARCHAR(100)	Operating company
status	VARCHAR(20)	Current well status

production_monthly

Column	Type	Description
production_idPK	INTEGER	Unique production record identifier
well_id	INTEGER	Referenced well identifier
production_month	DATE	Month represented by the production record
oil_bbl	INTEGER	Monthly oil production in barrels

Your question is SQL Running Total and Rank. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem