Monthly KPI SQL With Joins | Dataford Interview Questions

Your question is Monthly KPI SQL With Joins. Start with the requirements and the four tables on the right.

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

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