Monthly KPI SQL With Joins

MediumSQL · PostgreSQL

Problem

The Nature Conservancy needs a monthly operational KPI view for regional management reporting. Write a PostgreSQL query that combines program activity and finance data without double-counting either source.

Requirements

Return every region for January through March 2024, including months with no activity or spending.
Calculate active programs, completed deliverables, completed operational hours, and actual spend by region and month.
Compare each month's spend with the prior month using LAG.
Return zero for missing monthly activity or finance values and order by region and month.

Schema

regions

Column	Type	Description
region_idPK	INT	Unique regional identifier
region_name	VARCHAR(100)	Name of the operating region

programs

Column	Type	Description
program_idPK	INT	Unique program identifier
region_id	INT	Owning region
program_name	VARCHAR(150)	Program name

program_activities

Column	Type	Description
activity_idPK	INT	Unique activity identifier
program_id	INT	Related conservation program
activity_date	DATE	Date of the activity
activity_status	VARCHAR(30)	Operational status
hours	NUMERIC(10,2)	Hours recorded for the activity

finance_transactions

Column	Type	Description
transaction_idPK	INT	Unique finance transaction identifier
program_id	INT	Program charged for the transaction
transaction_date	DATE	Transaction posting date
amount	NUMERIC(12,2)	Transaction amount
expense_category	VARCHAR(60)	Finance expense category

Tablesregionsprogramsprogram_activitiesfinance_transactions