SQL Join and Monthly Summary

MediumSQL · PostgreSQL

Problem

Write a SQL query to join multiple tables and calculate a monthly summary of analyst-relevant metrics.

Use the provided analyst, activity, and target tables. Include activity from January through March 2025 and preserve months that have activity but no matching target.

Output

One row per analyst and activity month
Columns: analyst_name, month_start, activity_count, completed_count, total_metric_value, average_metric_value, target_value, attainment_percentage
Include all activity records in the period, including records with null metric values
Order by analyst_id, then month_start

Schema

analysts

Column	Type	Description
analyst_idPK	INT	Unique analyst identifier
analyst_name	VARCHAR(100)	Analyst display name
region	VARCHAR(50)	Analyst reporting region
active_flag	BOOLEAN	Whether the analyst is currently active

analyst_activity

Column	Type	Description
activity_idPK	INT	Unique activity identifier
analyst_id	INT	References analysts.analyst_id
activity_date	DATE	Date when the activity occurred
activity_type	VARCHAR(50)	Type of analyst activity
status	VARCHAR(30)	Current activity status
metric_value	DECIMAL(12,2)	Numeric value associated with the activity

monthly_targets

Column	Type	Description
target_idPK	INT	Unique monthly target identifier
analyst_id	INT	References analysts.analyst_id
target_month	DATE	First day of the target month
target_value	DECIMAL(12,2)	Target metric value for the month

Tablesanalystsanalyst_activitymonthly_targets