Advanced SQL With CTEs | Dataford Interview Questions

Your question is Advanced SQL With CTEs. Start with the requirements and the three 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

Business Context

Microsoft Teams administrators need a monthly view of license utilization and employee engagement across departments.

Task

Write a PostgreSQL query using multiple CTEs to produce adoption metrics for January and February 2025. Include departments with active licenses even when they have no Teams activity.

Requirements

Count active licensed employees and distinct active users by department and month.
Calculate license utilization as active users divided by active licensed employees, expressed as a percentage.
Calculate month-over-month percentage change in active users using LAG.
Rank departments within each month by utilization, using active users as a tie-breaker.
Treat activity from inactive licenses as invalid and avoid counting duplicate activity events.

Schema

employees

Column	Type	Description
employee_idPK	INT	Unique employee identifier
employee_name	VARCHAR(100)	Employee display name
department	VARCHAR(50)	Employee department

teams_licenses

Column	Type	Description
license_idPK	INT	Unique license record identifier
employee_id	INT	Employee assigned the license
license_type	VARCHAR(50)	Microsoft Teams license tier
license_status	VARCHAR(20)	License lifecycle status

teams_activity

Column	Type	Description
activity_idPK	INT	Unique activity event identifier
employee_id	INT	Employee generating the activity
activity_date	DATE	Date of the Teams activity
activity_type	VARCHAR(40)	Type of Teams activity